MySQL主从复制异步原理以及搭建

MySQL主从复制的原理：

　　1、首先，MySQL主库在事务提交时会把数据变更作为时间events记录在二进制日志文件binlog中；MySQL主库上的sync_binlog参数控制Binlog日志以什么样的方式刷新到磁盘上。

　　2、主库推送二进制日志文件Binlog中的事件到从库的中继日志Relay Log中，之后从库根据中继日志Relay Log重做数据变更操作，通过逻辑复制以此来达到主库和从库的数据一致。

MySQL主从复制的关键点：

　　MySQL通过3个线程来完成主从库间的数据复制，其中Binlog Dump线程跑在主库上，I/O线程和SQL线程跑在从库上。当在从库上启动复制时，首先创建I/O线程来连接主库，随后主库创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用到从库上来。

示意图：

主库端查看线程：

1mysql> show processlist\G
2*************************** 1. row ***************************
3     Id: 55
4   User: chaofeng
5   Host: ip-172-31-26-133.ec2.internal:35750
6     db: NULL
7Command: Binlog Dump
8   Time: 1433
9  State: Master has sent all binlog to slave; waiting for more updates
10   Info: NULL
11*************************** 2. row ***************************
12     Id: 56
13   User: root
14   Host: localhost
15     db: NULL
16Command: Query
17   Time: 0
18  State: starting
19   Info: show processlist
202 rows in set (0.00 sec)

从主库端的Binlog Dump线程的状态我们可以看到，MySQL的复制时主库主动推送日志到从库上去的，属于“推”日志的方式来做同步。

 MySQL主从复制中的各类文件

从MySQL的复制流程可以看到复制过程中涉及了两类非常重要的日志文件：二进制日志文件（Binlog）和中继日志文件（Relay Log）

 二进制日志文件会把MySQL中的所有数据修改操作以二进制的形式记录到日志文件中，其中包括：create、drop、insert、update、delete等。但是二进制日志文件并不会记录select操作，因为select并不对数据做修改。

我们可以来查看一下Binlog的格式，Binlog支持三种格式：Row、Statement、Mixed

1mysql> show variables like 'binlog_format';
2+---------------+-------+
3| Variable_name | Value |
4+---------------+-------+
5| binlog_format | ROW   |
6+---------------+-------+
71 row in set (0.00 sec)

中继日志Relay log与二进制日志文件Binlog在文件格式、内容上等都是一样的。唯一的区别就是：从库上的SQL线程在执行完Relay log中的事件之后，SQL线程还会自动删除当前中继日志文件。这样做的原因是为了避免中继日志Relay Log过多从而占用磁盘空间。

为了保证从库crash重启之后，从库的I/O线程和SQL线程仍然能够知道从哪里开始继续复制，从库默认还会创建两个日志文件master.info和relay-log.info用来保存复制的进度。master.info用来保存从库的I/O线程当前读取的二进制日志Binlog的进度，relay-log.info用来保存SQL线程应用中继日志Relay log的进度

下面青绿色表示master.info文件的信息。紫色表示relay-log.info文件的信息。

1MySQL [(none)]> show slave status\G*************************** 1. row ***************************
2               Slave_IO_State: Waiting for master to send event
3                  Master_Host: 172.31.22.29　　　　　　　　　　　　　　　　　　　　　　　　　 #主库IP
4                  Master_User: chaofeng　　　　　　　　　　　　　　　　　　　　　　　　　　　　#主库上用户主从复制的账号
5                  Master_Port: 3306　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#主库的mysql的端口
6                Connect_Retry: 60
7              Master_Log_File: mysql-bin.000001　　　　　　　　　　　　　　　　　　　　　　　#从库I/O线程当前读取的主库Binlog文件名
8          Read_Master_Log_Pos: 915　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 #从库I/O线程读取主库上Binlog的位置
9               Relay_Log_File: ip-172-31-26-133-relay-bin.000002　　　　　　　　　　　　 #SQL线程正在应用的Relay Log
10                Relay_Log_Pos: 479　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#SQL线程郑州应用的Relay Log的位置
11        Relay_Master_Log_File: mysql-bin.000001　　　　　　　　　　　　　　　　　　　　　　 #SQL线程正在应用的Relay Log对于的Binlog
12             Slave_IO_Running: Yes
13            Slave_SQL_Running: Yes
14              Replicate_Do_DB: 
15                 Skip_Counter: 0
16          Exec_Master_Log_Pos: 915　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 #SQL线程正在应用Relay Log的位置对应主库Binlog的位置　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
17              Relay_Log_Space: 697
18             Master_Server_Id: 1
19                  Master_UUID: ce15f67e-0ccc-11e9-9e0a-0af74ce261dc
20             Master_Info_File: /data/data_mysql/master.info
21                    SQL_Delay: 0
22          SQL_Remaining_Delay: NULL
23      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
24           Master_Retry_Count: 86400
25                  Master_Bind: 1 row in set (0.00 sec)

