# 《MySQL核心知识》第18章：复制

大家好，我是冰河~~

今天是《MySQL核心知识》专栏的第18章，今天为大家系统的讲讲MySQL中的复制特性，希望通过本章节的学习，小伙伴们能够举一反三，彻底掌握MySQL中复制相关的知识。好了，开始今天的正题吧。

# MySQL复制简介

MySQL 从3.25.15版本开始提供数据库复制功能（replication）。MySQL复制是指从一个MySQL主服务器（MASTER）将数据复制到另一台或多台MySQL从服务器（SLAVE）的过程，将主数据库的DDL和DML操作通过二进制日志传到复制服务器上，然后在从服务器上对这些日志重新执行，从而使从服务器的数据保持同步。

在MySQL中，复制操作是异步进行的，slave服务器不需要持续的保持连接接收master服务器的数据MySQL支持一台主服务器同时向多台从服务器进行复制操作，从服务器同时可以作为其他从服务器的主服务器，如果MySQL主服务器访问量大，可以通过复制数据，然后在从服务器上进行查询操作，从而降低主服务器的访问压力（读写分离），同时从服务器作为主服务器的备份，可以避免主服务器因为故障数据丢失的问题。

MySQL数据库复制操作大致可以分为三个步骤

1、主服务器将数据的改变记录到二进制日志（binlog）中。

2、从服务器将主服务器的binary log events复制到他的中继日志（relay log）中。

3、从服务器做中继日志中的事件，将数据的改变与从服务器保持同步。

首先，主服务器会记录二进制日志，每个事务更新完毕数据之前，主服务器将这些操作的信息记录在二进制日志里面，在事件写入

二进制日志完成后，主服务器通知存储引擎提交事务。SLAVE上面的I/O进程连接上MASTER，并发出日志请求，MASTER接收到来自SLAVE的I/O进程的请求后，通过负责复制的I/O进程根据请求信息读取指定日志位置之后的日志信息，返回给SLAVE的I/O进程。

返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到MASTER端的binlog文件的名称以及binlog的位置SLAVE的I/O进程接收到信息后，将接收到的日志内容依次添加到SLAVE端的relay-log文件的最末端，并将读取到的MASTER端的binlog文件名和位置记录到master-Info文件中。

SLAVE的SQL进程检测到relay-log中新增了内容后，会马上解析relay-log的内容成为在master端真实执行时候的那些可执行内容，并在自身执行mysql复制环境，90%以上都是一个master带一个或者多个slave的架构模式。如果master和slave压力不是太大的话，异步复制的延时一般都很少。尤其是slave端的复制方式改成两个进程处理之后，更是减少了slave端的延时。

提示：对于数据实时性要求不是特别严格的应用，只需要通过廉价的电脑服务器来扩展slave的数量，将读压力分散到多台slave的机器上面即可解决数据库端的读压力瓶颈。这在很大程度上解决了目前很多中小型网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈问题。

# Windows环境下的MySQL主从复制

本章，主要在Windows环境下演示MySQL的主从复制。

复制前的准备工作

在Windows环境下，如果想实现主从复制需要准备的操作环境。

角色	IP	端口	操作系统	MySQL版本
Master	192.168.1.100	3306	Win7	5.7
Slave	192.168.1.102	3306	Win10	5.7

Windows环境下实现主从复制

准备好两台安装MySQL5.7的服务器，即可实现两台MySQL服务器主从复制备份操作。

具体操作步骤如下：

1、在Windows下安装好两台MySQL服务器，配置好两台主机的ip地址，实现两台计算机可以网络连通

2、配置master的相关配置信息，在master主机上开启binlog日志，首先，看下datadir的具体路径。

show variables LIKE '%datadir%'

3、此时需要打开在D:\Program Files (x86)\MySQL\MySQL Server 5.7路径下的配置文件my.ini，添加如下代码，开启binlog功能

[mysqld]
log-bin="D:/MYSQLDataBase/binlog"
expire_logs_days=10
max_binlog_size=100M

1
2
3
4

提示：此事我们需要在D盘下面创建MYSQLDATABASE文件夹，binlog日志记录在该文件夹里面，该配置文件中的其他参数如下所示

expire_logs_days：表示二进制日志文件删除的天数
max_binlog_size：表示二进制日志文件最大的大小

