mysql 主从配置 维护_MySQL主从复制日常管理维护篇

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

1、查看从库状态

MySQL [(none)]>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master to send eventMaster_Host:172.31.22.29Master_User: chaofeng

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos:154Relay_Log_File: ip-172-31-26-133-relay-bin.000016Relay_Log_Pos:367Relay_Master_Log_File: mysql-bin.000002Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:154Relay_Log_Space:751Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1Master_UUID: ce15f67e-0ccc-11e9-9e0a-0af74ce261dc

Master_Info_File:/data/data_mysql/master.info

SQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingformore updates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0Replicate_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.01sec)

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.01sec)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.00sec)mysql> set @@auto_increment_offset=2;

Query OK,0 rows affected (0.00sec)

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 intauto_increment,primary key(id));

Query OK,0 rows affected (0.02sec)

mysql> insert into haha values (null),(null),(null);

Query OK,3 rows affected (0.01sec)

Records:3 Duplicates: 0 Warnings: 0mysql> select * fromhaha;+----+

| id |

+----+

| 2 |

| 4 |

| 6 |

+----+

3 rows in set (0.00 sec)

再来设置一个偏移量：

mysql> set @@auto_increment_increment=10;

Query OK,0 rows affected (0.00sec)mysql> set @@auto_increment_offset=5;

Query OK,0 rows affected (0.00sec)

mysql> show variables like 'auto_inc%';+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 10 |

| auto_increment_offset | 5 |

+--------------------------+-------+

2 rows in set (0.00sec)

mysql> insert into haha values (null),(null);

Query OK,2 rows affected (0.00sec)

Records:2 Duplicates: 0 Warnings: 0mysql> select * fromhaha;+----+

| 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:2User: system user

Host:

db: NULL

Command: Connect

Time:130State: Slave has read all relay log; waitingformore updates

Info: NULL

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

7、