MySQL数据库主从同步

一. 环境搭建

主master：192.168.76.133
从slave： 192.168.76.134

建立测试用数据库及数据表

mysql> create table employees(
-> employee_id INT NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL, 
-> e_mail varchar(50),
-> PRIMARY KEY(employee_id));

mysql> desc employees;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| employee_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | char(20)    | NO   |     | NULL    |                |
| e_mail      | varchar(50) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

插入数据

mysql> insert into employees values
    -> (1,'TOM','tom@example.com'),
    -> (2,'Jerry','jerry@example.com');

mysql> select * from employees;
+-------------+-------+-------------------+
| employee_id | name  | e_mail            |
+-------------+-------+-------------------+
|           1 | TOM   | tom@example.com   |
|           2 | Jerry | jerry@example.com |
+-------------+-------+-------------------+

二. 主库服务器配置

1. 设置服务器编号，开启二进制日志

编辑/etc/my.cnf文件

[mysqld]
server-id       = 1             #设置服务器编号
log-bin         = /usr/local/mysql/data/mysql-bin/binlog
#启用二进制日志 设定路径名 此目录mysql有权写入. 
#该路径指定了存放二进制日志的目录为mysql-bin  日志文件名前缀为binlog

-------可选------------------------------
#binlog-do-db = 数据库名   指定要同步的数据库
#binlog-ignore-db=数据库名 指定不同的数据库

重启mysqld服务使配置生效

service mysqld restart

查看是否生效

cd /usr/local/mysql/data/mysql-bin/
[root@wzz mysql-bin]# ll
total 8
-rw-rw----. 1 mysql mysql 106 Feb 16 12:23 binlog.000001
-rw-rw----. 1 mysql mysql  46 Feb 16 12:23 binlog.index

2. 创建从库复制账号

mysql> grant replication slave on *.* to 'rep'@'192.168.76.134' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3. 只读锁定拒绝写操作

mysql> flush tables with read lock;

4.获取当前日志文件和记录点

mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 |      334 |              |                  |
+---------------+----------+--------------+------------------+

5. 新开XShell窗口，dump数据库

dump数据

mysqldump -uroot -poldboy -A -B --events --master-data=2 >/opt/rep.sql

备份拷贝至从库服务器

scp /opt/rep.sql root@192.168.76.134:/home/wzz/ #拷贝至从库

主库解锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

备份灌到从库

mysql -uroot -poldboy < /home/wzz/rep.sql # 在从库执行此操作

三. 从库服务器配置

1. 设置从库ID

主从库ID必须唯一 修改my.cnf 后 重启mysql生效

server-id       = 2
#log-bin=mysql-bin  #从库不级联，可以不开启log-bin

重启mysql使配置生效

2. CHANGE MASTER TO设置连接信息

通过CHANGE MASTER TO 指定从库连接主库时所必须的信息

mysql> CHANGE MASTER TO  
MASTER_HOST='192.168.76.133', 
MASTER_PORT=3306,               # 注意数字不能有引号
MASTER_USER='rep', 
MASTER_PASSWORD='slave', 
MASTER_LOG_FILE='binlog.000001', 
MASTER_LOG_POS=334;             # 注意数字不能有引号

这些信息会存放在从库data目录下的master.info里。（我的data目录位于/usr/local/mysql)

[root@wzz ~]# cat /usr/local/mysql/data/master.info 
18
binlog.000001
334
192.168.76.133
rep
slave
3306
60
0

3. 从库开始同步

开启从库同步

mysql> start slave

检查从库状态

mysql> show slave status\G
Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.76.133
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 334
               Relay_Log_File: nagios-client03-relay-bin.000002
                Relay_Log_Pos: 249
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes              #主要看这行
            Slave_SQL_Running: Yes              #主要看这行
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 334
              Relay_Log_Space: 415
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1

四. 测试

主库的增删改操作会自动同步到从库上
relay-log.info
master.info
show processlist\G
show master status\G
show slave status\G