- RDS mysql版本為5.6.29 x86_64
1、下載數(shù)據(jù)備份登渣、binlog備份
- 內(nèi)網(wǎng)中轉(zhuǎn)摊求？數(shù)據(jù)量不大，直接下載
- 下載數(shù)據(jù)備份(外網(wǎng)下載鏈接)
wget -c "https://rdslog-st.oss-cn-shenzhen.aliyuncs.com/xxx" -O xxx.tar.gz
2索守、內(nèi)網(wǎng)測試機安裝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 mysqlmkdir -p /home/mysql/datachown -R mysql.mysql /home/mysql && chmod -R o=--- /home/mysql
3晕窑、恢復備份到內(nèi)網(wǎng)測試機
- 下載解壓腳本 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的主從復制關(guān)系卵佛，需要把這些主從復制關(guān)系清理掉杨赤，清理方法：
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中創(chuàng)建的用戶的，需要重新創(chuàng)建
- 重建用戶前先執(zhí)行一下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;
# 結(jié)果 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狀態(tài)
一開始 Seconds_Behind_Master 值會很大衙解，是因為使用的數(shù)據(jù)備份是前一天的阳柔，等它慢慢同步完就正常了
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>
參考文檔：
RDS for MySQL 備份文件恢復到自建數(shù)據(jù)庫
如何將RDS的數(shù)據(jù)同步到本地自建數(shù)據(jù)庫
MySQL 5.6版本GTID復制異常處理一例
