MySQL主从复制——MySQL-5.6基于GTID及多线程的复制

2022-07-31 12:48:59

一、Mysql 5.6 新特性

....

复制功能的改进

⒈支持多线程复制，（slave-parallel-workers=0 0: 表示禁用多线程功能；）事实上是针对每个database开启相应的独立线程。即每个库有一个单独的(sql thread)，如果线上业务中，只有一个database或者绝大多数压力集中在个别database的话，多线程并发复制特性就没有意义了。

⒉支持启用GTID，对运维人员来说应该是一件令人高兴的事情，在配置主从复制，传统的方式里，你需要找到binlog和POS点，然后change master to指向，而不是很有经验的运维，往往会将其找错，造成主从同步复制报错，在mysql5.6里，无须再知道binlog和POS点，需要知道master的IP、端口，账号密码即可，因为同步复制是自动的，mysql通过内部机制GTID自动找点同步。

二、GTID详解

MySQL 5.6 的新特性之一，是加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。

⒈ 什么是GTID？

官方文档：http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html

GTID的全称为 Global Transaction Identifier ，可以翻译为全局事务标示符（或全局事务），GTID在原始master上的事务提交时被创建。GTID需要在全局的主-备拓扑结构中保持唯一性。

MySQL 5.6 中，每一个 GTID 代表一个数据库事务，GTID由两部分组成：

GTID = source_id:transaction_id

source_id 用于标示源服务器，用server_uuid来表示，这个值在第一次启动时生成，并写入到配置文件data/auto.cnf中

transaction_id 则是根据在源服务器上第几个提交的事务来确定。transaction_id 是一个从 1 开始的自增计数，表示在这个主库上执行的第 n 个事务。MySQL 会保证事务与 GTID 之间的 1 : 1 映射。

例如，下面就是一个GTID

3E11FA47-71CA-11E1-9E33-C80AA9429562:50

表示在以 "3E11FA47-71CA-11E1-9E33-C80AA9429562" 为唯一标示的 MySQL 实例上执行的第 50 个数据库事务。很容易理解，MySQL 只要保证每台数据库的 server_uuid 全局唯一，以及每台数据库生成的transaction_id 自身唯一，就能保证 GTID 的全局唯一性。

一个GTID的生命周期包括：

1.事务在主库上执行并提交

给事务分配一个gtid（由主库的uuid和该服务器上未使用的最小事务序列号），该GTID被写入到binlog中。

2.备库读取relaylog中的gtid，并设置session级别的gtid_next的值，以告诉备库下一个事务必须使用这个值

3.备库检查该gtid是否已经被其使用并记录到他自己的binlog中。slave需要担保之前的事务没有使用这个gtid，也要担保此时已分读取gtid，但未提交的事务也不恩呢过使用这个gtid.

4.由于gtid_next非空，slave不会去生成一个新的gtid，而是使用从主库获得的gtid。这可以保证在一个复制拓扑中的同一个事务gtid不变。

由于GTID在全局的唯一性，通过GTID，我们可以在自动切换时对一些复杂的复制拓扑很方便的提升新主库及新备库，例如通过指向特定的GTID来确定新备库复制坐标。

当然，使用GTID也有一些限制：

1.事务中的更新包含非事务性存储引擎，这可能导致多个GTID分配给同一个事务。

2. create table…select语句不被支持，因为该语句会被拆分成create table 和insert两个事务，并且这个两个事务被分配了同一个GTID，这会导致insert被备库忽略掉。

3.不支持CREATE/DROP临时表操作

可以看到，支持GTID的复制对一些语句都有一些限制，MySQL也提供了一个选项disable-gtid-unsafe-statements以禁止这些语句的执行。

⒉ 什么是server_uuid？

MySQL 5.6 用 128 位的 server_uuid 代替了原本的 32 位 server_id 的大部分功能。原因很简单，server_id 依赖于 my.cnf 的手工配置，有可能产生冲突 —— 而自动产生 128 位 uuid 的算法可以保证所有的 MySQL uuid 都不会冲突。

在首次启动时 MySQL 会调用 generate_server_uuid() 自动生成一个 server_uuid，并且保存到 auto.cnf 文件 —— 这个文件目前存在的唯一目的就是保存 server_uuid。

全局唯一的 server_uuid 的一个好处是：可以解决由 server_id 配置冲突带来的 MySQL 主备复制的异常终止（BUG #33815?）

在MySQL 5.6，Slave 向 Master 申请 binlog 时，会首先发送自己的 server_uuid，Master 用 Slave 发送的 server_uuid 代替 server_id （MySQL 5.6 之前的方式）作为 kill_zombie_dump_threads 的参数，终止冲突或者僵死的 BINLOG_DUMP 线程。

[root@one ~]# cat /mydata/data/auto.cnf
[auto]
server-uuid=cc8d2c69-0ad2-11e4-8ec5-000c296f579d

mysql> SHOW GLOBAL VARIABLES LIKE ‘server_uuid‘;

+---------------+--------------------------------------+

| Variable_name | Value |

+---------------+--------------------------------------+

| server_uuid | cc8d2c69-0ad2-11e4-8ec5-000c296f579d |

+---------------+--------------------------------------+

1 row in set (0.00 sec)

三、多线程复制基于库

