centOs 6 Mysql设置主从 主主同步

设置MySQL主从同步
主从同步，也称master-slave，是开发过程中常见的提高程序性能的办法。通常情况下在主数据库服务器写入数据，查询数据的时候是在从服务器，主从服务器之间通过同步机制来保持一致，虽然主从数据库的一致性是存在一定时间差的，但因为这个时间差很小所以在一些对时间不是非常敏感的业务场景下被认为是实时一致的。在大多数主流数据库中都是可以通过配置来实现主从同步、读写分离的。
这里就以刚刚配置起来的两个数据实例来演示如何实现主从同步的，步骤有如下：
(1)主数据库实例设置server-id和开启bin-log；
(2)主数据库实例创建用于同步的账号；
(3)从数据库实例设置server-id；
(4)从数据库实例配置同步参数；
(5)从数据库实例启动同步开关。
5.1主数据库实例设置server-id和开启bin-log
其实在上面的3306的配置文件里已经配置了server-id和bin-log，就在/usr/local/mysql-5.7.18/data/3306/my.cnf文件的[mysqld]节点处：

[plain]  view plain  copy

1. [mysqld]  
2. #  
3. # * Basic Settings  
4. #  
5. server-id   = 1  
6. log-bin= /usr/local/mysql-5.7.18/data/3306/mysql-bin  

另外还需要注意的是在这个my.cnf配置文件中还有一句：

[plain]  view plain  copy

1. replicate-ignore-db = mysql  

它的意思是在进行主从同步时忽略mysql这个库，因为mysql库主要存放账号及授权信息的，不同数据库实例账号和授权信息不同的可能性极大，因此没有必要同步这个库。
通过如下命令可以快速查看是否已经正确配置：

[plain]  view plain  copy

1. [root@localhost ~]# egrep "server-id|log-bin" /usr/local/mysql-5.7.18/data/3306/my.cnf  
2. server-id   = 1  
3. log-bin         = /usr/local/mysql-5.7.18/data/3306/mysql-bin  

在数据库中也可以查看：

[sql]  view plain  copy

1. mysql> show variables like "server_id";  
2. +---------------+-------+  
3. | Variable_name | Value |  
4. +---------------+-------+  
5. | server_id     | 1     |  
6. +---------------+-------+  
7. 1 row in set (0.00 sec)  
8.   
9. mysql> show variables like "log_bin";  
10. +---------------+-------+  
11. | Variable_name | Value |  
12. +---------------+-------+  
13. | log_bin       | ON    |  
14. +---------------+-------+  
15. 1 row in set (0.00 sec)  

同时，这里也查看一下主数据库的状态：

[sql]  view plain  copy

1. mysql> show master status \G;  
2. *************************** 1. row ***************************  
3.              File: mysql-bin.000003  
4.          Position: 154  
5.      Binlog_Do_DB:  
6.  Binlog_Ignore_DB:  
7. Executed_Gtid_Set:  
8. 1 row in set (0.00 sec)  
9.   
10. ERROR:  
11. No query specified  

这里：File和Position的值是两个很重要的参数，这是从数据库下次同步时的起始位置。
5.2主数据库实例创建用于同步的账号
在主数据库创建一个replication账号用于从主库同步数据到从库，创建replication账号的语句如下：
grant replication slave on *.* to 'replication'@'%' identified by 'your_password';flush privileges;
在实际执行时请将your_password改为自己的密码，执行这个SQL语句后就会在mysql库中的user表中创建一个名为replication的用户，可以通过SQL语句查看：

[sql]  view plain  copy

1. mysql> show grants for "replication"@"%";  
2. +-----------------------------------------------------+  
3. | Grants for replication@%                            |  
4. +-----------------------------------------------------+  
5. | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' |  
6. +-----------------------------------------------------+  
7. 1 row in set (0.00 sec)  

5.3从数据库实例设置server-id
同样，其实在上面的3307的配置文件里已经配置了server-id和bin-log，就在/usr/local/mysql-5.7.18/data/3307/my.cnf文件的[mysqld]节点处：

[sql]  view plain  copy

1. [mysqld]  
2. #  
3. # * Basic Settings  
4. #  
5. server-id   = 2  

注意：在一个主从关系群中server-id是唯一的，另外在由于从数据库不向其它库同步数据，因此没有开启bin-log。这里也通过egrep查看一下：

[plain]  view plain  copy

1. [root@localhost ~]# egrep "server-id|log-bin" /usr/local/mysql-5.7.18/data/3307/my.cnf  
2. server-id   = 1  

5.4从数据库实例配置同步参数
登录3307端口对应的实例：

[plain]  view plain  copy

1. cd /usr/local/mysql-5.7.18  
2. ./bin/mysql -u root –p -S ./data/3307/mysql.sock  

然后执行以下语句：

[plain]  view plain  copy

1. CHANGE MASTER TO \  
2. MASTER_HOST='127.0.0.1', \  
3. MASTER_PORT=3306, \  
4. MASTER_USER='replication', \  
5. MASTER_PASSWORD='your_password', \  
6. MASTER_LOG_FILE='mysql-bin.000003', \  
7. MASTER_LOG_POS=154;  