4、登录mysql后，可以执行show VARIABLES LIKE '%log_bin%'命令来测试下log_bin是否成功开启

show VARIABLES LIKE '%log_bin%';

如果log_bin参数是ON的话，那么表示二进制日志文件已经成功开启，如果为OFF的话，那么表示二进制日志文件开启失败

5、在master上配置复制所需要的账户，这里创建一个repl的用户，%表示任何远程地址的repl用户都可以连接master主机。

GRANT replication slave ON *.*TO repl@'%' IDENTIFIED BY '123';
flush privileges;

1
2

6、在my.ini配置文件里配置master主机的相关信息

[mysqld]
log-bin="D:/MYSQLDataBase/binlog"
expire_logs_days=10
max_binlog_size=100M

server-id=1
binlog-do-db=test
binlog-ignore-db=mysql

1
2
3
4
5
6
7
8

这些配置语句的含义

server-id：表示服务器表示id号，master和slave主机的server-id不能一样
binlog-do-db：表示需要复制的数据库，这里以test库为例
binlog-ignore-db：表示不需要复制的数据库

7、重启master主机上的mysql服务，然后输入show master status命令查询master主机的信息

8、将master主机的数据备份出来，然后导入到slave主机中去，具体执行语句如下

mysqldump -u root -p -h 127.0.0.1 test >D:\TEST.TXT

TEST库里面的表和数据

innodb_monitor表是没有数据的

dump出来的txt文件内容

-- MySQL dump 10.13  Distrib 5.7.20, for Win32 (x86)
--
-- Host: 127.0.0.1    Database: test
-- ------------------------------------------------------
-- Server version    5.7.20-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `book`
--

DROP TABLE IF EXISTS `book`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `book`
--

LOCK TABLES `book` WRITE;
/*!40000 ALTER TABLE `book` DISABLE KEYS */;
INSERT INTO `book` VALUES (12,'dajiahao','NIHAO','??','henhao',1990);
/*!40000 ALTER TABLE `book` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `innodb_monitor`
--

DROP TABLE IF EXISTS `innodb_monitor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `innodb_monitor` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `innodb_monitor`
--

LOCK TABLES `innodb_monitor` WRITE;
/*!40000 ALTER TABLE `innodb_monitor` DISABLE KEYS */;
/*!40000 ALTER TABLE `innodb_monitor` ENABLE KEYS */;
UNLOCK TABLES;


/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-08-05 22:36:17

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

将D:\TEST.TXT文件复制到slave机器上，然后执行如下操作

在命令行登录mysql，然后 USE TEST;

记得一定要USE TEST，切换数据库上下文，否则会报错：NO DATABASE SELECTED 的错误信息

然后执行source命令导入TEXT.txt文件的内容

可以看到，数据已经导入到slave上面了。

9、配置slave机器（192.168.1.102）的my.ini配置文件

具体配置信息如下

[mysql]

default-character-set=utf8
log_bin="C:/MYSQLLOG/binlog"
expire_logs_days=10
max_binlog_size=100M

[mysqld]
server-id=2

1
2
3
4
5
6
7
8
9

提示：配置slave主机my.ini文件的时候，需要将server-id=2写到[mysqld]后面

另外如果配置文件中还有log_bin的配置，可以将他注释掉，如下所示

#Binary Logging
#log-bin
#log_bin="xxx"

1
2
3

10、重启slave主机（192.168.1.102）的mysql服务，在slave主机（192.168.1.102）的mysql中执行如下命令

关闭slave服务

stop slave;

11、设置slave从机实现复制相关的信息，命令如下。

change master to
master_host='192.168.1.100',
master_user='repl',
master_password='123',
master_log_file='binlog。000004',
master_log_pos=107;

Command(s) completed successfully.

1
2
3
4
5
6
7
8

各个参数所代表的具体含义如下：

master_host：表示实现复制的主机ip地址
master_user：表示实现复制的登录远程主机的用户
master_password：表示实现复制的登录远程主机的密码
master_log_file：表示实现复制的binlog日志文件
master_log_pos：表示实现复制的binlog日志文件的偏移量

12、继续在从机执行操作，显示slave从机的状况，如下所示

start slave;

