MySQL 5.6 一主多从的半同步复制搭建（已纠正）

本文主要是介绍MySQL 5.6 一主多从的半同步复制搭建（已纠正），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL 5.6 一主多从的半同步复制搭建

半同步简介:

在默认情况下，MySQL的复制是异步的，这意味着主服务器及其从服务器是独立的。异步复制可以提供最佳的性能，因为主服务器在将更新的数据写入它的二进制日志（Binlog）文件中后，无需等待验证更新数据是否已经复制到从服务器中，就可以自由处理其它进入的事务处理请求。但这也同时带来了很高的风险，如果在主服务器或从服务器端发生故障，会造成主从数据的不一致，甚至在恢复时造成数据丢失。

从MySQL5.5开始引入了一种半同步复制功能，该功能可以确保主服务器和访问链中至少一台从服务器之间的数据一致性和冗余。在这种配置结构中，一台主服务器和其许多从服务器都进行了配置，这样在复制拓扑中，至少有一台从服务器在父主服务器进行事务处理前，必须确认更新已经收到并写入了其中继日志（Relay Log）。当出现超时，源主服务器必须暂时切换到异步复制模式重新复制，直到至少有一台设置为半同步复制模式的从服务器及时收到信息。

继5.5半同步复制后，MySQL5.6又对其进行了优化和改进，其中有两个地方较为重要：

1、在主从切换后，在传统的方式里，需要找到binlog和POS点，然后更改master指向，而在mysql5.6里，你无须再知道binlog和POS点，你只需要知道master的IP、端口，账号密码即可，因为同步复制是自动的，mysql通过内部机制GTID自动找点同步。

2、多线程复制，以前的版本，同步复制是单线程的，只能一个一个执行，在MySQL5.6里，可以做到多个库之间的多线程复制，但一个库里的表，多线程复制是无效的。

一、试验环境准备

1.1、安装平台:vmware workstation 10

操作系统：CentOS release 6.6 (Final) x86_64，最小化安装。

MySQL版本：mysql-5.6.24-linux-glibc2.5-x86_64

关闭防火墙

1.2、服务器IP地址规划：

MySQL Master:192.168.1.152

MySQL Slave1:192.168.1.201

MySQL Slave2:192.168.1.202

1.3、实验拓扑：

二、安装Mysql5.6

2.1、由于是最小化安装的系统，需要安装一些基本的组建

  # yum -y groupinstall "Perl Support" 
 # yum -y instdall vim wget 
 # cd /usr/local/src 
 # wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz 
 

2.2、新建用户和数据目录。

  # mkdir -pv /mydata/data 
 # groupadd -r mysql 
 # useradd -g mysql -r -s /sbin/nologin -M -d /mydata/data mysql 
 # chown -R mysql:mysql /mydata/data 
 

2.3、安装并初始化mysql

  # tar zxvf mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz -C /usr/local 
 # cd /usr/local/ 
 方法一（推荐） 
 mv mysql-5.6.24-linux-glibc2.5-x86_64  mysql 
 
  
 方法二（不建议） 
 # ln -sv mysql-5.6.24-linux-glibc2.5-x86_64  mysql 
 
  
 # cd mysql 
 # chown -R mysql:mysql . 
 # scripts/mysql_install_db --user=mysql --datadir=/mydata/data 
 # chown -R root . 
 

2.4、为mysql提供主配置文件

  # cd /usr/local/mysql 
 # cp support-files/my-default.cnf /etc/my.cnf 
 

2.5、为mysql配置sysv服务脚本

  # cd /usr/local/mysql 
 # cp support-files/mysql.server  /etc/rc.d/init.d/mysqld 
 # chkconfig --add mysqld    //添加至服务列表 
 # chkconfig mysqld on 
 

为了使用mysql的安装符合系统使用规范，并将其开发组件导出给系统使用，这里还需要进行如下步骤:

2.6、输出mysql的man手册至man命令的查找路径。

编辑/etc/man.config，添加如下行即可

  MANPATH  /usr/local/mysql/man 
 

2.7、输出mysql的头文件至系统头文件路径/usr/include

这可以通过简单的创建链接实现

  # ln -sv /usr/local/mysql/include  /usr/include/mysql 
 

