一、前言
MySQL在5.6版本之前复制一直是基于二进制日志的复制，到了MySQL5.6时开始支持基于事务（GTIDs）的复制，并且开始支持多线程复制；但MySQL5.6版本的多线程只能基于多库。这就牵扯到了一个应用场景，就是从基于日志的复制在线变更到基于事务的复制，在MySQL5.6版本时这一动作只能重启主服务器才可以做到。但是到了MySQL 5.7版本时已经可以支持在线变更复制类型了，也就是在线从基于二进制日志的复制变更为基于事务的复制。当然MySQL5.7在复制方面的改进不止这一点，还做到了基于表的多线程复制，以及多源复制。这篇文章只针对在线把基于日志的复制变更为基于事务的复制，其他方面的改进，如多线程复制和多源复制可以看其他文章。
二、安装MySQL 5.7.16
详情可看：MySQL 5.7多方式安装
首先从MySQL官方网站下载YUM源，地址：http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
这里我选择MySQL 5.7的源进行安装MySQL 5.7，手动添加一个YUM源。
|
1 |
$ cat /etc/yum.repos.d/mysql.repo |
|
1 2 3 4 5 6 7 |
# Enable to use MySQL 5.7 [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/ enabled=1 gpgcheck=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql |
检查一下mysql repo
|
1 2 |
$ yum repolist enabled | grep mysql mysql57-community MySQL 5.7 Community Server 146 |
安装启动配置MySQL
|
1 |
$ 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。
首先创建一些标准目录。
|
1 2 3 4 5 |
$ 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 |
下面开始进行初始化操作。
|
1 2 |
$ mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3306/data $ mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3307/data |
看一下初始化后的目录文件，如下：
|
1 2 3 4 5 6 7 8 9 10 11 |
$ 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 ibtmp1 drwxr-x--- 2 mysql mysql 4096 Nov 21 10:43 mysql drwxr-x--- 2 mysql mysql 4096 Nov 21 10:43 performance_schema drwxr-x--- 2 mysql mysql 12288 Nov 21 10:43 sys |
三、启动多实例
1. 给每个实例提供配置文件
给3306（master）实例提供一份配置文，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
$ cat /data/mysql/3306/conf/my.cnf [mysqld] ############################basic settings################# port=3306 bind-address=0.0.0.0 datadir=/data/mysql/3306/data socket=/data/mysql/3306/mysql.sock pid-file=/data/mysql/3306/mysql.pid user=mysql server-id = 10 character_set_server = utf8mb4 skip_name_resolve = 1 max_allowed_packet = 16777216 max_connections = 800 max_connect_errors = 1000 tmpdir = /data/mysql/3306/tmp tmp_table_size = 67108864 explicit_defaults_for_timestamp = 1 join_buffer_size = 134217728 interactive_timeout = 1800 wait_timeout = 1800 read_buffer_size = 16777216 read_rnd_buffer_size = 33554432 sort_buffer_size = 33554432 key_buffer_size = 256M thread_cache_size = 8 transaction_isolation = READ-COMMITTED ###########################log settings##################### log-bin = /data/mysql/3306/log/binlog/mysql-bin log_bin_index = /data/mysql/3306/log/binlog/mysql-bin.index expire_logs_days = 30 binlog_format = ROW log_error = /data/mysql/3306/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/3306/log/slowlog/slow.log min_examined_row_limit = 100 binlog-rows-query-log_events = 1 ##########################innodb settings################### innodb_buffer_pool_size = 512m innodb_sort_buffer_size = 27108864 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lock_wait_timeout = 5 innodb_flush_method = O_DIRECT innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_thread_concurrency = 24 innodb_flush_neighbors = 1 innodb_purge_threads = 4 innodb_large_prefix = 1 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_file_per_table = ON innodb_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）实例提供一份配置文件，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
$ cat /data/mysql/3307/conf/my.cnf [mysqld] ############################basic settings################# port=3307 bind-address=0.0.0.0 datadir=/data/mysql/3307/data socket=/data/mysql/3307/mysql.sock pid-file=/data/mysql/3307/mysql.pid user=mysql server-id = 20 character_set_server = utf8mb4 skip_name_resolve = 1 max_allowed_packet = 16777216 max_connections = 800 max_connect_errors = 1000 tmpdir = /data/mysql/3307/tmp tmp_table_size = 67108864 explicit_defaults_for_timestamp = 1 join_buffer_size = 134217728 interactive_timeout = 1800 wait_timeout = 1800 read_buffer_size = 16777216 read_rnd_buffer_size = 33554432 sort_buffer_size = 33554432 key_buffer_size = 256M thread_cache_size = 8 transaction_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 = ROW log_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 = 512m innodb_sort_buffer_size = 27108864 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lock_wait_timeout = 5 innodb_flush_method = O_DIRECT innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_thread_concurrency = 24 innodb_flush_neighbors = 1 innodb_purge_threads = 4 innodb_large_prefix = 1 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_file_per_table = ON innodb_flush_log_at_trx_commit=2 #########################replication######################### relay-log = /data/mysql/3307/log/relaylog/relay-log skip-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，开启对应的参数即可。
重新赋予一下权限
|
1 |
$ chown mysql.mysql -R /data/mysql |
四、基于日志做主从复制配置
1）启动两个实例
|
1 2 |
$ nohup mysqld --defaults-file=/data/mysql/3306/conf/my.cnf & $ nohup mysqld --defaults-file=/data/mysql/3307/conf/my.cnf & |
|
1 2 3 |
$ netstat -nplt | grep mysqld tcp 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）创建具有复制权限的用户
|
1 2 |
mysql> grant replication slave on *.* to 'mysql_slave'@'%' identified by '123456'; mysql> flush privileges; |
3）Slave（3307）连接至主库（3306）
|
1 2 |
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; |
|
1 2 3 4 5 6 |
master_host #主服务器地址; master_port #主服务器端口（不要指定双引号）; master_log_file #指定从主服务器哪个二进制日志文件开始复制; master_log_pos #指定从主服务器哪个二进制日志文件的位置开始复制(不需要双引号); master_user #连接到主服务器的用户; master_password #连接到主服务器的用户密码; |
4）启动Slave
|
1 |
mysql> start slave; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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状态。
|
1 2 3 4 5 6 7 |
mysql> show variables like '%gtid_mode%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_mode | OFF | +---------------+-------+ 1 row in set (0.00 sec) |
1）Master（3306）操作
为了更加模拟线上环境，我们写一个脚本，一直往主库插入数据，同时从库也一直再同步主库的数据。
|
1 2 3 4 5 6 7 |
CREATE DATABASE `test`; USE test; 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表中插入数据。
|
1 2 3 4 |
#!/bin/bash for 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。
|
1 2 |
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中出现警告如下：
|
1 |
2016-11-21T22:35:24.322055Z 55 [Warning] Statement violates GTID consistency: CREATE TABLE ... SELECT. |
然后看一下error log。
|
1 2 3 |
$ tail /data/mysql/3306/log/error.log 2016-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操作。
|
1 2 |
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事务。
|
1 2 3 4 5 |
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）操作
|
1 2 3 4 5 6 7 8 9 10 11 |
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）操作
|
1 2 |
mysql> set global gtid_mode=on_permissive; Query OK, 0 rows affected (0.03 sec) |
开启之后，由于脚本在一直写数据，你可以立马看见二进制状态的变化。
|
1 2 3 4 5 6 7 |
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即可。
|
1 2 3 4 5 6 7 |
mysql> show status like 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | Ongoing_anonymous_transaction_count | 0 | +-------------------------------------+-------+ 1 row in set (0.00 sec) |
|
1 |
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）操作
|
1 2 |
mysql> set global gtid_mode=on_permissive; Query OK, 0 rows affected (0.03 sec) |
接下来show slave status就可以看到从库已经切换为GTID复制了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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: 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: 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了。
|
1 2 |
mysql> set global gtid_mode=on; Query OK, 0 rows affected (0.04 sec) |
查看gtid的开启。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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后就又失效了。具体可以看：MySQL基于GTIDs的复制实现。
change master
|
1 2 3 |
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工具进行修复即可，详情请看这篇文章：使用pt-table-checksum&pt-table-sync检查和修复主从数据一致性。
六、在线关闭基于GTID的复制
1）Slave操作
|
1 |
mysql> stop slave; |
然后记录slave status
|
1 2 |
Exec_Master_Log_Pos: 7631438 Relay_Master_Log_File: bin_log.000016 |
重新执行CHANGE MASTER
|
1 |
mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 0,MASTER_LOG_FILE = 'bin_log.000016', MASTER_LOG_POS = 7631438; |
重新开启Slave。
|
1 |
mysql> start slave; |
2）Master&slave操作
生成的是GTID事物，slave可以应用匿名和GTID事物。
|
1 |
mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE; |
3）Master&Slave操作
生成的是匿名事物，slave可以应用匿名和GTID事物。
|
1 |
mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE; |
4）Master&Slave操作
|
1 |
mysql> SELECT @@GLOBAL.GTID_OWNED; |
等到主库和备库此显示为空，并且Retrieved_Gtid_Set/Executed_Gtid_Set不再变动。（甚至你可以在slave上使用：SELECT MASTER_POS_WAIT(file, position)；来强制等待slave端直到指定位置，这个位置就是你确定的没有使用GTID事务的位置）
完成这一步实际上GTID事物已经没有生成和应用了。
5）Master&Slave操作
|
1 |
mysql> SET @@GLOBAL.GTID_MODE = OFF; |
最后别忘记修改配置文件my.cnf，使其永久生效。
如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。
