MySQL搭建主从复制流程及相关问题

一、关于主从复制

1.1 关于主从复制

主从复制是将主数据库中的 DDL 和 DML 操作通过二进制日志（binlog）传输到从数据库上，在从数据库上，将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

1.2 应用场景

• 单库压力大，主从同步后读写分离减轻压力。
• 单个数据库宕机、损坏则造成数据丢失，业务瘫痪。

1.3 优缺点

优点：

• 读写分离，提高效率
• 作为备份，避免数据丢失

缺点：

• 高并发情况下，加上同步问题，导致MySQL效率低。
• 主从复制延迟，导致数据没及时更新。

1.4 原理

• Binary log：主数据库的二进制日志
• Relay log：从服务器的中继日志

第一步：master 将操作记录到 binlog 日志中。

第二步：slave 的 IO线程读取 master 的 binlog 日志到 relay log 日志中。

第三步：slave 的 SQL 线程读取 relay log 日志，该改变应用到自己的数据库中。

二、配置主从复制

Master：主，简称 M1
Slave：从，简称 S1丶S2

2.1 同步各个服务器的时间

2.2 修改主库（M1）配置

• 修改主库 MySQL 配置文件：vim /etc/my.cnf

server-id = 1   //指定的任意id号，服务器的唯一标识，但是和其它从库不能相同
log-slave-updates=true       //从服务器更新二进制日志
1
2

• 重启 MySQL 服务

systemctl restart mysqld
1

2.3 主库（M1）为从库（S1\S2）增加账号

GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'176.24.33.%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'102.34.44.%' IDENTIFIED BY '123456';
1
2

其中， myslave 是自定义的用户名，后面的（176.24.33.%）是从库的 IP 地址，123456 是账号密码。

PS：例如，我有两个从库（S1、S2），而且每个从库所属 IP 都不一致，根据自己的从库数量 来增加相应数量账号即可。

增加好后，刷新权限列表：

flush privileges;#刷新权限列表
1

2.3 查看主库（M1）日志状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 13242693 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

2.4 修改从库（S1） MySQL 配置

下面只举配置从库 S1 的例子，如果你有多个从库 S2、S3、S4……Sn，配置流程都是一模一样的。

修改 S1 的 MySQL 配置文件：

server-id = 2 #指定的任意id号，服务器的唯一标识，但是不能和其它主库或者从库相同
read_only = 1 #只读不写模式
1
2

2.5 从库（S1）连接主库（M1）

建立连接：

mysql> change master to master_host='49.100.13.244',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=13242693;
Query OK, 0 rows affected, 8 warnings (0.08 sec)
1
2

开启复制：start slave

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
1
2

查看状态：show slave status\G

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 49.100.13.244
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 13242693
               Relay_Log_File: VM-0-7-centos-relay-bin.000004
                Relay_Log_Pos: 1181
        Relay_Master_Log_File: mysql-bin.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: 13242693
              Relay_Log_Space: 1396
              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: 0dbd386f-49cb-11ec-940c-00163e12a2c5
             Master_Info_File: /www/server/data/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)
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

只要 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes就表示 主从复制已打通。

2.5 测试

主库（M1）新建数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| diary              |
| mycar              |
| mysql              |
| onlinetool         |
| performance_schema |
| video              |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database slave_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| diary              |
| mycar              |
| mysql              |
| onlinetool         |
| performance_schema |
| slave_test         |
| video              |
+--------------------+
8 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

从库（S1）查看：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| slave_test         |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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

三、相关问题

问题一：重启从库 MySQL后 是否还要重新 start slave？

不需要，MySQL 重启后自动开启 start slave。

问题二：在搭建主从复制之前，主库本身存在其它数据库，会影响主从复制吗？

会影响。

例如搭建好主从复制后，在主库新建一个 B 数据库，并在 B 数据库新建了一个数据表，添加了一些数据。这些流程都能正常同步到从库中。

但是，

如果此时主库存在 A 数据库，修改了 A 数据库中的某个表，由于从库中原先就没该数据库，导致主从复制失败，无法进行后续的复制操作（即使主库修改的数据存在）。

解决：

方法一：

锁定主从，先将主库数据同步到从库中。

方法二：

通过修改配置文件可以跳过错误，即使主库修改了从库中不存在的数据库或数据表，也不会影响从库的后续主从复制流程。

修改从库的 MySQL 配置文件：

slave-skip-errors=all
1

问题三：如何给从库设置为只读

方法一：

mysql5.7.8 开始支持 super_read_only 参数，该参数权限要高于 read_only。

将 super_read_only 和 read_only 设置为 on 即可。

mysql> set global super_read_only=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

方法二：

配置文件中：

read_only = 1 #只读不写模式
1

问题四：MySQL 配置文件的 server-id 必须不一样吗？

是的，必须不一样。

一个 MySQL 服务器对应一个 server-id，是服务器的唯一标识，不能出现相同的 server-id。

否则会报错：

The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

问题五：主从复制过程中出现数据不一致的情况

产生原因一：

主库和从库的配置文件中，有 binlog_format 参数，参数值分别为：

• STATEMENT：每一条会修改数据的sql都会记录到 binlog 中。（条）
• ROW：会将每一条数据的变化写到 binlog 中。（行）
• MIXED：mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。（混合）

尽量将主库和从库配置文件中的格式保持一致。

问题六：主从复制延迟同步

原因有以下：

• 主库读写压力大，导致复制延迟。
• 主从库之间的网络延迟。
• 慢SQL语句太多。
• 主从复制单线程，如果主库写并发太大，来不及传送到从库。

解决方案有以下：

• 在业务中加入 非关系型数据库层（memcache、redis、MongoDB），降低 MySQL 压力。
• 读写分离，读从库，写主库，i减轻主库压力。
• 提高硬件能力。
• 直接禁用 Slave 的binlog。
• 禁用 log-slave-updates，从服务器 从主服务器接受的更新日志不计入二进制日志。、
• 业务层面：强制走主库方案、根据需求适当 sleep。