2.8、输出mysql的库文件给系统库查找路径。

  # echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf 
 # ldconfig    //让系统重新载入系统库 
 

2.9、修改PATH环境变量，让系统可以直接使用mysql的相关命令。

  # echo "export PATH=$PATH:/usr/local/mysql/bin " >/etc/profile.d/mysql.sh 
 # source /etc/profile.d/mysql.sh 
 

vim /etc/my.cnf

datadir = /mydata/data #添加

log-error = /mydata/data/error.log #添加

#pid-file = /mydata/data/mysql.pid #注释掉

tmpdir = /var/tmp #不建议放在/tmp下

service mysqld start

以上安装过程在三台服务器均需执行。

三、主从模式配置

3.1、配置master节点：

[root@master ~]# cat /etc/my.cnf

  [mysqld] 
 binlog-format=ROW 
 log -bin=master-bin 
 log -slave-updates= true 
 gtid-mode=on  
 enforce-gtid-consistency= true 
 master-info-repository=TABLE 
 relay- log -info-repository=TABLE 
 sync-master-info=1 
 slave-parallel-workers=2 
 binlog-checksum=CRC32 
 master-verify-checksum=1 
 slave-sql-verify-checksum=1 
 binlog-rows-query-log_events=1 
 server-id=1  #改成主机的ID 
 report-port=3306 
 port=3306 
 datadir=/mydata/data 
 socket=/tmp/mysql.sock 
 report-host=master   #改成自己的主机名 
 rpl_semi_sync_master_enabled=ON 
 rpl_semi_sync_master_enabled=ON 
 

禁用 rpl_semi_sync_master_enabled=ON 参数不然启动报错

注意：添加

 上面的配置文件都可以不要，对于新手来说，看不懂，就很容易出错，新手朋友照着这个方案搭建起来也不能主从复制 

选填下面的my.cnf配置

 # The following options will be passed to all MySQL clients
 [client]
 #password       = your_password
 port            = 3306
 socket          = /tmp/mysql.sock
 
 
 # Here follows entries for some specific programs
 
 
 # The MySQL server
 [mysqld]
 socket  = /tmp/mysql.sock
 basedir = /usr/local/mysql
 datadir = /data/mysql
 tmpdir = /tmp
 slave-load-tmpdir = /tmp
 pid-file = /data/mysql/mysql.pid
 #skip-name-resolve
 ##skip-symbolic-links
 #replicate-wild-ignore-table = mysql.%
 #replicate-wild-ignore-table = test.%
 #log-bin-index = mysql-bin.index
 #relay-log-index = relay-log.index
 max_connect_errors = 10000
 max_connections = 500
 wait-timeout = 30
 skip-external-locking
 key_buffer_size = 16M
 max_allowed_packet = 1M
 table_open_cache = 64
 sort_buffer_size = 512K
 net_buffer_length = 8K
 read_buffer_size = 256K
 read_rnd_buffer_size = 512K
 myisam_sort_buffer_size = 8M
 #new add DBA
 server-id=11
 report-port=3306
 port=3306
 log-bin=mysql-bin.log
 #report-h
  

 
 
 [mysqldump]
 quick
 max_allowed_packet = 16M
 
 
 [mysql]
 no-auto-rehash
 # Remove the next comment character if you are not familiar with SQL
 #safe-updates
 
 
 [myisamchk]
 key_buffer_size = 20M
 sort_buffer_size = 20M
 read_buffer = 2M
 write_buffer = 2M
 
 
 [mysqlhotcopy]
 interactive-timeout
  

 mysql> show variables like '%dir%';  
 +-----------------------------------------+----------------------------+
 | Variable_name                           | Value                     |
 +-----------------------------------------+----------------------------+
 | basedir                                 | /usr                       |                      | |
 | datadir                                 | /var/lib/mysql/            |  |
 | slave_load_tmpdir                       | /tmp                       |
 | tmpdir                                  | /tmp                       |
 +-----------------------------------------+----------------------------+
  

