一、前言
MySQL在5.6版本之前复制一直是基于二进制日志的复制，到了MySQL5.6时开始支持基于事务（GTIDs）的复制，并且开始支持多线程复制；但MySQL5.6版本的多线程只能基于多库。这就牵扯到了一个应用场景，就是从基于日志的复制在线变更到基于事务的复制，在MySQL5.6版本时这一动作只能重启主服务器才可以做到。但是到了MySQL 5.7版本时已经可以支持在线变更复制类型了，也就是在线从基于二进制日志的复制变更为基于事务的复制。当然MySQL5.7在复制方面的改进不止这一点，还做到了基于表的多线程复制，以及多源复制。这篇文章只针对在线把基于日志的复制变更为基于事务的复制，其他方面的改进，如多线程复制和多源复制可以看其他文章。
二、安装MySQL 5.7.16
详情可看：
首先从MySQL官方网站下载YUM源，地址：http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
这里我选择MySQL 5.7的源进行安装MySQL 5.7，手动添加一个YUM源。
$ cat /etc/yum.repos.d/mysql.repo
# Enable to use MySQL 5.7[mysql57-community]name=MySQL 5.7 Community Serverbaseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/enabled=1gpgcheck=0gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
检查一下mysql repo
$ yum repolist enabled | grep mysqlmysql57-community MySQL 5.7 Community Server 146
安装启动配置MySQL
$ yum install mysql-community-server-5.7.16 mysql-community-devel-5.7.16 mysql-community-client-5.7.16 mysql-community-common-5.7.16
MySQL 5.7以及安装完成了，为了简便，我这里使用多实例的方式进行测试在线变更复制类型，开两个端口3306和3307。
首先创建一些标准目录。
$ mkdir /data/mysql/3306/{conf,data,log,tmp} -p$ mkdir /data/mysql/3306/log/{binlog,relaylog,slowlog} -p$ mkdir /data/mysql/3307/{conf,data,log,tmp} -p$ mkdir /data/mysql/3307/log/{binlog,relaylog,slowlog} -p$ chown mysql.mysql -R /data/mysql 下面开始进行初始化操作。
$ mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3306/data$ mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3307/data
看一下初始化后的目录文件，如下：
$ ll /data/mysql/3306/data/total 122908-rw-r----- 1 mysql mysql 56 Nov 21 10:43 auto.cnf-rw-r----- 1 mysql mysql 260 Nov 21 11:58 ib_buffer_pool-rw-r----- 1 mysql mysql 50331648 Nov 21 11:58 ib_logfile0-rw-r----- 1 mysql mysql 50331648 Nov 21 10:43 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Nov 21 11:58 ibdata1-rw-r----- 1 mysql mysql 12582912 Nov 21 11:58 ibtmp1drwxr-x--- 2 mysql mysql 4096 Nov 21 10:43 mysqldrwxr-x--- 2 mysql mysql 4096 Nov 21 10:43 performance_schemadrwxr-x--- 2 mysql mysql 12288 Nov 21 10:43 sys
三、启动多实例
1）给每个实例提供配置文件
给3306（master）实例提供一份配置文，如下：
$ cat /data/mysql/3306/conf/my.cnf[mysqld]############################basic settings#################port=3306bind-address=0.0.0.0datadir=/data/mysql/3306/datasocket=/data/mysql/3306/mysql.sockpid-file=/data/mysql/3306/mysql.piduser=mysqlserver-id = 10character_set_server = utf8mb4skip_name_resolve = 1max_allowed_packet = 16777216max_connections = 800max_connect_errors = 1000tmpdir = /data/mysql/3306/tmptmp_table_size = 67108864explicit_defaults_for_timestamp = 1join_buffer_size = 134217728interactive_timeout = 1800wait_timeout = 1800read_buffer_size = 16777216read_rnd_buffer_size = 33554432sort_buffer_size = 33554432key_buffer_size = 256Mthread_cache_size = 8transaction_isolation = READ-COMMITTED###########################log settings#####################log-bin = /data/mysql/3306/log/binlog/mysql-binlog_bin_index = /data/mysql/3306/log/binlog/mysql-bin.indexexpire_logs_days = 30binlog_format = ROWlog_error = /data/mysql/3306/log/error.logslow_query_log = 1long_query_time = 2log_slow_admin_statements = 1log_slow_slave_statements = 1slow_query_log_file = /data/mysql/3306/log/slowlog/slow.logmin_examined_row_limit = 100binlog-rows-query-log_events = 1##########################innodb settings###################innodb_buffer_pool_size = 512minnodb_sort_buffer_size = 27108864innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_lock_wait_timeout = 5innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_thread_concurrency = 24innodb_flush_neighbors = 1innodb_purge_threads = 4innodb_large_prefix = 1innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_file_per_table = ONinnodb_flush_log_at_trx_commit=2##########################start gtid############################gtid-mode = on#enforce-gtid-consistency = true#master-info-repository = table#relay-log-info-repository = table#log-slave-updates = true#binlog-checksum = CRC32#master-verify-checksum = 1#slave-sql-verify-checksum = 1#slave_allow_batching = 1
给3307（slave）实例提供一份配置文件，如下：
$ cat /data/mysql/3307/conf/my.cnf[mysqld]############################basic settings#################port=3307bind-address=0.0.0.0datadir=/data/mysql/3307/datasocket=/data/mysql/3307/mysql.sockpid-file=/data/mysql/3307/mysql.piduser=mysqlserver-id = 20character_set_server = utf8mb4skip_name_resolve = 1max_allowed_packet = 16777216max_connections = 800max_connect_errors = 1000tmpdir = /data/mysql/3307/tmptmp_table_size = 67108864explicit_defaults_for_timestamp = 1join_buffer_size = 134217728interactive_timeout = 1800wait_timeout = 1800read_buffer_size = 16777216read_rnd_buffer_size = 33554432sort_buffer_size = 33554432key_buffer_size = 256Mthread_cache_size = 8transaction_isolation = READ-COMMITTED###########################log settings######################log-bin = /data/mysql/3307/log/binlog/mysql-bin#log_bin_index = /data/mysql/3307/log/binlog/mysql-bin.index#expire_logs_days = 30#binlog_format = ROWlog_error = /data/mysql/3307/log/error.log#slow_query_log = 1#long_query_time = 2#log_slow_admin_statements = 1#log_slow_slave_statements = 1#slow_query_log_file = /data/mysql/3307/log/slowlog/slow.log#min_examined_row_limit = 100##########################innodb settings###################innodb_buffer_pool_size = 512minnodb_sort_buffer_size = 27108864innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_lock_wait_timeout = 5innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_thread_concurrency = 24innodb_flush_neighbors = 1innodb_purge_threads = 4innodb_large_prefix = 1innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_file_per_table = ONinnodb_flush_log_at_trx_commit=2#########################replication#########################relay-log = /data/mysql/3307/log/relaylog/relay-logskip-slave-start = true#####################start gtid###############################gtid-mode = on#enforce-gtid-consistency = true#slave-parallel-workers = 1#binlog-checksum=CRC32#master-verify-checksum = 1#slave-sql-verify-checksum = 1#slave_allow_batching = 1#relay_log_purge = 1#relay_log_recovery = 1#master-info-repository = table#relay-log-info-repository = table#report-port = 3308#report-host = 10.0.60.143
PS：配置文件很清楚，如果想使用GTID，开启对应的参数即可。
重新赋予一下权限
$ chown mysql.mysql -R /data/mysql
四、基于日志做主从复制配置
1）启动两个实例
$ nohup mysqld --defaults-file=/data/mysql/3306/conf/my.cnf &$ nohup mysqld --defaults-file=/data/mysql/3307/conf/my.cnf &
$ netstat -nplt | grep mysqldtcp 0 0 10.0.60.143:3306 0.0.0.0:* LISTEN 39854/mysqld tcp 0 0 10.0.60.143:3307 0.0.0.0:* LISTEN 40256/mysqld
PS：进入MySQL需要使用-S指定各自的mysql.sock文件。
2）Master（3306）创建具有复制权限的用户
mysql> grant replication slave on *.* to 'mysql_slave'@'%' identified by '123456';mysql> flush privileges;
3）Slave（3307）连接至主库（3306）
mysql> reset slave all;mysql> change master to master_host='10.0.60.143',master_user='mysql_slave',master_password='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1;
master_host #主服务器地址;master_port #主服务器端口（不要指定双引号）;master_log_file #指定从主服务器哪个二进制日志文件开始复制;master_log_pos #指定从主服务器哪个二进制日志文件的位置开始复制(不需要双引号);master_user #连接到主服务器的用户;master_password #连接到主服务器的用户密码;
4）启动Slave
mysql> start slave;
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.60.143 Master_User: mysql_slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 591 Relay_Log_File: relay-log.000003 Relay_Log_Pos: 804 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ........
OK，现在基于日志的主从复制已经完成了。
五、在线开启基于GTID的复制
接下来，就是在线切换复制类型了。首先确定主从的gtid_mode都是off状态。
mysql> show variables like '%gtid_mode%';+---------------+-------+| Variable_name | Value |+---------------+-------+| gtid_mode | OFF |+---------------+-------+1 row in set (0.00 sec)
1）Master（3306）操作
为了更加模拟线上环境，我们写一个脚本，一直往主库插入数据，同时从库也一直再同步主库的数据。
mysql> CREATE DATABASE `test`;mysql> USE test;mysql> CREATE TABLE `tt` (`id` int(11) NOT NULL AUTO_INCREMENT,`count` int(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
然后创建一个脚本一直往test.tt表中插入数据。
$ cat test.sh#!/bin/bashfor i in `seq 1 1000000`;do mysql -S /data/mysql/3306/mysql.sock -e "insert into test.tt(count) values($i);"done
现在就可以开始丢在后台执行。
接下来，在主库（3306）和把enforce_gtid_consistency变成warn。
mysql> set global enforce_gtid_consistency=warn;Query OK, 0 rows affected (0.00 sec)
ENFORCE_GTID_CONSISTENCY这个参数主要有如下设置，主要用于不让违反GTID设置的操作执行，如果执行会报错如：Statement violates GTID consistency: CREATE TABLE … SELECT.
- OFF：所有操作允许。
- ON：不允许违反gtid的操作，并且报错。
- WARN：在MySQL 5.7.6添加，所有操作允许，但是违反GTID的操作会报出警告。
ENFORCE_GTID_CONSISTENCY=WARN是确定事务都支持gtid，不会在err log中出现警告如下：
2016-11-21T22:35:24.322055Z 55 [Warning] Statement violates GTID consistency: CREATE TABLE ... SELECT.
然后看一下error log。
$ tail /data/mysql/3306/log/error.log2016-11-21T05:24:10.064466Z 5 [Note] Start binlog_dump to master_thread_id(5) slave_server(20), pos(, 4)2016-11-21T08:12:52.480029Z 8 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN.
如果没有错误信息，才可以进行下一步开启enforce_gtid_consistency操作。
mysql> set global enforce_gtid_consistency=on;Query OK, 0 rows affected (0.00 sec)
然后进行开启gtid_mode操作，gtid_mode这个参数有四个值，一定要按照如下顺序进行gtid_mode的开启操作（关闭是相反的）。
- off：生成的是匿名事务，slave也只能应用匿名事务。
- off_permissive：生成的是匿名事务，slave可以应用匿名事务和GTID事务。
- on_permissive：生成的是GTID事务，slave可以应用匿名事务和GTID事务。（这一步操作完成后，主节点二进制日志就会变成gtid模式）
- on：生成的是GTID事务，slave也只能应用GTID事务。
mysql> set global gtid_mode=off;Query OK, 0 rows affected (0.03 sec)mysql> set global gtid_mode=off_permissive;Query OK, 0 rows affected (0.04 sec)
当主库设置完gtid_mode=off_permissive之后，这个时候也要在从库执行到这一步，为的是slave可以应用匿名事务和GTID事务。切记，主从设置是交叉的，如果从库没有设置到gtid_mode=off_permissive，而主库下一步操作gtid_mode = on_permissive后，从库的IO线程就会断掉。带来的后果就是如果主从有延迟，那么主从数据很有可能会不一致。并且这种情况下，也不能算是一个完整的在线切换复制类型，只能算是半在线。
正确的做法就是如下操作，在从库也先进行如下设置：
2）Slave（3307）操作
mysql> set global enforce_gtid_consistency=warn;Query OK, 0 rows affected (0.00 sec)mysql> set global enforce_gtid_consistency=on;Query OK, 0 rows affected (0.00 sec)mysql> set global gtid_mode=off;Query OK, 0 rows affected (0.03 sec)mysql> set global gtid_mode=off_permissive;Query OK, 0 rows affected (0.04 sec)
当从库也设置完gtid_mode=off_permissive之后，就可以在主库进行开启GTID了。
3）Master（3306）操作
mysql> set global gtid_mode=on_permissive;Query OK, 0 rows affected (0.03 sec)
开启之后，由于脚本在一直写数据，你可以立马看见二进制状态的变化。
mysql> show master status;+------------------+----------+--------------+------------------+--------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+--------------------------------------------+| mysql-bin.000004 | 28388 | | | 3b4cc092-af94-11e6-8a81-001dd8b71e2b:1-117 |+------------------+----------+--------------+------------------+--------------------------------------------+1 row in set (0.00 sec)
查看确定已经没有匿名事务了，这个值ONGOING_ANONYMOUS_TRANSACTION_COUNT有一次为0即可。
mysql> show status like 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';+-------------------------------------+-------+| Variable_name | Value |+-------------------------------------+-------+| Ongoing_anonymous_transaction_count | 0 |+-------------------------------------+-------+1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.GTID_OWNED;
确定此时的Retrieved_Gtid_Set/Executed_Gtid_Set正常增长（甚至你可以在slave上使用：SELECT MASTER_POS_WAIT(file, position);
来强制等待slave端直到指定位置，这个位置就是你确定的使用GTID事务的位置）。
可以看到当主库设置完gtid_mode = on_permissive后，二进制状态变成了GTID模式。这个时候就可以在从库开启gtid_mode = on_permissive了。4）Slave（3307）操作
mysql> set global gtid_mode=on_permissive;Query OK, 0 rows affected (0.03 sec)
接下来show slave status就可以看到从库已经切换为GTID复制了。
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.60.143 Master_User: mysql_slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 207164 Relay_Log_File: relay-bin.000005 Relay_Log_Pos: 207377 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 207164 Relay_Log_Space: 207636 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: 0Master_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: 103306 Master_UUID: 6edc34c8-d23d-11e6-b440-fa163e2a6390 Master_Info_File: /data/mysql/3307/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 6edc34c8-d23d-11e6-b440-fa163e2a6390:1-135 Executed_Gtid_Set: 6edc34c8-d23d-11e6-b440-fa163e2a6390:1-135 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.01 sec)
5）Master&Slave都操作
下面就可以在主库和从库上分别设置gtid_mod = on了。mysql> set global gtid_mode=on;Query OK, 0 rows affected (0.04 sec)
查看gtid的开启。
mysql> show variables like '%gtid%';+----------------------------------+-----------+| Variable_name | Value |+----------------------------------+-----------+| binlog_gtid_simple_recovery | ON || enforce_gtid_consistency | ON || gtid_executed_compression_period | 1000 || gtid_mode | ON || gtid_next | AUTOMATIC || gtid_owned | || gtid_purged | || session_track_gtids | OFF |+----------------------------------+-----------+8 rows in set (0.01 sec)
至此，主从复制在线切换GTID已经完成了。最后别忘了把gtid相关信息写进配置文件中，不然重启MySQL后就又失效了。具体可以看：。
change master
mysql> stop slave;mysql> CHANGE MASTER TO MASTER_HOST='10.0.60.143',MASTER_PORT=3306,master_user='mysql_slave',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;mysql> start slave;
如果出现主从不一致的情况，那么可以使用pt工具进行修复即可，详情请看这篇文章：。
六、在线关闭基于GTID的复制
1）Slave操作
mysql> stop slave;
然后记录slave status
Exec_Master_Log_Pos: 7631438Relay_Master_Log_File: bin_log.000016
重新执行CHANGE MASTER
mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 0,MASTER_LOG_FILE = 'bin_log.000016', MASTER_LOG_POS = 7631438;
重新开启Slave。
mysql> start slave;
2）Master&slave操作
生成的是GTID事物，slave可以应用匿名和GTID事物。mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
3）Master&Slave操作
生成的是匿名事物，slave可以应用匿名和GTID事物。
mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
4）Master&Slave操作
mysql> SELECT @@GLOBAL.GTID_OWNED;
等到主库和备库此显示为空，并且Retrieved_Gtid_Set/Executed_Gtid_Set不再变动。（甚至你可以在slave上使用：SELECT MASTER_POS_WAIT(file, position)；来强制等待slave端直到指定位置，这个位置就是你确定的没有使用GTID事务的位置）
完成这一步实际上GTID事物已经没有生成和应用了。5）Master&Slave操作
mysql> SET @@GLOBAL.GTID_MODE = OFF;
最后别忘记修改配置文件my.cnf，使其永久生效。
为了方便大家交流，本人开通了微信公众号（关注看更多精彩）和QQ群，QQ群1（291519319）和QQ群2（659336691）。喜欢技术的一起来交流吧