说明：
MASTER_HOST为主服务器IP或主机名；
MASTER_PORT为主服务器端口；
MASTER_USER为主服务器上用于同步的数据库账户名；
MASTER_PASSWORD为主服务器上用于同步的数据库账户对应的密码；
MASTER_LOG_FILE为当前bin-log日志文件名；
MASTER_LOG_POS为当前偏移量；
其中MASTER_LOG_FILE和MASTER_LOG_POS可以在主数据库上执行” show master status \G;”SQL语句来获得，见本文“主数据库实例设置server-id和开启bin-log”一节。
5.5从数据库实例启动同步开关
登录从数据库执行”start slave”即可。
在本篇中由于连个数据库都是刚刚初始化的，所以数据都是一致的。在实际情况中，需要先将两个数据库实例中的除mysql库之外的数据一致后才可启动同步，否则两个库中的就会不一致。
这里我们来检查一下效果：
在主库中创建一个数据库，如下：

[sql]  view plain  copy

1. mysql> create database zhoufoxcn;  
2. Query OK, 1 row affected (0.00 sec)  
3.   
4. mysql> show databases;  
5. +--------------------+  
6. | Database           |  
7. +--------------------+  
8. | information_schema |  
9. | mysql              |  
10. | performance_schema |  
11. | sys                |  
12. | zhoufoxcn          |  
13. +--------------------+  
14. 5 rows in set (0.01 sec)  

然后在从库中查看数据库的情况：

[sql]  view plain  copy

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

可见主库的数据被自动同步到从库了，这时再在主库中删除zhoufoxcn这个库，可以看到从库中也自动删掉了。

这时在主库查看状态：

[sql]  view plain  copy

1. mysql> show master status \G;  
2. *************************** 1. row ***************************  
3.              File: mysql-bin.000003  
4.          Position: 488  
5.      Binlog_Do_DB:  
6.  Binlog_Ignore_DB:  
7. Executed_Gtid_Set:  
8. 1 row in set (0.00 sec)  
9.   
10. ERROR:  
11. No query specified  

在从库查看状态：

[sql]  view plain  copy

1. mysql> show slave status \G;  
2. *************************** 1. row ***************************  
3.                Slave_IO_State: Waiting for master to send event  
4.                   Master_Host: 127.0.0.1  
5.                   Master_User: replication  
6.                   Master_Port: 3306  
7.                 Connect_Retry: 60  
8.               Master_Log_File: mysql-bin.000003  
9.           Read_Master_Log_Pos: 488  
10.                Relay_Log_File: relay-bin.000008  
11.                 Relay_Log_Pos: 701  
12.         Relay_Master_Log_File: mysql-bin.000003  
13.              Slave_IO_Running: Yes  
14.             Slave_SQL_Running: Yes  
15.               Replicate_Do_DB:  
16.           Replicate_Ignore_DB: mysql  
17.            Replicate_Do_Table:  
18.        Replicate_Ignore_Table:  
19.       Replicate_Wild_Do_Table:  
20.   Replicate_Wild_Ignore_Table:  
21.                    Last_Errno: 0  
22.                    Last_Error:  
23.                  Skip_Counter: 0  
24.           Exec_Master_Log_Pos: 488  
25.               Relay_Log_Space: 1115  
26.               Until_Condition: None  
27.                Until_Log_File:  
28.                 Until_Log_Pos: 0  
29.            Master_SSL_Allowed: No  
30.            Master_SSL_CA_File:  
31.            Master_SSL_CA_Path:  
32.               Master_SSL_Cert:  
33.             Master_SSL_Cipher:  
34.                Master_SSL_Key:  
35.         Seconds_Behind_Master: 0  
36. Master_SSL_Verify_Server_Cert: No  
37.                 Last_IO_Errno: 0  
38.                 Last_IO_Error:  
39.                Last_SQL_Errno: 0  
40.                Last_SQL_Error:  
41.   Replicate_Ignore_Server_Ids:  
42.              Master_Server_Id: 1  
43.                   Master_UUID: da584607-4d19-11e7-8903-080027f376f5  
44.              Master_Info_File: /usr/local/mysql-5.7.18/data/3307/data/master.info  
45.                     SQL_Delay: 0  
46.           SQL_Remaining_Delay: NULL  
47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates  
48.            Master_Retry_Count: 86400  
49.                   Master_Bind:  
50.       Last_IO_Error_Timestamp:  
51.      Last_SQL_Error_Timestamp:  
52.                Master_SSL_Crl:  
53.            Master_SSL_Crlpath:  
54.            Retrieved_Gtid_Set:  
55.             Executed_Gtid_Set:  
56.                 Auto_Position: 0  
57.          Replicate_Rewrite_DB:  
58.                  Channel_Name:  
59.            Master_TLS_Version:  
60. 1 row in set (0.00 sec)  
61.   
62. ERROR:  
63. No query specified  

可以看出通过配置，两台数据库能够自动同步数据了。

配置双主

master的binlog位置

linux-node2 master端

d /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      613 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

linux-node3 master端

cd /data/app/mysql-3306/
./bin/mysql -uroot -p -S mysql.sock -P 3306
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      613 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

linux-node2 master配置跟linux-node3 master同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.13', 
                 MASTER_PORT=3306, 
                 MASTER_USER='repl', 
                 MASTER_PASSWORD='mysql', 
                 MASTER_LOG_FILE='mysql-bin.000001', 
                 MASTER_LOG_POS=613;
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.13
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: linux-node2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes         

linux-node3 master配置跟linux-node2 master同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12', 
                 MASTER_PORT=3306, 
                 MASTER_USER='repl', 
                 MASTER_PASSWORD='mysql', 
                 MASTER_LOG_FILE='mysql-bin.000001', 
                 MASTER_LOG_POS=613;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.12
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: linux-node3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes