生产环境修复mysql主从失败

1.从主服务器备份数据

[mysql@db1-Z01 data]$ mkdir /data/mysql0114
[mysql@db1-Z01 data]$ /data/tool/percona-xtrabackup-2.4.7-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --user=yidong --password=ZTE_s0ft --socket=/data/mysql/mysql.sock  --slave-info  --no-timestamp /data/mysql0114
--no-timestamp：不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹
--slave-info：对slave进行备份的时候使用，打印出master的名字和binlog pos，同样将这些信息以change master的命令写入xtrabackup_slave_info文件

......
210114 14:52:23 Executing UNLOCK TABLES
210114 14:52:23 All tables unlocked
210114 14:52:23 [00] Copying ib_buffer_pool to /data/mysql0114/ib_buffer_pool
210114 14:52:23 [00]        ...done
210114 14:52:23 Backup created in directory '/data/mysql0114/'
MySQL binlog position: filename 'bin-log.000091', position '463762'
210114 14:52:23 [00] Writing backup-my.cnf
210114 14:52:23 [00]        ...done
210114 14:52:23 [00] Writing xtrabackup_info
210114 14:52:23 [00]        ...done
xtrabackup: Transaction log of lsn (23305405708) to (23305405717) was copied.
210114 14:52:23 completed OK

2.还原mysql备份

[mysql@db1-Z01 ~]$ /data/tool/percona-xtrabackup-2.4.7-Linux-x86_64/bin/innobackupex --use-memory=1024m --apply-log  /data/mysql0114/
......
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 23305405708
InnoDB: Doing recovery: scanned up to log sequence number 23305405717 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 23305405717 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 463762, file name bin-log.000091
InnoDB: xtrabackup: Last MySQL binlog file position 463762, file name bin-log.000091

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 23305405726
InnoDB: Number of pools: 1
210114 15:00:24 completed OK!

3.传送还原后的包到从服务器

[mysql@db1-Z01 data]$ tar czf mysql0114.tar.gz mysql0114
[mysql@db1-Z01 data]$ scp mysql0114.tar.gz root@172.30.236.14:/data/

4.恢复从服务器

[root@db2-Z01 mysql]# /etc/init.d/mysql stop
[root@db2-Z01 mysql]# mv /data/mysql/data /data/mysqldata20200114
[root@db2-Z01 mysql]# mkdir /data/mysql/data
[root@ftp1-G05 data]# tar zxf mysql0114.tar.gz
[root@db2-Z01 mysql]# /data/tool/percona-xtrabackup-2.4.7-Linux-x86_64/bin/innobackupex --defaults-file=/etc/my.cnf --use-memory=1024m --copy-back /data/mysql0114/
[root@db2-Z01 mysql]# chown -R mysql.mysql /data/mysql
[root@ftp2-G05 mysql]# /etc/init.d/mysqld start
[root@ftp2-G05 mysql]# ss -anptl | grep 3306
LISTEN     0      128         :::3306                    :::*                   users:(("mysqld",pid=51178,fd=25))

5.从库进行主从同步

#查看binlog值
[root@db2-Z01 mysql]# cat /data/mysql0114/xtrabackup_binlog_info
mysql-bin.000006 732
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.10',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=732;
mysql> start slave;

6.重做主从

#查看xtrabackup_slave_info文件内容，这个内容就是为搭建从库时需要change master to的参数
[mysql@db1-Z01 mysql0114]$ cat xtrabackup_binlog_info
bin-log.000091  463762
[mysql@db1-Z01 mysql0114]$ mysql -uyundiao -ppadjbq@ASBJD
mysql> CHANGE MASTER TO MASTER_HOST='10.0.22.97',MASTER_USER='yundiao', MASTER_PASSWORD='ZTE_s0ft',MASTER_LOG_FILE='bin-log.003404', MASTER_LOG_POS=279459760;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 10.0.22.97
                  Master_User: yundiao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.003405
          Read_Master_Log_Pos: 680509421
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 243549267
        Relay_Master_Log_File: bin-log.003404
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes