主从遇到的各种问题的详细解决方法

主从同步维护

由于特殊情况，主服务器更新频繁，从服务器由于各种原因，导致更新特别慢，这种情况，我们需要定期进行主从的数据同步维护，具体方法如下，在负载较低的时候暂时阻塞主数据库更新，强制主从数据库的更新同步

操作1、在master上执行以下语句：

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 | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000039 | 974      |              |                  | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec) 

记录 SHOW 语句的输出的日志名和偏移量，这些是从服务器复制的目的坐标。

2、在从服务器上，执行下面语句，其中 MASTER_POS_WAIT()函数的参数是前面步骤中得到的复制坐标值：

mysql> select MASTER_POS_WAIT('mysql-bin.000039','974'); 
+-------------------------------------------+ 
| MASTER_POS_WAIT('mysql-bin.000039','974') | 
+-------------------------------------------+ 
| 0                                         | 
+-------------------------------------------+ 
1 row in set (0.00 sec) 

这个 SELECT 语句会阻塞直到从服务器达到指定的日志文件和偏移量后，返回 0 ，如果返回 -1 ，则表示超时退出。查询返回 0 时，则从服务器与主服务器同步。

3、在主服务器上，执行下面的语句允许主服务器重新开始处理更新：

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

从服务器复制出错的处理

在某些情况下、会遇到从服务器复制出错的情况，首先，

1、确定是否是从服务器的表与主服务器的不同造成的。如果是表结构不同导致的，则修改从服务器的表与主服务器的相同，然后重新运行START SLAVE 语句。 

2、如果不是表结构不同导致的更新失败，则需要确认手动更新是否安全，然后忽视来自主服务器的更新失败的语句。跳过来自主服务器的语句的命令为SET GLOBAL 

SQL_SLAVE_SKIP_COUNTER = n，其中 n 的取值为 1 或者 2。如果来自主服务器的更新语句不使用 AUTO_INCREMENT或 LAST_INSERT_ID()，n 值应为 1，否则，值应为 2。原因是使用 AUTO_INCREMENT或 LAST_INSERT_ID()的语句需要从二进制日志中取两个事件。以下例子就是在从服务器端模拟跳过主服务器的两个更新语句的效果。 

1、   首先，在从服务器端先停止复制进程，并设置跳过两个语句：

mysql> select * from repl_test; 
+------+ 
| id   | 
+------+ 
| 1    | 
+------+ 
1	row in set (0.00 sec) 
 
mysql> stop slave; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> SET GLOBAL SQL_slave_SKIP_COUNTER = 2; Query OK, 0 rows affected (0.01 sec) 

2、 然后在主服务器端插入 3条记录：

mysql> select * from repl_test; 
+------+ 
| id   | 
+------+ 
| 1    | 
+------+ 
1	row in set (0.00 sec) 
 
mysql> insert into repl_test values(2); Query OK, 1 row affected (0.00 sec) 
 
mysql> insert into repl_test values(3); Query OK, 1 row affected (0.00 sec) 
 
mysql> insert into repl_test values(4); Query OK, 1 row affected (0.00 sec) 
 mysql> select * from repl_test; 
+------+ 
| id   | 
+------+ 
| 1    | 
| 2    | 
| 3    | 
| 4    | 
+------+ 
4 rows in set (0.00 sec) 

3、从服务器端启动复制进程，检查测试的表，发现首先插入的两条记录被跳过了，只执行了第3 条插入语句：

mysql> start slave; 
Query OK, 0 rows affected (0.00 sec) 
 mysql> select * from repl_test; 
+------+ 
| id   | 
+------+ 
| 1    | 
| 4    | 
+------+ 
2 rows in set (0.00 sec) 

多主复制时的自增长变量冲突问题

某些情况下，可能会需要使用多主复制（多台主服务器对一台从服务器）。这个时候，如果主服务器的表采用自动增长变量，那么复制到从服务器的同一张表后很可能会引起主键冲突，因为系统参数auto_increment_increment和 auto_increment_offset默认值为1，这样多台主服务器的自增变量列迟早会发生冲突。

在单主复制时，可以采用默认设置，不会有主键冲突发生。但是使用多主复制时，就需要定制auto_increment_increment 和 auto_increment_offset 的设置，保证多主之间复制到从数据库不会有重复冲突。比如，两个 master的情况可以按照以下设置。

Master1上：auto_increment_increment= 2，auto_increment_offset= 1；（1,3,5,7…序列）。

Master2上：auto_increment_increment= 2，auto_increment_offset= 0；（0,2,4,6…序列）。

下面的例子中创建了测试表ai，只有一个自增字段 i，我们开始演示修改这两个参数的效果。

首先在参数是默认值的时候，往表 ai 中插入记录，可以看到自动增长列的值是连续的。

