MySQL的一主二从异步复制(半同步复制)

来源：互联网发布：js给输入框赋值编辑：程序博客网时间：2024/05/22 07:40

如何源码安装mysql，请参考：mysql-5.7.20 源码安装

1、运行环境
ubuntu 16.04
mysql-5.7.20

此处有两种方式准备mysql主从复制：
1）、在同一台虚拟机上，初始化三个mysql服务，并设置到不同的datadir路径
2）、克隆2份安装mysql服务的虚拟机，组成三个mysql服务器

ps:
第二种方式由于克隆，导致mysql的uuid一致，因此需要对克隆的两台设置不同uuid,可通过mysql命令行 select uuid() 生成
比如我的mysql安装在/usr/local/mysql，那么修改文件为/usr/local/mysql/data/auto.cnf

此处我采用第2种方式进行配置，三台虚拟机对应为：
10.168.1.227 Master
10.168.1.228 Slave1
10.168.1.226 Slave2

2、修改/etc/my.cnf配置
2.1、Master配置，需要在/usr/local/mysql路径下创建文件夹binarylog，并创建对应的mysql_bin.index
log_bin = /usr/local/mysql/binarylog/mysql_bin.index
expire_logs_days = 7
server_id = 1

2.2、Slave配置，需要在/usr/local/mysql路径下创建文件夹binarylog，并创建对应的relay_log、relay_log.index
Slave1
relay_log = /usr/local/mysql/binarylog/relay_log
relay_log_index = /usr/local/mysql/binarylog/relay_log.index
expire_logs_days = 7
server_id = 3
read_only = 1

Slave2
relay_log = /usr/local/mysql/binarylog/relay_log
relay_log_index = /usr/local/mysql/binarylog/relay_log.index
expire_logs_days = 7
server_id = 2
read_only = 1

3、在Master上创建同步帐号，默认可同步所有库
GRANT REPLICATION SLAVE ON *.* TO 'lvfk'@'10.168.1.%' IDENTIFIED BY 'lvfk';
FLUSH PRIVILEGES;

4、在Master上查看Master端的二进制日志记录到哪里，用于决定Slave复制的起始位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000005 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#Slave服务器如果是一个空的数据库而主服务器不为空：
#在同步之前可以先用Master的全量备份，恢复到Slave数据库中。
#然后再从备份那一刻记录的Position开始复制。

#假设Master和Slave都为空，上面的情况就表示Slave从二进制日志mysql_bin.000005的154开始复制

mysqldump -uroot -p --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases test > db.sql

这里需要注意几个参数的使用：
--single-transaction 这个参数只对innodb适用。
--databases 后面跟除mysql以后的其他所有数据库的库名，我这里只有一个test库。
--master-data 参数会记录导出快照时候的mysql二进制日志位置，一会会用到。

或者执行
mysqldump -uroot -p --databases test > db.sql

5、在Slave1和Slave2上复制权限的用户账号连接至主服务器
mysql> CHANGE MASTER TO
MASTER_HOST='10.168.1.227',
MASTER_USER='lvfk',
MASTER_PASSWORD='lvfk',
MASTER_LOG_FILE='mysql_bin.000005',
MASTER_LOG_POS=154;

6、在从服务器中开启复制线程
mysql> START SLAVE;

介绍一些管理MYSQL主从同步的命令：
6.1. 停止MYSQL同步
STOP SLAVE IO_THREAD; #停止IO进程
STOP SLAVE SQL_THREAD; #停止SQL进程
STOP SLAVE; #停止IO和SQL进程
6.2. 启动MYSQL同步
START SLAVE IO_THREAD; #启动IO进程
START SLAVE SQL_THREAD; #启动SQL进程
START SLAVE; #启动IO和SQL进程
6.3. 重置MYSQL同步
RESET SLAVE;

7、查看从服务器的状态信息
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.168.1.227
Master_User: lvfk
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
Relay_Log_File: relay_log.000005
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000005
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: 154
Relay_Log_Space: 734
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
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: e13d18c2-bf6e-11e7-9a63-000c2951eef3
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

