mysql主节点down机后如何恢复操作

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;