MySQL 5.6.10 跨平台GTID复制实践

根据业务需要，建立MySQL复制来实现数据冗余。

1：binlog_format   默认值是：statement

            有效值: ROW,基于行的复制

                       STATEMENT 基于语句级别的复制

                       MASTER_LOG_POS,MIXED  基于以上2种混合

     都有个各自的优缺点，可根据实际情况选择

    
2：gtid_mode   默认是：off

             有效值: on   开启gtid

                        off  关闭gtid

     从 5.6 开始mysql 增加了gtid （Global Transaction Identifiers） ，开启了这个参数，就在change master to 的时候就无需再指定 MASTER_LOG_FILE 和 MASTER_LOG_POS，而只需要增加 auto_master_postition =1 就行了，如开启这个参数需要相应增加--log-slave-updates  --enforce-gtid-consistency 这2个参数

3：slave_skip_errors  默认值：off

            有效值：相关错误号

                        all

                        ddl_exist_errors

            如果在复制的过程中，slave 遇到复制错误，就会停止复制，如果想跳过错误，继续复制，那就可以采用这个参数

             Examples:

                           --slave-skip-errors=1062,1053

                           --slave-skip-errors=all

                           --slave-skip-errors=ddl_exist_errors

   

      在复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。

　　Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。

　　--slave-skip-errors=[err1,err2,…….|ALL]

　　但必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本

4：slave_parallel_workers ：默认值 0，表示不开启并行复制

       有效值：0-1024

       5.6 版本开始支持并行复制，可以减少mysql slave 的复制时间

       设置：

       stop slave

       set global skip_parallel_works=4 ;

       start slave；

       或者在my.cnf 配置文件中加入

       skip_parallel_works 参数

5： 延时复制

     如果你想slave 延时复制的话，可以把slave 停掉之后，用命令  change master to master_delay=n n  为你想要延时的时间

6： replicate-do-db ，replicate-do-table，replicate-ignore-db，replicate-ignore-tables 前2个参数都是告诉slave 要复制那个数据库或者那个表

，而后2个参数告诉slave ，那些是要忽略复制的

7： log-slave-updates

　　log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作

8 ：master-connect-retry

　　master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒

9：read-only

　　read-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作

10：enforce-gtid-consistency

      gtid-mode=on 

      enforce-gtid-consistency=true

      开启gtid模式必须开启此参数

11：master-info-repository  = TABLE，relay-log-info-repository = TABLE

       在MySQL 5.6.2之前，slave记录的master信息以及slave应用binlog的信息存放在文件中，即master.info与relay-log.info。在5.6.2版本之后，允许记录到table中

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

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

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

15：Binlog_gtid_simple_recovery = 1 这个参数控制了当mysql启动或重启时，mysql在搜寻GTIDs时是如何迭代使用binlog文件的,这个选项设置为真，会提升mysql执行恢复的性能。因为这样mysql-server启动和binlog日志清理更快。该参数为真时，mysql-server只需打开最老的和最新的这2个binlog文件，gtid_purged参数的值和gtid_executed参数的值可以根据这些文件中的Previous_gtids_log_event 或者 Gtid_log_event计算得出。这确保了当mysql-server重启或清理binlog时，只需打开2个binlog文件,在MySQL-5.6中，调整这个选项设置也同样会提升性能，但是在一些特殊场景下，计算gtids值可能会出错。而保持这个选项值为false，能确保计算总是正确,在MySQL-5.7.7中，几乎不需要在速度和安全性之间做权限。设置该选项为TRUE总能得到正确的结果，除了以下2个极端场景

a．最新的binlog是MySQL-5.7.5(或更老版本)产生的，并且gtid_mode对一些binlog设置为ON，但是对最新的binlog设置为OFF

b．GTID_PURGED的状态是MySQL-5.7.7之前的版本发布的，并且在当时激活清理的binlog在当前仍然没有清理完成

因此，开启这个选项几乎总是更好一些，所以这个选项默认开启,如果这个参数设置为off，在mysql恢复期间为了初始化gtid_executed，所有以最新文件开始的binlog都要被检查。并且为了初始化gtid_purged，所有的binlog都要被检查。这可能需要非常长的时间

 

MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。

在官方文档中提到，最保险可靠的复制方式，是基于row的复制，所以宁可牺牲一些性能也要保证数据的安全。

现实环境中，master主数据库MySQL 5.6.10(msi安装方式)安装在Windows 2008 Server x64上，slave从服务器是一台老旧的DELL服务器，运行CentOS 6.4 x64系统，源码编译安装MySQL 5.6.10的Linux版本，安装过程可以参考我以前的博文：http://www.cnblogs.com/jlzhou/archive/2013/03/09/2951544.html

不同平台下，MySQL是有一些差异的，要小心处理。

第一个问题是，Windows平台下，文件名大小写不敏感，造成对应的MySQL的数据表名称默认都采用小写字母方式，同时大小不写敏感，参考我以前的博文：http://www.cnblogs.com/jlzhou/archive/2013/03/18/2966106.html 为了能将数据同步复制到Linux平台的MySQL，我们需要设置Linux平台下MySQL的数据表名称设置：(修改my.cnf文件)

[mysqld]
lower_case_table_names=1

第二个问题是，自增字段0值的问题。因为现有数据库是MSSQL，业务逻辑需要某些表的自增字段从0开始。参考我以前的博文：http://www.cnblogs.com/jlzhou/archive/2013/03/18/2965384.html 为了在Windows平台和Linux平台的MySQL之间复制数据，增加全局变量设置，在my.ini和my.cnf中分别添加NO_AUTO_VALUE_ON_ZERO设置到sql-mode行：

//my.ini 该文件默认在Windows7或Windows2008操作系统中位于 C:\ProgramData\MySQL\MySQL Server 5.6 目录下（采用MSI安装方式），如果你自定义了数据目录，则该配置文件在数据目录下。
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO"

现在开始配置GTID复制，先配置master端的my.ini文件，加入下述配置，然后重启master的MySQL服务：

binlog-format=ROW
log-bin=master-bin.log
log-bin-index=master-bin.index
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
sync_binlog=1

 再修改slave端的my.cnf文件，加入下述配置，然后重启slave的MySQL服务：

binlog-format=ROW
log-bin=slave-bin.log
log-bin-index=slave-bin.index
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=2
sync_binlog=1

其实，并不需要在slave端启用binlog，但是为了在master故障时，方便的转换slave到master，并且方便建立slave的slave，所以采用和主服务器类似的配置。

复制设置会将用于复制的用户和密码以明文形式保存在master.info文件中，最好为复制建立专用的用户，授予 REPLICATION SLAVE 权限。

在master端执行：

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.1.101' IDENTIFIED BY '12345678';

最后，在slave执行指向master的命令，并开启slave复制。

CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_PORT=3306, MASTER_USER='repluser',MASTER_PASSWORD='12345678', master_auto_position=1;

START slave;

这时就可以测试在master上建立数据库，建表，然后监控slave的复制状态了。

本文中没有涉及到已有数据库在master上的情况，请参考这篇博文：http://www.zhaokunyao.com/archives/4131

 

后记：

附上备份和恢复脚本：命令行执行

Backup from remote server scripts:

// for test database:
"C:\MySQL\MySQL Server 5.6\bin\mysqldump.exe" --user=root --max_allowed_packet=1G --host=10.192.8.105 --port=3306 --default-character-set=utf8 --set-gtid-purged=OFF --password --databases test > "C:\\Backup\\test.dump.sql" 

Restore to local dev machine scripts:

// for test database:
"C:\MySQL\MySQL Server 5.6\bin\mysql.exe" --host=localhost --user=root --port=3306 --password --default-character-set=utf8 --comments < "C:\\Backup\\test.dump.sql"

注意，上述脚本中，备份的部分要加入--set-gtid-purged=OFF参数，防止在备份出的sql脚本中生成 SET @@global.gtid_purged 语句：

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF.

官方文档关于set-gtid-purged是这样写的：

This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output.