RDS数据库与自建库的gtid主从同步

一、在centos7上部署MySQL数据库

1、从MYSQL官网下载安装文件

wget

http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

2、卸载mariadb

rpm -qa | grep mariadb #查询已安装的mariadb rpm

rpm -e --nodeps mariadb-libs-xxxxx.x86_64 #强制卸载

3、安装MySQL

rpm -ivh mysql-community-release-el7-5.noarch.rpm

yum install mysql-community-server

4、启动MySQL

systemctl start mysqld.service

5、停止MySQL

systemctl stop mysqld.service

二、修改从库配置文件/etc/my.cnf

character-set-server=utf8

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

wait_timeout=2880000

interactive_timeout = 2880000

max_allowed_packet = 100M

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

server-id = 10000

gtid_mode = on

enforce_gtid_consistency = 1

master-info-repository = file

relay-log-info_repository = file

log-bin = mysqlslave-bin

relay-log = mysqlslave-relay-bin

expire_logs_days = 30

log-slave-updates = 1

binlog_format = row

replicate-do-db = db1

replicate-do-db = db2

replicate-do-db = db3

replicate-ignore-db = mysql

replicate-ignore-db = information_schema

replicate-ignore-db = performance_schema

slave-skip-errors = 1007,1008,1053,1062,1213,1158,1159

三、重启从库

systemctl restart mysqld.service

四、从RDS的数据库导数据出来

mysqldump -u db1_user -pdb1_password -hmasterhost.mysql.rds.aliyuncs.com> /tmp/db1.sql

mysqldump -u db2_user -pdb2_password -hmasterhost.mysql.rds.aliyuncs.com> /tmp/db2.sql

mysqldump -u db3_user -pdb3_password -hmasterhost.mysql.rds.aliyuncs.com> /tmp/db3.sql

五、主库查询master gtid_purged

mysql>show global variables like '%gtid%';

找到gtid_purged的值，第七步要用到

六、将RDS导出的数据库，导入到从库

mysql -uroot -p root_password < /tmp/db1.sql

mysql -uroot -p root_password < /tmp/db2.sql

mysql -uroot -p root_password < /tmp/db3.sql

七、进入从库，设置同步信息

mysql>reset master;

mysql>set @@global.gtid_purged='主库的GITD值';

mysql>change master to master_host = 'masterhost.mysql.rds.aliyuncs.com', master_port = 3306, master_user = 'repl', master_password='repl_password', master_auto_position = 1;

mysql>start slave;

八、查询返回信息

mysql>show slave status G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

两个都是yes，表示同步成功。