Command(s) completed successfully.
<pre>mysql<span style="color:#808080;">></span><span style="color:#000000;"> SHOW SLAVE STATUS \G;
</span><span style="color:#808080;">***************************</span> <span style="color:#800000;font-weight: bold;">1</span>. row <span style="color:#808080;">***************************</span><span style="color:#000000;">
               Slave_IO_State:
                  Master_Host: </span><span style="color:#800000;font-weight: bold;">192.168</span>.<span style="color:#800000;font-weight: bold;">1.100</span><span style="color:#000000;">
                  Master_User: repl
                  Master_Port: </span><span style="color:#800000;font-weight: bold;">3306</span><span style="color:#000000;">
                Connect_Retry: </span><span style="color:#800000;font-weight: bold;">60</span><span style="color:#000000;">
              Master_Log_File: binlog銆?</span><span style="color:#800000;font-weight: bold;">00004</span><span style="color:#000000;">
          Read_Master_Log_Pos: </span><span style="color:#800000;font-weight: bold;">107</span><span style="color:#000000;">
               Relay_Log_File: Steven</span><span style="color:#808080;">-</span>PC<span style="color:#808080;">-</span>relay<span style="color:#808080;">-</span>bin.<span style="color:#800000;font-weight: bold;">000002</span><span style="color:#000000;">
                Relay_Log_Pos: </span><span style="color:#800000;font-weight: bold;">4</span><span style="color:#000000;">
        Relay_Master_Log_File: binlog銆?</span><span style="color:#800000;font-weight: bold;">00004</span><span style="color:#000000;">
             Slave_IO_Running: No
            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: </span><span style="color:#800000;font-weight: bold;">0</span><span style="color:#000000;">
                   Last_Error:
                 Skip_Counter: </span><span style="color:#800000;font-weight: bold;">0</span><span style="color:#000000;">
          Exec_Master_Log_Pos: </span><span style="color:#800000;font-weight: bold;">107</span><span style="color:#000000;">
              Relay_Log_Space: </span><span style="color:#800000;font-weight: bold;">107</span><span style="color:#000000;">
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: </span><span style="color:#800000;font-weight: bold;">0</span><span style="color:#000000;">
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: </span><span style="color:#0000ff;">NULL</span><span style="color:#000000;">
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: </span><span style="color:#800000;font-weight: bold;">1236</span><span style="color:#000000;">
                Last_IO_Error: Got fatal error </span><span style="color:#800000;font-weight: bold;">1236</span> <span style="color:#0000ff;">from</span> master <span style="color:#0000ff;">when</span><span style="color:#000000;"> reading dat
a </span><span style="color:#0000ff;">from</span> <span style="color:#0000ff;">binary</span> <span style="color:#ff00ff;">log</span>: <span style="color:#ff0000;">'</span><span style="color:#ff0000;">Could not find first log file name in binary log index file</span><span style="color:#ff0000;">'</span><span style="color:#000000;">

               Last_SQL_Errno: </span><span style="color:#800000;font-weight: bold;">0</span><span style="color:#000000;">
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: </span><span style="color:#800000;font-weight: bold;">1</span>
<span style="color:#800000;font-weight: bold;">1</span> row <span style="color:#808080;">in</span> <span style="color:#0000ff;">set</span> (<span style="color:#800000;font-weight: bold;">0.00</span><span style="color:#000000;"> sec)

ERROR:
No query specified</span>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

在上述执行show slave status \G命令中很显然存在一些问题，问题如下。

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading dat
a from binary log: 'Could not find first log file name in binary log index file'

1
2
3

下面的步骤可以解决问题，具体步骤如下

1、重启master（192.168.1.100）主机的mysql服务，执行show master status \G命令

记下File和Position的值，后面slave主机会用到，命令执行如下

SHOW MASTER STATUS;

2、在slave（192.168.1.102）主机上重新设置信息，命令执行如下

stop slave;

change master to
master_log_file='binlog.000005',
master_log_pos=107;

start slave;

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000005
          Read_Master_Log_Pos: 107
               Relay_Log_File: Steven-PC-relay-bin.000002
                Relay_Log_Pos: 250
        Relay_Master_Log_File: binlog.000005
             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: 107
              Relay_Log_Space: 410
              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
1 row in set (0.00 sec)

ERROR:
No query specified

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

这次正常了，实际上刚才有两个地方是错误的

第一个：在从机的my.ini里面

[mysql]
default-character-set=utf8
#log_bin="C:/MYSQLLOG/binlog"
#expire_logs_days=10
#max_binlog_size=100M

