rhel6系统中，mysql 5.6复制新特性下主从复制配置[基于GTID]

古城微笑少年丶 2022-08-21 02:14 148阅读 0赞

1.mysql5.6在复制方面的新特性：

(1).支持**多线程复制**:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sql thread).针对这样的改进,如果我们想实现多线程复制,无疑要对现存的数据库结构进行重新设计,分库分表.对于压力都集中在个别database的,多线程并发复制特性就没有意义.

(2).支持**启用GTID**,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向.在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,mysql通过内部机制GTID自动找点同步.

(3).基于**Row复制只保存改变的列**,大大节省Disk Space/Newwork resources和Memory usage.

(4).支持把Master 和Slave的相关**信息记录在Table中**,原来是记录在文件里,记录在表里,增强可用性

(5).支持**延迟复制**

注：

关于 server\_uuid 的解释：服务器身份ID。在第一次启动Mysql时，会自动生成一个server\_uuid并写入到数据目录下auto.cnf文件里，官方不建议修改。

1.  \[root@client102 ~\]\# cat /home/mysql/data/auto.cnf  
2.  \[auto\]  
3.  server-uuid=14be3ddd-4e92-11e3-8335-000c299c1b31

关于GTID的解释： 全局事务标识符。当开启这个功能时，每次事务提交都会在binlog里生成一个唯一的标示符，它由server\_uuid和事务ID组成。首次提交的事务ID为1，第二次为2，第三次为3，依次类推。

2.mysql主从复制的原理图：\[此原理没有变化\]

[![mysql主从复制原理图][mysql]][mysql]

3. MySQL5.6开始主从复制有两种方式：**基于日志（binlog）**\[基于日志的搭建,上篇文章已搭建过\];**基于GTID（全局事务标识符）**

本次配置是基于GTID（全局事务标识符）,但其也有劣势,实际生产环境中,暂时不推荐使用

基于GTID（全局事务标识符）的局限性：

(1).GTID同步复制是基于事务。所以Myisam表不支持，这可能导致多个GTID分配给同一个事务。(5.6.9版本已经修改，支持修改Myisam表)

(2).gtid\_mode和enforce-gtid-consistency=true 必须同时使用，不同时使用，启动Mysql报错。

(3).无法修改myisam表的数据，会提示"Updates to non-transactional tables are forbidden when disable-gtid-unsafe-statements"   --> 这个我测试5.6.14,是可以正常修改数据,所以这点劣势待定,大家可以分享测试结果

(4).不支持对临时表操作：CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE     --> 这个劣势,5.6.14也可以做,大家可以测试,留言反馈，最近，在5.6.19上测试，是不可以操作，以前5.6.14可能测试有误，这里补充一下。

(5).**不支持CREATE TABLE ... SELECT语句**。因为该语句会被拆分成create table 和insert两个事务，并且这个两个事务被分配了同一个GTID，这会导致insert被备库忽略掉\[这条语句在游戏数据库用的比较多，通常用来将大表分成小表\]

4.基于GTID（全局事务标识符）mysql主从复制配置演示：

(1).默认主DB server和从DB server数据库都已经安装好,我的两台DB server都已经安装好(5.6.14版本),都会是双实例\[这里我使用3307端口的实例\]

注：两台机器的的selinux都是disable(永久关闭selinux,请修改/etc/selinux/config,将SELINUX改为disabled),防火墙可以选择关闭，开启的话也行\[不行的话,添加防火墙策略\]

主DB server：192.168.1.102:3307

从DB server：192.168.1.100:3307

(2).修改主DB server的配置文件(/etc/my.cnf) \[client102为主DB server\] --> 主DB sever的配置文件和从DB server文件基本一样,方便搭建HA

