mysql 主从复制

MySQL 5.7 基于GTID及多线程主从复制

Posted by Sunday on 2018-04-20

MySQL主从同步原理

MySQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的，通过设置在Master MySQL上的binlog(使其处于打开状态)，Slave MySQL上通过一个I/O线程从Master MySQL上读取binlog，然后传输到Slave MySQL的中继日志中，然后Slave MySQL的SQL线程从中继日志中读取中继日志，然后应用到Slave MySQL的数据库中。这样实现了主从数据同步功能。

MySQL中主从复制的优点

  • 横向扩展解决方案
    在多个从库之间扩展负载以提高性能。在这种环境中，所有写入和更新在主库上进行。但是，读取可能发生在一个或多个从库上。该模型可以提高写入的性能（由于主库专用于更新），同时在多个从库上读取，可以大大提高读取速度。

  • 数据安全性
    由于主库数据被复制到从库，从库可以暂停复制过程，可以在从库上运行备份服务，而不会破坏对应的主库数据。

  • 分析
    可以在主库上创建实时数据，而信息分析可以在从库上进行，而不会影响主服务器的性能。

Gtid概念

从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量进行增量同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。借助GTID，在发生主备切换的情况下，MySQL的其它从库可以自动在新主库上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

什么是Gitd

GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 GTID 实际上 是由
UUID+TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
下面是一个GTID的具体形式：3E11FA47-71CA-11E1-9E33-C80AA9429562:23，冒号分割前边为uuid，后边为TID。

GTID 集合可以包含来自多个 MySQL 实例的事务，它们之间用逗号分隔。

如果来自同一MySQL实例的事务序号有多个范围区间，各组范围之间用冒号分隔。例如： e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18,e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27 可以使用show master status实时查看当前事务执行数

Gtid的作用

Gtid采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。

Gtid的工作原理

①当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
②binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
③sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
④如果有记录，说明该GTID的事务已经执行，slave会忽略。
⑤如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，
在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
⑥在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

操作环境

系统：CentOS 7
数据库：Percona MySQL 5.7
主库：192.168.11.31
从库：192.168.11.32

主库配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[mysqld]
datadir=/data/mysql/3306
socket=/tmp/mysql.sock
symbolic-links=0

server_id=31                              #服务器ID
log-bin=master-bin                        #二进制日志文件名
binlog_format = row                       #强烈建议，其他格式可能造成数据不一致
log-slave-updates = 1                     #是否记录从服务器同步数据动作
gtid-mode = on                            #启用gitd功能
enforce-gtid-consistency = 1              #开启强制GTID一致性
master-info-repository = TABLE            #记录IO线程读取已经读取到的master binlog位置，用于slave宕机后IO线程根据文件中的POS点重新拉取binlog日志  
relay-log-info-repository = TABLE         #记录SQL线程读取Master binlog的位置，用于Slave 宕机后根据文件中记录的pos点恢复Sql线程
sync-master-info = 1                      #启用确保无信息丢失；任何一个事务提交后, 将二进制日志的文件名及事件位置记录到文件中
slave-parallel-workers = 2                #设定从服务器的复制线程数；0表示关闭多线程复制功能
binlog-checksum = CRC32                   #设置binlog校验算法（循环冗余校验码）
master-verify-checksum = 1                #设置主服务器是否校验
slave-sql-verify-checksum = 1             #设置从服务器是否校验
binlog-rows-query-log_events = 1          #用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度
sync_binlog = 1                           #保证master crash safe，该参数必须设置为1
innodb_flush_log_at_trx_commit = 1        #保证master crash safe，该参数必须设置为1

从库配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[mysqld]
server_id = 32
log-bin=mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
slave-parallel-workers = 4
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log_events = 1
#sync_binlog = 1
#innodb_flush_log_at_trx_commit = 1
log-slave-updates = 0 # crash safe slave 5.6版本需要开启
relay_log_recovery = 1  # crash safe slave
read_only=on        #设置一般用户为只读模式
super_read_only=on       #设置super（root）用户为只读模式
#tx_read_only=on     #设置事务为只读模式

主库权限设置

1
2
mysql > grant replication slave on *.* to slave@'192.168.11.32' identified by 'slave123';
mysql > flush privileges;

自动同步连接主库(方法一)

适用于master也是新建不久的情况。
1、如果你的master所有的binlog还在。可以安装slave，slave直接change master to到master端。
2、原理是直接获取master所有的GTID并执行。
3、优点：简单方便。
4、缺点：如果binlog太多，数据完全同步需要时间较长，并且master一开始就启用了GTUD。

