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节点主从同步复制方案测试》