1.  \[root@client102 ~\]\# vim /etc/my.cnf  
2.  \# 在\[mysqld\]里加入如下代码\[里面原代码保留,有重复的部分,以这部分为准\]  
3.  \# 设置server\_id,一般建议设置为IP,或者再加一些数字\[在以前版本为server-id\]  
4.  server\_id =1021  
5.  \# 二进制日志的格式：有row、statement和mixed三种  
6.  \# 注：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致; 推荐使用 row  
7.  binlog-format=ROW  
8.  \# 这个选项允许应用程序只能对行的镜像数据进行复制,而不在关心行是否已经进行了DML操作.这提高了主从机器的复制吞吐量,减少了二进制日志所占用的磁盘空间、网络资源和内存占用.  
9.  binlog-row-image = minimal  
10. \# 开启二进制日志功能,可以随便取,最好有含义  
11. log-bin=mysql3307-bin  
12. \# log-slave-updates/gtid-mode/enforce-gtid-consistency/report-port/report-host：用于启动GTID及满足附属的其它需求\[其中启动GTID必须同时设置gtid-mode/enforce-gtid-consistency/\]  
13. report-host=192.168.1.102  
14. report-port=3307  
15. gtid-mode=on  
16. enforce-gtid-consistency=true  
17. log-slave-updates=true  
18. \# master-info-repository/relay-log-info-repository都设置为TABLE,mysql.slave\_master\_info与 mysql.slave\_relay\_log\_info 中,table都是innodb类型的,支持事务,比文件安全  
19. \# 默认值是FILE, 比如master info就保存在master.info文件中,relay log info保存在relay-log.info文件中,如果服务器意外关闭,正确的relay info 没有来得及更新到 relay-log.info文件,这样会造成数据丢失  
20. master-info-repository=TABLE  
21. relay-log-info-repository=TABLE  
22. \# 启用之后,使binlog在每N次binlog写入后与硬盘 同步  
23. sync-master-info=1  
24. \# 以下是对二进制日志一些设置  
25. binlog\_cache\_size = 4M  
26. max\_binlog\_size = 1G  
27. max\_binlog\_cache\_size = 2G  
28. \# 以下这几个参数是启用binlog/relaylog的校验，防止日志出错  
29. binlog-checksum=CRC32  
30. slave\_allow\_batching = 1  
31. master-verify-checksum=1  
32. slave-sql-verify-checksum=1  
33. \# 启用这个参数,可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度  
34. \# 只对row binlog格式有效.启用后,会向binlog中写入更多的调试信息，比如sql语句自身都会被写进去. mysqlbinlog -vv 可以看到.  
35. binlog-rows-query-log\_events=1  
36. \# 开启基于库的多线程复制.默认是0,不开启,最大并发数为1024个线程  
37. slave-parallel-workers=4  
38. \# 这两个是启用relaylog的自动修复功能,避免由于网络之类的外因造成日志损坏,主从停止.  
39. relay\_log\_purge = 1  
40. relay\_log\_recovery = 1

(3).启动数据库服务器,并登陆数据库,授予相应的用户用于同步

1.  \# 查看GTID是否开启\[enforce\_gtid\_consistency/gtid\_mode 为ON,表示已经开启\]  
2.  mysql> show **global** variables like '%gtid%';  
3.  \+--------------------------+-------+  
4.  | Variable\_name | Value |  
5.  \+--------------------------+-------+  
6.  | enforce\_gtid\_consistency | ON |  
7.  | gtid\_executed | |  
8.  | gtid\_mode | ON |  
9.  | gtid\_owned | |  
10. | gtid\_purged | |  
11. \+--------------------------+-------+  
12. \# 我这里是多实例mysql,所以启动是这样的,如果大家是单实例的,就直接启动就可以\[/etc/init.d/mysqld start\]  
13. \[root@client102 ~\]\# mysqld\_multi start 3307  
14. \# 登陆mysql 服务器  
15. \[root@client102 ~\]\# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
16. \# 授予用户权限用于主从同步  
17. mysql> grant replication slave on \*.\* to 'kongzhong'@'192.168.1.100' identified by 'kongzhong';  
18. \# 刷新授权表信息  
19. mysql> flush privileges;

(4).从DB server配置文件添加代码如下,和主基本一样

1.  \[root@client100 ~\]\# vim /etc/my.cnf  
2.  \# 在\[mysqld\]下添加如下代码\[里面原代码保留,有重复的部分,以这部分为准\]  
3.  server\_id =1002 \# 此处和主DB server不一样,唯一值  
4.  binlog-format=ROW  
5.  binlog-row-image = minimal  
6.  log-bin=mysql33071-bin \# 此处和主DB server不一样  
7.  report-host=192.168.1.100 \# 此处和主DB server不一样  
8.  report-port=3307  
9.  gtid-mode=on  
10. enforce-gtid-consistency=true  
11. master-info-repository=TABLE  
12. relay-log-info-repository=TABLE  
13. sync-master-info=1  
14. slave-parallel-workers=4  
15. binlog\_cache\_size = 4M  
16. max\_binlog\_size = 1G  
17. max\_binlog\_cache\_size = 2G  
18. binlog-checksum=CRC32  
19. master-verify-checksum=1  
20. slave-sql-verify-checksum=1  
21. binlog-rows-query-log\_events=1  
22. log-slave-updates=true  
23. relay\_log\_purge = 1  
24. relay\_log\_recovery = 1