1
2
change master to master_host='192.168.11.31',master_user='slave',master_password='slave123',master_port=3306,master_auto_position=1
#master_auto_position=1 从库自动找同步点

备份导入连接主库(方法二)

1、Xtrabackup_binlog_info文件中，包含global.gtid_purged=’XXXXXX:XXXX’的信息。
2、然后到slave去手工的 SET @@GLOBAL.GTID_PURGED=’XXXXXX:XXXX’。
3、恢复备份，开启change master to 命令。

备份导入连接主库(方法三)

适用于拥有较大数据的情况。（推荐）
1、通过master或者其他slave的备份搭建新的slave。
2、原理：获取master的数据和这些数据对应的GTID范围，然后通过slave设置master_auto_position=1,自动同步，跳过备份包含的gtid。
3、缺点：相对来说有点复杂。

将主库设为只读模式

注：生产环境会影响不能写入数据

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

mysql> set global read_only=on;
Query OK, 0 rows affected (0.00 sec)

主库使用mysqldump导出

可以同时导出多个数据库，如music、record

1
2
mysqldump --databases <数据库名>  --single-transaction --order-by-primary -r <备份文件名> --routines -h<服务器地址>  -P<端口号> -u<用户名> -p<密码>
mysqldump --default-character-set=utf8mb4 --single-transaction --triggers --routines --events --hex-blob --databases muisc record > music_record.sql

记录GTID_PURGED

1
2
grep -r "GLOBAL.GTID_PURGED" music_record.sql
SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

[!NOTE]
mysql服务器内置的库包括mysql库和test库不需要导出。

将主库设为可读写模式

数据库导出完成后将主库重新设为可读写模式。

1
2
mysql> set global read_only=off;
mysql> unlock tables;

从库数据导入

1
2
3
4
5
6
##mysql> create database `music`;
##mysql -u root -p muisc < /root/music.sql
mysql -u root -p < /root/music_record.sql 
mysql> reset slave all;
mysql> reset master;
mysql> SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

从库连接主库

1
change master to master_host='192.168.11.31',master_user='slave',master_password='slave123',master_port=3306,master_auto_position=1;

从库启动复制线程

1
mysql> start slave;

从库查看复制状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.31
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 149375983
               Relay_Log_File: db2-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000002
             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: 149375983
              Relay_Log_Space: 526
              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: 31
                  Master_UUID: 834449ff-4487-11e8-8b27-000c294b06ca
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

检查主从复制通信状态

Slave_IO_State #从站的当前状态
Slave_IO_Running： Yes #读取主程序二进制日志的I/O线程是否正在运行
Slave_SQL_Running： Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样
Seconds_Behind_Master #是否为0，0就是已经同步了

如果再次查询状态仍然 发现Slave_IO_Running 或者Slave_SQL_Running 不同时为YES,尝试执行

1
2
3
mysql> stop slave;
mysql> reset slave;
mysql> start slave;

主库查看状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> show master status;
+-------------------+-----------+--------------+------------------+--------------------------------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+-------------------+-----------+--------------+------------------+--------------------------------------------+
| master-bin.000002 | 149375983 |              |                  | 834449ff-4487-11e8-8b27-000c294b06ca:1-254 |
+-------------------+-----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        32 |      | 3306 |        31 | 68303133-4489-11e8-84e9-000c293eaee6 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%gtid%';
+----------------------------------+--------------------------------------------+
| Variable_name                    | Value                                      |
+----------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                         |
| enforce_gtid_consistency         | ON                                         |
| gtid_executed                    | 834449ff-4487-11e8-8b27-000c294b06ca:1-255 |
| gtid_executed_compression_period | 1000                                       |
| gtid_mode                        | ON                                         |
| gtid_owned                       |                                            |
| gtid_purged                      |                                            |
| session_track_gtids              | OFF                                        |
+----------------------------------+--------------------------------------------+
8 rows in set (0.00 sec)

其他命令

1
2
3
4
5
mysql> show binlog events;
mysql> show binlog events in 'master-bin.000001';
mysql> show master logs;
mysql> show processlist
mysql> show full processlist;

GTID与crash safe slave

查看错误
mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1007\G;

https://docs.azure.cn/zh-cn/mysql/mysql-database-data-replication
GTID原理和一些问题解答
MySQL 5.7 Replication 相关新功能说明

CATALOG
    FEATURED TAGS
    mysql 主从复制
    FRIENDS