MySQL5.6基于GTID复制配置

一、什么是GTID?

 GTID(Global Transaction Identifiers)是全局事务标识
当使用GTIDS时,在主上提交的每一个事务都会被识别和跟踪,并且运用到所有从MySQL,而且配置主从或者主从切换时不再需要指定 master_log_files和master_log_pos;由于GTID-base复制是完全基于事务的,所以能很简单的决定主从复制的一致性;官方建议Binlog采用Row格式

二、GTID的表示方式

1
2
3
4
5
6
7
8
9
10
11
12
source_id:transaction_id
source_id:表示执行事务的主库的UUID(server_uuid:Mysql5.6的data目录下启动时会生成auto.cnf文件记录了uuid,重启后uuid不变,删除文件后会重新生成新的uuid);
transaction_id:是一个从1开始自增的计数,表示在这个主库上执行的第n个事务;
由于每台Mysql的uuid是全球唯一的,transaction_id自身唯一,就保证了GTID全局唯一性
mysql> show variables like 'server_uuid';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 4468c0e8-ef6f-11e3-9c2c-0200c0a80ad8 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

三、基于GTID的复制配置

master:192.168.10.216
slave :192.168.10.217
步骤:
修改主从my.cnf增加GTID支持—>主只读—>拷贝数据到从数据目录—>重启主从—>在从上进行配置

  1. 修改主从my.cnf增加GTID支持
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
主Mysql配置:
server-id=216
binlog-format=ROW
gtid-mode=on
enforce-gtid-consistency=true
log-bin=mysql-bin
log-slave-updates=true slave更新是否记入日志
从Mysql配置:
server-id=217 同一个复制拓扑中的所有服务器的id号必须惟一
binlog-format=ROW
gtid-mode=on 启用gtid类型,否则就是普通的复制架构
enforce-gtid-consistency=true 强制GTID的一致性
log-bin=mysql-bin
log-slave-updates=true slave更新是否记入日志
只从库配置:
slave-paralles-workers 设定从服务器的SQL线程数;0表示关闭多线程复制功能;
  1. 主只读

mysql> SET @@global.read_only = ON;
拷贝主数据到从目录

  1. 重启主从Mysql
  2. 在从上配置基于GTID的复制

    1
    2
    3
    4
    5
    6
    mysql> CHANGE MASTER TO
    > MASTER_HOST = ‘192.168.10.216’,
    > MASTER_PORT = 3306,
    > MASTER_USER = 'rep',
    > MASTER_PASSWORD = 'geekwolf',
    > MASTER_AUTO_POSITION = 1;
  3. 启动从库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    mysql> start slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    mysql> show slave status \G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.10.216
    Master_User: rep
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000002
    Read_Master_Log_Pos: 41921904
    Relay_Log_File: relay-bin.000002
    Relay_Log_Pos: 64520
    Relay_Master_Log_File: mysql-bin.000002
    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: mysql.%
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 41921904
    Relay_Log_Space: 64718
    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: 216
    Master_UUID: 21ad8db5-f038-11e3-a14a-0200c0a80ad8
    Master_Info_File: /usr/local/mysql/data/master.info
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Reading event from the relay log
    Master_Retry_Count: 86400
    Master_Bind:
    Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
    Master_SSL_Crl:
    Master_SSL_Crlpath:
    Retrieved_Gtid_Set: 21ad8db5-f038-11e3-a14a-0200c0a80ad8:76793-77026
    Executed_Gtid_Set: 21ad8db5-f038-11e3-a14a-0200c0a80ad8:1-77025
    Auto_Position: 1
    1 row in set (0.00 sec)

注:
两个Yes代表复制正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

基于GTID复制的新特性:
Retrieved_Gtid_Set: 21ad8db5-f038-11e3-a14a-0200c0a80ad8:76793-77026
Executed_Gtid_Set: 21ad8db5-f038-11e3-a14a-0200c0a80ad8:1-77025

