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 = row
gtid_mode = ON
enforce-gtid-consistency = ON
slave_parallel_workers=4 --开启基于库的多线程复制默认0不开启
binlog_cache_size = 8M
max_binlog_size = 50M
max_binlog_cache_size = 100M
sync_binlog = 1
expire_logs_days = 1
log-slave-updates=true
SLAVE:
my.cnf添加以下参数
binlog_format = row
gtid_mode = ON
enforce-gtid-consistency = ON
binlog_cache_size = 8M
max_binlog_size = 50M
max_binlog_cache_size = 100M
sync_binlog = 1
expire_logs_days = 1
slave_parallel_workers=4
max_relay_log_size = 50M
relay_log_purge = 1
relay_log_recovery = 1
master_verify_checksum = 1 --主事件校验
slave_sql_verify_checksum = 1 --从事件校验
slave_allow_batching = 1
log-slave-updates=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;
+-------------------+
| Tables_in_percona |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from test;
Empty set (0.02 sec)
mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
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='192.168.40.176',master_port=3306,master_user='repl',master_password='123456',master_auto_position=1,master_delay=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 statusG
*************************** 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 statusG
*************************** 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@localhost ~]$ cat /mysql/data/auto.cnf
[auto]
server-uuid=69a73914-62ca-11e3-870f-080027dff846
GTID:全局事务标识符,每次BINLOG写入事务都会产生唯一一个标识符,由UUID+Transcation ID组成.
例:
mysql> show master status;
+------------------+----------+--------------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------------+------------------+-------------------------------------------+
| mysql-bin.000050 | 191 | test,percona,mysql | | 69a73914-62ca-11e3-870f-080027dff846:1-36 |
+------------------+----------+--------------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)