MySQL主主要資料同步配置詳解

MySQL主主同步和主從同步的原理一樣，只是雙方都是主從角色。

環境

作業系統版本：CentOS7 64位

MySQL版本：mysql5.6.33

節點1IP：192.168.1.205 主機名稱：edu-mysql-01

節點2IP：192.168.1.206 主機名稱：edu-mysql-02

MySQL 主從複製官方文檔： http://dev.mysql.com/doc/refman/5.6/en/replication.html

注意：

1> 主從伺服器作業系統版本和位元要保持一致

2> Master和Slave資料庫的版本要一致

3> Master和Slave資料庫中的資料要一致

配置

配置之前先參考 《MySQL5.7安裝與配置（YUM）》 安裝好MySQL（注意本文示範的是5.6版本，需要修改文章中的yum源為5.6）

1、安全配置

1> 防火牆

添加mysql通訊連接埠（預設為3306）

shell> vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
shell> service  iptables restart
或關閉防火牆

shell> service iptables stop
2> 關閉selinux

shell> vi /etc/selinux/config
SELINUX=disabled
將SELINUX的值修改為disabled

2. 節點1配置（192.168.1.205）

2.1 添加資料同步配置

shell> vim /etc/my.cnf
在[mysqld]中增加以下配置項：

# 伺服器的ID,必須唯一，一般設定自己的IP
server_id=205
# 複製過濾：不需要備份的資料庫（MySQL庫一般不同步）
binlog-ignore-db=mysql
# 開啟二進位日誌功能，名字可以隨便取，最好有含義（比如項目名）
log-bin=edu-mysql-bin
# 為每個 session 分配的記憶體,在事務過程中用來儲存二進位日誌的緩衝
binlog_cache_size=1M
# 主從複製的格式(mixed,statement,row,預設格式是 statement)
binlog_format=mixed
# 二進位日誌自動刪除/到期的天數。預設值為 0,表示不自動刪除。
expire_logs_days=7
## 跳過主從複製中遇到的所有錯誤或指定類型的錯誤,避免 slave 端複製中斷。
## 如:1062 錯誤是指一些主鍵重複,1032 錯誤是因為主從資料庫資料不一致
slave_skip_errors=1062
# 作為從伺服器時的中繼日誌
relay_log=edu-mysql-relay-bin
# log_slave_updates 表示 slave 將複製事件寫進自己的二進位日誌
log_slave_updates=1
# 主鍵自增規則，避免主從同步ID重複的問題
auto_increment_increment=2  # 自增因子（每次加2）
auto_increment_offset=1     # 自增位移（從1開始），單數
2.2 Master配置

# 先重啟一下服務
shell> service mysqld restart 
# 登入到mysql
shell> mysql -uroot -p
# 建立資料庫同步處理的使用者，並授予相應的許可權
mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.1.206' identified by 'root123456';
# 重新整理授權表資訊
mysql> flush privileges;
# 查看binlog檔案的position（位移）和File（記錄檔）的值，從機上需要用到
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 |      120 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.3 Slave配置

# master_user和master_password：在206上執行grant replication slave...建立的使用者和密碼
# master_log_file和master_log_pos：在206上運行show master status;命令執行結果對應File和Position欄位的值
mysql> change master to master_host='192.168.1.206',master_user='repl', master_password='root123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=439, master_connect_retry=30;
# 查看作為從節點的狀態資訊
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.206
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 439
               Relay_Log_File: edu-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
          # 省略其它配置。。。
由於此時從節點還沒有啟動，Slave_IO_State的值為空白，Slave_IO_Running和Slave_SQL_Running線程為No表示也沒有運行。

2.4 啟動Slave

注意：要在節點2上建立同步帳戶後再啟動，否則會報連不上master錯誤

# 啟動從節點，開始工作接收主節點發送事件（資料庫資料變更的所有事件）
mysql> start slave;
# 此時再查看slave節點的狀態
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.206
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 439
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 287
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
            # ...省略其它配置
3. 節點2配置（192.168.1.206）

3.1 添加資料同步配置

shell> vim /etc/my.cnf
在[mysqld]中增加以下配置項：

server_id=206
binlog-ignore-db=mysql
log-bin=edu-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=edu-mysql-relay-bin
log_slave_updates=1
#ID自增從2開始，雙數
auto_increment_increment=2
auto_increment_offset=2
3.2 Master配置

# 先重啟一下服務
shell> service mysqld restart 
# 登入到mysql
shell> mysql -uroot -p
# 建立資料庫同步處理的使用者，並授予相應的許可權（只允許repl使用者從192.168.1.205上登入）
mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.1.205' identified by 'root123456';
# 重新整理授權表資訊
mysql> flush privileges;
# 查看binlog檔案的position（位移）和File（記錄檔）的值，從機上需要用到
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 |      439 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
這時可以啟動節點1(205)的slave服務

3.3 Slave配置

# master_log_file和master_log_pos：205節點上執行show master status;對應File和position的值
mysql> change master to master_host='192.168.1.205',master_user='repl', master_password='root123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=120, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.205
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: edu-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
              #...省略其它配置
3.4、啟動Slave

shell> 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.1.205
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000001
          Read_Master_Log_Pos: 439
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 287
        Relay_Master_Log_File: edu-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
              ...省略其它配置
4、驗證

# 登入205建立一個資料庫
shell> mysql -u root -p
mysql> create database if not exists mydb default character set utf8 collate utf8_general_ci;
mysql> create table user (id int, username varchar(30), password varchar(30));
mysql> insert into user values (1, 'yangxin', '123456');
# 下面是在206節點上的操作
#1、登入206查詢所有庫，是否包含mydb資料庫
#2、切換到mydb庫，是否包含user表，並有一條資料
#3、在206的mydb.user表插入一條資料，查看205是否同步過去
mysql> insert into user values (2,'yangxin2','123456')