配置参数简要说明：

  binlog-format：二进制日志的格式，有row、statement和mixed三种类型；当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致； 
 log -bin： 二进制日志存名，可指定存放路径； 
 log -slave-updates：slave更新时是否记录到日志中； 
 gtid-mode：指定GTID的类型，否则就是普通的复制架构； 
 enforce-gtid-consistency：是否强制GTID的一致性； 
 master-info-repository和relay- log -info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能； 
 binlog-rows-query- log -events：启用之可用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度； 
 server-id：同一个复制拓扑中的所有服务器的id号必须惟一； 
 report-port：产生复制报告时在哪个端口上提供相关功能； 
 report-host：产生复制报告时在哪个主机上提供相关功能，一般为自己的主机名； 
 sync-master-info：启用之可确保服务器崩溃时无信息丢失； 
 slave-paralles-workers：设定从服务器启动几个SQL复制线程数；0表示关闭多线程复制功能；数字太大也无意义最好与要复制的数据库的数目相同 
 binlog-checksum：复制时是否校验二进制文件的完整性等相关功能；binlog的校验格式校验算法（CRC32：循环冗余校验码32位）； 
 datadir：数据文件存放路径； 
 master-verify-checksum：检验主服务器二进制日志的相关功能； 
 slave-sql-verify-checksum：校验从服务器中继日志的相关功能的； 
 rpl_semi_sync_master_enabled：是否开启半同步复制； 
 

3.2、配置slave节点

[root@slave1 ~]# cat /etc/my.cnf

  [mysqld] 
 binlog-format=ROW 
 log -slave-updates= true 
 gtid-mode=on  
 enforce-gtid-consistency= true 
 master-info-repository=TABLE 
 relay- log -info-repository=TABLE 
 sync-master-info=1 
 slave-parallel-workers=2 
 binlog-checksum=CRC32 
 master-verify-checksum=1 
 slave-sql-verify-checksum=1 
 binlog-rows-query-log_events=1 
 server-id=11  #改成主机的ID 
 report-port=3306 
 port=3306 
 log -bin=mysql-bin. log 
 datadir=/mydata/data 
 socket=/tmp/mysql.sock 
 report-host=slave1   #改成自己的主机名 
 rpl_semi_sync_slave_enabled=ON 
 

选填：

basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
socket=/var/lib/mysql/mysql.sock
slave_load_tmpdir=/tmp
max_connect_errors = 10000
max_connections = 500
wait-timeout = 30
#new add dba
log-bin=master-bin
server-id=1
report-port=3306
port=3306

report-host=WIN-P11PIHOSG63.lan
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

说明：楼主搭建测试是5.5以后的半同步复制（新功能），建议数据库版本一致，不要用5.6到5.5，如果不懂my.cnf配置的新手会出很多问题。

针对半同步复制技术，建议在MHA里面打开，如果是测试环境不要要求那么高，不然会出问题。当然，看你怎么去思考。

[root@slave2 ~]# cat /etc/my.cnf

  [mysqld] 
 binlog-format=ROW 
 log -slave-updates= true 
 gtid-mode=on  
 enforce-gtid-consistency= true 
 master-info-repository=TABLE 
 relay- log -info-repository=TABLE 
 sync-master-info=1 
 slave-parallel-workers=2 
 binlog-checksum=CRC32 
 master-verify-checksum=1 
 slave-sql-verify-checksum=1 
 binlog-rows-query-log_events=1 
 server-id=12  #改成主机的ID 
 report-port=3306 
 port=3306 
 log -bin=mysql-bin. log 
 datadir=/mydata/data 
 socket=/tmp/mysql.sock 
 report-host=slave2   #改成自己的主机名 
 rpl_semi_sync_slave_enabled=ON 
 

3.3、创建复制用户

在主库上执行

  mysql> GRANT REPLICATION SLAVE ON *.* TO slave@192.168.1.201（备库IP） IDENTIFIED BY  'passwd' ; 
 mysql> GRANT REPLICATION SLAVE ON *.* TO slave@192.168.1.202（备库IP） IDENTIFIED BY  'passwd' ; 
 mysql> FLUSH PRIVILEGES; 
 

3.4、在主从节点上安装相关的插件并启动复制

在主库上执行

  master> INSTALL PLUGIN rpl_semi_sync_master SONAME  'semisync_master.so' ; 
 

在从库上执行

  slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME  'semisync_slave.so' ; 
 

查看主库的复制线程和pid号：

mysql> show master status\G;
*************************** 1. row ***************************
File: master-bin.000001
Position: 539
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 22915959-e5e3-11e5-a894-000c29c0d786:1-2
1 row in set (0.00 sec)

