使用GTID主从同步阿里云RDS到自建Mysql

基础概念

传统的MYSQL主从就是主库每做一个操作会在binlog上做一个position，每做一个event就在binlog做一个起始编号、一个终止编号。然后主库把binlog传递给从库，然后从库根据这个binlog的pos值就按照顺序做一样的操作，达到两个数据库保持一致的目的。

GTID不用这个position的方式，而是用了全局事物标识，这个标识的格式是source_id:transaction_id，如3E11FA47-71CA-11E1-9E33-C80AA9429562:23：

source_id即是server_uuid，在第一次启动时生成(函数 generate_server_uuid)，并持久化到DATADIR/auto.cnf文件里；
transaction_id是顺序化的序列号(sequence number)，在每台 MySQL 服务器上都是从 1 开始自增长的序列，是事务的唯一标识；

它的主从过程是这样的：主库更新数据时，会在事务前产生GTID，连通sql记录到binlog日志中。从库的i/o线程将变更的binlog写入到relay log中,读取值是根据gitd_next变量，告诉从库下一个执行哪个GTID。从库的sql线程从relay log中获取GTID，然后对比从库的的binlog是否有记录。如果有记录，说明该GTID的事务已经执行，从库会忽略。如果没有记录，从库就会从relay log中执行该GTID的事务，并记录到从库binlog。在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有二级索引就用全部扫描。

也就是说，无论是级联情况，还是一主多从情况，都可以通过GTID自动找点儿，而无需像之前那样通过binlog和binlog_position找点儿了。更多GTID 原理的知识可以去查看：https://keithlan.github.io/2016/06/23/gtid/ 。

注意！由于RDS for MySQL 5.6版本引入了GTID特性，因此要求应用不能够在事务中创建和删除临时表。那么如何对待临时表？

将SQL语句里的temporary table语句更改为table，使用普通表替代临时表，规避这个问题；
修改代码，将临时表的创建和删除操作放在事务外，并且保证会话的参数autocommit=1；

源RDS地址（内网）：rm-bp12k8yne0909uv68.mysql.rds.aliyuncs.com
目标ECS地址（内网）：172.31.0.67

工具准备

首先是现在ECS里安装mysql 5.6。由于centos 7默认安装的数据库是mariabd，所以使用tar.gz包安装。

wget -c "http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz"
tar zxvf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
cd /usr/local && ln -s mysql-5.6.33-linux-glibc2.5-x86_64 mysql
mkdir -p /home/mysql/data
chown -R mysql.mysql /home/mysql && chmod -R o=--- /home/mysql		# chmod -o是其他以外的人

使用service mysqld start启动，再用mysql -u root -p登录进去，修改掉root的空密码：

1
2
3

mysql> update mysql.user set password=password('123123123') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

要进行GTID，首先要确认阿里云RDS作为主库是否支持GTID，如图：

可见阿里云那一段是没问题的。

然后安装xtrabackup，由于我们是mysql 5.6，所以xtrabackup的版本是2.3：

1
2

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.5/binary/redhat/7/x86_64/percona-xtrabackup-2.3.5-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-2.3.2-1.el7.x86_64.rpm

最后，检查RDS白名单，确保ECS与RDS可通。

具体操作

登录阿里云的RDS控制台，选择对应的RDS之后，点击左侧栏的备份恢复，将最近的一次备份下载下来：

由于是内网，直接就复制内网地址，在ECS上执行如下操作：

1 2	wget -c '<数据备份文件外网下载地址>' -O /home/mysql/shopoms.tar.gz #注意引号一定要带！ tar -izxvf shopoms.tar.gz -C /home/mysql/shopoms #解压缩

解压缩完毕之后，打开/opt/shopoms看一下文件组成：

用xtrabackup工具恢复解压好的备份文件，于是innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data。稍等一会，解压缩成功，备份一下backup-my.cnf，改名叫slave-oms.cnf：

[mysqld]
# from rds backup-my.cnf
innodb_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=524288000
innodb_undo_directory=.
innodb_undo_tablespaces=0
# need for slave 
server-id = 666 	# 从实例的id,不能与master的id相同
port=3306
master-info-repository = file
relay-log-info_repository = file
binlog-format = ROW		# 这里必须是ROW
skip-grant-tables   ##忽略mysql权限问题，免密码直接登录
# GTID
gtid-mode = ON		# 打开了gtid模式
enforce-gtid-consistency = true
log-bin = hostname-bin
relay-log = /tmp/relay.log	#注意这个文件要被mysql用户读取到，如果这里不设定对，就会有Slave failed to initialize relay log info structure from the repository的错误
expire_logs_days=10 # 控制binlog日志文件保留时间
max_binlog_size=100M             
replicate-ignore-db=mysql   # 不需要同步的库  
log-slave-updates=1
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
socket = /tmp/mysql.sock

