MySQL Master/Slave同步配置

linuxdatabase

安装及配置MySQL

Ubuntu

1
sudo apt-get install mysql-server mysql-client

编辑配置文件：

1
2
sudo chmod 777 /etc/mysql/mysql.conf.d/mysqld.cnf
gedit /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address修改为0.0.0.0：

1
2
3
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 0.0.0.0

server-id配置以及新增配置：

1
2
3
4
5
6
7
8
9
10
11
12
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id			= 2
log_bin				= /var/log/mysql/mysql-bin.log
expire_logs_days		= 10
max_binlog_size   		= 100M
#binlog_do_db			= include_database_name
binlog_ignore_db		= mysql
log_bin_index 			= /var/log/mysql/mysql-bin.log.index
relay_log 			= /var/log/mysql/mysql-relay-bin
relay_log_index 		= /var/log/mysql/mysql-relay-bin.index

还原配置文件权限： 

1
sudo chmod 0444 /etc/mysql/mysql.conf.d/mysqld.cnf

Windows

从MySQL官网下载msi安装（下载链接），其中有几个步骤需要注意：
Type and Networking：勾选Show Advanced and Loggin Options显示扩展选项；
Logging Options：配置错误日志路径，开启慢查询日志和二进制日志并配置路径；
Advanced Options：指定唯一的Server ID。

主从配置

主（Master）从（Slave）配置如下： 

1
2
3
4
5
6
7
8
9
Master:   
IP: 192.168.0.1  
server-id: 1
MySQL account/password: root, 123456  

Slave:  
IP: 192.168.0.2  
server-id: 2  
MySQL account/password: root, 123456

设置允许远程连接

1
2
mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option;
mysql> flush privileges;

或： 

1
2
mysql> use mysql;
mysql> update user set host = '%' where user = 'root';

给从库授权

主库服务器执行以下SQL语句： 

1
mysql> grant replication slave on *.* to root@'192.168.0.2' identified by '123456';

获取主库状态

主库服务器执行show master status查看Master状态，结果如下： 

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

记录下File和Position值。

同步数据

如果主库没有数据（例如是全新安装），则无需进行该步骤。
停止主库服务器上的MySQL服务，从主库服务器导出所有数据，之后导入到从库服务器。

从库配置

在从库服务器上执行以下SQL语句： 

1
2
3
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=586;
mysql> START SLAVE;

之后执行show slave status\G查看slave状态，如果Slave_IO_Running和Slave_SQL_Running都为YES，说明配置正确。

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
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.88.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 882
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 616
        Relay_Master_Log_File: mysql-bin.000006
             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: 882
              Relay_Log_Space: 1141
              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: ccdf9df4-f4a0-11e8-bd9f-74d4356f2db7
             Master_Info_File: /var/lib/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)

ERROR: 
No query specified

验证

在主库服务器上执行create database test_for_slave;创建一个测试数据库，在从库服务器上执行show databases;验证结果如下：
Master: 

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_for_slave     |
+--------------------+
6 rows in set (0.00 sec)

Slave: 

1
2
3
4
5
6
7
8
9
10
11
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_for_slave     |
+--------------------+
5 rows in set (0.00 sec)

错误处理

从库服务器上执行create database test2;创建一个数据库，创建完成后在主库服务器上执行create database test2;创建test2数据库，之后在从库服务器上执行show slave status;查看从库状态，结果如下：

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
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.88.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1547
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 1119
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1007
                   Last_Error: Error 'Can't create database 'test2'; database exists' on query. Default database: 'test2'. Query: 'create database test2'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1385
              Relay_Log_Space: 1806
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1007
               Last_SQL_Error: Error 'Can't create database 'test2'; database exists' on query. Default database: 'test2'. Query: 'create database test2'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: ccdf9df4-f4a0-11e8-bd9f-74d4356f2db7
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 181130 23:48:42
               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)

ERROR: 
No query specified

在此之后，主库服务器上执行任何SQL语句都不会同步到从库。要修复该问题，在从库服务器上执行以下SQL语句： 

1
2
3
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

其中sql_slave_skip_counter=1表示跳过执行出错的那一条语句，如果要跳过多条语句，修改1为对应值。
再次执行show slave status\G;查看从库状态： 

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
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.88.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1709
               Relay_Log_File: mysql-relay-bin.000009
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000006
             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: 1709
              Relay_Log_Space: 1816
              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: ccdf9df4-f4a0-11e8-bd9f-74d4356f2db7
             Master_Info_File: /var/lib/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)

ERROR: 
No query specified

再次执行show databases;可以看到多出了test3数据库，该数据库是在从库同步出错后在主库创建的： 

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
| test3              |
| test_for_slave     |
+--------------------+
7 rows in set (0.00 sec)

本文标题:MySQL Master/Slave同步配置

文章作者:lfeng

发布时间:2018年12月01日 - 10时00分

最后更新:2019年03月13日 - 22时55分

原始链接:https://blog.lfengs.com/2018/Set-up-Master-Slave-Replication-in-MySQL.html

许可协议: "署名-非商用-相同方式共享 3.0" 转载请保留原文链接及作者。

文章目录
  1. 1. 安装及配置MySQL
    1. 1.1. Ubuntu
    2. 1.2. Windows
  2. 2. 主从配置
  3. 3. 设置允许远程连接
  4. 4. 给从库授权
  5. 5. 获取主库状态
  6. 6. 同步数据
  7. 7. 从库配置
  8. 8. 验证
  9. 9. 错误处理