【MySQL进阶】CentOS 7.4 配置MySQL 5.7.19双节点主备同步

CentOS 7.4（内核版本3.10.0-693）
MySQL 5.7.19 # 安装参考我的上一篇文章：http://blog.51cto.com/13946719/2309261
Keepalived 1.4.0 #将在下一篇文章介绍
JDK1.8_171（不用自带OpenJDK）
DB1：192.168.200.180 # 这2台主机为VMware克隆，会有一个坑，后面介绍
DB2：192.168.200.181

2、配置MySQL

2.1、修改配置

首先修改DB1主机的配置文件，在/etc/my.cnf文件中的[mysqld]段添加以下内容

[root@mysql01 ~]# vim /etc/my.cnf

[mysqld]
server-id = 111    # 2台MySQL的ID不能相同，范围0~255之间
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
#
# Remove leading # and set to the amount of RAM for the most important data

然后修改DB2主机的配置文件

[root@mysql01 ~]# vim /etc/my.cnf

[mysqld]
server-id = 222
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
#
# Remove leading # and set to the amount of RAM for the most important data

最后分别重启DB1和DB2使配置生效：

service mysqld restart

2.2、创建复制用户并赋权

首先在DB1的mysql库中创建复制用户：

mysql> grant replication slave on *.* to ‘用户名‘@‘192.168.200.181‘ identified by ‘密码‘;
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      271 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

然后在DB2的mysql库中将DB1设为自己的主服务器：

mysql> change master to     -> master_host=‘192.168.200.180‘,  
    -> master_user=‘用户名‘,
    -> master_password=‘密码‘,
    -> master_log_file=‘mysql-bin.000004‘,  
    -> master_log_pos=271;
Query OK, 0 rows affected (0.07 sec)

这里需要注意master_log_file和master_log_pos两个选项，这两个选项的值是在DB1上通过“show master status” 查询到的结果
接着在DB2上启动slave服务

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

下面查看DB2上slave的运行状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.180
                  Master_User: chenghao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 63008557
               Relay_Log_File: mysql-relay-bin.000025
                Relay_Log_Pos: 63008770
        Relay_Master_Log_File: mysql-bin.000009
             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: mysql.%,test.%,information_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 63008557
              Relay_Log_Space: 63009143
              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: 111
                  Master_UUID: 46249b75-3685-11e8-be57-00505636ed4d   # 本文的坑位置
             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.01 sec)

mysql> 

上面2个重点都为yes，到这里，从DB1到DB2的mysql同步复制已经完成。
 
接下来开始配置从DB2到DB1的mysql同步复制。
在DB2的mysql库中创建复制用户

mysql> grant replication slave on *.* to ‘用户名‘@‘192.168.200.180‘ identified by ‘密码‘;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      271 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

然后在DB1的mysql库中将DB2设为自己的主服务器

mysql> change master to     -> master_host=‘192.168.200.181‘,
    -> master_user=‘用户名‘,
    -> master_password=‘密码‘,
    -> master_log_file=‘mysql-bin.000005‘,
    -> master_log_pos=271;
Query OK, 0 rows affected (0.07 sec)

最后，在DB1上启动slave服务并查看状态

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.181
                  Master_User: chenghao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 16985774
               Relay_Log_File: mysql-relay-bin.000026
                Relay_Log_Pos: 16985780
        Relay_Master_Log_File: mysql-bin.000010
             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: mysql.%,test.%,information_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 16985774
              Relay_Log_Space: 16985987
              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: 222
                  Master_UUID: 85c9a8fc-3687-11e8-91bc-005056327a6d   # 本文的坑位置
             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)

mysql> 

上面2个重点都为yes后，说明DB2到DB1的mysql同步复制已经配置好，到这里这2台MySQL的主从同步配置已经完成。
 
文章开头提到了一个坑，这里介绍一下：
如果2台MySQL的主机是在VMware里克隆的，2台机器上面的Master_UUID是一样的，会造成之前的主从配置失败（SlaveIORunning或者SlaveSQLRunning状态不为yes），解决办法：
在MySQL的主目录：/var/lib/mysql目录下，第一行会有一个auto开头的一个文件，打开后里面有一串UUID，克隆的主机这2台的UUID是一模一样的，然后随便找一台，删除这个文件，然后重启数据库，会自动生成
 
都配好后，建议重启2台数据库，然后再登录2台数据库，使用命令查看同步是否有问题（SlaveIORunning和SlaveSQLRunning都必须为yes）

mysql> show slave status\G   # 查看同步状态

3、测试数据库同步

先查看2台数据库中默认的数据库是否一致
DB1

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

mysql>

DB2

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

mysql>

3.1、创建数据库

可以看到都是有默认的4个数据库，在DB1上创建一个名为test1的数据库

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

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

mysql> 

查询DB2，发现同样创建了一个test1数据库

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

mysql> 

然后可以使用同样方式在DB2数据库创建一个数据库test2，在分别查询下（略过）

3.2、测试删除数据库

在DB2上面执行命令删除test1数据库命令：

mysql> drop database test1;
Query OK, 0 rows affected (0.03 sec)

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
4 rows in set (0.01 sec)

mysql> 

然后查询DB1，发现也已经同步删除了

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
4 rows in set (0.01 sec)

mysql> 

3.3、测试DB1故障，在DB2进行写入或删除操作，再启动DB1查看是否同步

现在2个库都只剩下4个默认数据库，现在把DB1停掉

然后在DB2数据库创建2个数据库test1和test2，查看已经创建成功

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

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

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
| test1               |
| test2               |
+---------------------+
6 rows in set (0.00 sec)

mysql> 

这个时候启动DB1，查看数据库

[root@mysql01 ~]# service mysqld stop
Stopping mysqld (via systemctl):                           [  确定  ]
[root@mysql01 ~]# 
[root@mysql01 ~]# service mysqld start
Starting mysqld (via systemctl):                           [  确定  ]
[root@mysql01 ~]# 
[root@mysql01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| performance_schema  |
| sys                 |
| test1               |
| test2               |
+---------------------+
6 rows in set (0.00 sec)

mysql> 

已经同步过来了，用同样方式，反过来故障测试一遍（略过）
 
通过以上模拟故障的方式，已经实现了MySQL的主从复制的配置，下一篇将介绍如何配置Keepalived实现VIP自动漂移实现MySQL同步复制的高可用

【MySQL进阶】CentOS 7.4 配置MySQL 5.7.19双节点主备同步

原文：http://blog.51cto.com/13946719/2309514