同时chown -R mysql:mysql /home/mysql/data修改文件属主，并确定文件所属为MySQL用户。然后执行/usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/data/slave-oms.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/data &启动mysql。如图：

然后使用root账号登陆该mysql：

添加主库信息：

mysql> change master to
    -> master_host='rm-bp12k8yne0909uv68.mysql.rds.aliyuncs.com',
    -> master_user='oms',master_port=3306,master_password='对应密码',
    -> master_auto_position=1;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

原因是由于RDS的备份文件中包含了RDS的主从复制关系，需要把这些主从复制关系清理掉，清理方法：

mysql> truncate table mysql.slave_relay_log_info;
Query OK, 0 rows affected (0.14 sec)

mysql> truncate table mysql.slave_master_info;
Query OK, 0 rows affected (0.03 sec)

重启mysql，注意！恢复完成的mysql.user是不包含rds中创建的用户的，需要重新创建：

mysql> delete from mysql.db where user<>'root' and char_length(user)>0;
Query OK, 10 rows affected (0.02 sec)

mysql> delete from mysql.tables_priv where user<>'root' and char_length(user)>0;
Query OK, 42 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='rm-bp12k8yne0909uv68.mysql.rds.aliyuncs.com',
    -> master_user='oms',master_port=3306,master_password='对应密码',
    -> master_auto_position=1;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

这里是因为在/home/mysql/data下有一个master.info，需要先删掉他，然后重新执行change master to ...就可以了，这期间不用停止mysql进程。

然后就是启动同步：

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

使用show slave status\G查看一下主从状态：

出现1236错误

但是此时大概率应该出现了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.'这个错误，如图：

这里要先说两个概念：

gtid_executed(global)：MySQL数据库已经执行过的Gtid事务，处于内存中。show master status/show slave status中的Executed_Gtid_Set也取自这里；
gtid_purged(global)：由于binlog文件的删除(如purge binary logs或者超过expire_logs_days设置)已经丢失的Gtid事务，它是gtid_executed的子集；

明白了上面两个概念，我们再来回溯一下这个过程：

我们用RDS做的备份集来恢复出一个自建ECS的mysql实例；
启动主从同步；
从库在开始同步前,主库会依靠GTID来确认从库在开始同步以后, 能够把每一个主库上执行过的事务(包括slave的SQL Thread)都复现一次,最终保持和主库完全一致;
但是从库指定的GTID_PURGED并不等于主库的GTID_EXECUTED，为什么呢?因为主库多出来的这些gtid已经purge（丢失）掉了或者是新的从库未指定过GTID_PURGED；
出现1236的故障报错；

此时我们分别进入到主从mysql里，执行show global variables like '%gtid%'\G，查询一下gtid_purged，如图：

我们先在主库上show global variables like 'GTID_EXECUTED';看一下效果：

mysql> show global variables like 'GTID_EXECUTED';
+---------------+------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                          |
+---------------+------------------------------------------------------------------------------------------------+
| gtid_executed | 18e99fbf-5f38-11e8-8a6f-6c92bf21d8b5:1-1519695, 4ccd3917-5f58-11e8-8b41-7cd30adfe86a:1-9717278 |
+---------------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在从库上执行如下的内容：

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> reset master;		# 这一步是为了把GTID_EXECUTED清空
Query OK, 0 rows affected (0.03 sec)

mysql> show global variables like 'GTID_EXECUTED';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global GTID_PURGED='18e99fbf-5f38-11e8-8a6f-6c92bf21d8b5:1-1519695,4ccd3917-5f58-11e8-8b41-7cd30adfe86a:1-9717278';	# 把主库的GTID_EXECUTED值填进来
Query OK, 0 rows affected (0.03 sec)

mysql> show global variables like 'GTID_EXECUTED';                                              
+---------------+------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                          |
+---------------+------------------------------------------------------------------------------------------------+
| gtid_executed | 18e99fbf-5f38-11e8-8a6f-6c92bf21d8b5:1-1519695, 4ccd3917-5f58-11e8-8b41-7cd30adfe86a:1-9717278 |
+---------------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)