MySQL的3節點主從同步復制方案

上篇文章《為什么要對MySQL做主從同步復制》我們說明了MySQL主從同步的作用，主從同步的原理和主從同步的缺點。下面我們介紹下3節點中：2個節點互為主從，1個節點作為前2個節點的從，用于實現MySQL5.6的3節點主從同步復制方案。

  主要步驟如下：

1.配置MasterA端同步復制時所需要的選項

2.在MasterA主庫上創建同步復制時的用戶并授權

3.MasterA主庫鎖表

4.記錄MasterA主庫的binlog以及pos位置節點

5.導出MasterA主庫m_s_rep數據庫

6.配置MasterB端同步復制時所需要的選項

7.在MasterB上創建數據庫m_s_rep并導入主庫備份

8.在MasterB上創建同步復制時的用戶并授權

9.記錄MasterB從庫的binlog以及pos位置節點

10.配置MasterA和MasterB互為主從并開啟從庫同步

11.查看MasterB的relay-log以及master.info

12.MasterA主庫解鎖

13.配置Slave端同步復制時所需要的選項

14.在Slave端上創建數據庫m_s_rep并導入主庫備份

15.配置Salve端為MasterA和MasterB同步并開啟同步

一．環境配置說明

  1.IP地址分配

Hostname      IP地址              節點

MYSQL01       10.62.83.201  MasterA

MYSQL02       10.62.83.202  MasterB

MYSQL03       10.62.83.203  Slave

   2.協議和端口

節點   服務   端口

MasterA       mysqld 3306

MasterB       mysqld 3306

Slave  mysqld 3306

 3.軟件包版本

軟件   版本號

OS     CentOSrelease 6.7 (Final)

MySQL  MySQL-5.6.27-1.el6.x86_64.rpm-bundle.tar

JDK    java-1.8.0-openjdk-1.8.0.20-3.b26.el6.x86_64

二．安裝MySQL前準備

在3個節點中，分別安裝如下MySQL5.6.27依賴包，執行步驟如下： 

  1.依賴包安裝

 # yum install -y openssl* openssl-devel zlib-devel gcc gcc-c++make autoconf readline-devel curl-devel expat-devel gettext-devel ruby

  2.卸載OS默認低版本的openjdk

  # rpm-qa | grep jdk
  # yum-y remove java-1.6.0 java-1.7.0

  3.安裝java運行環境openjdk-1.8.0

 # yum-y install java-1.8.0

  4.配置JDK環境變量$JAVA_HOME

  # vim/etc/profile
 exportJAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.20-3.b26.el6.x86_64/bin/java#new add content

  5.配置DNS本地域名解析

  # vim/etc/hosts
  10.62.83.201     MYSQL01 #new add content
  10.62.83.202     MYSQL02
  10.62.83.203     MYSQL03

  6.卸載OS中舊版本的mysql-libs

  # cd / && rpm-qa |grep mysql*
  # yum -y removemysql-libs-5.1.73-3.el6_5.x86_64

        7.防火墻配置

  你可以選擇關閉iptables，或者配置iptables規則。

三．安裝MySQL程序包

  在3個節點分別安裝MySQL5.6.27如下軟件包，執行步驟如下：

  1.解壓tar包

  # cd /usr/local/data
  # tar xfMySQL-5.6.27-1.el6.x86_64.rpm-bundle.tar

  2.安裝rpm包

  # yum -y installMySQL-client-5.6.27-1.el6.x86_64.rpm MySQL-server-5.6.27-1.el6.x86_64.rpm

