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

客官°小女子只卖身不卖艺 2022-08-09 02:08 235阅读 0赞

**一、Mysql 5.6 复制管理工具**  
官方下载：[http://dev.mysql.com/downloads/tools/utilities/\#downloads][http_dev.mysql.com_downloads_tools_utilities_downloads]  
mysqlreplicate 快速启动复制  
mysqlrplcheck 快速检查复制环境  
mysqlrplshow 显示复制拓扑  
mysqlfailover 故障转移  
mysqlrpladmim 管理工具  
**二、GTID详解**  
MySQL 5.6 的新特性之一，是加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。  
官方文档：[http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html][http_dev.mysql.com_doc_refman_5.6_en_replication-gtids.html]  
GTID（Global Transaction Identifier）称为全局事务标示符，是由mysql服务器自动管理的在原始master上提交事务时被创建。GTID需要在全局的主-备拓扑结构中保持唯一性，每一个 GTID 代表一个数据库事务。**GTID由两部分组成： GTID = source\_id：transaction\_id**  
**source\_id：** 用于标示源服务器，用server\_uuid来表示，在首次启动时 MySQL 会调用 generate\_server\_uuid() 自动生成一个 server\_uuid，并且保存到MySQL数据目录下auto.cnf中，MySQL 5.6 用 128 位的 server\_uuid 代替了原本的 32 位 server\_id 的大部分功能，全局唯一的 server\_uuid 的一个好处是：可以解决由 server\_id 配置冲突带来的 MySQL 主备复制的异常终止（BUG \#33815?）在MySQL 5.6，Slave 向 Master 申请 binlog 时，会首先发送自己的 server\_uuid，Master用Slave发送的server\_uuid代替server\_id 作为 kill\_zombie\_dump\_threads 的参数，终止冲突或者僵死的 BINLOG\_DUMP 线程。  
![这里写图片描述][20151004205958619]  
**transaction\_id：** 则是根据在源服务器上第几个提交的事务来确定。transaction\_id 是一个从 1 开始的自增计数，表示在这个主库上执行的第 n 个事务。MySQL 会保证事务与 GTID 之间的 1 : 1 映射。  
**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以禁止这些语句的执行。  
**多线程复制基于库**  
MySQL 5.6之前的版本，同步复制是单线程的，队列的，只能一个一个执行，在5.6里，可以做到多个库之间的多线程复制，例如数据库里，存放着用户表，商品表，价格表，订单表，那么将每个业务表单独放在一个库里，这时就可以做到多线程复制，但一个库里的表，多线程复制是无效的。  
说明：事实上多线程是针对每个database开启相应的独立线程。即每个库有一个单独的(sql thread)，如果线上业务中，只有一个database或者绝大多数压力集中在个别database的话，多线程并发复制特性就没有意义了（slave-parallel-workers=0 表示禁用多线程功能）。  
**举例：**  
A为主BC为从时，BC复制速度会不同，则在A故障的那一刻，BC上的数据也会有不同，此时通过GTID的方式实现让B成为一个新的Master，那么在C上有的但B上没有的数据都先要复制到B上，然后把B提升为Master，然后再让C成为B的Slave，GTID追踪会把记录在二进制中每个事务中首部都标示一个GTID号；此时B可向C发出通告，告知C自己已经完成了哪些事务，从而实现服务器两端自动的发现各自在对方上缺少的数据，而不需要再进行手动指定的binglog以及master\_log\_pos。  
因此GTID能够保证让一个从服务器到其他的从服务器那里实现数据复制而且能够实现数据整合。GTID在分布式架构中可以保证数据的一致性。从而也实现了MySQL 的高可用性。默认情况下GTID将一个事务记录进二进制文件时将首先记录它的GTID，而且GTID和事务相关的信息一并要发送给从服务器，由从服务器在本地应用认证但是绝对不会改变原来的事务ID号。因此在GTID的架构上就算有了N层架构，复制是N级架构、事务ID依然不会改变；有效的保证了数据的完整和安全性。  
**GTID配置选项**  
要在MySQL 5.6中使用复制功能，其服务配置段\[mysqld\]中于少应该定义如下选项：  
**log-slave-updates**(slave更新是否记入日志)、**gtid-mode**(gtid类型)、**enforce-gtid-consistency**(强制gtid一致性)、**report-port**和**report-host**：用于启动GTID及满足附属的其它需求；  
**binlog-format：**二进制日志的格式，有row、statement和mixed3种类型；  
注意：当设置隔离级别为读提交READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；  
**report-port：** The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.  
**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.  
**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.  
**log-bin：**启用二进制日志，这是保证复制功能的基本前提；  
**server-id：**同一个复制拓扑中的所有服务器的id号必须惟一；  
**sync-master-info：**确保服务器崩溃时无信息丢失；  
**binlog-rows-query-log-events：**用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度；  
**binlog-checksum** 、**master-verify-checksum**、**slave-sql-verify-checksum：**启用复制有关的所有校验功能；  
**slave-paralles-workers：**设定从服务器的SQL线程数；0表示关闭多线程复制功能；值与要复制的数据库数目相同即可；  
**master-info-repository**(资源库)和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；  
**relay-log-info-repository：** This option causes the server to log its relay log info to a file or a table.  
**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)  
**三、配置 MySQL Master&&Slave两台服务器基于GTID实现主从复制**  
MySQL Master服务器：10.33.100.88  
MySQL Slave服务器：10.33.100.99  
实验系统：CentOS6.4\_x86\_64+5.6.12 MySQL Community Server (GPL)  
**修改主服务器配置文件**