(5).启动从数据库,添加主DB server指向\[关键位置\]

1.  \# 启动数据库  
2.  \[root@client100 ~\]\# mysqld\_multi start 3307  
3.  \# 登陆数据库,添加相关参数(主DBserver的ip/端口/同步用户/密码/\*\*\*\*) 这里也需要看是否启动GTID,我这里省略  
4.  \[root@client100 ~\]\# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
5.  mysql> change master to  
6.  \-> master\_host='192.168.1.102',  
7.  \-> master\_user='kongzhong',  
8.  \-> master\_password='kongzhong',  
9.  \-> master\_port=3307,  
10. \-> master\_auto\_position = 1,  
11. \-> master\_delay=30;  
12. \#/\* 下面是一部分注解：  
13. \#/\* 指定主DB server的IP地址  
14. master\_host='192.168.1.102'  
15. \#/\* 指定用于同步的用户\[这个就是我们在主DB server授权的用户\]  
16. master\_user='kongzhong'  
17. \#/\* 指定用于同步的用户的密码  
18. master\_password='kongzhong'  
19. \#/\* 指定主DB server的端口\[下面一个例子,可以重点看这个\]  
20. master\_port=3306  
21. \#/\* 自动寻找position号  
22. master\_auto\_position = 1  
23. \#/\* 延时30秒执行复制,relay日志会及时同步到slave机,只是日志的中的事件会根据事件的时间戳延时30秒执行.此参数具有实用性  
24. master\_delay=30

(6).开启同步,验证同步状态

1.  \# 开启主从同步\[以前的版本slave start,新版本已不支持此写法\]  
2.  mysql> start slave;  
3.  \# 查看主从同步状态  
4.  mysql> show slave status\\G;  
5.  \# 主要看以下两个参数：\[这两个参数如果是yes就表示主从同步正常\]  
6.  Slave\_IO\_Running: Yes  
7.  Slave\_SQL\_Running: Yes  
8.  \# 在新特性下,我们还可以看到如下几个参数  
9.  Retrieved\_Gtid\_Set: 1f14026d-53e7-11e3-a5f9-000c299c1b31:4  
10. Executed\_Gtid\_Set: 1f14026d-53e7-11e3-a5f9-000c299c1b31:1-4  
11. \#/\* Retrieved\_Gtid\_Set项：记录了relay日志从Master获取了binlog日志的位置  
12. \#/\* Executed\_Gtid\_Set项：记录本机执行的binlog日志位置（如果是从机，包括Master的binlog日志位置和slave本身的binlog日志位置）  
13. \# 此时,我们可以在主DB sever 查看一下,现在gtid标识是否和从DB server 一致：  
14. \# 登陆到主DB server执行如下命令\[查看gtid\_executed和从DB server上的Executed\_Gtid\_Set是否一致,一致表示同步完成\]  
15. mysql> show **global** variables like '%GTID%';  
16. \+--------------------------+------------------------------------------+  
17. | Variable\_name | Value |  
18. \+--------------------------+------------------------------------------+  
19. | enforce\_gtid\_consistency | ON |  
20. | gtid\_executed | 1f14026d-53e7-11e3-a5f9-000c299c1b31:1-4 |  
21. | gtid\_mode | ON |  
22. | gtid\_owned | |  
23. | gtid\_purged | |  
24. \+--------------------------+------------------------------------------+  
25. \# 此时可以大家可以在主上执行操作,看是否能同步到从上,并时刻对比上面几个参数  
26. 上面的搭建，省了数据一致部分\[我这里是新装的实例,所以数据是一致的\],各位如果不是新环境,可以备份,还原数据  
27. 备份使用如下语句, --triggers --routines --events 这几个参数没有认真揣摩其含义,如有对其了解的,可留言告知  
28. \[root@client102 ~\]\# mysqldump -uroot -p -S /usr/local/mysql/mysqld3307.sock --all-databases --triggers --routines --events >/tmp/mysql3307.sql  
29. \# 还原语句还和原理一样  
30. \[root@client100 ~\]\# mysql -uroot -p -S /usr/local/mysql/mysqld3307.sock < /tmp/mysql3307.sql

mysql 5.6新特性下的主从,这里研究的还不够深入，有时间还需进一步研究。

各位如果发现文章中的错误，请及时指正，交流，谢谢！

[mysql]: http://www.xuchanggang.cn/wp-content/uploads/2013/11/replication.png