四．主從關系配置準備    

    MySQL5.6.27初始登陸的密碼是一串salt密碼，為了便于登錄MySQL，在3個節點分別執行如下配置：

  1.登錄MySQL配置

  啟動mysql服務：

  # service mysql start
  Starting MySQL. SUCCESS!

  查看各個節點的mysql初始密鑰文件：

  # cat/root/.mysql_secret
  # The random passwordset for the root user at Thu Nov  511:12:13 2015 (local time): MzmPQ1uPJ0Sgyki4

  登錄mysql：

  # mysql -u root –p                  # 復制粘貼或手動輸入密鑰

  登錄后，設置mysql登錄的新密碼：

  mysql > SET PASSWORDFOR 'root'@'localhost' = PASSWORD('mysql');
  mysql > flushprivileges;
  mysql > exit;

  2.配置允許Client遠程登陸Server

  mysql> use mysql;
  Database changed
  mysql> selecthost,user,password from user; #默認只允許本地client遠程登錄

mysql> update user set host='%' where user='root' andhost='localhost';
mysql> flush privileges;
mysql> exit;

  注意：

  另外一種修改user密碼的方法是：

mysql> update user setpassword=password('PASSWORD') where user='USER';

 3.在MasterA上創建數據庫m_s_rep

  在MasterA上創建數據庫m_s_rep，為主從節點之間同步復制數據做準備。

  在masterA上創建數據庫和表：

  mysql> createdatabase m_s_rep;

五.主從同步復制配置

 首先，配置MasterA端和MasterB端互為主從，最后實現Slave端為從。

配置MasterA端同步復制時所需要的選項

1.復制模板文件

  # cp /usr/share/mysql/my-default.cnf  /etc/my.cnf
  # vim /etc/my.cnf

2.MasterA 節點的配置/etc/my.cnf文件如下

[mysqld]
user=mysql
server-id=1 #表示mysql服務器ID，該ID必須在該主從中是唯一的，默認是1，該ID可以自行自定義，但必須為數字。
log-bin=mysql-bin #表示啟用mysql二進制日志，該項必須要啟用，否則mysql主從不會生效
log-bin-index=mysql-bin.index
relay-log=mysql-relay
relay-log-index=mysql-relay.index
expire-logs-days=10
max-binlog-size=100M #表示每個binlog文件最大大小，當此文件大小等于100M時，會自動生成一個新的日志文件。注意：一條記錄不會寫在2個日志文件中，所以有時日志文件會超過此大小。
log-slave-updates=1
binlog-do-db=m_s_rep #表示需要同步的數據庫名字，如果是多個數據庫，就以此格式再寫一行即可。
 replicate-do-db= m_s_rep 
  binlog-ignore-db=mysql #表示不需要同步的數據庫名字，如果是多個數據庫，就以此格式再寫一行即可。
replicate-ignore-db=mysql
sync-binlog=1
auto-increment-increment=2
auto-increment-offset=1
# Remove leading # and set to the amount of RAM for the mostimportant data
# cache in MySQL. Start at 70% of total RAM for dedicatedserver, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrityoption: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reportingservers.
# The server defaults are faster for transactions and fastSELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assortedsecurity risks
symbolic-links=0
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #默認mysql mode
lower_case_table_names=1
explicit_defaults_for_timestamp=true
skip-character-set-client-handshake
collation-server=utf8_general_ci
character-set-server=utf8

  注意：

    如果binlog-do-db和binlog-ignore-db不加的話，那么默認是同步復制整個mysql數據庫。

    通過設置sql模式配置多種服務器操作特征：

        指定mysql對接受輸入數據的寬容度

        設置與其他數據庫系統的兼容性

        控制查詢處理

       －－雇用或禁用與sql符合性相關的行為。

        覆蓋sql的”空“默認模式

       －－空模式不雇用限制或符合性行為

       默認的sql模式為no_engine_substitution

        默認的配置文件會添加STRICT_TRANS_TABLES

在MasterA主庫上創建同步復制時的用戶并授權

1. 在masterA上新建2個賬戶，用于masterB和slave同步數據

mysql>  GRANTREPLICATION SLAVE ON *.* TO 'rep201'@'10.62.83.%' IDENTIFIED BY 'rep201';   
mysql> GRANTREPLICATION SLAVE ON *.* TO 'rep203'@'10.62.83.%' IDENTIFIED BY 'rep202';   
mysql> FLUSH PRIVILEGES;

