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，表示同步成功。