阿里云RDS同步到本地自建mysql数据库从库
- RDS mysql版本为5.6.29 x86_64
1、下载数据备份、binlog备份
- 内网中转？数据量不大，直接下载
- 下载数据备份(外网下载链接)
wget -c "https://rdslog-st.oss-cn-shenzhen.aliyuncs.com/xxx" -O xxx.tar.gz
2、内网测试机安装mysql5.6
-
wget -c "http://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz" tar xf 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
3、恢复备份到内网测试机
- 下载解压脚本 rds_backup_extract.sh
- 下载恢复工具 Percona-XtraBackup
-
sh rds_backup_extract.sh -f hins1438123_xtra_20160912032142.tar.gz -C /home/mysql/data /home/backup/percona-xtrabackup-2.4.4-Linux-x86_64/bin/innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
- 编辑mysql配置文件，开启gtid
cd /home/mysql/data cp backup-my.cnf slave-my.cnf vim slave-my.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 = 17 master-info-repository = file relay-log-info_repository = file binlog-format = ROW gtid-mode = ON enforce-gtid-consistency = true log-bin = hostname-bin relay-log = hostname-relay-bin log-slave-updates
- 启动mysqld
/usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/data/slave-my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/data &
- 清除slave信息
sql> reset slave; # 报错 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的主从复制关系，需要把这些主从复制关系清理掉，清理方法： sql> truncate table mysql.slave_relay_log_info; sql> truncate table mysql.slave_master_info; # 然后重启服务 /usr/local/mysql/bin/mysqladmin -S /tmp/mysql.sock -uroot shutdown
- 恢复完成的mysql.user 是不包含rds中创建的用户的，需要重新创建
- 重建用户前先执行一下sql
delete from mysql.db where user<>'root' and char_length(user)>0; delete from mysql.tables_priv where user<>'root' and char_length(user)>0; flush privileges;
4、配置主从同步
- 尝试
master_auto_position=1让主从自行寻找开始复制的pos
sql> change master to master_host=’rdsxfjwiofjwofe.mysql.rds.aliyuncs.com’, master_user=’user_name’,master_port=3306,master_password=’xxxxx’, master_auto_position=1; # 结果 start slave 报错 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.'
- 上面的报错有两种解决方法：
- 设置 master_auto_position=0 并指定 binlog 文件和 pos 位置开始复制
# binlog pos 可以从恢复的备份文件中获得
cat xtrabackup_slave_filename_info
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000348', MASTER_LOG_POS='5569205'
# 配置复制 sql> change master to master_host=’rdsxdjsfiwfojief.mysql.rds.aliyuncs.com’, master_user=’user_name’,master_port=3306,master_password=’xxxxx’, master_auto_position=0,master_log_file='mysql-bin.000348',master_log_pos=5569205; sql> start slave;
# 可以正常开始复制，但重新设置 master_auto_position=1 又会报同样的错
* 手工修改 GTID_PURGED 值
# 同样的备份文件里有信息
cat /home/mysql/data/xtrabackup_slave_info SET GLOBAL gtid_purged='016ced19-9d47-11e5-8f1b-1051721bd1ff:1-1875724, 068bb241-3474-11e6-a8e5-8038bc0c695e:1-1858400, 0e1a732e-9d47-11e5-8f1b-d89d672b932c:1-4'; CHANGE MASTER TO MASTER_AUTO_POSITION=1
# 配置主从
sql> change master to
master_host=’rdsxfslfiewfiewfji.mysql.rds.aliyuncs.com’,master_user=’user_name’,master_port=3306,master_password=’xxxxx’, master_auto_position=1; sql> start slave; # 报错 sql> stop slave; sql> reset master; sql> SET GLOBAL gtid_purged='016ced19-9d47-11e5-8f1b-1051721bd1ff:1-1875724, 068bb241-3474-11e6-a8e5-8038bc0c695e:1-1858400, 0e1a732e-9d47-11e5-8f1b-d89d672b932c:1-4'; sql> start slave;
5、最后看下slave状态
一开始 Seconds_Behind_Master 值会很大，是因为使用的数据备份是前一天的，等它慢慢同步完就正常了
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: rdsxxxxxxxxxxxxx.mysql.rds.aliyuncs.com Master_User: user_name Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000350 Read_Master_Log_Pos: 1717466 Relay_Log_File: hostname-relay-bin.000006 Relay_Log_Pos: 1717596 Relay_Master_Log_File: mysql-bin.000350 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: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1717466 Relay_Log_Space: 1717886 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: 2224019206 Master_UUID: 068bb241-3474-11e6-a8e5-8038bc0c695e Master_Info_File: /home/mysql/data/master.info SQL_Delay: 0 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: 068bb241-3474-11e6-a8e5-8038bc0c695e:1858401-1875134 Executed_Gtid_Set: 016ced19-9d47-11e5-8f1b-1051721bd1ff:1-1875724, 068bb241-3474-11e6-a8e5-8038bc0c695e:1-1875134, 0e1a732e-9d47-11e5-8f1b-d89d672b932c:1-4 Auto_Position: 1 1 row in set (0.00 sec) ERROR: No query specified mysql>
作者：Eraz
链接：https://www.jianshu.com/p/4582db124579
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。
