基于mysqlrouter+gtid的半同步复制MySQL集群

读写分离

读写分离是基于主从复制来实现的。在实际的应用环境中，肯定是读操作多，就像我们在电商平台上去购买东西，可能看了100个也就买了一两个。所以读操作永远比写这种更新操作多很多。所以我们基于主从复制的读写分离配置，就是让一个主库专门用来做数据的修改，写的时候专门在主库上写，主库通过主从复制把数据的更改通过binlog同步到从库上去，那么其他的客户端查询的请求都会最终映射到从库上去，而我们一个主库带上两三个从库，主库专门用来做数据的更新（写操作），从库专门用来做读操作这样一来可以很好的分摊读写的压力，不用全部都集中在主库上，对于后端服务的并发处理能力有很大的提高，另外就是它的高可用容灾，当主库挂了以后，可以把指定的从库变成主库。

读写分离工具

MySQLrouter工作原理：

 

读写分离实验（mysqlrouter-gtid半同步复制的MySQL集群）

一、实验准备

准备八台服务器（2G，2核）

此四台服务器需安装MySQL、且配置好主从配置：
一台master
三台slave，其中一台slave做延迟backup服务器（备份延迟）

此四台服务器不需安装MySQL：
两台MySQLrouter进行数据转发
两台用作keepalived实现高可用

二、gtid实现主从复制并开启半同步

1、在所有的slave机器上导入基础数据

# master导出备份文件
[root@master mysql]# mysqldump --all-databases -uroot -p'zjx3203766770.' >all_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 正常警告，来自于密码暴露不安全。

# master将备份文件复制到slave中的/data/mysql文件夹下去
[root@master mysql]# scp /data/mysql/all_db.sql root@192.168.220.100:/data/mysql
root@192.168.220.100’s password: 
all_db.sql                                                                100%  865KB  89.7MB/s   00:00    

# slave上传备份文件，使数据与master一致
[root@slave mysql]# mysql -uroot -p'zjx3203766770.' <all_db.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

2、创建有复制权限的用户（提倡先用create创建，后用grant授权）

root@(none) 01:23  mysql>grant replication slave on *.* to 'zhang'@'192.168.220.%' identified by 'zjx3203766770.'
Query OK, 0 rows affected, 1 warning (0.02 sec)

# relication slave 授予slave复制的权限

3、开启gtid功能，启动主从复制服务

# 开启gtid功能的配置（须在所有服务器上开启）
gtid-mode=on
enforce-gtid-consistency=on

# 首先停止slave服务器上MySQL服务的slave服务
stop slave;

# 在所有slave中执行此命令
CHANGE MASTER TO MASTER_HOST='192.168.220.110' ,
MASTER_USER='zhang',
MASTER_PASSWORD='zjx3203766770.',
MASTER_PORT=3306,
master_auto_position=1;

# 重新启动slave服务器上MySQL服务的slave服务
start slave;

4、配置延迟备份服务器，从slave1上拿二进制日志

# 在延迟备份服务器上配置，同样要刷新slave服务
CHANGE MSATER TO MASTER_DELAY=1800  # 延迟30分钟

# 在slave1上配置主从复制级联，同样要刷新slave服务
log_slave_updates=ON

5、所有服务器上开启半同步

# master
root@(none) 15:37  mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
root@(none) 15:59  mysql>SET GLOBAL rpl_semi_sync_master_enabled = 1;
root@(none) 16:02  mysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| 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.01 sec)

# slave
root@(none) 15:37  mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
root@(none) 16:03  mysql>SET GLOBAL rpl_semi_sync_slave_enabled =1;
root@(none) 16:03  mysql>show variables like "%semi_sync%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.02 sec)

# 此设置都为临时配置，永久配置需要修改my.cnf文件里的配置
[root@zjx111 mysql]# cat /etc/my.cnf
[mysqld_safe]

[client]
socket=/data/mysql/mysql.sock

[mysqld]
socket=/data/mysql/mysql.sock
port=3306
open_files_limit=8192
innodb_buffer_pool_size=512M
character-set-server=utf8

# slow_log
#slow_query_log=1
#long_query_time=1
#log_slow_admin_statements=1
#general_log=1
log_bin=1
server_id=1

# 开启半同步操作
rpl_semi_sync_master_enabled=1
# 设置半同步时间阙值
rpl_semi_sync_master_timeout=3000 # 3 second

expire_logs_days=7
gtid-mode=on
enforce-gtid-consistency=on

[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>

三、部署mysqlrouter中间件软件实现读写分离

1、在master服务器上的MySQL创建验证读写分离的用户

create user 'zhuwenpei'@'%' identified by '020303';
grant all on *.* to 'zhuwenpei'@'%';

create user 'zhangjiaxin'@'%' identified by '220054';
grant select on *.* to 'zhangjiaxin'@'%';

2、在mysqlrouter服务器上添加mysqlroute的配置

[routing:read_write]
bind_address = 192.168.220.10
bind_port = 7001
mode = read-write
destinations = 192.168.220.110:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9

[routing:read_only]
bind_address = 192.168.220.10
bind_port = 7002
mode = read-only
destinations = 192.168.220.100:3306,192.168.220.200:3306,192.168.220.110:3306
max_connections = 65535
max_connec_errors = 100
client_connect_timeout = 9

3、重启mysqlrouter服务，并查看是否配置成功

# 重启mysqlrouter服务
[root@zjx ~]# service mysqlrouter restart
Redirecting to /bin/systemctl restart mysqlrouter.service

# 查看服务是否开启
[root@zjx ~]# ss -anplut | grep mysql
tcp    LISTEN     0      128    192.168.220.10:7001                  *:*                   users:(("mysqlrouter",pid=1510,fd=9))
tcp    LISTEN     0      128    192.168.220.10:7002                  *:*                   users:(("mysqlrouter",pid=1510,fd=8))

四、测试

在一台拥有mysql的同网段服务器上进行测试

# 读写登录
mysql -h 192.168.220.10 --port=7001 -uzhuwenpei -p'020303'

若登录成功且可读可写，则成功


# 只读登录
mysql -h 192.168.220.10 --port=7002 -uzhangjiaxin -p'220054'

若登录成功且只可读不可写，则成功