mysql> select user,repl_slave_priv from user ; #查看授權的replication slave的用戶；

2.授權完畢后，我們需要在MasterB和Slave測試rep201和rep203用戶是否可以連接master。如下：

# mysql -h 10.62.83.201 -umysql01 –p

MasterA主庫鎖表

  先鎖住主庫MasterA的表，防止數據再寫入，導致主從數據庫不一致。

mysql> FLUSH TABLES WITH READ LOCK; #主庫master鎖表；
mysql> insert into TABLE values(6); #測試是否表被鎖；

  注意：鎖表命令窗口不要退出，退出后鎖表無效。

記錄MasterA 主庫的binlog以及pos位置節點

mysql> show master status;
mysql> show master status\G

  注意：這個時候數據庫仍然是被鎖定的，不要退出這個窗口，退出鎖表就無效了；

導出MasterA主庫m_s_rep數據庫

  # mysqldump -u root -p --databases m_s_rep > /tmp/m_s_rep.sql
  Enter password:
  # scp /tmp/m_s_rep.sql  root@10.62.83.202:/tmp

配置MasterB端同步復制時所需要的選項

1.復制模板文件

  # cp /usr/share/mysql/my-default.cnf  /etc/my.cnf
  # vim /etc/my.cnf

2.MasterB 節點配置/etc/my.cnf文件如下 :

[mysqld]
 
user=mysql
server-id=2
log-bin=mysql-bin
log-bin-index=mysql-bin.index
relay-log=mysql-relay
relay-log-index=mysql-relay.index
expire-logs-days=10
max-binlog-size=100M
log-slave-updates
skip-slave-start
slave-skip-errors=all
binlog-do-db=m_s_rep
replicate-do-db= m_s_rep
binlog-ignore-db=mysql
replicate-ignore-db=mysql
sync-binlog=1
auto-increment-increment=2
auto-increment-offset=2
 
#
# Remove leading # and set to the amount of RAM for the mostimportant data
# cache in MySQL. Start at 70% of total RAM for dedicatedserver, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrityoption: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reportingservers.
# The server defaults are faster for transactions and fastSELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assortedsecurity risks
symbolic-links=0
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
explicit_defaults_for_timestamp=true
skip-character-set-client-handshake
collation-server=utf8_general_ci
character-set-server=utf8

在MasterB上創建數據庫m_s_rep并導入主庫備份

1.在MasterA上復制數據庫備份

# scp /tmp/m_s_rep.sql root@10.62.83.203:/tmp/

2. 在MasterB上創建數據庫m_s_rep并導入數據庫備份

在MasterB上創建同步復制時的用戶并授權

1.在masterB創建授權賬戶，用于開啟同步

mysql> GRANTREPLICATION SLAVE ON *.* TO 'rep202'@'10.62.83.%' IDENTIFIED BY 'rep202';

mysql> select user,repl_slave_priv from user ; #查看授權的replication slave的用戶；

2.授權完畢后，我們需要在MasterA和Slave測試rep202用戶是否可以連接MasterB。

記錄MasterB 從庫的binlog以及pos位置節點

1.記錄主庫masterB的binlog文件名以及pos位置節點:

mysql> show master status;
mysql> show master status\G;

配置MasterA和MasterB互為主從并開啟從庫同步

1.在MasterB 10.62.83.202上配置MasterA的從庫，并開啟從庫同步

mysql> 
CHANGE MASTER TO MASTER_HOST='10.62.83.201',MASTER_USER='rep201', MASTER_PASSWORD='rep201', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1544, MASTER_CONNECT_RETRY=10;
mysql> start slave;

2. 在MasterA 10.62.83.201上配置MasterB的從庫，并開啟從庫同步

mysql> 
CHANGE MASTER TO MASTER_HOST='10.62.83.202', MASTER_USER='rep202',MASTER_PASSWORD='rep202', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1691, MASTER_CONNECT_RETRY=10;
mysql> start slave;

