MySQL主从复制案例–主库备份困局

##

MySQL主从复制案例–主库备份困局

1、场景描述

业务层使用了一套MySQL主从复制，但是主节点在东北，从节点在云南，且网络使用专线，且带宽仅为10MB，数据为TB级。不允许使用该网络传输数据库的备份或进行远程备份。

在主从复制搭建初期，在主库备份数据到硬盘，然后人肉带着备份集到从库，然后配置主从复制(基于binlog，没有使用GTID)。

现状：从库经常出现问题，各种各样的问题(表丢失，表损坏，数据异常等等)。急需解决问题。

思考：就像第一次搭建从库一样，人肉带数据到从库，然后重做不就完了吗？？？-----能不能解决问题 > 能。但是需要时间，人力以及钱。

那么怎么解决这个问题呢？

2、环境描述

主库IP：192.168.5.130

从库IP：192.168.5.140

检查从库信息

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|   1823306 |      | 3306 |   1813306 | d8d30f73-adf1-11ee-b6a7-000c298cdbc3 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

从库状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.130
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mydb-binlog.000004
          Read_Master_Log_Pos: 801
               Relay_Log_File: mydb-relay.000006
                Relay_Log_Pos: 1018
        Relay_Master_Log_File: mydb-binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试表数据

mysql> select * from test_db.t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

3、场景再现(从库损坏)

3.1、备份从库

至于为什么要备份从库，后面再讲.(如果库很大，可以考虑物理备份，此处仅演示)

mysqldump -uroot -proot -h127.0.0.1 -P3306 --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --events  --triggers -A > /mysql/backup/alldb.sql

3.2、主库写入数据

mysql> insert into t1 values(4);commit;flush logs;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(5);commit;flush logs;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(6);commit;flush logs;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

理论上：从库在没有问题的情况下，数据是和主库一致。总共6条。

3.3、停止从库并再次初始化

systemctl stop mysql
rm -fr /mysql/data/3306/data/*
rm -fr /mysql/log/3306/mydb-error.err
rm -fr /mysql/log/3306/binlog/*
rm -fr /mysql/log/3306/relaylog/*
mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data/

这里删除了从库数据目录中的所有文件，删除了binlog和relaylog。错误日志也一并删除，然后初始化数据库。

3.4、主库再次写入数据

mysql> insert into t1 values(7);commit;flush logs;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(8);commit;flush logs;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(9);commit;flush logs;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from test_db.t1;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.01 sec)

频繁的刷日志，写数据。此时主库的数据为9条。

4、恢复主从复制

4.1、导入从库备份的数据

[root@mydb02 ~]# mysql < /mysql/backup/alldb.sql

4.2、配置复制关系

这一步非常重要，如何找到正确的binlog和POS。这需要从mysql的参数说起。如果我们使用传统的方法去从库的备份文件找，如

[root@mydb02 ~]# head -100 /mysql/backup/alldb.sql |grep -i 'change master'
-- CHANGE MASTER TO MASTER_LOG_FILE='mydb-binlog.000003', MASTER_LOG_POS=154;

当你使用这个binlog和pos时，你也许(这里只是也许，我实验了几次，有时是成功，有时会出现错误，后面解释为什么会错误)会发现主从复制能成功搭建，双Yes也在，但是数据丢失

mysql> change master to master_host='192.168.5.130',master_user='repuser',master_password='repuser123',master_port=3306,master_log_file='mydb-binlog.000003',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.130
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mydb-binlog.000010
          Read_Master_Log_Pos: 154
               Relay_Log_File: mydb-relay.000004
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mydb-binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

你还能遇到如下错误：

 Slave_SQL_Running: No
                   Last_Errno: 1032
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mydb-binlog.000004, end_log_pos 770. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

错误的原因是：这个binlog和pos是从库的，而配置主从复制时需要配置主库的binlog和pos信息。

所以正确的binlog和pos在哪里呢？让我们回到 <4.1>。在我们导入数据库之后，我们会在mysql.slave_worker_info中发现如下信息，这才是我们需要的。

mysql> select * from slave_worker_info\G
*************************** 1. row ***************************
                        Id: 1
            Relay_log_name: /mysql/log/3306/relaylog/mydb-relay.000006
             Relay_log_pos: 1018
           Master_log_name: mydb-binlog.000004
            Master_log_pos: 801
 Checkpoint_relay_log_name: /mysql/log/3306/relaylog/mydb-relay.000006
  Checkpoint_relay_log_pos: 679
Checkpoint_master_log_name: mydb-binlog.000004
 Checkpoint_master_log_pos: 462
          Checkpoint_seqno: 0
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap:
              Channel_name:

这些信息是如何来的呢？有两个参数非常重要

master_info_repository=table
relay_log_info_repository=table

让我们继续配置主从关系

mysql> change master to master_host='192.168.5.130',master_user='repuser',master_password='repuser123',master_port=3306,master_log_file='mydb-binlog.000004',master_l
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.130
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mydb-binlog.000010
          Read_Master_Log_Pos: 154
               Relay_Log_File: mydb-relay.000014
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mydb-binlog.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4.3、检查数据

从库

mysql> select count(*) from test_db.t1;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

数据也是9条，和主库一致，随后可以在主库写入数据测试，数据一致，且双Yes.

主库再次写入数据

mysql> insert into test_db.t1 values(10);commit;flush logs;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from test_db.t1;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

从库再次检查

mysql> select count(*) from test_db.t1;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.130
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mydb-binlog.000013
          Read_Master_Log_Pos: 154
               Relay_Log_File: mydb-relay.000020
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mydb-binlog.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5、总结

从结果来看，完成了最初的需求，使用从库的备份恢复主从复制关系。

6、疑问：从库的备份周期是多少

从这个案例中我们知道，我们需要使用binlog和pos位置进行主从复制的再次配置，这意味着主库的binlog必须要存在，然后才能应用日志。所以从库备份的周期应该小于主库的日志过期时间。

mysql> show variables like 'expire%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.00 sec)

这就是为什么我们需要备份从库的原因。

7、其他（广告）

如果你想看这个案例的视频版，那么请移步B站，同时如果你对主从复制有兴趣，你可以在墨天伦上找到我的课程。基于MySQL8.0。

https://www.modb.pro/course/193