MySQL5.6GTID特性详解与多线程复制机制解析

MySQL5.6新特性GTID、多线程复制

在Oracle发布MySQL5.6看到众多新特性之后很兴奋,包括对复制的改进.在MySQL5.5半同步复制之后MySQL5.6又引入GTID、多线程复制,在这里总结这一下这两个新特性.

1、引入GTID,在MySQL5.6以前对于主从复制出现问题有时候需要你分析BINLOG找到POS点,然后在CHANG MASTER TO.对于新手来说很容易犯错,造成主从复制错误.在新版本中,不必在需要寻找BINLOG和POS点,你只需要知道MASTER的IP、密码、端口就可以,因为MySQL会从内部GTID机制自动找到同步点.

2、多线程复制,在MySQL5.6之前,复制是单线程队列式的,只能一个一个运行.在新版中支持基于库的多线程复制,但是库里的表不能多线程.

下面实现以下这两个新特性

注:以下实验数据库版本MySQL5.6.15

MASTER:

my.cnf添加以下参数

binlog_format &＃61; row

gtid_mode &＃61; ON

enforce-gtid-consistency &＃61; ON

slave_parallel_workers&＃61;4           --开启基于库的多线程复制默认0不开启

binlog_cache_size &＃61; 8M

max_binlog_size &＃61; 50M

max_binlog_cache_size &＃61; 100M

sync_binlog &＃61; 1

expire_logs_days &＃61; 1

log-slave-updates&＃61;true

SLAVE:

my.cnf添加以下参数

binlog_format &＃61; row

gtid_mode &＃61; ON

enforce-gtid-consistency &＃61; ON

binlog_cache_size &＃61; 8M

max_binlog_size &＃61; 50M

max_binlog_cache_size &＃61; 100M

sync_binlog &＃61; 1

expire_logs_days &＃61; 1

slave_parallel_workers&＃61;4

max_relay_log_size &＃61; 50M

relay_log_purge &＃61; 1

relay_log_recovery &＃61; 1

master_verify_checksum &＃61; 1        --主事件校验

slave_sql_verify_checksum &＃61; 1     --从事件校验

slave_allow_batching &＃61; 1

log-slave-updates&＃61;true

MASTER:

mysql> use percona;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql>

mysql> show tables;

&＃43;-------------------&＃43;

| Tables_in_percona |

&＃43;-------------------&＃43;

| test              |

&＃43;-------------------&＃43;

1 row in set (0.00 sec)

mysql> select * from test;

Empty set (0.02 sec)

mysql> desc test;

&＃43;-------&＃43;---------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

| Field | Type    | Null | Key | Default | Extra |

&＃43;-------&＃43;---------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

| id    | int(11) | YES  |     | NULL    |       |

&＃43;-------&＃43;---------&＃43;------&＃43;-----&＃43;---------&＃43;-------&＃43;

1 row in set (0.00 sec)

mysql> insert into test values (1);

Query OK, 1 row affected (0.04 sec)

mysql> insert into test values (1);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test select * from test;

Query OK, 64 rows affected (0.00 sec)

Records: 64  Duplicates: 0  Warnings: 0

mysql> flush logs;

Query OK, 0 rows affected (0.02 sec)

mysql>

SLAVE:

mysql> change master to master_host&＃61;&＃39;192.168.40.176&＃39;,master_port&＃61;3306,master_user&＃61;&＃39;repl&＃39;,master_password&＃61;&＃39;123456&＃39;,master_auto_position&＃61;1,master_delay&＃61;20;   --master_delay此参数表示relay日志会同步到slave机,但是会根据事件时间戳延时20秒,在某些场景会用到.

Query OK, 0 rows affected, 0 warnings (0.05 sec)

mysql> start slave;

Query OK, 0 rows affected, 0 warning (0.04 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.40.176

Master_User: repl

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000046

Read_Master_Log_Pos: 191

Relay_Log_File: mysqld-relay-bin.000004

Relay_Log_Pos: 401

Relay_Master_Log_File: mysql-bin.000046

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test,percona,mysql

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: 191

Relay_Log_Space: 693

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: 2

Master_UUID: 69a73914-62ca-11e3-870f-080027dff846

Master_Info_File: /mysql/data/master.info

SQL_Delay: 30

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:

Auto_Position: 1

1 row in set (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.40.176

Master_User: repl

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000050

Read_Master_Log_Pos: 191

Relay_Log_File: mysqld-relay-bin.000009

Relay_Log_Pos: 401

Relay_Master_Log_File: mysql-bin.000050

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test,percona,mysql

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: 191

Relay_Log_Space: 856

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: 2

Master_UUID: 69a73914-62ca-11e3-870f-080027dff846

Master_Info_File: /mysql/data/master.info

SQL_Delay: 20

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: 69a73914-62ca-11e3-870f-080027dff846:13-36

Executed_Gtid_Set: 69a73914-62ca-11e3-870f-080027dff846:1-36

Auto_Position: 1

1 row in set (0.00 sec)

开启SLAVE观察Retrieved_Gtid_Set与Executed_Gtid_Set

Retrieved_Gtid_Set:记录从MASTER获取BINLOG的位置

Executed_Gtid_Set:记录从机执行的MASTER和SLAVE BINLOG日志位置

注:

UUID:身份ID,第一次启动MySQL时会产生UUID写入到auto.cnf文件,不建议修改

例:

[mysql&＃64;localhost ~]$ cat /mysql/data/auto.cnf

[auto]

server-uuid&＃61;69a73914-62ca-11e3-870f-080027dff846

GTID:全局事务标识符,每次BINLOG写入事务都会产生唯一一个标识符,由UUID&＃43;Transcation ID组成.

例:

mysql> show master status;

&＃43;------------------&＃43;----------&＃43;--------------------&＃43;------------------&＃43;-------------------------------------------&＃43;

| File             | Position | Binlog_Do_DB       | Binlog_Ignore_DB | Executed_Gtid_Set                         |

&＃43;------------------&＃43;----------&＃43;--------------------&＃43;------------------&＃43;-------------------------------------------&＃43;

| mysql-bin.000050 |      191 | test,percona,mysql |                  | 69a73914-62ca-11e3-870f-080027dff846:1-36 |

&＃43;------------------&＃43;----------&＃43;--------------------&＃43;------------------&＃43;-------------------------------------------&＃43;

1 row in set (0.00 sec)

OK了,今天到此基于GTID、多线程的MySQL5.6新特性演示完成&＃xff0c;当然有些还需要进一步探究.在以后的博文中会继续讨论.希望大家有所收获,也欢迎同行指正.^_^