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

 

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

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

 

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

新的名词解释

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

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解

 

并且server_uuidGTID有密切联系。

[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组成。首次提交的事务ID1,第二次为2,第三次为3,依次类推。

 

查看binlog,会看到如下:

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_02MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_03MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_04MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_05

 

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

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_06

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

GTID的工作流程是这样的:

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

2.binlog日志发送到slaveslave接收完并写入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方式的主从同步?

答:需要同时在masterslave上,在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 < ./yourDB.sql

 

然后做指向即可。

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,那么就不能再使用传统binlogPOS方式

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;

否则会报错:

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_07

 

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

GTID的局限性: 

 

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

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

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_08

 

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

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_09

 

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

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_10

 

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

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

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_11

 

set global slave_parallel_workers = 4;

当设置为4个线程时,show processlist,你会发现:

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_12

 

4Waiting 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就有2IO/SQL线程,3个库slave就有2IO/SQL线程,依次类推。

如下图所示:

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_13

 

此外,

relay_log_info_repository=TABLE

master_info_repository=TABLE

会将master.inforelay.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引擎,防止表损坏后自行修复。

MySQL5.6同步复制新特性详解_MySQL5.6同步复制新特性详解_14

 

本文出自 “hcymysql” 博客,请务必保留此出处http://hcymysql.blog.51cto.com/5223301/962712