1 row in set (0.00 sec)

ERROR:
No query specified

如果Slave_IO_Running: Yes，Slave_SQL_Running: Yes 两者都为Yes，则表示主从同步配置成功

8、测试
在Master上新增一条数据
mysql> select * from user;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | lvfkk | 20 |
+----+-------+-----+
1 row in set (0.00 sec)

mysql> insert user value(null,'aa',21);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | lvfkk | 20 |
| 2 | aa | 21 |
+----+-------+-----+
2 rows in set (0.01 sec)

分别在Slave1 和 Slave2 上查看
mysql> select * from user;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | lvfkk | 20 |
| 2 | aa | 21 |
+----+-------+-----+

2 rows in set (0.00 sec)

9、延迟同步

stop slave;change master to master_delay=2;//延迟同步2秒start slave;

至此，mysql的主从异步复制搭建完成，接下来可以在此基础上搭建mysql的主从半同步复制，具体可以参考：MySQL的异步复制和半同步复制

补充：在上面搭建好的异步复制基础上搭建半同步复制

1、查看插件位置
mysql> show variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+

2、Master节点
由于上面已经进行了异步复制的配置，下面仅进行半同步复制的操作。
(1)Master安装插件并修改变量:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
(2)启用选项
mysql> SET GLOBAL VARIABLES rpl_semi_sync_master_enabled=on;
或
mysql> set global rpl_semi_sync_master_enabled=on;

3、Slave节点
(1)Master安装插件并修改变量:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
(2)启用选项
mysql> SET GLOBAL VARIABLES rpl_semi_sync_slave_enabled=1;
或者
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

4、Slave上重启复制线程
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

5、查看
Master：
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
注：设置rpl_semi_sync_master_enabled=1的效果，第一行是ON则表示半同步复制已经开启。

mysql> show status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
注：
上述状态变量中，比较重要的有以下几个

Rpl_semi_sync_master_clients

半同步复制从的个数

Rpl_semi_sync_master_no_tx
The number of commits that were not acknowledged successfully by a slave.
具体到上面的测试中，指的是insert into test.test values(2)这个事务。

Rpl_semi_sync_master_yes_tx
The number of commits that were acknowledged successfully by a slave.
具体到上面的测试中，指的是以下四个事务

create database test;
create table test.test(id int);
insert into test.test values(1);
insert into test.test values(3);

Slave：
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)

6、优化
6.1、rpl_semi_sync_master_timeout 单位是毫秒，默认为10000，即10s
防止半同步复制超时，master在超时之前没有收到确认，将恢复到异步复制，继续执行半同步没有进行的操作

7、监控相关参数
rpl_semi_sync_master_clients
配置半同步复制的Slave节点数量，只有当Master节点的半同步复制插件安装后，这个参数才会生效

rpl_semi_sync_master_status
指示Master节点的半同步复制特性是否开启。如果这个参数的值时ON，则半同步复制插件已经启用且提交响应已经发生；如果这个参数的值是OFF，则说明半同步复制插件没有启用，或者Master节点因为提交响应超时而回到异步复制模式

rpl_semi_sync_slave_status
指示Slave节点的半同步复制特性是否开启。如果这个参数的值为ON，则说明半同步复制插件已经开启且Slave节点的I/O线程在运行。只有当Slave节点安装半同步复制插件后，这个参数才会生效。

rpl_semi_sync_master_no_tx
没有被Slave节点成功响应的提交事务数

rpl_semi_sync_master_yes_tx
被Slave节点成功响应的提交事务数

rpl_semi_sync_master_net_wait_time
Master节点等待Slave节点回复的总时间，单位是微秒。只有当Master节点安装了半同步复制插件后，这个参数才会生效

rpl_semi_sync_master_net_avg_wait_time
Master节点等待Slave节点回复的平均时间，单位是微秒

阅读全文

0 0