mysql> CREATE TABLE ai ( 
    ->   i bigint(20) NOT NULL AUTO_INCREMENT, 
    ->   PRIMARY KEY (i) 
    -> ) ENGINE=MyISAM DEFAULT CHARSET=gbk; 
Query OK, 0 rows affected (0.03 sec) 
 mysql> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ 
| Variable_name            | Value | 
+--------------------------+-------+ 
| auto_increment_increment | 1     | 
| auto_increment_offset    | 1     | 
+--------------------------+-------+ 
2 rows in set (0.00 sec) 
 
mysql> insert into ai values(null),(null),(null); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 mysql> select * from ai; 
+---+ 
| i | 
+---+ 
| 1 | 
| 2 | 
| 3 | 

然后把参数 auto_increment_increment 的值修改成 10，再插入记录：

mysql> SET @@auto_increment_increment=10; 
Query OK, 0 rows affected (0.00 sec) 
 mysql> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ 
| Variable_name            | Value | 
+--------------------------+-------+ 
| auto_increment_increment | 10    | 
| auto_increment_offset    | 1     | 
+--------------------------+-------+ 
2 rows in set (0.00 sec) 
 
mysql> insert into ai values(null),(null),(null); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 mysql> select * from ai; 
+----+ | i  | +----+ | 1  | 
| 2  | 
| 3  | 
| 11 | 
| 21 | 
| 31 | +----+ 
6 rows in set (0.00 sec) 

从测试的结果上看，新插入的记录不再连续了，每次增加 10 。接着再修改 auto_increment_offset 参数，了解插入记录的效果：

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 ai values(null),(null),(null); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 mysql> select * from ai; 
+----+ | i  | +----+ | 1  | 
| 2  | 
| 3  | 
| 11 | 
| 21 | 
| 31 | 
| 35 | 
| 45 | 
| 55 | +----+ 
9 rows in set (0.00 sec) 

从插入记录的结果上可以了解，auto_increment_offset参数设置的是每次增加后的偏移量，也就是每次按照10 累加之后，还需要增加 5 个偏移量。

通过这两个参数可以方便地设置不同的主服务器上的自动增长列的值的范围，这样在这些数据复制到从服务器上时可以有效地避免主键的重复。

切换主从服务器

一个主数据库服务器 M，两个从数据库服务器 S1、S2 同时指向主数据库服务器 M。当主数据库 M 因为某种原因出现故障的时候，需要将其中的一个从数据库服务器（假设选中S1）切换成主数据库服务器，同时修改另一个从数据库（S2）的配置，使其指向新的主数据库（S1）。此外还需要通知应用修改主数据库的 IP地址，如果可能，将出现故障的主数据库（M）修复或者重置成新的从数据库。

下面详细介绍一下切换主从服务器的操作步骤。

1、首先要确保所有的从数据库都已经执行了 relay log 中的全部更新，在每个从服务器上，执行 STOP SLAVEIO_THREAD，然后检查 SHOWPROCESSLIST 的输出，直到看到状态是 Hasread all relay log，表示更新都执行完毕。

mysql> STOP SLAVE IO_THREAD; 
Query OK, 0 rows affected (0.00 sec) 
 mysql> SHOW PROCESSLIST \G 
*************************** 1. row *************************** 
     Id: 2 
   User: system user    Host:  
     db: NULL 
Command: Connect 
   Time: 4137 
  State: Has read all relay log; waiting for the slave I/O thread to update it    Info: NULL 
*************************** 2. row *************************** 
…… 
2 rows in set (0.00 sec) 

2、 在从数据库 S1 上，执行 STOP SLAVE 停止从服务，然后 RESETMASTER 重置成主数据库。

mysql> STOP SLAVE; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> reset master; 
Query OK, 0 rows affected (0.06 sec) 

3、 在 S2 上，执行 STOPSLAVE 停止从服务，然后执行 CHANGE MASTER TO MASTER_HOST = 'S1' 重新设置主数据库，然后再执行 START SLAVE 启动复制。

mysql> STOP SLAVE; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.1.101'; 
Query OK, 0 rows affected (0.05 sec) 
 
mysql> start slave; 
Query OK, 0 rows affected (0.00 sec) 

4、通知所有的客户端将应用指向 S1，这样客户端发送的所有的更新语法写入到 S1 的二进制日志。
5、 删除新的主数据库服务器上的 master.info 和 relay-log.info 文件，否则下次重启的时候还会按照从服务器启动。

6、最后，如果M服务器可以修复，则可以按照S2的方法配置成S1 的从服务器。

注意：上面测试的步骤是默认 S1 是打开 log-bin 选项的，这样重置成主数据库后可以将二进制日志传输到其他从服务器。其次，S1上没有打开 log-slave-updates参数，否则重置成主数据库后，可能会将已经执行过的二进制日志重复传输给S2，导致S2 的同步错误。