[root@Master ~]# vim /etc/my.cnf 
    [mysqld]
    user=mysql
    port=3306
    datadir=/mnt/SQLdata
    socket=/tmp/mysql.sock
    server-id=1
    report-port=3306                            ##提供复制报告端口 
    report-host=10.33.100.88               ##提供复制报告主机
    log-bin=Master-bin                       ##二进制日志文件
    binlog-format=ROW
    log-slave-updates=true               ##将slave更新是记入日志
    gtid-mode=on                               ##启用gtid类型，否则就是普通的复制架构
    enforce-gtid-consistency=true       ##强制GTID的一致性
    master-info-repository=TABLE       ##主服器信息记录库=表/文件
    relay-log-info-repository=TABLE   ##中继日志信息记录库
    sync-master-info=1                         ##同步主库信息
    slave-parallel-workers=4                ##从服务器的SQL线程数，每个数据库仅能使用一个线程
    binlog-checksum=CRC32               ##校验码
    master-verify-checksum=1             ##主服校验
    slave-sql-verify-checksum=1          ##从服校验 
    binlog-rows-query-log_events=1   ##二进制日志详细记录事件

重启主服务器mysqld服务，并查看GTID相关信息  
![这里写图片描述][20151004211239779]  
查看master status  
![这里写图片描述][20151004211246745]  
查看UUID  
![这里写图片描述][20151004211254012]  
赋予从库权限账号，允许用户在主库上读取日志  
![这里写图片描述][20151004211311927]  
修改从服务器配置文件

[root@Slave ~]# vim /etc/my.cnf
    [mysqld]
    user=mysql
    port=3306
    datadir=/mnt/SQLdata
    socket=/tmp/mysql.sock
    server-id=11
    report-port=3306
    report-host=10.33.100.77
    binlog-format=ROW
    log-bin=Slave-bin   
    ##可以不启用二进制日志，但在架设HA的MySQL场景中必须启用从服上二进制日志，在主服出现问题时，从服可以升级为主服务器
    relay_log=slave_relay
    ##如不定义中继日志在start slave时会报错Slave failed to initialize relay log info structure from the repository
    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

Slave连接Master  
配置主从复制，5.6以前的版本，需要找到binlog和pos点，然后change master to指向，在mysql5.6里启用GTID后，只需要知道Master的IP、端口，账号密码即可，mysql会自动找点同步。  
![这里写图片描述][20151004211410251]  
![这里写图片描述][20151004211416930]  
![这里写图片描述][20151004211424729]  
主从测试：刚才在配置文件中定义了4个复制线程，Master上创建4个数据库，每个数据库事务启动一个复制线程，这些复制线程可同时启动；  
![这里写图片描述][20151004211449216]  
![这里写图片描述][20151004211456151]

[root@Master ~]# cd /mnt/SQLdata/
    [root@Master SQLdata]# mysqlbinlog Master-bin.000001

![这里写图片描述][20151004211522440]  
在以上的GTID表示为唯一标识的 MySQL 上执行的第 6 个数据库事务。很容易理解，MySQL 只要保证每台数据库的 server\_uuid 全局唯一，以及每台数据库生成的transaction\_id 自身唯一，就能保证 GTID 的全局唯一性。  
MySQL默认开启了自动提交事务，会导致大量的日志，可关闭自动commit的功能（mysql>set autocommit=1）只有输入commit，事务才提交.  
![这里写图片描述][20151004211630693]  
再对数据库做一些增删查改的操作  
![这里写图片描述][20151004211637682]  
Master上查看Master状态信息和Slave状态信息  
![这里写图片描述][20151004211644572]  
Slave上查看状态信息  
![这里写图片描述][20151004211655109]  
mysql> show slave status\\G  
![这里写图片描述][20151004211717357]

[http_dev.mysql.com_downloads_tools_utilities_downloads]: http://dev.mysql.com/downloads/tools/utilities/#downloads
[http_dev.mysql.com_doc_refman_5.6_en_replication-gtids.html]: http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html
[20151004205958619]: /images/20220731/3d7fcb5d646a4c68ad56ba3cf2d1ecfd.png
[20151004211239779]: /images/20220731/4420fd0346f34838a768b1c2ff12d597.png
[20151004211246745]: /images/20220731/6192a3e513534c5da19595b3e008e3ba.png
[20151004211254012]: /images/20220731/2b304a3a1a374eb097c3bd92312c0b45.png
[20151004211311927]: /images/20220731/b8e9ad19c20c47f8ab3d9e231f240e25.png
[20151004211410251]: /images/20220731/4bbd63981c1a44c28058eafe00b9a814.png
[20151004211416930]: /images/20220731/17f9b2283cdd412285960b78f00d6d40.png
[20151004211424729]: /images/20220731/8ae4e76b2c7d48a5984da77689556acd.png
[20151004211449216]: /images/20220731/4953f96f6b68480d889e80e5ccb8837d.png
[20151004211456151]: /images/20220731/cd38a841665e40529362004dfb4effec.png
[20151004211522440]: /images/20220731/5f8ddbcbccea40e292cbe1c6a14fef81.png
[20151004211630693]: /images/20220731/1beef9f9c6fc4afba884a1dee8bc6a13.png
[20151004211637682]: /images/20220731/d497294c58e3494297f5f4c46848f75f.png
[20151004211644572]: /images/20220731/ee39d3dab8f0412693c04100145a9b56.png
[20151004211655109]: /images/20220731/e8bbc559ba364843b8a75403b38772e5.png
[20151004211717357]: /images/20220731/715429d7cf7c423c91757d1d8f6cfd7e.png