安装完毕后在两从库上定义到主库的复制连接

  mysql> CHANGE MASTER TO MASTER_HOST= '192.168.1.152( 主库IP )' ,MASTER_USER= 'slave' , MASTER_PASSWORD= 'passwd' , MASTER_AUTO_POSITION=1; 
 mysql> START SLAVE; 
 mysql> show global variables like '%gtid%' ; 
 

可以看到主从服务器已经启用gtid功能。

在主库上查看已连接的slave主机

  mysql> show slave hosts; 
 +-----------+--------+------+-----------+--------------------------------------+ 
 | Server_id | Host   | Port | Master_id | Slave_UUID                           | 
 +-----------+--------+------+-----------+--------------------------------------+ 
 |        11 | slave1 | 3306 |         1 | 9cb0c60a-9676-11e4-9d56-000c2989857d | 
 |        12 | slave2 | 3306 |         1 | 485767ea-9676-11e4-9d54-005056291b33 | 
 +-----------+--------+------+-----------+--------------------------------------+ 
 2 rows in set (0.00 sec) 
 

查看slave1的复制状态

再去查看slave2的状态

  mysql> show slave status\G; 
 

可以看到此时两个从库的复制进度是一样的。

3.5、在主库写入数据进行测试

  mysql> create database just_for_test; 
 Query OK, 1 row affected (0.15 sec) 
 mysql> show databases; 
 +--------------------+ 
 | Database           | 
 +--------------------+ 
 | information_schema | 
 | just_for_test      | 
 | mysql              | 
 | performance_schema | 
 | test               | 
 +--------------------+ 
 5 rows in set (0.01 sec) 
 

在从库上执行查询可以看到

  slave1> show databases; 
 +--------------------+ 
 | Database           | 
 +--------------------+ 
 | information_schema | 
 | just_for_test      | 
 | mysql              | 
 | performance_schema | 
 | test               | 
 +--------------------+ 
 5 rows in set (0.03 sec) 
 slave1> show slave status\G; 
 *************************** 1. row *************************** 
                 Slave_IO_State: Waiting  for  master to send event 
                    Master_Host: 192.168.1.152 
                    Master_User: slave 
                    Master_Port: 3306 
                  Connect_Retry: 60 
                Master_Log_File: master-bin.000002 
            Read_Master_Log_Pos: 360 
                 Relay_Log_File: slave1-relay-bin.000005 
                  Relay_Log_Pos: 572 
          Relay_Master_Log_File: master-bin.000002 
               Slave_IO_Running: Yes 
              Slave_SQL_Running: Yes 
 从库2 
 slave2> show databases; 
 +--------------------+ 
 | Database           | 
 +--------------------+ 
 | information_schema | 
 | just_for_test      | 
 | mysql              | 
 | performance_schema | 
 | test               | 
 +--------------------+ 
 5 rows in set (0.11 sec) 
 slave2> show slave status\G; 
 *************************** 1. row *************************** 
                 Slave_IO_State: Waiting  for  master to send event 
                    Master_Host: 192.168.1.152 
                    Master_User: slave 
                    Master_Port: 3306 
                  Connect_Retry: 60 
                Master_Log_File: master-bin.000002 
            Read_Master_Log_Pos: 360 
                 Relay_Log_File: slave2-relay-bin.000003 
                  Relay_Log_Pos: 572 
          Relay_Master_Log_File: master-bin.000002 
               Slave_IO_Running: Yes 
              Slave_SQL_Running: Yes 
 

四、配置基于ssl的加密

4.1、主服务器上生成私钥

  # mkdir /usr/local/mysql/ssl 
 # cd /usr/local/mysql/ssl 
 # umask 077 openssl genrsa 2048 > ca-key.pem 
 # openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem 
 

4.2、主服务器上生成自签证书

  # openssl req -newkey rsa:2048 -days 3650 -nodes -keyout master-key.pem -out master-req.pem 
 # openssl rsa -in master-key.pem -out master-key.pem 
 # openssl x509 -req -in master-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out master-cert.pem 
 

权限设置

  # chown mysql.mysql -R * 
 # chmod 600 * 
 

4.3、为从服务器生成证书

  # openssl req -newkey rsa:2048 -days 3650 -nodes -keyout slave-key.pem -out slave-req.pem 
 # openssl x509 -req -in slave-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out slave-cert.pem 
 

