MySQL主从复制日常管理维护篇

r囧r小猫 2022-01-08 01:17 244阅读 0赞

日常工作中，我们需要经常进行一些监控和管理维护工作，以便能及时发现一些复制中的问题，并尽快解决，以此来保证复制能够正常工作

**1、查看从库状态**

MySQL [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.31.22.29
                      Master_User: chaofeng
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000002
              Read_Master_Log_Pos: 154
                   Relay_Log_File: ip-172-31-26-133-relay-bin.000016
                    Relay_Log_Pos: 367
            Relay_Master_Log_File: mysql-bin.000002
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 154
                  Relay_Log_Space: 751
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: ce15f67e-0ccc-11e9-9e0a-0af74ce261dc
                 Master_Info_File: /data/data_mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

上面所有的信息中，我们只需要关注上面背景为蓝色的两行信息，即“SQL\_IO\_Running”和"Slave\_SQL\_Running"这两个状态是否为“YES”，必须两个都是“YES”，说明主从复制没有问题。只要其中有一个不是“YES”，那么主从复制就有问题。

**2、主从库同步维护**

这个是说主从复制中由于某些原因从库的数据总是滞后于主库，这时候就需要我们手动定期的进行主从库的数据同步了，使得主从数据差距能够减到最小。常用的方法就是：在负载较低时暂时阻塞主数据库的更新，强制主从数据库更新同步。

方法步骤：

1、在主库上执行读锁操作（这是会阻塞主数据库的所有更新操作）

mysql> FLUSH TABLES WITH READ LOCK;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show  master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000002 |      154 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

记住上面的这两个数值，file的二进制文件名字，Position的数值154。这个数值是从库复制的目的坐标。

2、在从库上，执行MASTER\_POS\_WAIT函数

MySQL [(none)]> select MASTER_POS_WAIT('mysql-bin.000002','154');
    +-------------------------------------------+
    | MASTER_POS_WAIT('mysql-bin.000002','154') |
    +-------------------------------------------+
    |                                         0 |
    +-------------------------------------------+
    1 row in set (0.00 sec)

这个select语句会阻塞直到从库达到指定的日志文件和偏移量后，返回0，如果返回-1，则表示超时退出。

3、最后在主库上，执行解锁操作,使其能够进行读写操作。

mysql> UNLOCK TABLES;
    Query OK, 0 rows affected (0.00 sec)

**3、从库复制出错的处理**

某些情况下，会出现从库更新失败，这时，需要确定一下是否是从库与主库的表的不同造成的。如果不是的话，我们需要研究一下SQL\_SLAVE\_SKIP\_COUNTER这个变量的意义了。

**4、从库报错：log event entry exceeded max\_allowed\_packet的处理**

一般主从复制中，主库上的表的含有BLOG列或长字符串的时候，在从库上恢复数据时可能会报上面的这个错误。这是因为含有打文本的记录无法通过网络传输导致，解决的方法就是在主从库上增加max\_allowed\_packet参数的大小，这个参数默认值为1MB，我们可以根据实际情况来进行设置。

mysql> show variables like 'max_allowed_packet';
    +--------------------+----------+
    | Variable_name      | Value    |
    +--------------------+----------+
    | max_allowed_packet | 1047552 |
    +--------------------+----------+
    1 row in set (0.01 sec)
    
    mysql> set global max_allowed_packet=16777216;
    Query OK, 0 rows affected (0.00 sec)

尽量在my.cnf文件也设置好，下次启动可以及时生效。

**5、主主复制时的自增长变量冲突问题。**

双主架构多用于运维人员做维护等需要主从切换的场景，通过双主架构避免了重复搭建从库的麻烦。

不过主主复制的缺点就是如果主库的表采用自动增长变量，那么复制到从库的同一张表后很可能会引起主键冲突。因此我们就需要定制auto\_increment\_increment和auto\_increment\_offset的设置，保证多主之间不会有重复冲突。我们看一下这两个变量的作用：

mysql> set  @@auto_increment_increment=2;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set  @@auto_increment_offset=2;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 2     |
    | auto_increment_offset    | 2     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)

接下来我们看看结果：

mysql> create table haha ( id int auto_increment,primary key(id));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into haha values (null),(null),(null);
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from haha;
    +----+
    | id |
    +----+
    |  2 |
    |  4 |
    |  6 |
    +----+
    3 rows in set (0.00 sec)

再来设置一个偏移量：

mysql> set @@auto_increment_increment=10;
    Query OK, 0 rows affected (0.00 sec)
    mysql> set @@auto_increment_offset=5;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 10    |
    | auto_increment_offset    | 5     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)
    
    mysql> insert into haha values (null),(null);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from haha;
    +----+
    | id |
    +----+
    |  2 |
    |  4 |
    |  6 |
    | 15 |
    | 25 |
    +----+
    5 rows in set (0.00 sec)

从上面的例子可以看出来，我们通过这两个参数可以方便地设置不同的主库上的自动增长列的值的范围，这样子在这些数据复制到从库上我们可以有效地避免主键的重复。

在多主上，比如这里有两个master，我们可以这样子设置

Master1上：auto_increment_increment = 2,auto_increment_offset = 2;(2,4,6,8,10.....)
    Master2上：auto_increment_increment = 2,auto_increment_offset = 1;(1,3,5,7,9......)

大家记住一句：auto\_increment\_offset表示我们设置的序号是从哪个数值开始的。（这个数值最好不超过auto\_increment\_increment）。而auto\_increment\_increment表示我们自动增长的键依次增加的数值

6、查看从库的复制进度。

通过查看从库的复制进度，我们可以判断出来是否需要我们手工进行主从的同步工作等等。

我们主要是获取从库上的time值，这个值记录了从库当前执行的SQL时间戳与系统时间之间的差距，单位是秒。比如下面这个：

*************************** 2. row ***************************
         Id: 2
       User: system user
       Host: 
         db: NULL
    Command: Connect
       Time: 130
      State: Slave has read all relay log; waiting for more updates
       Info: NULL

由于MySQL复制的机制是执行主库传输过来的二进制日志，二进制日志中的每个语句通过设置时间戳来保证执行时间和顺序的正确性，所以每个语句执行之前都会设置时间戳，而通过查询这个进程的time就可以知道最后设置的时间戳和当前时间的差距。

7、

转载于:https://www.cnblogs.com/FengGeBlog/p/10233760.html