RDS备份恢复到本地ECS服务器，搭建主从

曾经终败给现在 2022-06-08 07:47 160阅读 0赞

一、恢复云数据库MySQL的备份文件到自建数据库

参考阿里文档
    https://help.aliyun.com/knowledge_detail/41817.html
    a.数据库的版本至少为5.6.16及以上(大于等于RDS版本)，我用的是5.6.37
    b.我使用percona-xtrabackup-2.3.8
    c.下载rds_backup_extract.sh
    
    1.下载RDS备份
    wget -c '<数据备份文件外网下载地址>' -O <自定义文件名>.tar.gz
    2.解压备份至data目录
    bash rds_backup_extract.sh -f <数据备份文件名>.tar.gz -C /home/mysql/data
    3.应用日志
    innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
    4.修改配置文件my.cnf
    innodb_checksum_algorithm=innodb
    server-id             = 152053308
    master-info-repository=file
    relay-log-info_repository=file
    gtid_mode=on
    enforce-gtid-consistency=on
    log_slave_updates
    binlog_format = row
    replicate-wild-ignore-table     = mysql.%
    replicate-wild-ignore-table     = performance_schema.%
    5.启动MySQL
    /usr/local/mysql/bin/mysqld --defaults-file=/data/apps/mysql/mysql3308/my.cnf &
    小版本升级
    /usr/local/mysql/bin/mysql_upgrade -uroot -S /data/apps/mysql/mysql3308/data/mysql.sock

二、搭建主从

备份文件中的 xtrabackup_slave_info 文件是我们将要执行的脚本
    [root@]# more xtrabackup_slave_info
    SET GLOBAL gtid_purged='0c0e0c92-deb5-11e6-bf17-7cd30abeb86a:1-16282080, 29ed6371-deb5-11e6-bf18-7cd30abeb14e:1-21992780';
    CHANGE MASTER TO MASTER_AUTO_POSITION=1
    
    
    mysql> SET GLOBAL gtid_purged='0c0e0c92-deb5-11e6-bf17-7cd30abeb86a:1-16282080, 29ed6371-deb5-11e6-bf18-7cd30abeb14e:1-21992780';
    ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
    mysql> reset master;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> SET GLOBAL gtid_purged='0c0e0c92-deb5-11e6-bf17-7cd30abeb86a:1-16282080, 29ed6371-deb5-11e6-bf18-7cd30abeb14e:1-21992780';
    Query OK, 0 rows affected (0.01 sec)
    
    
    清理残留数据
    truncate table  mysql.slave_relay_log_info;
    truncate table  mysql.slave_master_info;
    truncate table  mysql.slave_worker_info;
    
    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;
    
    主库创建备份账户
    GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*  TO 'repl'@'10.%' IDENTIFIED BY 'xxx';
    
    从库执行slave命令
    change master to master_host='aaa.mysql.rds.aliyuncs.com',master_port=3306, master_user='repl', master_password='xxx', master_auto_position=1;
    验证
    show slave status\G