MySQL5.6一主多从的半同步复制实例

jyhrte

半同步简介:
        在默认情况下，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、由于是最小化安装的系统，需要安装一些基本的组建

1 2 3 4

# yum -y groupinstall "Perl Support" # yum -y instdall vim wget # cd /usr/local/src # wget http://mirrors.sohu.com/mysql/My ... bc2.5-x86_64.tar.gz

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

1 2 3 4

# 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

1 2 3 4 5 6 7

# tar zxvf mysql-5.6.24-linux-glibc2.5-x86_64.tar.gz -C /usr/local # cd /usr/local/ # 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提供主配置文件

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

2.5、为mysql配置sysv服务脚本

1 2 3 4

# 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，添加如下行即可

1	MANPATH  /usr/local/mysql/man

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

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

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

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

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

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

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

以上安装过程在三台服务器军需执行。
三、主从模式配置
3.1、配置master节点：
[iyunv@master ~]# cat /etc/my.cnf

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[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 report-port=3306 port=3306 datadir=/mydata/data socket=/tmp/mysql.sock report-host=master rpl_semi_sync_master_enabled=ON

配置参数简要说明：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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节点

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

[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 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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

[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 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、创建复制用户
在主库上执行

1 2 3

mysql> GRANT REPLICATION SLAVE ON *.* TOslave@192.168.1.201 IDENTIFIED BY 'passwd'; mysql> GRANT REPLICATION SLAVE ON *.* TOslave@192.168.1.202 IDENTIFIED BY 'passwd'; mysql> FLUSH PRIVILEGES;

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

1	master> INSTALL PLUGINrpl_semi_sync_master SONAME 'semisync_master.so';

在从库上执行

1	slave> INSTALL PLUGINrpl_semi_sync_slave SONAME 'semisync_slave.so';

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

1 2 3

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

可以看到主从服务器已经启用gtid功能。
在主库上查看已连接的slave主机

1 2 3 4 5 6 7 8

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的状态

1	mysql> show slave statusG;

可以看到此时两个从库的复制进度是一样的。
3.5、在主库写入数据进行测试

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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)

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

slave1> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | just_for_test      | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.03 sec) slave1> show slave statusG; *************************** 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 statusG; *************************** 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、主服务器上生成私钥

1 2 3 4

# 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、主服务器上生成自签证书

1 2 3

# 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

权限设置

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

4.3、为从服务器生成证书

1 2	# 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服务器上

1 2	# 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配置文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

# 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(该步骤两台从服务器相同)

1	# mkdir /usr/local/mysql/ssl

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

1 2 3 4 5 6 7 8

# 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

启动从服务器上的复制

1 2 3 4

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

可以看到如下信息

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

mysql> show slave statusG; *************************** 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

        但是到这里之后发现了一个问题，那就是我使用的编译好的mysql二进制包不支持ssl,要想支持需要重新编译mysql,限于时间有限，这里就不重来了，有需要的朋友就自己折腾下吧。