重启复制

验证从站准备工作是否仍然满足；

https://dev.mysql.com/doc/refman/8.0/en/replication-setup-slaves.html

传统/无 GTID

Slave 将依靠二进制日志文件及其偏移量从正确的位置重新启动复制。

slave:# /home/restore # grep "Last MySQL binlog" 2019-03-29_11-39-04/meta/MEB_2019-03-29.11-39-04_copy_back_cmprs_img_to_datadir.log
190329 11:39:43 PCR1    INFO: Last MySQL binlog file position 0 381892696, file name binlog.000001
slave:# /home/restore # mysql
mysql> change master to master_host='server',master_log_file='binlog.000001', master_log_pos=381892696;
Query OK, 0 rows affected, 1 warning (0.00 sec)

使用从 MEB 还原日志中找到的值来配置复制。

GTID

执行以下语句来重置二进制日志：

mysql> RESET MASTER;

并执行以下语句来停止二进制日志记录：

mysql> SET sql_log_bin=0;

当使用GTID特性的服务器进行备份时，mysqlbackup会生成一个名为backup_gtid_executed.sql的文件，该文件可以在新从服务器的restored data目录中找到。
该文件包含一条 SQL 语句，用于在从站上设置 GTID_PURGED 配置选项：

# On a new slave, issue the following command if GTIDs are enabled:
SET @@GLOBAL.GTID_PURGED='f65db8e2-0e1a-11e5-a980-080027755380:1-3';

它还包含一个注释掉的 CHANGE MASTER TO 语句，用于初始化从站：

# Use the following command if you want to use the GTID handshake protocol:
# CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

取消对命令的注释并向其添加任何所需的连接和身份验证参数（例如，MASTER_HOST、MASTER_USER、MASTER_PASSWORD 和 MASTER_PORT）：

# Use the following command if you want to use the GTID handshake protocol:
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='muser', MASTER_PASSWORD='mpass', MASTER_PORT=18675, MASTER_AUTO_POSITION = 1;

用mysql客户端执行文件：

mysql> source /path-to-backup_gtid_executed.sql/backup_gtid_executed.sql

启动从属mysql

mysql> start slave user='repl' password='repl';
Query OK, 0 rows affected, 1 warning (0.00 sec)

恢复备份

slave:# /home/restore # /usr/bin/mysqlbackup --backup-dir=/home/restore/ --with-timestamp --uncompress --backup-image=/home/restore/backup.img copy-back-and-apply-log
...
190329 11:39:43 PCR1    INFO: We were able to parse ibbackup_logfile up to lsn 30670310615.
190329 11:39:43 PCR1    INFO: Last MySQL binlog file position 0 381892696, file name binlog.000001
190329 11:39:43 PCR1    INFO: The first data file is '/var/lib/mysql/ibdata1' and the new created log files are at '/var/lib/mysql'
190329 11:39:43 MAIN    INFO: No Keyring file to process.
190329 11:39:43 MAIN    INFO: Apply-log operation completed successfully.
190329 11:39:43 MAIN    INFO: Full Backup has been restored successfully.
mysqlbackup completed OK! with 4 warnings
slave:# /home/restore # chown mysql -R /var/lib/mysql

启动新的从服务器。

验证从属mysql

mysql> show slave status\G
*** 1. row ***
               Slave_IO_State: Waiting for master to send event
                  Master_Host: server
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 510350800
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 16526902
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

复制到从站

server:# /6/BACKUPS/MySQLBACKUPS # scp 2019-03-25_04-00-01/backup.img slave.example.org:/home/restore/
root@slave.example.org's password:
backup.img
100% 1450MB  85.3MB/s   00:17
server:# /6/BACKUPS/MySQLBACKUPS #

但是，环境/操作系统允许将备份复制到 SLAVE。

问题

复制从站无法继续从其主站复制。
一个这样的例子是，在停止很长时间（或者比预期的时间更长）之后，主服务器上所需的二进制日志已被清除。