搭建步骤：

主从同步的MySQL版本最好一致，我们这里使用两台机器来做演示，每台机器均部署一个MySQL实例

master：172.31.22.29

slave：172.31.26.133

1、在主库上，设置一个复制使用的账户，并授予" REPLICATION SLAVE "权限。

1mysql> GRANT REPLICATION SLAVE ON *.* TO 'chaofeng'@'172.31.26.133' IDENTIFIED BY '123456789';
2Query OK, 0 rows affected, 0 warning (0.00 sec)

这个操作表示创建一个复制用户chaofeng，可以从172.31.26.133这个主机上进行连接，密码为123456789

2、在主库这台机器上，修改my.cnf文件，开启binlog日志功能，并设置server_id的值。修改完并重启服务

1[mysqld]
2
3#secure_file_priv = /root
4sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
5basedir = /usr/local/mysql
6datadir = /data/data_mysql
7log-bin=/usr/local/mysql/logs/mysql-bin.log　　　　　　　　　　　　　　　　　　　　#这里我把日志单独放置一个目录下。
8server_id=1
9log-error=/usr/local/mysql/logs/mysql.log
10port = 3306

3、在主库上，设置读锁定有效，这个操作是为了确保没有数据库操作，以便获取一致性的快照。

1mysql> flush tables with read lock;
2Query OK, 0 rows affected (0.00 sec)

4、查看主库上此时的二进制日志名以及偏移位。

1mysql> show master status;
2+------------------+----------+--------------+------------------+-------------------+
3| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4+------------------+----------+--------------+------------------+-------------------+
5| mysql-bin.000001 |      756 |              |                  |                   |
6+------------------+----------+--------------+------------------+-------------------+
71 row in set (0.01 sec)

5、接下来我们就开始备份主库的数据了，这里我们直接通过将mysql数据目录进行打包操作发送给从库。因为我们这里的主库服务可以停止，因此我们直接复制文件是最快的操作，但是在生产环境中你需要考虑很多。

1[root@:vg_adn_tidbCkhsTest:172.31.22.29 /data/data_mysql]#tar -zcf mysql.tar.gz ./*
2[root@:vg_adn_tidbCkhsTest:23.22.172.65:172.31.22.29 /data/data_mysql]#ls
3auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ib_logfile2  ibtmp1  mysql  mysql.pid  mysql.tar.gz  performance_schema  sys

6、主库完成之后我们就可以恢复主库的写操作了

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

7、将mysql.tar.gz发送给从库。并解压到从库的数据目录下

1[root@:vg_adn_tidbCkhsTest:172.31.26.133 /data]#tar -zxf mysql.tar.gz -C /data/data_mysql
2[root@:vg_adn_tidbCkhsTest:172.31.26.133 /data]#ls data_mysql
3auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ib_logfile2  ibtmp1  mysql  mysql.pid  performance_schema  sys

9、接下来我们开始配置从库，修改从库的配置文件/etc/my.cnf，其中有个server_id参数，注意这个server_id的值必须与主库的server_id不一样，如果是多个从库，每个服务器的server_id值都不能相同。

1[mysqld]
2server_id=2

从库不必开启binlog功能。

10、在mysql5.7以上的版本中，除了保证server_id不一样外，还有保证server_uuid不一样才行。server_uuid的值在MySQL数据目录下的auto.cnf文件下

查看主库的server_uuid

[root@:vg_adn_tidbCkhsTest:172.31.22.29 /data/data_mysql]#cat auto.cnf
[auto]
server-uuid=ce15f67e-0ccc-11e9-9e0a-0af74ce261dc

查看从库的server_uuid

[root@:vg_adn_tidbCkhsTest:172.31.26.133 /data/data_mysql]#cat auto.cnf
[auto]
server-uuid=ce15f67e-0ccc-11e9-9e0a-0af74ce261dc

 因为我们是直接将主库的数据目录复制过来的，因此两个文件下的auto.cnf的值是一模一样的，我们修改一下从库的这个值，只修改一位即可，只要能保证这两个值不一样就行。

修改从库的server_uuid值为

server-uuid=ce15f67e-0ccc-11e9-9e0a-0af74ce261dd

11、接下来重启从服务器，对从数据库服务器进行设置，这里主要设置一下几项：

1MySQL [(none)]> CHANGE MASTER TO
2    　　　　  -> MASTER_HOST='172.31.22.29',
3      　　　　-> MASTER_PORT=3306,　　　　　　　 -> MASTER_USER='chaofeng',
4      　　　　-> MASTER_PASSWORD='123456789',
5      　　　　-> MASTER_LOG_FILE='mysql-bin.000001',
6      　　　　-> MASTER_LOG_POS=756;
7Query OK, 0 rows affected, 2 warnings (0.02 sec)

12、从库上启动线程

1MySQL [(none)]> start slave;
2Query OK, 0 rows affected (0.00 sec)

13、这时在slave上执行show processlist命令将显示类似如下的信息：

1MySQL [(none)]> show processlist\G
2*************************** 1. row ***************************
3     Id: 4
4   User: root
5   Host: localhost
6     db: NULL
7Command: Query
8   Time: 0
9  State: starting
10   Info: show processlist
11*************************** 2. row ***************************
12     Id: 6
13   User: system user
14   Host: 
15     db: NULL
16Command: Connect
17   Time: 5
18  State: Waiting for master to send event
19   Info: NULL
20*************************** 3. row ***************************
21     Id: 7
22   User: system user
23   Host: 
24     db: NULL
25Command: Connect
26   Time: 5
27  State: Slave has read all relay log; waiting for more updates
28   Info: NULL
293 rows in set (0.00 sec)

或者你也可以这样子查看：

1MySQL [(none)]> show slave status\G
2*************************** 1. row ***************************
3               Slave_IO_State: Waiting for master to send event
4                  Master_Host: 172.31.22.29
5                  Master_User: chaofeng
6                  Master_Port: 3306
7                Connect_Retry: 60
8              Master_Log_File: mysql-bin.000001
9          Read_Master_Log_Pos: 756
10               Relay_Log_File: ip-172-31-26-133-relay-bin.000002
11                Relay_Log_Pos: 320
12        Relay_Master_Log_File: mysql-bin.000001
13             Slave_IO_Running: Yes
14            Slave_SQL_Running: Yes
15              Replicate_Do_DB: 
16          Replicate_Ignore_DB: 
17           Replicate_Do_Table: 
18       Replicate_Ignore_Table: 
19      Replicate_Wild_Do_Table: 
20  Replicate_Wild_Ignore_Table: 
21                   Last_Errno: 0
22                   Last_Error: 
23                 Skip_Counter: 0
24          Exec_Master_Log_Pos: 756
25              Relay_Log_Space: 538
26              Until_Condition: None
27               Until_Log_File: 
28                Until_Log_Pos: 0
29           Master_SSL_Allowed: No
30           Master_SSL_CA_File: 
31           Master_SSL_CA_Path: 
32              Master_SSL_Cert: 
33            Master_SSL_Cipher: 
34               Master_SSL_Key: 
35        Seconds_Behind_Master: 0
36Master_SSL_Verify_Server_Cert: No
37                Last_IO_Errno: 0
38                Last_IO_Error: 
39               Last_SQL_Errno: 0
40               Last_SQL_Error: 
41  Replicate_Ignore_Server_Ids: 
42             Master_Server_Id: 1
43                  Master_UUID: ce15f67e-0ccc-11e9-9e0a-0af74ce261dc
44             Master_Info_File: /data/data_mysql/master.info
45                    SQL_Delay: 0
46          SQL_Remaining_Delay: NULL
47      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
48           Master_Retry_Count: 86400
49                  Master_Bind: 
50      Last_IO_Error_Timestamp: 
51     Last_SQL_Error_Timestamp: 
52               Master_SSL_Crl: 
53           Master_SSL_Crlpath: 
54           Retrieved_Gtid_Set: 
55            Executed_Gtid_Set: 
56                Auto_Position: 0
57         Replicate_Rewrite_DB: 
58                 Channel_Name: 
59           Master_TLS_Version: 
601 row in set (0.00 sec)

以上都是查看主从复制是否正常的方法，只要能看到上面标记青绿色的信息，就说明主从复制搭建成功了

主从复制异步存在的问题：

在MySQL5.5之前的版本中，MySQL的复制是异步复制，主库和从库的数据之间存在一定的延迟，比如网络故障等各种原因，这样子容易存在隐患就是：当在主库写入一个事务成功后并提交了，但是由于从库延迟没有及时得到主库推送的Binlog日志时，主库突然宕机了，那么此时从库就可能损失这个事务，从而造成主从不一致的状况。

因此我们MySQL5.5版本之后引入了半同步复制的概念，看下节文章