Retrieved_Gtid_Set项:记录了relay日志从Master获取了binlog日志的位置
Executed_Gtid_Set项:记录本机执行的binlog日志位置(如果是从机,包括Master的binlog日志位置和slave本身的binlog日志位置)

四、基于GTID复制增加新的slav

备份主MySQL数据,记录主gtid_executed—>将备份数据恢复到从数据目录—>设置从gtid_purged的值为主的gtid_executed值—>启动复制即可

  1. 使用mysqldump备份主数据
    mysqldump —all-databases —single-transaction —triggers —routines —host=127.0.0.1 —port=3306 —user=root —password=geekwolf > backup.sql
    亦可以使用xtrabackup也支持GTID:
    请参考:http://www.mysqlperformanceblog.com/2013/05/09/how-to-create-a-new-or-repair-a-broken-gtid-based-slave-with-percona-xtrabackup/
  2. 传到从MySQL,恢复数据
    由于新版本msqldump会记录并设置GTID_PURGED的值等于主的GTID_EXECUTED,所以只需要将sql导入到从库即可
  3. 启动主从复制

从库执行

1
2
mysql > CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PASSWORD=geekwolf', MASTER_PORT=3306, MASTER_AUTO_POSITION = 1;
mysql > START SLAVE;

五、基于GTID复制出错的解决办法

问题:

1
2
3
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

解决思路:

从复制跳过已经丢失的binlog,继续复制或者重新做主从(可以参考上面的操作)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
主MySQL:
mysql> show global variables like '%gtid_executed%';
+---------------+-----------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------+
| gtid_executed | 21ad8db5-f038-11e3-a14a-0200c0a80ad8:1-223937 |
+---------------+-----------------------------------------------+
1 row in set (0.00 sec)
从MySQL:
mysql> set global GTID_PURGED="21ad8db5-f038-11e3-a14a-0200c0a80ad8:1-223937";
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql> reset master;
Query OK, 0 rows affected (0.19 sec)
mysql> show global variables like 'GTID_EXECUTED';
+---------------+-----------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------+
| gtid_executed | |
+---------------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set global GTID_PURGED="21ad8db5-f038-11e3-a14a-0200c0a80ad8:1-223937";
Query OK, 0 rows affected (0.13 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G
[...]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[...]

注意事项: 使用基于GTID复制时,不需要再关心master_log_file和master_log_os,替代的是只需要知道master上的GTID,并且配置在从上即可;
记录GTID的有两个全局变量:gtid_executed和gtid_purged
与GTID复制相关的参数:
gtid

1
2
3
4
5
6
7
GTID_EXECUTED :表示已经在该实例上执行过的事务;执行RESET MASTER可以置空该参数;也可以设置GTID_NEXT执行一个空事务来影响GTID_EXECUTED
GTID_NEXT :是SESSION级别参数,表示下一个事务被执行使用的GTID(show variables like ‘gtid_%’;)
GTID_PURGED :表示被删除的binlog事务GTID,它是GTID_EXCUTED的子集,MySQL5.6.9,该参数无法被设置
GTID_OWENED :表示正在执行的事务的GTID以及对应的线程ID
如果设置MASTER_AUTO_POSITION = 1表示主从复制连接使用基于GTID的方式复制
CHANGE MASTER TO MASTER_HOST='192.168.10.216',MASTER_USER='rep',MASTER_PASSWORD='geekwolf',MASTER_AUTO_POSITION=1;

如果在GTID复制模式下想要使用基于文件的复制协议需要MASTER_AUTO_POSITION=0(至少指定其中MASTER_LOG_FILE、MASTER_LOG_POSITION一个)

1
CHANGE MASTER TO MASTER_HOST='192.168.10.216',MASTER_USER='rep',MASTER_PASSWORD='geekwolf',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=120,MASTER_AUTO_POSITION=0;

参考

MYSQL 5.6 GTID-based Replication
MYSQL 5.6 GTID模式下手工删除日志导致备库数据丢失
How to create a new (or repair a broken) GTID based slave with Percona XtraBackup

坚持原创分享,您的支持将鼓励我继续创作