[ERROR] [MY-013114] [Repl] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. To find the missing transactions, see the master's error log or the bananaal for GTID_SUBTRACT.', Error_code: MY-013114

解决方案

Slave 无法复制的可能原因有很多。
例如：

• Slave 无法访问 Master 上所需的文件，可能是由于 expire_logs_days 或者类似原因。
• 奴隶经历过腐败。
• DML 已在 Slave 上执行。

定期备份计划是企业环境所必需的。
下面的示例假设按照以下时间表每晚充满。
但是，它可以是每周完整 + 增量，但是，恢复时间会按预期增加。

示例备份计划

server:# /6/BACKUPS/MySQLBACKUPS # ls -lR 2019-03-25_04-00-01
2019-03-25_04-00-01:
total 1485296
-rw-r--r-- 1 backups backups 1520905049 Mar 25 04:01 backup.img
-rw-r--r-- 1 backups backups        356 Mar 25 04:00 backup-my.cnf
drwxr-x--- 2 backups backups         10 Mar 25 04:01 datadir
drwxr-x--- 2 backups backups       4096 Mar 25 04:01 meta
-rw-r--r-- 1 backups backups      16611 Mar 25 04:01 server-all.cnf
-rw-r--r-- 1 backups backups       5260 Mar 25 04:01 server-my.cnf
2019-03-25_04-00-01/datadir:
total 0
2019-03-25_04-00-01/meta:
total 316
-rw-r--r-- 1 backups backups 219455 Mar 25 04:01 backup_content.xml
-rw-r--r-- 1 backups backups   6366 Mar 25 04:00 backup_create.xml
-rw-r--r-- 1 backups backups    811 Mar 25 04:01 backup_variables.txt
-rw-r--r-- 1 backups backups  59996 Mar 25 04:01 image_files.xml
-rw-r--r-- 1 backups backups  26034 Mar 25 04:01 MEB_2019-03-25.04-00-01_compress_img_backup.log

server:# /6/BACKUPS/MySQLBACKUPS # id backups
uid=10007(backups) gid=1005(backups) groups=1005(backups),27(mysql)
server:# /6/BACKUPS/MySQLBACKUPS # crontab -l -u backups
0 4 * * 1 /home/backups/backup_mysql.sh
server:# /6/BACKUPS/MySQLBACKUPS # cat /home/backups/backup_mysql.sh
#!/bin/bash
mysqlbackup --defaults-file=~/.my.cnf --backup-dir=/6/BACKUPS/MySQLBACKUPS/ backup-to-image --with-timestamp --compress-level=9 --backup-image=backup.img
server:# /6/BACKUPS/MySQLBACKUPS #

这显示了主机上的专用备份用户，该用户位于 mysql 组中。
包含 datadir 下的文件和目录（以及任何其他数据库目录）都具有适当的组权限，允许 READ 和 WRITE，例如 770。

准备恢复

将现有的 Slave DATADIR 移动到安全位置，或者简单地删除现有的 DATADIR 内容。
确保数据库事先停止。

slave:# /home # ps -ef|grep mysqld
root     26336 26077  0 11:34 pts/0    00:00:00 grep --color=auto mysqld
slave:# /home # systemctl stop mysqld
slave:# /home # systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Fri 2019-03-29 11:31:38 AEDT; 2min 42s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 26199 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 26176 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 26199 (code=exited, status=0/SUCCESS)
   Status: "SERVER_SHUTTING_DOWN"
Mar 29 11:31:32 slave.example.org systemd[1]: Starting MySQL Server...
Mar 29 11:31:33 slave.example.org systemd[1]: Started MySQL Server.
Mar 29 11:31:35 slave.example.org systemd[1]: Stopping MySQL Server...
Mar 29 11:31:38 slave.example.org systemd[1]: Stopped MySQL Server.
slave:# /home #
slave:# /home # rm -rf /var/lib/mysql/*