MySQL 5.6同步复制新特性详解

作者：hcymysql 来源：Linux社区日期：2013-11-16 11:23

继5.5半同步复制后，MySQL 5.6又对其进行了优化与改进，其中有两个地方较为重要：

1、对运维人员来说应该是一件大喜的事情，在主从切换后，在传统的方式里，你需要找到binlog和POS点，然后change master to指向，而不是很有经验的运维，往往会将其找错，造成主从同步复制报错，在mysql5.6里，你无须再知道binlog和POS点，你只需要知道master的IP、端口，账号密码即可，因为同步复制是自动的，mysql通过内部机制GTID自动找点同步。

2、多线程复制基于库。之前的版本，同步复制是单线程的，队列的，只能一个一个执行，在5.6里，可以做到多个库之间的多线程复制，例如yourDB库里，存放着用户表，商品表，价格表，订单表，那么将每个业务表单独放在一个库里，这时就可以做到多线程复制，但一个库里的表，多线程复制是无效的。

MySQL5.6下载

2014-12-04MySQL 管理工具 5.6.22 正式版（32位）

新的名词解释：

server_uuid：服务器身份ID。在第一次启动Mysql时，会自动生成一个server_uuid并写入到数据目录下auto.cnf文件里，官方不建议修改。

并且server_uuid跟GTID有密切联系。

[root@mysql5_6 data]# pwd
/usr/local/mysql/data
[root@mysql5_6 data]# cat auto.cnf

[auto]
server-uuid=b0869d03-d4a9-11e1-a2ee-000c290a6b8f

GTID：全局事务标识符。当开始这个功能时，每次事务提交都会在binlog里生成一个唯一的标示符，它由UUID和事务ID组成。首次提交的事务ID为1，第二次为2，第三次为3，依次类推。

查看binlog，会看到如下：

开启GTID时，slave在做同步复制时，无须找到binlog日志和POS点，直接change master to master_auto_position=1即可，自动找点同步。

GTID的工作流程是这样的：

1.在master上一个事务提交，并写入binlog里。

2.binlog日志发送到slave，slave接收完并写入relay log中继日志里，slave读取到这个GTID，并设置gtid_next的值,例如：

SET@@SESSION.GTID_NEXT='B0869D03-D4A9-11E1-A2EE-000C290A6B8F:3';然后告诉slave接下来的事务必须使用GTID并写入到它自己的binlog里。

3.slave检查并确认这个GTID没有被使用并写入到它自己的binlog里。如果没有被使用，那么开始执行这个事务并写入到它自己的binlog里。

4.由于gtid_next的值不是空的，slave不会尝试去生成一个新的gtid，而是通过主从同步来获取GTID。

=============================================================

如何设置GTID方式的主从同步？

答：需要同时在master和slave上，在my.cnf文件上加入如下：

log-bin = mysql-bin
binlog_format = row
log_slave_updates
gtid-mode = ON
disable-gtid-unsafe-statements = ON

在master上导出：

mysqldump -uroot -p123456 -q --single-transaction -R -E --triggers --default-character-set=utf8 -B yourDB > ./yourDB.sql

在slave上导入：

mysql -uroot -p123456

然后做指向即可。

mysql> CHANGE MASTER TO
> MASTER_HOST = master-host,
> MASTER_PORT = master-port,
> MASTER_USER = repl-user,
> MASTER_PASSWORD = repl-password,
> MASTER_AUTO_POSITION = 1;

注：如果你使用了GTID，那么就不能再使用传统binlog和POS方式

CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

否则会报错：

GTID的局限性：

1.GTID同步复制是基于事务。所以Myisam表不支持，这可能导致多个GTID分配给同一个事务。

2.CREATE TABLE ... SELECT语句不支持。因为该语句会被拆分成create table 和insert两个事务，并且这个两个事务被分配了同一个GTID，这会导致insert被备库忽略掉。

如果把disable_gtid_unsafe_statements参数关闭，启动mysql时会报错，也就是说开启GTID,disable_gtid_unsafe_statements参数必须开启。

3.不支持CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE 临时表操作。

设置slave_parallel_workers参数，开启基于库的多线程复制。默认是0，不开启，最大并发数为1024个线程。

set global slave_parallel_workers = 4;

当设置为4个线程时，show processlist，你会发现：

有4个Waiting for an event from Coordinator线程

分别开两个终端，用sysbench分别对两个库进行压力测试，

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000 --max-requests=1000 --num-threads=16 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-db=test --mysql-socket=/tmp/mysql.sock run

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000 --max-requests=1000 --num-threads=16 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-db=test1 --mysql-socket=/tmp/mysql.sock run

然后select * from mysql.slave_worker_info\G; 你会发现：

mysql> select * from mysql.slave_worker_info\G;

*************************** 1. row ***************************
                 Master_id: 165
                 Worker_id: 0
            Relay_log_name:
             Relay_log_pos: 0
           Master_log_name:
            Master_log_pos: 0
 Checkpoint_relay_log_name:
  Checkpoint_relay_log_pos: 0
Checkpoint_master_log_name:
 Checkpoint_master_log_pos: 0
          Checkpoint_seqno: 0
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap:
*************************** 2. row ***************************
                 Master_id: 165
                 Worker_id: 1
            Relay_log_name:
             Relay_log_pos: 0
           Master_log_name:
            Master_log_pos: 0
 Checkpoint_relay_log_name:
  Checkpoint_relay_log_pos: 0
Checkpoint_master_log_name:
 Checkpoint_master_log_pos: 0
          Checkpoint_seqno: 0
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap:
*************************** 3. row ***************************
                 Master_id: 165
                 Worker_id: 2
            Relay_log_name: ./mysql5_6-relay-bin.000009
             Relay_log_pos: 2091034
           Master_log_name: mysql-bin.000003
            Master_log_pos: 2090832
 Checkpoint_relay_log_name: ./mysql5_6-relay-bin.000009
  Checkpoint_relay_log_pos: 2082941
Checkpoint_master_log_name: mysql-bin.000003
 Checkpoint_master_log_pos: 2082739
          Checkpoint_seqno: 5
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap: 0
*************************** 4. row ***************************
                 Master_id: 165
                 Worker_id: 3
            Relay_log_name: ./mysql5_6-relay-bin.000009
             Relay_log_pos: 2954634
           Master_log_name: mysql-bin.000003
            Master_log_pos: 2954432
 Checkpoint_relay_log_name: ./mysql5_6-relay-bin.000009
  Checkpoint_relay_log_pos: 2951772
Checkpoint_master_log_name: mysql-bin.000003
 Checkpoint_master_log_pos: 2951570
          Checkpoint_seqno: 1
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap: 
4 rows in set (0.01 sec)

有两个Worker_id的数值在不断的变化，那么多线程复制就开始起作用了。

注：如果一个库有N个请求，那么不会使用多线程复制，但是两个库有N个请求，那么会使用多线程复制。尽可能的把一个库中的表，按照业务逻辑拆分成多个库保存，这样在写操作时，slave上就会开启多线程复制，减少了同步延时。2个库slave就有2个IO/SQL线程，3个库slave就有2个IO/SQL线程，依次类推。

如下图所示：

此外，

relay_log_info_repository=TABLE

relay_log_info_repository=TABLE
master_info_repository=TABLE

会将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用

alter table slave_master_info engine=innodb;
alter table slave_relay_log_info engine=innodb;
alter table slave_worker_info engine=innodb;

改为Innodb引擎，防止表损坏后自行修复。

2014-12-04MySQL 管理工具 5.6.22 正式版（32位）

MySQL 5.6同步复制新特性详解

特别下载