MySQL 5.6 GTID双主复制（主从复制）设置记录
近期借着十一黄金周，将生产系统上的MySQL服务器进行了版本升级，在对比了目前多个稳定发行版后，决定升级到5.6版本，支持GTID全局事务复制同步，同时InnoDB版本也更新为1.2版，相比老版本加入了更多新的特性和性能的提升。虽然没有5.7的多源复制，但考虑实际生产环境需求，5.6明显更适合。
MySQL的升级操作在本例中就不再阐述，如果不是全新安装，存在老版本数据的，建议不要跨版本升级，而是循序渐进提升版本号，同时每次提升一个版本号后一定要使用mysql_upgrade将系统库架构进行升级，再继续提升版本号。为了安全期间，如果数据量不大建议mysqldump全局备份一次，如数据量很大，建议直接再服务器上对数据目录的文件进行cp操作，以防万一。
本文示例将从全新安装一个MySQL 5.6开始，关于如何编译安装MySQL，可参考本站内其他相关文章，本例中以配置双主为主，故使用RPM安装模式。下述的操作步骤请同时在两台或以上服务器&虚拟机上进行，后续双主同步设置时需要进行可靠性验证。
一、MySQL 5.6 安装
访问MySQL社区下载页面，根据操作系统获取最新的官方RPM安装包，本例中使用的是CentOS/RHEL 6系列系统的安装包。将安装包下载到服务器目录以备执行安装步骤。
cd /tmp wget https://dev.mysql.com/get/mysql57-community-release-el6-11.noarch.rpm
运行rpm包安装命令
rpm -Uvh mysql57-community-release-el6-11.noarch.rpm
安装官方源后，使用yum进行安装，由于默认官方源启用的是5.7版本的，在这里我们要使用yum-utils包工具将官方源改成默认启用5.6版本的
yum-config-manager --disable mysql57-community yum-config-manager --enable mysql56-community
执行好上述命令后，默认下载的源已被变更为5.6版本，使用yum进行安装
yum install mysql-server
安装时，会提示软件包GPG-KEY不在系统默认的授权列表内
warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Importing GPG key 0x5072E1F5: Userid : MySQL Release Engineering <[email protected]> Package: mysql57-community-release-el6-11.noarch (installed) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Is this ok [y/N]:
输入y后回车授信Oracle公司的GPG-KEY完成安装过程。
二、MySQL 5.6 初始化
接下来需要对全新安装的MySQL进行初始化，运行命令启用mysqld服务
service mysqld start
接下来服务器会提示一系列信息，主要内容是关于初始化服务器数据库文件以及一些信息提示，我们使用如下命令对服务器进行初始化
mysql_secure_installation
回车后有如下信息，请根据注释自行选择操作
Enter current password for root： //要求输入服务器默认的root密码，如果是全新安装，是不存在默认密码的，直接回车跳过 Set root password? [Y/n] //要求为数据库服务器的root用户创建密码的询问，这里建议创建一个，输入Y后回车 New password: //为root用户设置一个密码，本示例为了方便演示，使用123456 Re-enter new password: //重复输入一次密码 Password updated successfully! //密码更新完毕 Reloading privilege tables.. //刷新权限表，默认root用户是可以通过非本机访问的 ... Success! Remove anonymous users? [Y/n] //移除匿名用户，即移除安装时用于测试的匿名用户连接，选择Y Disallow root login remotely? [Y/n] //不允许root用户从远端登陆，若输入Y回车则只有本机才可以使用root登陆数据库服务器，本例中为测试，暂选N Remove test database and access to it? [Y/n] //移除安装时用于测试的数据库，选择Y Reload privilege tables now? [Y/n] //刷新权限，选择Y以应用刚才的所有设置
至此数据库的初始化已经完成了，使用命令可以在本机登陆进入，在多台服务器上均需要执行以下命令创建一个同步账号
mysql -u root -p //回车 Enter password: //输入刚才为root设置的密码后回车 mysql> use mysql; mysql> grant replication slave on *.* to 'repl'@'192.168.100.12' identified by 'qwerty'; //为同步创建一个具有同步权限的账号 mysql> flush privileges; quit; //退出数据库CLI
注意命令中的ip地址，在第一台服务器上设置时ip地址输入第二台的，在第二台服务器上输入该命令时，ip地址改成第一台服务器的。如果有第三台服务器用于只读从模式的话，则建议所有服务器上创建repl用户时，将ip地址统一输成服务器的局域网段，如输入'192.168.100.%'，这样就有了一个C类网的访问许可定义。
接下来要修改配置文件my.cnf开启同步复制以及一些GTID功能，使用命令将数据库服务停止
service mysqld stop
三、MySQL 主主同步设置
找到my.cnf配置文件，默认它在/etc目录下。本示例中提供一个小型的初始化配置以供测试，将下述配置内容覆盖掉原先的默认配置后，MySQL可能因为ibdata文件分区大小改变而无法启动，若发生这样的情况，请去数据目录删除旧的文件(默认为/var/lib/mysql)再启动服务即可。
[client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] character-set-server = utf8 skip-character-set-client-handshake init-connect='SET NAMES utf8' user = mysql port = 3306 socket = /var/lib/mysql/mysql.sock datadir = /var/lib/mysql open_files_limit = 10240 back_log = 600 max_connections = 2000 max_connect_errors = 1000 table_open_cache = 614 external-locking = FALSE max_allowed_packet = 8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 300 #thread_concurrency = 8 query_cache_size = 8M query_cache_limit = 2M query_cache_min_res_unit = 2k default-storage-engine = INNODB thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 8M max_heap_table_size = 8M long_query_time = 3 key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 8M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 256M myisam_repair_threads = 1 myisam_recover_options=force,backup #ft_min_word_len = 8 interactive_timeout = 120 wait_timeout = 120 #skip-networking skip-name-resolve skip-slave-start #master-connect-retry = 10 slave-skip-errors = 1032,1062,126,1114,1146,1048,1396 server-id = 11 //第二台服务器时，id改为12，第三台13，依此类推 auto_increment_offset = 1 //第二台服务器时，漂移值为2 auto_increment_increment = 10 //自增值步进需配合漂移值使用，本例中为10，具体设置概念请参考文末内容 sync_binlog = 1 #5.6 Feature: binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 binlog_cache_size = 4M binlog_format = MIXED max_binlog_cache_size = 8M max_binlog_size = 512M expire_logs_days = 30 binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema #5.6 Feature: gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE slave-parallel-workers=0 log-bin = mysql-11-bin //第二台服务器时，改为mysql-12-bin log-slave-updates=true relay-log=mysql-relay-11-bin //第二台服务器时，改为mysql-relay-12-bin innodb_additional_mem_pool_size = 8M innodb_buffer_pool_size = 8M innodb_data_file_path = ibdata1:256M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 64M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 #long_query_time = 6 #slow_query_log = 1 #slow_query_log_file=/var/log/slow-query.log #log-queries-not-using-indexes = 1 [mysqldump] quick max_allowed_packet = 32M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
在相应的服务器上编辑并保存配置文件后，重新启动数据库服务器，登陆各个服务器到达mysql CLI界面。首先我们操作第一台服务器的数据库命令行：
mysql -uroot -p Enter password: //输入root密码后回车 mysql> change master to //回车 > master_host='192.168.100.12', //此处的IP地址是要求输入第二台数据库的地址 > master_user='repl', //输入前面建立的用于同步的数据库用户账号 > master_password='qwerty' //输入同步账号的密码 > master_auto_position=1; //使用自动定位开启GTID同步，如使用master_log_file与master_log_pos来定位，则使用mysql5.5版之前的同步模式
接下来操作第二台数据库服务器的命令行：
mysql -uroot -p Enter password: //输入root密码后回车 mysql> change master to //回车 > master_host='192.168.100.11', //此处的IP地址是要求输入第一台数据库的地址 > master_user='repl', //输入前面建立的用于同步的数据库用户账号 > master_password='qwerty' //输入同步账号的密码 > master_auto_position=1; //使用自动定位开启GTID同步，如使用master_log_file与master_log_pos来定位，则使用mysql5.5版之前的同步模式
两台数据库的同步均以指向它们的彼此，组成了一个主主同步的模式，此时分别在两台都运行一次start slave;命令即可以启用同步；使用show slave status\G命令可以查看当前同步的情况，如下示例所示：
mysql> show slave status\G //在第一台数据库服务器运行此命令 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.88.12 //显示是对端，即第二台数据库的地址 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-12-bin.000002 //显示的是对端数据库配置文件里定义的master文件名 Read_Master_Log_Pos: 2921 Relay_Log_File: mysql-relay-11-bin.000006 //显示的是本地数据库配置文件里定义的relay-log文件名 Relay_Log_Pos: 609 Relay_Master_Log_File: mysql-12-bin.000002 //显示的是对端数据库配置文件里定义的relay-log文件名 Slave_IO_Running: Yes //显示Yes则同步功能正常 Slave_SQL_Running: Yes //显示Yes则同步功能正常,若显示No请查看Last_SQL_Error: 条目的报错内容 Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema 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: 2921 Relay_Log_Space: 1614 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: 12 Master_UUID: 9bde5589-9df3-11e7-9e2a-005056388615 //显示的是本地数据库的UUID标识符 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 the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 9bde5589-9df3-11e7-9e2a-005056388615:1-4 //显示的是当前检索的执行条目，其中前半段UUID为服务器标识，冒号后为事物进程 Executed_Gtid_Set: 563030eb-9df3-11e7-9e29-005056362217:1-14, 9bde5589-9df3-11e7-9e2a-005056388615:1:3 //显示的是当前已执行的条目，其中前半段UUID为服务器标识，冒号后为事物进程 Auto_Position: 1 //若此设置显示为0，则表示使用的是二进制日志同步模式，非GTID标识模式
至此，双主配置的同步数据库已经建立完成，若遇到报错，服务器上也不存在重要的历史数据，可使用reset master;和reset slave;这两条数据库命令对同步状态进行初始化，然后检查同步设置的地址、用户名、密码、模式是否有误，再行测试。
四、MySQL 只读从库设置
有时因业务需要，除了双主同步架构以外，还需要一些服务器作为读操作负载，或给应用进行只读隔离，此时我们需要一台服务器即能存有最新的数据，又能提供读操作。
按照上面的步骤，初始化一台数据库服务器并更改好my.cnf配置文件、设置同步用户，配置文件中可以对读操作进行一些优化，具体请自行前往mysql.com搜索文档。本例中，将设这台服务器为第三台服务器，并且前面两台双主可以暂时停机以供确定导出的数据文件的同步标识符。
特别注意：如果双主服务器上已经存在业务数据，在从库执行下列操作前，使用mysqldump将指定的有业务数据的库文件(请勿包含系统库)导出后恢复到将要开始设置的从库数据库内，并且使用mysqldump命令时，使用--master-data=2与--set-gtid-purged选项，这样导出的文件将包含set global gtid_purged信息。从库同步建立后，数据的完整性不会受到影响，从库将从备份文件导出那一刻的事务开始同步。
进入第三台服务器的MySQL 命令行模式：
mysql -u root -p //回车 Enter password: //输入root的密码后回车 mysql> show global variables like'%gtid%'; //首先查看本机sql是否具有gtid信息 +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | | //gtid_executed的值如果不为空，说明这台数据库服务器曾经有过数据 | gtid_mode | ON | | gtid_owned | | | gtid_purged | | //gtid_purged的值如果不为空，说明这台数据库服务器曾经有过数据 | simplified_binlog_gtid_recovery | OFF | //加入同步需要数据库为全新初始化，若上述两项存在数据，请使用命令重置同步状态 +---------------------------------+-------+ mysql> reset master; //重置数据库同步状态 mysql> reset slave; //重置数据库从库同步状态 mysql> set global gtid_purged="9bde5589-9df3-11e7-9e2a-005056388615:1-4,9bde5589-9df3-11e7-9e2a-005056388615:1:3" //设置从库的gtid_purged标识符为同步数据来源的数据库服务器，这里使用了第一台数据库服务器 的Retrieved_Gtid_Set后显示的值。 ！！！如使用了包含该设置的mysqldump导出的文件，此步骤可略过！！！ mysql> change master to //回车 > master_host='192.168.100.11', //这里在双主里选一台作为从库的数据来源服务器 > master_user='repl', //输入前面建立的用于同步的数据库用户账号 > master_password='qwerty' //输入同步账号的密码 > master_auto_position=1; //使用自动定位开启GTID同步，如使用master_log_file与master_log_pos来定位，则使用mysql5.5版 之前的同步模式
最后在第三台从库服务器上执行start slave;从库服务器即会按照gtid_purged的值去指定的主服务器寻找事务，并同步操作到自己的数据库。使用show slave status\G命令查看同步是否正常运行，确认没有问题后，编辑my.cnf文件，在[mysqld]标签下增加一行read-only=1的设置，重启从库服务器即可将从库运行在只读模式下，只读模式下除非账号拥有服务器的super权限，否则不能进行写操作，但不影响自动同步的写操作执行。
同步的状态若存在error，通过show slave status\G内提示的信息确定mysql执行的错误编号和说明，进行逐步排查一般都可解决。要注意任何重启，停止mysql数据库服务的操作之后，都需要重新执行start slave;开启同步，一旦积累的未同步数据条目超过my.cnf中max_binlog_size设置的值，则数据同步架构必须完全重建，否则同步无法执行，因为缺失了事务的连续性。(MariaDB除外，可以允许事务非连续性）
附录：
官方关于自增值参数的说明和示例
简单举例参考：
若自增值auto_increment_increment = 1 漂移值auto_increment_offset = 1 则数据插入时是ID是连续的1,2,3,4,5,6,7,8，…… 若自增值auto_increment_increment = 10 漂移值auto_increment_offset = 1 则数据插入时是ID是1,11,21,31……，以1为基准，每下一个ID的编号加10 若自增值auto_increment_increment = 10 漂移值auto_increment_offset = 5 则数据插入时是ID是5,15,25,35……，以5为基准，每下一个ID的编号加10 编号的逻辑为 auto_increment_offset + N × auto_increment_increment，其中N表示第几次，从0的基数开始计算。 若自增值auto_increment_increment = 2 漂移值auto_increment_offset = 1 则数据插入时是ID是1,3,5,7,9,11,13,15，…… 若自增值auto_increment_increment = 2 漂移值auto_increment_offset = 2 则数据插入时是ID是2,4,6,8,10,12,14,16，……
下一篇文章预订介绍MySQL Route中间件的使用，同时会有一些关于MyCAT的功能对比。