将CA证书、签署的slave端证书、master证书及私钥传送到slave服务器上

  # scp ca-cert.pem slave-key.pem slave-cert.pem  master-cert.pem  master-key.pem 192.168.1.201:/usr/local/mysql/ssl/ 
 # scp ca-cert.pem slave-key.pem slave-cert.pem  master-cert.pem  master-key.pem 192.168.1.202:/usr/local/mysql/ssl/ 
 

4.4、配置主从ssl

编辑master服务器my.cnf配置文件

  # vim /etc/my.cnf 
 //添加内容如下 
 ssl 
 ssl-ca = /usr/local/mysql/ssl/ca-cert.pem 
 ssl-cert = /usr/local/mysql/ssl/master-cert.pem 
 ssl-key = /usr/local/mysql/ssl/master-key.pem 
 //更改证书属主属组 
 # chown mysql.mysql -R /usr/local/mysql/ssl 
 # chmod 600 * /usr/local/mysql/ssl 
 //重启mysql服务 
 # service mysqld restart 
 //在主服务器上创建ssl连接用户 
 mysql> grant replication client,replication slave on*.* to  "ssl" @ "192.168.1.201"  identified by  'mysql' ; 
 mysql> grant replication client,replication slave on*.* to  "ssl" @ "192.168.1.202"  identified by  'mysql' ; 
 mysql> flush privileges; 
 

配置从服务器ssl(该步骤两台从服务器相同)

  # mkdir /usr/local/mysql/ssl 
 

编辑slave服务器my.cnf配置文件

  # vim /etc/my.cnf  
 //添加内容如下 
 ssl 
 ssl-ca = /usr/local/mysql/ssl/ca-cert.pem 
 ssl-cert = /usr/local/mysql/ssl/slave-cert.pem 
 ssl-key = /usr/local/mysql/ssl/slave-key.pem 
 //重启mysql服务 
 # service mysqld restart 
 

启动从服务器上的复制

  mysql > stop slave; 
 mysql > change master to master_host= '192.168.1.152' , master_user= 'ssl' ,master_password= 'mysql' ,MASTER_AUTO_POSITION=1,master_ssl=1,master_ssl_ca= '/usr/local/mysql/ssl/ca-cert.pem' ,master_ssl_cert= '/usr/local/mysql/ssl/master-cert.pem' ,master_ssl_key= '/usr/local/mysql/ssl/master-key.pem' ; 
 mysql> start slave; 
 mysql> show slave status \G 
 

可以看到如下信息

  mysql> show slave status\G; 
 *************************** 1. row *************************** 
                 Slave_IO_State: Waiting  for  master to send event 
                    Master_Host: 192.168.1.152 
                    Master_User: ssl 
                    Master_Port: 3306 
                  Connect_Retry: 60 
                Master_Log_File: master-bin.000003 
            Read_Master_Log_Pos: 872 
                 Relay_Log_File: slave2-relay-bin.000002 
                  Relay_Log_Pos: 411 
          Relay_Master_Log_File: master-bin.000003 
               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:  
                     Last_Errno: 0 
                     Last_Error:  
                   Skip_Counter: 0 
            Exec_Master_Log_Pos: 872 
                Relay_Log_Space: 616 
                Until_Condition: None 
                 Until_Log_File:  
                  Until_Log_Pos: 0 
             Master_SSL_Allowed: Yes 
             Master_SSL_CA_File: /usr/local/mysql/ssl/ca-cert.pem 
             Master_SSL_CA_Path:  
                Master_SSL_Cert: /usr/local/mysql/ssl/master-cert.pem 
              Master_SSL_Cipher:  
                 Master_SSL_Key: /usr/local/mysql/ssl/master-key.pem 
          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: 1 
                    Master_UUID: 963e99b9-9674-11e4-9d49-000c297410f1 
               Master_Info_File: mysql.slave_master_info 
                      SQL_Delay: 0 
            SQL_Remaining_Delay: NULL 
        Slave_SQL_Running_State: Slave has read all relay  log ; waiting  for  the slave I/O  thread  to update it 
             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: 963e99b9-9674-11e4-9d49-000c297410f1:1-6 
                  Auto_Position: 1 
 