注意：

我們查看同步是否主要是查看Slave_IO_Running與Slave_SQL_Running選項。如果正常同步，這兩選項必須同時為YES。

如果Slave_IO_Running為NO，說明可能是從庫與主庫的網絡不通。

如果Slave_SQL_Running為NO，說明很可能是從庫與主庫的數據不一致。

通過上圖，我們可以看到目前Slave_IO_Running和Slave_SQL_Running都為YES。說明現在主從同步是正常的。

并且通過上圖，我們也可以看到從庫slave與主庫master剛開始同步時的binlog文件名以及開始同步時的pos位置節點。

查看MasterB的relay-log以及master.info

1. 查看MasterB的relay-log

現在我們來查看MasterB的relay-log以及master.info信息，我們首先看relay-log信息，如下：

# mysqlbinlog mysql-relay-bin.000002|more

通過這張圖，我們可以在relay-log日志中看到，從庫MasterB開始同步主庫MasterA的binlog文件名以及同步復制時的pos位置節點。

2.現在我們來查看MasterB的master.info，如下：

# cat master.info |more

可以看到這個文件保存了從庫MasterB同步主庫MasterA時的相關信息：IP、用戶、密碼、binlog文件名、pos位置節點、同步周期。

3.查看MasterB的進程狀態

mysql> SHOW PROCESSLIST\G

注意：

查看狀態, 其中第1 、第2 和第3進程上是此服務器做為從開始的3個線程;第4的BinlogDump進程做為主提供給從的發生binlog的線程;

4.查看MasterA的進程狀態

注意：

查看狀態, 其中第3 、第4 和第5進程上是此服務器做為從開始的3個線程;第1和第2的Binlog Dump進程做為主提供給從的發生binlog的線程;

MasterA主庫解鎖

mysql> UNLOCK TABLES;

配置Slave端同步復制時所需要的選項

1.復制模板文件

  # cp /usr/share/mysql/my-default.cnf  /etc/my.cnf
  # vim /etc/my.cnf

2.Slave端配置/etc/my.cnf文件如下:

[mysqld]
user=mysql
 
server-id=3
log-bin=mysql-bin
log-bin-index=mysql-bin.index
relay-log=mysql-relay
relay-log-index=mysql-relay.index
expire-logs-days=10
max-binlog-size=100M
log-slave-updates
skip-slave-start
slave-skip-errors=all
binlog-do-db= m_s_rep
replicate-do-db= m_s_rep
binlog-ignore-db=mysql
replicate-ignore-db=mysql
sync-binlog=1
 
#
# Remove leading # and set to the amount of RAM for the mostimportant data
# cache in MySQL. Start at 70% of total RAM for dedicatedserver, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrityoption: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reportingservers.
# The server defaults are faster for transactions and fastSELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assortedsecurity risks
symbolic-links=0
 
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
explicit_defaults_for_timestamp=true
skip-character-set-client-handshake
collation-server=utf8_general_ci
character-set-server=utf8

注意：修改文件/etc/my.cnf后重新啟動mysql服務；

在Slave端上創建數據庫m_s_rep并導入主庫備份

1.在Slave端創建數據庫m_s_rep

2.在MasterA上復制數據庫備份

# scp /tmp/m_s_rep.sql root@10.62.83.203:/tmp/

3.導入主庫數據備份

注意：導入數據之前，確保數據的一致性；

配置Salve端為MasterA和MasterB同步并開啟同步

1. 在Slave 10.62.83.203上配置MasterA的從庫，并開啟從庫同步

mysql> 
CHANGE MASTER TO MASTER_HOST='10.62.83.201',MASTER_USER='rep203', MASTER_PASSWORD='root', MASTER_PORT=3306,   MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1544,MASTER_CONNECT_RETRY=10;
mysql> start slave;

下接《MySQL的3節點主從同步復制方案測試》