MySQL 5.6之前的版本，同步复制是单线程的，队列的，只能一个一个执行，在5.6里，可以做到多个库之间的多线程复制，例如数据库里，存放着用户表，商品表，价格表，订单表，那么将每个业务表单独放在一个库里，这时就可以做到多线程复制，但一个库里的表，多线程复制是无效的。

注，每个数据库仅能使用一个线程，复制涉及到多个数据库时多线程复制才有意义。

四、Mysql 5.6 复制管理工具

官方下载地址：http://dev.mysql.com/downloads/tools/utilities/#downloads
mysqlreplicate 快速启动复制
mysqlrplcheck 快速检查复制环境
mysqlrplshow 显示复制拓扑
mysqlfailover 故障转移
mysqlrpladmim 管理工具

五、基于GTID及多线程的复制实验

要在MySQL 5.6中使用复制功能，其服务配置段[mysqld]中于少应该定义如下选项：

⑴ binlog-format：二进制日志的格式，有row、statement和mixed几种类型；

需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；

⑵ log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求；

⑶ master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；

⑷ sync-master-info：启用之可确保无信息丢失；

⑸ slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；

⑹ binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；

⑺ binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；

⑻ log-bin：启用二进制日志，这是保证复制功能的基本前提；

⑼ server-id：同一个复制拓扑中的所有服务器的id号必须惟一；

report-host：

The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.

report-port:

The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.

master-info-repository:

The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)

relay-log-info-repository：

This option causes the server to log its relay log info to a file or a table.

log_slave_updates：

Whether updates received by a slave server from a master server should be logged to the slave‘s own binary log. Binary logging must be enabled on the slave for this variable to have any effect.

简单主从模式配置步骤

㈠、简单主从模式配置步骤
1、配置主从节点的服务配置文件
1.1、配置master节点：
[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=192.168.1.170
1.2、配置slave节点：
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=192.168.1.180

重启主从mysqld服务，并查看GTID相关信息

[root@one ~]# service mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL. [ OK ]

mysql> show global variables like ‘%gtid%‘;

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| enforce_gtid_consistency | ON |

| gtid_executed | |

| gtid_mode | ON |

| gtid_owned | |

| gtid_purged | |

+--------------------------+-------+

5 rows in set (0.03 sec)

2、创建复制用户
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@192.168.1.180 IDENTIFIED BY ‘replpass‘;
说明：192.168.1.180是从节点服务器；如果想一次性授权更多的节点，可以自行根据需要修改；
3、为备节点提供初始数据集
锁定主表，备份主节点上的数据，将其还原至从节点；如果没有启用GTID，在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。
4、启动从节点的复制线程
如果启用了GTID功能，则使用如下命令：
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.1.170‘, MASTER_USER=‘repluser‘, MASTER_PASSWORD=‘replpass‘, MASTER_AUTO_POSITION=1;
mysql> start slave;

没启用GTID，需要使用如下命令：
slave> CHANGE MASTER TO MASTER_HOST=‘192.168.1.170‘,
-> MASTER_USER=‘repluser‘,
-> MASTER_PASSWORD=‘replpass‘,
-> MASTER_LOG_FILE=‘master-bin.000003‘,
-> MASTER_LOG_POS=1174;

5、验证

mysql> show slave hosts;

+-----------+---------------+------+-----------+--------------------------------------+

+-----------+---------------+------+-----------+--------------------------------------+

| 11 | 192.168.1.180 | 3306 | 1 | f669daac-0ad3-11e4-8ecd-000c299c0b0f |

+-----------+---------------+------+-----------+--------------------------------------+

1 row in set (0.00 sec)

注：每一条语句，自动commit，会导致大量的日志，可关闭自动commit的功能，只有输入commit，事务才提交.

mysql>set autocommit=1

㈡、半同步复制
1、分别在主从节点上安装相关的插件
master> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;
2、启用半同步复制
在master上的配置文件中，添加
rpl_semi_sync_master_enabled=ON

在至少一个slave节点的配置文件中添加
rpl_semi_sync_slave_enabled=ON
而后重新启动mysql服务即可生效。
或者，也可以mysql服务上动态启动其相关功能：
master> SET GLOBAL rpl_semi_sync_master_enabled = ON;
slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;
slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
3、确认半同步功能已经启用
master> CREATE DATABASE magedudb;
master> SHOW STATUS LIKE ‘Rpl_semi_sync_master_yes_tx‘;
slave> SHOW DATABASES;

附件一：如何忽略复制错误

当备库复制出错时，传统的跳过错误的方法是设置sql_slave_skip_counter,然后再START SLAVE。

但如果打开了GTID，就会设置失败：

mysql> set global sql_slave_skip_counter = 1;

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

提示的错误信息告诉我们，可以通过生成一个空事务来跳过错误的事务。

我们手动产生一个备库复制错误：

Last_SQL_Error: Error ‘Unknown table ‘test.t1” on query. Default database: ‘test’. Query: ‘DROP TABLE `t1` /* generated by server */’

查看binlog中，该DDL对应的GTID为7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131

在备库上执行：

mysql> STOP SLAVE;

Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION GTID_NEXT = ’7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131′;

Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN; COMMIT;

Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION GTID_NEXT = AUTOMATIC;