1
2
3
4
5

在从机的my.ini里面的mysql配置节下面配置了binlog，实际上这样做是错误的，要配置binlog需要在[mysqld]配置节下

第二个：第一次配置从机的同步的时候本人写错了标点符号，.号写成。号

master_log_file='binlog。000004',

这时候，我们可以在从机上面执行show processlist来查询从服务器的进程状态

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost:60968
     db: information_schema
Command: Sleep
   Time: 3613
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 5
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 3613
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 6
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 2769
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 4. row ***************************
     Id: 7
   User: root
   Host: localhost:61007
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
4 rows in set (0.04 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

结果表明slave已经连接上master，开始接收并执行日志relay-log.info文件里面的内容

.\Steven-PC-relay-bin.000002
250
binlog.000005
107
7

1
2
3
4
5

relay-log.info文件里面记录了slave端的relaylog的当前文件名和位置，还有master端的binlog文件名和位置

# Windows环境下主从复制测试

1、在master端的mysql环境下，执行下面命令

use test;
create table rep_test(data integer);
insert into rep_test values(2);

1
2
3

2、在slave端的mysql环境下，查看主机刚才添加的表和数据是否成功同步到从机上

use test;
show tables;
select * FROM REP_TEST;

1
2
3

测试表明，数据已经成功地同步到slave上，实验中只是用到了主从同步，在实际生产环境中MYSQL架构可能会用到一主多从的架构

# MySQL主要复制启动选项

（1）log-slave-updates

log-slave-updates这个参数主要用来配置从服务器的更新是否写入二进制日志，该选项默认是不打开的，如果这个从服务器同时也作为其他服务器的主服务器，搭建一个链式的复制，那么就需要开启这个选项，这样从服务器才能获取他的二进制日志进行同步操作。

（2）master-connect-retry

master-connect-retry这个参数用来设置和主服务器连接丢失的时候进行重试的时间间隔，默认是60秒。

（3）read-only

read-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作。如果主数据库创建了一个普通用户，在默认情况下，该用户是可以更新从数据库的数据的，如果使用read-only选项启动从数据库以后，用户对从数据库进行更新时会提示错误。

在Linux下启动mysql例子

[root@localhost~]#mysqld_safe -read-only

（4）slave-skip-errors

在复制过程中，从服务器可以会执行BINLOG中的错误SQL语句，此时如果不忽略错误，从服务器会停止复制进程，等待用户处理错误。这种错误如果不能及时发现，将会对应用或者备份产生影响。slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，设置该参数后，mysql会自动跳过所配置的一系列错误，直接执行后面的SQL语句，该参数可以定义多个错误号，如果设置成all，则表示跳过所有的错误，在my.ini或者my.cnf里配置如下。

slave-skip-errors=1007,1051,1062

如果从数据库主要作为主库的备份，那么就不应该使用这个启动参数，因为一旦设置不当很可能造成主从库的数据不同步。如果从库仅仅是为了分担主库的查询压力，并且对数据的完整性要求不高，那么这个选项可以减轻DBA维护从库的工作量

# 查看slave的复制进度

很多情况下，用户都想知道从服务器复制的进度，从而判断从服务器上复制数据的完整性，同时判断是否需要手工来做主从同步工作。

事实上，用户可以通过show processlist列表中的Slave_SQL_Running线程的Time值得到，他记录了从服务器当前执行的SQL时间戳和系统时间之间的差距，例如下面的例子

  Id: 6
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 2769
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

1
2
3
4
5
6
7
8

Time时间说明从服务器最后执行的更新操作大概是主服务器2769秒前的更新操作

# 日常管理和维护

复制配置完成后，DBA需要进行日常的监控和管理维护工作，以便能够及时发现问题和解决问题。以保证主从数据库能够正常工作。

1、了解服务器的状态

一般使用show slave status命令来检查从服务器，在查看服务器信息中，首先要查看下面的两个进程是否为YES。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1
2

Slave_IO_Running表明此进程是否能够由从服务器到主服务器上正确地读取binlog日志，并写入到从服务器的中继日志中
Slave_SQL_Running表明此进程能否读取并执行中继日志中的binlog信息

2、服务器复制出错原因

问题一：出现“log event entry exceeded max_allowed_pack”错误

如果在应用中使用大的BLOB列或CLOB列或者长字符串，那么在从服务器上回复时，可能会出现“log event entry exceeded max_allowed_pack”的错误，这是因为含有达文本的记录无法通过网络进行传输而导致的。

解决方法是在主服务器和从服务器上添加max_allowed_packet参数，该参数默认设置为1MB

show variables LIKE '%max_all%'

Variable_name      Value   
------------------ ------- 
max_allowed_packet 1048576 

(1 row(s) affected)

set @@global.max_allowed_packet=16777216;

1
2
3
4
5
6
7
8
9

同时在my.ini或my.cnf文件里设置max_allowed_packet=16M，数据库重启之后该参数将有效

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

大多数情况下使用一台主服务器对一台或者多台从服务器，但是在某些情况下可能会存在多个服务器配置为复制主服务器，使用auto_increment时应采取特殊步骤以防止键值冲突，否则插入时多个主服务器会试图使用相同的auto_increment值。

服务器变量auto_increment_increment和auto_increment_offset可以协调多主服务器复制auto_increment列在多主服务器复制到从服务器的过程中会发生主键冲突问题，可以将不同的主服务器的这两个参数重新进行设置，将A库上设置auto_increment_increment=1，auto_increment_offset=1，此时B库上设置auto_increment_increment=1，auto_increment_offset=0。

提示：一般不建议使用双主或多主，因为这样会带来意想不到的冲突状况，就像SQLSERVER的对等复制，虽然有很多冲突检测措施

但是有时候冲突是不可预料的，出现冲突DBA要排查，维护成本较高，我们生产环境里是没有使用双主和多主，主要使用的是一主多从或一主一从。

# 切换主从服务器

在实际生产环境，如果主机上的主库发生故障，需要将从机上的从库切换成主库，同时需要修改服务器C的配置文件，使程序连接到从机。下面介绍主从切换的步骤。

1、首先要确保所有的从库都已经执行了relay log中的全部更新，看从库的状态是否是Has read all relay log，是否更新都已经执行完成

在从库上执行下面命令。

STOP SLAVE IO_THREAD;

Command(s) completed successfully.

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost:60968
     db: test
Command: Sleep
   Time: 45
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 3949
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 7
   User: root
   Host: localhost:61007
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

2、在从库上停止slave服务，然后执行reset master重置成为主库。

STOP SLAVE;

Command(s) completed successfully.

RESET MASTER;
Command(s) completed successfully.

1
2
3
4
5
6

注意：如果从库上并未开binlog，那么在执行reset master的时候会报错：ERROR 1186(HY000):BINLOG CLOSED ,CANNOT RESET MASTER。

在切换之后，在从库的数据目录会多出master.info文件。

master.info文件里的内容。

18
binlog.000005
393
192.168.1.100
repl
123
3306
60
0

0
1800.000

0
0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

基本上记录了主库的复制用户、密码和binlog文件名和位置等。

3、在从库B（192.168.1.102）上添加具有replication权限的用户repl，查询主库状态，命令如下。

GRANT REPLICATION SLAVE ON *.*TO 'repl'@'localhost' identified by '123';
show master status;

1
2

4、修改主服务器的my.ini文件里的server-id为1，从服务器的server-id为2。

5、在原来的主库（192.168.1.100）上配置复制参数。

change master TO
master_host='192.168.1.102',
master_user='repl',
master_password='123',
master_port=3306,
master_log_file='on.000004',
master_log_pos=107;

1
2
3
4
5
6
7

6、在从库（192.168.1.100）上执行show slave status命令查看从库是否启动成功。

START SLAVE;

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.102
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: on.000004
          Read_Master_Log_Pos: 107
               Relay_Log_File: joe-relay-bin.000006
                Relay_Log_Pos: 246
        Relay_Master_Log_File: on.000004
             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: 107
              Relay_Log_Space: 436
              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
1 row in set (0.00 sec)

ERROR:
No query specified

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

注意：如果在主库上面（192.168.1.102）的复制用户repl没有允许远程主机从库的访问，那么在执行show slave status的时候就会报错。

Last_IO_Errno: 1130
Last_IO_Error: error connecting to master 'repl@192.168.1.102:3360  retries: 8640006' - retry-time: 60  retries: 86400

1
2

这时候，只需要在主库（192.168.1.102）上面执行下面语句即可。

use mysql;
select * from user where user='repl';
update user set host = '%' where user ='repl';
flush privileges;

1
2
3
4

7、在主库和从库上面是否成功设置复制功能，首先在主库（192.168.1.102）上查看test库中的表。

use test;
show tables;

1
2

查询从库中（192.168.1.100）test库里表的情况。

use test;
show tables;

1
2

跟主库一样。

8、在主库（192.168.1.102）中增加表rep_t ，并插入数据。

create table rep_t(data int);
insert into rep_t values(1);

1
2

9、在从库（192.168.1.100）上查询表是否已经创建并复制数据到从库中

USE test;
show variables like '%server%';
show tables;
SELECT * FROM rep_t;

1
2
3
4

至此，主从库成功切换

主机和从机server-id一样如何解决

通常情况下，master和slave的server-id是不会一样的，如果一样的话会出现报错。出现这种情况，用户可以使用如下命令来查看服务器的server-id，然后手动进行修改，如下所示

show variables like '%server_id%';

Variable_name Value 
------------- ----- 
server_id     2     

(1 row(s) affected)

SET global server_id=1

1
2
3
4
5
6
7
8
9

修改完成后，执行slave start命令，查询slave主机的状态，查看问题可否解决

从机状态显示Last_IO_Error错误代码为2013的原因

有时候会遇到这样的情况，在执行show slave status \G 命令中 Slave_IO_Running和Slave_SQL_Running的值都是YES

但是Last_IO_Error发生2013错误

发生这种问题主要原因是网络问题，首先要检查下master主机创建的用户是否授予远程连接的权限

GRANT replication slave ON *.*TO repl@'%' IDENTIFIED BY '123';

这里%表示任何的repl用户都可以访问master主机，另外需要查看是否有防火墙设置和网络的其他故障

# MySQL复制不同步的原因

mysql replication（复制）采用binlog进行网络传输，所以网络延时是产生mysql主从不同步的主要原因，这会给我们进行读写分离带来

一定困难为了避免这种情况，在配置服务器的时候推荐使用INNODB存储引擎的表，在主机上可以设置sync_binlog

下面内容摘抄自《MySQL行调优和架构设计》

“sync_binlog”：这个参数是对于 MySQL 系统来说是至关重要的，他不仅影响到 Binlog 对 MySQL 所带来的性能损耗，而且还影响到 MySQL 中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

● sync_binlog=0，当事务提交之后，MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候来做同步，或者 cache 满了之后才同步到磁盘。

● sync_binlog=n，当每进行 n 次事务提交之后，MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。

在 MySQL 中系统默认的设置是 sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统 Crash，在 binlog_cache 中的所有 binlog 信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使系统Crash，也最多丢失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。

如果master主机上的max_allowed_packet比较大，但是从机上没有配置该值的话，该参数还是使用默认值1MB此时很有可能导致同步失败，建议主从两台机器都设为5MB比较合适

好了，如果文章对你有点帮助，记得给冰河一键三连哦，欢迎将文章转发给更多的小伙伴，冰河将不胜感激~~

# 星球服务

加入星球，你将获得：

1.项目学习：微服务入门必备的SpringCloud Alibaba实战项目、手写RPC项目—所有大厂都需要的项目【含上百个经典面试题】、深度解析Spring6核心技术—只要学习Java就必须深度掌握的框架【含数十个经典思考题】、Seckill秒杀系统项目—进大厂必备高并发、高性能和高可用技能。

2.框架源码：手写RPC项目—所有大厂都需要的项目【含上百个经典面试题】、深度解析Spring6核心技术—只要学习Java就必须深度掌握的框架【含数十个经典思考题】。

3.硬核技术：深入理解高并发系列（全册）、深入理解JVM系列（全册）、深入浅出Java设计模式（全册）、MySQL核心知识（全册）。

4.技术小册：深入理解高并发编程（第1版）、深入理解高并发编程（第2版）、从零开始手写RPC框架、SpringCloud Alibaba实战、冰河的渗透实战笔记、MySQL核心知识手册、Spring IOC核心技术、Nginx核心技术、面经手册等。

5.技术与就业指导：提供相关就业辅导和未来发展指引，冰河从初级程序员不断沉淀，成长，突破，一路成长为互联网资深技术专家，相信我的经历和经验对你有所帮助。

冰河的知识星球是一个简单、干净、纯粹交流技术的星球，不吹水，目前加入享5折优惠，价值远超门票。加入星球的用户，记得添加冰河微信：hacker_binghe，冰河拉你进星球专属VIP交流群。

# 星球重磅福利

跟冰河一起从根本上提升自己的技术能力，架构思维和设计思路，以及突破自身职场瓶颈，冰河特推出重大优惠活动，扫码领券进行星球，直接立减149元，相当于5折， 这已经是星球最大优惠力度！

领券加入星球，跟冰河一起学习《SpringCloud Alibaba实战》、《手撸RPC专栏》和《Spring6核心技术》，更有已经上新的《大规模分布式Seckill秒杀系统》，从零开始介绍原理、设计架构、手撸代码。后续更有硬核中间件项目和业务项目，而这些都是你升职加薪必备的基础技能。

100多元就能学这么多硬核技术、中间件项目和大厂秒杀系统，如果是我，我会买他个终身会员！

# 其他方式加入星球

链接：打开链接 http://m6z.cn/6aeFbs (opens new window) 加入星球。
回复：在公众号 冰河技术 回复星球领取优惠券加入星球。

特别提醒： 苹果用户进圈或续费，请加微信 hacker_binghe 扫二维码，或者去公众号 冰河技术 回复星球扫二维码加入星球。

# 星球规划

后续冰河还会在星球更新大规模中间件项目和深度剖析核心技术的专栏，目前已经规划的专栏如下所示。

# 中间件项目

《大规模分布式定时调度中间件项目实战（非Demo）》：全程手撸代码。
《大规模分布式IM（即时通讯）项目实战（非Demo）》：全程手撸代码。
《大规模分布式网关项目实战（非Demo）》：全程手撸代码。
《手写Redis》：全程手撸代码。
《手写JVM》全程手撸代码。

# 超硬核项目

《从零落地秒杀系统项目》：全程手撸代码，在阿里云实现压测（已上新）。
《大规模电商系统商品详情页项目》：全程手撸代码，在阿里云实现压测。
其他待规划的实战项目，小伙伴们也可以提一些自己想学的，想一起手撸的实战项目。。。

既然星球规划了这么多内容，那么肯定就会有小伙伴们提出疑问：这么多内容，能更新完吗？我的回答就是：一个个攻破呗，咱这星球干就干真实中间件项目，剖析硬核技术和项目，不做Demo。初衷就是能够让小伙伴们学到真正的核心技术，不再只是简单的做CRUD开发。所以，每个专栏都会是硬核内容，像《SpringCloud Alibaba实战》、《手撸RPC专栏》和《Spring6核心技术》就是很好的示例。后续的专栏只会比这些更加硬核，杜绝Demo开发。

小伙伴们跟着冰河认真学习，多动手，多思考，多分析，多总结，有问题及时在星球提问，相信在技术层面，都会有所提高。将学到的知识和技术及时运用到实际的工作当中，学以致用。星球中不少小伙伴都成为了公司的核心技术骨干，实现了升职加薪的目标。

# 联系冰河

# 加群交流

本群的宗旨是给大家提供一个良好的技术学习交流平台，所以杜绝一切广告！由于微信群人满 100 之后无法加入，请扫描下方二维码先添加作者 “冰河” 微信(hacker_binghe)，备注：星球编号。

冰河微信

# 公众号

分享各种编程语言、开发技术、分布式与微服务架构、分布式数据库、分布式事务、云原生、大数据与云计算技术和渗透技术。另外，还会分享各种面试题和面试技巧。内容在 冰河技术 微信公众号首发，强烈建议大家关注。

公众号：冰河技术

# 视频号

定期分享各种编程语言、开发技术、分布式与微服务架构、分布式数据库、分布式事务、云原生、大数据与云计算技术和渗透技术。另外，还会分享各种面试题和面试技巧。

视频号：冰河技术

# 星球

加入星球 冰河技术 (opens new window)，可以获得本站点所有学习内容的指导与帮助。如果你遇到不能独立解决的问题，也可以添加冰河的微信：hacker_binghe，我们一起沟通交流。另外，在星球中不只能学到实用的硬核技术，还能学习实战项目！

关注冰河技术 (opens new window)公众号，回复 星球 可以获取入场优惠券。

知识星球：冰河技术

← 第17章：性能优化第19章：安全地关闭MySQL实例 →