1 停机维护 (1) 先停止上层应用 (2) 检查backup和slave的中继日志是否已经完成了回放及gtid_executed保持一致 mysql> show slave status\G; Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates mysql> show global variables like "%gtid%"; +----------------------------------+---------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+---------------------------------------------------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 7b59201e-1696-11eb-ab21-000c29b9ac88:1-23, aa7489a2-1694-11eb-9b82-000c299c9831:1-891 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | 7b59201e-1696-11eb-ab21-000c29b9ac88:1-23, aa7489a2-1694-11eb-9b82-000c299c9831:1-891 | | session_track_gtids | OFF | +----------------------------------+---------------------------------------------------------------------------------------+ 8 rows in set (0.01 sec) (3) 关闭backup的keepalived # systemctl stop keepalived 2 backup库 (1) xtrabackup 官方下载地址: wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm (2) 安装 # yum install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm -y # xtrabackup -v (3) 使用xtrabackup在线备份 # mkdir -p /mysql_backup/ # innobackupex -uroot -p"mysql5.7@2020" --stream=tar /mysql_backup/ 2>/tmp/result_backup.txt |gzip >/mysql_backup/`date +%F_%H-%M-%S`.tar.gz # du -sh /mysql_backup/ (4) 将备份的主库发送到master库 # scp -rp /mysql_backup/*.tar.gz root@172.16.1.215:/root/ (5) 清除库的binlog和relay_log信息 mysql> stop slave; mysql> reset slave; mysql> reset master; mysql> show global variables like "%gtid%"; (6) 启动keepalived # systemctl start keepalived 3 原主库变从库操作 (1) 解压备份包 # cd /root/ # mkdir -p /root/mysql_data && cd /root/mysql_data # tar -xzf /root/*.tar.gz # ls -l /root/mysql_data/ (2) 回滚事务日志 # innobackupex --apply-log --redo-only /root/mysql_data/ # ls -l /root/mysql_data/ # systemctl stop mysqld # rm -rf /usr/local/mysql/data/ (3) 修改my.cnf文件 # vim /etc/my.cnf [mysqld] slave-skip-errors=1007,1022,1050,1062,1169 relay-log=/usr/local/mysql/data/relay-log max_relay_log_size=512M relay-log-purge=ON slave-parallel-type = LOGICAL_CLOCK slave-parallel-workers = 8 master-info-repository = TABLE relay-log-info-repository = TABLE (4) 还原库 # innobackupex --defaults-file=/etc/my.cnf --copy-back /root/mysql_data/ 2>/tmp/result_restore.txt (5) 启动数据库 # chown -R mysql.mysql /usr/local/mysql/ # systemctl start mysqld.service # cat /usr/local/mysql/data/auto.cnf [auto] server-uuid=bef5754b-9aaf-11eb-9902-000c2961b826 (6) 启动复制线程 mysql> reset slave; mysql> reset master; mysql> CHANGE MASTER TO MASTER_HOST=‘172.16.1.215‘, MASTER_PORT=3306, MASTER_USER=‘repl‘, MASTER_PASSWORD=‘repl@2020‘, MASTER_AUTO_POSITION=1; mysql> start slave; mysql> show slave status\G; (7) 启动keepalived # systemctl start keepalived 4 slave 库操作 mysql> stop slave; mysql> reset slave; mysql> reset master; mysql> show global variables like "%gtid%"; mysql> CHANGE MASTER TO MASTER_HOST=‘172.16.1.203‘, MASTER_PORT=3306, MASTER_USER=‘repl‘, MASTER_PASSWORD=‘repl@2020‘, MASTER_AUTO_POSITION=1; mysql> start slave; mysql> show slave status\G;