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

2021-09-15 08:50:13

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

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

参考：

http://zhangjianjian.blog.51cto.com/2944771/1632109

http://www.it165.net/database/html/201403/5562.html

码农公寓

相关文章