时间：2021-07-01 10:21:17 帮助过：21人阅读
mysql [localhost] {msandbox} ((none)) > show variables like ‘%gtid%‘;
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| enforce_gtid_consistency | OFF |
| gtid_executed | |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-----------+
6 rows in set (0.01 sec)
mysql [localhost] {msandbox} ((none)) >
binlog里面也不会有GTID相关的记录，和普通复制时是一样的，如下：
[root@mysql-server-01 data]# mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS mysql_sandbox5619-bin.000006 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #140706 20:53:36 server id 1 end_log_pos 120 Start: binlog v 4, server v 5.6.19-log created 140706 20:53:36 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; # at 120 #140706 21:32:15 server id 1 end_log_pos 207 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1404653535/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; drop database yayunz /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mysql-server-01 data]#
当开启GTID特性以后，binlog记录格式是这样的（具体开启后面介绍）
注意：（当启用GTID以后，binlog格式变化很大，如果继续采用低版本的mysqlbinlog命令查看，将会得到如下错误）。
[root@mysql-server-01 data]# mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS mysql-bin.000004 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #140706 22:08:25 server id 1 end_log_pos 120 Start: binlog v 4, server v 5.6.19-log created 140706 22:08:25 ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed‘, data_len: 71, event_type: 35 ERROR: Could not read entry at offset 120: Error in log format or read error. DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mysql-server-01 data]#
下面是使用MySQL 5.6的mysqlbinlog命令查看开启了GTID日志的记录。
[root@mysql-server-01 data]# /data/sandbox_mysql/5.6.19/bin/mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS mysql-bin.000002 | tail -n 30 DELIMITER /*!*/; # at 4 #140706 21:58:35 server id 1 end_log_pos 120 CRC32 0xcb49c4fe Start: binlog v 4, server v 5.6.19-log created 140706 21:58:35 # at 120 #140706 21:58:35 server id 1 end_log_pos 191 CRC32 0x0e40da64 Previous-GTIDs # 9b0147c3-eed0-11e3-9821-000c29e3621e:1 # at 191 #140706 22:00:37 server id 1 end_log_pos 239 CRC32 0x52fc16ed GTID [commit=yes] SET @@SESSION.GTID_NEXT= ‘9b0147c3-eed0-11e3-9821-000c29e3621e:2‘/*!*/; # at 239 #140706 22:00:37 server id 1 end_log_pos 330 CRC32 0xf652f593 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1404655237/*!*/; SET @@session.pseudo_thread_id=3/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create database dyy /*!*/; SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/; # at 330 #140706 22:00:42 server id 1 end_log_pos 377 CRC32 0x68ff3fc0 Rotate to mysql-bin.000003 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mysql-server-01 data]#
我们从上面输出可以清楚的看见事务id如下（根据上面提到的，这个是第二个事务）：
SET @@SESSION.GTID_NEXT= ‘9b0147c3-eed0-11e3-9821-000c29e3621e:2‘/*!*/;
GTID的简单工作流程如下：
（1）在master上产生一个事务并且提交，并且写入binlog
（2）master上的binlog发送到slave，slave接收完毕并且写入relay log，slave读取到这个GTID，并设置gtid_next的值，例如：
SET @@SESSION.GTID_NEXT= ‘9b0147c3-eed0-11e3-9821-000c29e3621e:2
然后告诉slave接下来的事务必须使用GTID，并写入到它自己的binlog里。
（3）slave检查并确认这个GTID没有被使用，如果没有被使用，那么开始执行这个事务并写入到它自己的binlog里。
（4）由于gtid_next的值不是空的，slave不会尝试去生成一个新的gtid，而是通过主从复制来获取GTID。
二. 基于GTID的Replication的配置如下（最少配置选项，master和slave一样，server-id则需要不一样了，下面的参数只是针对于GTID，对于复制过滤选项和普通复制一样，因为GTID模式的复制也可以转换为普通模式的复制）
binlog_format = row gtid-mode = ON enforce-gtid-consistency = ON log-bin=mysql-bin log-slave-updates
少了相关选项，启动则报错，这是其中的报错日志，如下：
2014-07-06 22:47:44 15228 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates
其中binlog_format = row不是必须的，但是推荐使用ROW格式，具体的参考我前面的文章提到的原因。
上面参数主从都配置以后，下面我们就来完成一个基于GTID的Replication（和普通复制有少量区别）
（1）在master上授权账户；
mysql [localhost] {root} ((none)) > GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘192.168.0.10‘ IDENTIFIED BY ‘123456‘;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) > flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {root} ((none)) >
（2）在master上备份要同步的库（这里的备份参数大家自行查阅文档，当然有几个参数或许用不到）
[root@mysql-server-01 msb_5_6_19]# mysqldump -uroot -pmsandbox -S /tmp/mysql_sandbox5619.sock -q -R --triggers --opt --single-transaction --flush-logs --master-data=2 dyy > /tmp/dyy.sql [root@mysql-server-01 msb_5_6_19]#
（3）将备份的数据在slave上导入：
[root@mysql-server-02 ~]# mysql -uroot -pmsandbox -S /tmp/mysql_sandbox5619.sock < dyy.sql [root@mysql-server-02 ~]#
（4）进行change master to操作
mysql [localhost] {root} (dyy) > CHANGE MASTER TO MASTER_HOST=‘192.168.0.100‘,MASTER_PORT=5619,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘123456‘,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql [localhost] {root} (dyy) > show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
| Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax‘ in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql [localhost] {root} (dyy) >
可以看见成功，但是有两个警告，原来在MySQL 5.6里面越来越严格了，说不推荐这种使用方法。
mysql [localhost] {root} (dyy) > start slave;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {root} (dyy) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.100
Master_User: repl
Master_Port: 5619
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 151
Relay_Log_File: mysql_sandbox5619-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000001
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: 151
Relay_Log_Space: 577
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: 1
Master_UUID: 9b0147c3-eed0-11e3-9821-000c29e3621e
Master_Info_File: /root/sandboxes/msb_5_6_19/data/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:
Executed_Gtid_Set: 9b0147c3-eed0-11e3-9821-000c29e3621e:1-13,
d3c1aada-fad2-11e3-a66f-000c29ce3f18:1-85
Auto_Position: 1
1 row in set (0.00 sec)
mysql [localhost] {root} (dyy) >
如果使用GTID模式的复制而change master to还使用普通模式的，则会报错。
三. GTID的局限性（或者说还不够完善的地方）
（1）目前GTID还不支持MyISAM表（GTID是基于事务的）
（2）对CREATE TABLE ..... SELECT语句不支持。
mysql [localhost] {root} (dyy) > create table dyy select * from t1;
ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.
mysql [localhost] {root} (dyy) >
（3）必须开启enforce-gtid-consistency参数，否则启动MySQL报错，如下所示：
2014-07-06 23:44:27 17563 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 requires --enforce-gtid-consistency 2014-07-06 23:44:27 17563 [ERROR] Aborting
注：在5.6.9的版本还不支持创建临时表，我测试的是5.6.19的版本，已经可以创建临时表了。
四. 多线程复制（基于库）
多线程复制由参数slave-paralles-workers控制，设定从服务器的SQL线程数；0表示关闭多线程复制功能；默认为0，最大可以设置为1024个线程。在从库的配置文件[mysqld]段落添加配置：
slave_parallel_workers = 6
重启MySQL以后使用show processlist看到如下所示：
mysql [localhost] {msandbox} ((none)) > show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 173 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 2 | system user | | NULL | Connect | 173 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 173 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 173 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 173 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 173 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 173 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 173 | Waiting for an event from Coordinator | NULL |
| 10 | msandbox | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
9 rows in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) >
此外用于保证relog信息以及master信息不丢失，需要设置相关参数来保证，可以参考我前面的文章。
总结：
我们尽可能的把一个库中的表按照业务逻辑拆分为多个库，这样在master上写操作时，slave就可以根据我们设置的线程数进行多线程复制，减少了传统复制的问题--延时。比如2个库我们就可以开启2个线程，3个库就可以开启3个线程，以此类推。还有目前GTID好像还不太成熟，官方的版本里面也没有默认开启，所以还需要进行大量的测试。
参考资料
http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html
http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html
转自
MySQL 5.6 GTID Replication - yayun - 博客园
http://www.cnblogs.com/gomysql/p/3816031.html
MySQL 5.6 GTID Replication【转】
标签：www. start 单线程 date 主从切换 war 文档 output message