GTID即全局事务ID(global transaction identifier),GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增,所以GTID能够保证每个MySQL实例事务的执行(不会重复执行同一个事务,并且会补全没有执行的事务)。下面是一个GTID的具体形式:
4e659069-3cd8-11e5-9a49-001c4270714e:1-77
通过GTID来进行主从复制,不用进行二进制日志与master POS号的设定
- 半同步复制的原理
1、当Slave主机连接到Master时,能够查看其是否处于半同步复制的机制。
2、当Master上开启半同步复制的功能时,至少应该有一个Slave开启其功能。此时,一个线程在Master上提交事务将受到阻塞,直到得知一个已开启半同步复制功能的Slave已收到此事务的所有事件,或等待超时。
3、当一个事务的事件都已写入其relay-log中且已刷新到磁盘上,Slave才会告知已收到。
4、如果等待超时,也就是Master没被告知已收到,此时Master会自动转换为异步复制的机制。当至少一个半同步的Slave赶上了,Master与其Slave自动转换为半同步复制的机制。
5、半同步复制的功能要在Master,Slave都开启,半同步复制才会起作用;否则,只开启一边,它依然为异步复制。- 同步,异步,半同步复制的对比
- 同步复制:Master提交事务,直到事务在所有的Slave都已提交,此时才会返回客户端,事务执行完毕。缺点:完成一个事务可能会有很大的延迟。
- 异步复制:当Slave准备好才会向Master请求binlog。缺点:不能保证一些事件都能够被所有的Slave所接收。
- 半同步复制:半同步复制工作的机制处于同步和异步之间,Master的事务提交阻塞,只要一个Slave已收到该事务的事件且已记录。它不会等待所有的Slave都告知已收到,且它只是接收,并不用等其完全执行且提交。
master:server1:172.25.11.1
slave:server2:172.25.11.2
server2 stop slave;
server1 添加gtid模式
vim /etc/my.cnf
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=mysql-bin
server-id=1
gtid-mode=on
enforce-gtid-consistency=1
grant replication slave on *.* to repl@'172.25.11.%' identified by 'Yakexi+007';
Flush privileges;
show master status;
systemctl restart mysqld
server2
im /etc/my.cnf
server-id=2
gtid-mode=on
enforce-gtid-consistency=1
mysql> change master to master_host='172.25.11.1',
> master_user='repl', # 授权用户
> master_password='Yakexi+007', # 授权用户的密码
> master_log_file='mysql-bin.000002', # master的二进制文件
> master_log_pos=1247;
start slave;
systemctl restart mysqld
下面加入半同步
主从库都要检查
show variables like 'have_dynamic_loading';
server1
主库
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=ON;
show variables like '%semi%';
server2从库
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=ON;
show variables like '%semi%';
server1查看
show status like '%rpl_semi_sync%';
on为打开
slave 重起IO
stop slave io_thread;
start slave io_thread;
通过关闭slave io_thread 来进行测试
slave端关闭IO线程
stop slave io_thread;
master端进行测试
show databases;
现在三台
server4 5 6
4 master
5 6 slave 1 2
slave并行复制
[root@localhost mysql]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2 ##服务器标识
binlog_format=row
gtid_mode=ON ## 开启gtid模式
enforce-gtid-consistency=true ## 强制gtid复制
slave-parallel-type=LOGICAL_CLOCK ##开启逻辑时钟的复制
slave-parallel-workers=10 ##最大线程10
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
-
保存退出并重启mysql
[root@localhost mysql]# systemctl restart mysqld
这时已经有了10个线程
-
主库master配置binlog_group_commit
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1 ##服务器标识
binlog_format=row
log-bin=mysql-bin ##开启二进制日志
gtid_mode=ON ## 开启gtid模式
enforce-gtid-consistency=true ## 强制gtid复制
rpl_semi_sync_master_enabled = 1
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_timeout = 1000 # 1s
##不配置binlog_group_commit从库无法做到基于事物的并行复制。
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10
##为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit=1
[root@localhost mysql]# systemctl restart mysqld
mysql> use mysql;
Database changed
mysql> show tables;
|....................................|
| slave_worker_info |
五、增强半同步
半同步复制
默认情况下,MySQL的复制是异步的,master将新生成的binlog发送给各 slave后,无需等待slave的ack回复(slave将接收到的binlog写进relay log后才会回复ack),直接就认为这次DDL/DML成功了。半同步复制(semi-synchronous replication)是指master在将新生成的binlog发送给各slave时,只需等待一个(默认)slave返回的ack信息就返回成功。
MySQL 5.7对半同步复制作了大改进,新增了一个master线程。在MySQL 5.7以前,master上的binlog dump线程负责两件事:dump日志给slave的io_thread;接收来自slave的ack消息。它们是串行方式工作的。在MySQL 5.7中,新增了一个专门负责接受ack消息的线程ack collector thread。这样master上有两个线程独立工作,可以同时发送binlog到slave和接收slave的ack。还新增了几个变量,其中最重要的 是 rpl_semi_sync_master_wait_point ,它使得MySQL半同步复制有两种工作模型。解释如下。
半同步复制的两种类型
从MySQL 5.7.2开始,MySQL支持两种类型的半同步复制。这两种类型由变量 rpl_semi_sync_master_wait_point (MySQL 5.7.2之前没有该变量)控制,它有两种值:AFTER_SYNC和AFTER_COMMIT。在MySQL 5.7.2之后,默认值为AFTER_SYNC,在此版本之前,等价的类型为AFTER_COMMIT。这个变量控制的是master何时提交、何时接收 ack以及何时回复成功信息给客户端的时间点。
- AFTER_SYNC模式:master将新的事务写进binlog(buffer),然后发送给slave,再sync到自己的binlog file(disk)。之后才允许接收slave的ack回复,接收到ack之后才会提交事务,并返回成功信息给客户端。
- AFTER_COMMIT模式:master将新的事务写进binlog(buffer),然后发送给slave,再sync到自己的binlog file(disk),然后直接提交事务。之后才允许接收slave的ack回复,然后再返回成功信息给客户端。
画图理解就很清晰。(前提:已经设置了sync_binlog=1,否则binlog刷盘时间由操作系统决定)
再来分析下这两种模式的优缺点。
- AFTER_SYNC:
-
- 对于所有客户端来说,它们看到的数据是一样的,因为它们看到的数据都是在接收到slave的ack后提交后的数据。
- 这种模式下,如果master突然故障,不会丢失数据,因为所有成功的事务都已经写进slave的relay log中了,slave的数据是最新的。
- AFTER_COMMIT:
-
- 不同客户端看到的数据可能是不一样的。对于发起事务请求的那个客户端,它只有在master提交事务且收到slave的ack后才能看 到提交的数据。但对于那些非本次事务的请求客户端,它们在master提交后就能看到提交后的数据,这时候master可能还没收到slave的ack。
- 如果master收到ack回复前,slave和master都故障了,那么将丢失这个事务中的数据。
在MySQL 5.7.2之前,等价的模式是 AFTER_COMMIT ,在此版本之后,默认的模式为 AFTER_SYNC ,该模式能最大程度地保证数据安全性,且性能上并不比 AFTER_COMMIT 差。
master加载
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
|。。。。。。。。。。。。。。。。。。。。。。。。。。。。。|
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
查看加载模块信息
mysql> show variables like '%rpl_semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| 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)
mysql> set global rpl_semi_sync_master_enabled=1; ##global全局 启动半同步
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%rpl_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.01 sec)
修改半同步默认超时时间
mysql> set global rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%rpl_semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| 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)
########################## 以上的启动方式是在命令行操作 ########################################
slave1加载模块
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so'; ##加载slave模块
mysql> set global rpl_semi_sync_slave_enabled=1; ##开启slave模块
mysql> show variables like '%rpl_semi%'; ##查看信息
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.01 sec)
mysql> STOP SLAVE IO_THREAD; ##关闭slaveIO线程
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE IO_THREAD; ##开启slaveIO线程
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
########################## 以上的启动方式是在命令行操作 ########################################
主Master:配置文件里加载semisync_master.so
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1 ##服务器标识
binlog_format=row
log-bin=mysql-bin ##开启二进制日志
gtid_mode=ON
enforce-gtid-consistency=true
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000 # 1s
保存退出并重启mysql
[root@localhost mysql]# systemctl restart mysqld
Slave1: 配置文件里加载semisync_slave.so,并开启bin-log日志
[root@localhost mysql]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2 ##服务器标识
binlog_format=row
gtid_mode=ON ## 开启gtid模式
log-bin=mysql-bin-slave1 ##开启bin-log日志
log-slave-updates=ON
enforce-gtid-consistency=true ## 强制gtid复制
slave-parallel-type=LOGICAL_CLOCK ##开启逻辑时钟的复制
slave-parallel-workers=4 ##最大线程16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
保存退出并重启mysql
[root@localhost mysql]# systemctl restart mysqld
授权:
mysql> grant REPLICATION SLAVE ON *.* to otter@'192.168.148.%' identified by '!tp!gNp667aPT';
Query OK, 0 rows affected, 1 warning (0.00 sec)
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
再加一台slave2
slave2:配置server-id,开启gtid
在slave2上配置server-id,开启gtid
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=3 ##服务器标识
binlog_format=row
gtid_mode=ON ## 开启gtid模式
enforce-gtid-consistency=true ## 强制gtid复制
保存并退出,重启mysql。
mysql>change master to master_host='192.168.148.61',
master_user='otter',
master_password='!tp!gNp667aPT',
master_auto_position=1;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.148.61
Master_User: otter
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: Yes
##查看状态发现有报错:Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
- 在slave1上进行mysqldump整库全备份,mysqldump -uroot -p密码 -A >all.sql
- 然后打开all.sql我们可以看到如下语句:
[root@localhost ~]# more all.sql
-- MySQL dump 10.13 Distrib 5.7.22, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.7.22-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='4cfd948e-88c8-11e8-a94a-000c29da1af6:1-2,
4e552c02-8345-11e8-b571-000c294897b5:1-487';
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
--
-- Table structure for table `columns_priv`
--
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
SET @@GLOBAL.GTID_PURGED='4cfd948e-88c8-11e8-a94a-000c29da1af6:1-2,
4e552c02-8345-11e8-b571-000c294897b5:1-487';
此值即为slave1上gtid_executed的值。
- 在slave2上恢复slave1的备份。
mysql> source all.sql
mysql> reset master; ##在slave上做一下reset master来清除gtid的一些信息。
Query OK, 0 rows affected (0.01 sec)
mysql> set global gtid_purged='4cfd948e-88c8-11e8-a94a-000c29da1af6:1-2,4e552c02-8345-11e8-b571-000c294897b5:1-487';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bini-slave2.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 4cfd948e-88c8-11e8-a94a-000c29da1af6:1-2,
4e552c02-8345-11e8-b571-000c294897b5:1-487
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> start slave; ##启动slave。
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; ##查看状态恢复正常。双YES表示恢复正常。
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.148.61
Master_User: otter
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-slave1.000001
Read_Master_Log_Pos: 1071
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 435
Relay_Master_Log_File: mysql-bin-slave1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
master进行一条事务,slave1和slave2同步
master创建个数据库 aaa
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kkk |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database aaa;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| kkk |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
slave1查看是否同步。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| kkk |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
slave2查看是否同步。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| kkk |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
七、读写分离
安装MySQL中间件ProxySQL。
ProxySQL是用C++语言开发的,虽然也是一个轻量级产品,但性能很好(据测试,能处理千亿级的数据),功能也足够,能满足中间件所需的绝大多数功能,包括:
- 最基本的读/写分离,且方式有多种。
- 可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说,规则很灵活。基于schema和与语句级的规则,可以实现简单的sharding。
- 可缓存查询结果。虽然ProxySQL的缓存策略比较简陋,但实现了基本的缓存功能,绝大多数时候也够用了。此外,作者已经打算实现更丰富的缓存策略。
- 监控后端节点。ProxySQL可以监控后端节点的多个指标,包括:ProxySQL和后端的心跳信息,后端节点的read-only/read-write,slave和master的数据同步延迟性(replication lag)。
# 以下是slave1的配置文件
server-id=2 ##服务器标识
binlog_format=row
log-bin=mysql-bin-slave1
log-slave-updates=ON
gtid_mode=ON ## 开启gtid模式
enforce-gtid-consistency=true ## 强制gtid复制
slave-parallel-type=LOGICAL_CLOCK ##开启逻辑时钟的复制
slave-parallel-workers=4 ##最大线程16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
read_only=1
# 以下是slave2的配置文件
server-id=3 ##服务器标识
log-bin=mysql-bini-slave2
binlog_format=row
gtid_mode=ON ## 开启gtid模式
enforce-gtid-consistency=true ## 强制gtid复制
read_only=1
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo] name= ProxySQL
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
yum -y install proxysql
[root@localhost ~]# systemctl start proxysql ##启动 proxysql
[root@localhost ~]# systemctl status proxysql ##查看 proxysql状态
Active: active (running) since 四 2018-07-19 18:04:48 CST; 40min ago
默认为6032和6033。6032端口是ProxySQL的管理端口,6033是ProxySQL对外提供服务的端口。
[root@localhost ~]# netstat -tnlp
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 977/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 977/proxysql
然后使用mysql客户端连接到ProxySQL的管理接口(admin interface),该接口的默认管理员用户和密码都是admin。
[root@localhost ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.5.30 (ProxySQL Admin Module)
admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
ProxySQL提供了几个库,每个库都有各自的意义。主要修改main和monitor数据库中的表。
admin> show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
20 rows in set (0.00 sec)
admin> show tables from monitor;
+------------------------------------+
| tables |
+------------------------------------+
| mysql_server_connect_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+------------------------------------+
5 rows in set (0.00 sec)
runtime_开头的是运行时的配置,这些是不能修改的。要修改ProxySQL的配置,需要修改了非runtime_ 表,修改后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效,执行save ... to disk才能将配置持久化保存到磁盘。
admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.148.62',3306);
admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.148.61',3306);
admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.148.64',3306);
#使用insert语句添加主机到mysql_servers表中,其中:hostgroup_id 10 表示写组,20表示读组。
查看这3个节点是否插入成功,以及它们的状态。
admin> select * from mysql_servers\G
*************************** 1. row ***************************
hostgroup_id: 10
hostname: 192.168.148.62
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 20
hostname: 192.168.148.61
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 20
hostname: 192.168.148.64
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
3 rows in set (0.00 sec)
修改后,加载到RUNTIME,并保存到disk。
admin> load mysql servers to runtime; admin> save mysql servers to disk;
添加节点之后,还需要监控后端节点。对于后端是主从复制的环境来说,这是必须的,因为ProxySQL需要通过每个节点的read_only值来自动调整它们是属于读组还是写组。
首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可,因 为会复制到slave上),这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master(这个俗语叫做"拖后 腿",术语叫做"replication lag"),则还需具备replication client权限。这里直接赋予这个权限。
# 在master上执行:
mysql> create user monitor@'192.168.148.%' identified by 'P@ssword1!'; mysql> grant replication client on *.* to monitor@'192.168.148.%';
#然后回到ProxySQL上配置监控。
admin> set mysql-monitor_username='monitor'; admin> set mysql-monitor_password='P@ssword1!';
#修改后,加载到RUNTIME,并保存到disk。
admin> load mysql variables to runtime; admin> save mysql variables to disk;
#验证监控结果:ProxySQL监控模块的指标都保存在monitor库的log表中。
以下是连接是否正常的监控(对connect指标的监控):(在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常)
admin> select * from mysql_server_connect_log;
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.148.61 | 3306 | 1532003209576812 | 1113 | NULL |
| 192.168.148.62 | 3306 | 1532003209587641 | 428 | NULL |
| 192.168.148.64 | 3306 | 1532003209598596 | 6290 | NULL |
| 192.168.148.62 | 3306 | 1532003749593625 | 676 | NULL |
| 192.168.148.64 | 3306 | 1532003749604600 | 1459 | NULL |
+----------------+------+------------------+-------------------------+---------------+
30 rows in set (0.00 sec)
#以下是对心跳信息的监控(对ping指标的监控):
admin> select * from mysql_server_ping_log;
+----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.168.148.61 | 3306 | 1532003210786402 | 717 | NULL |
| 192.168.148.64 | 3306 | 1532003610811775 | 382 | NULL |
| 192.168.148.61 | 3306 | 1532003620807891 | 408 | NULL |
| 192.168.148.62 | 3306 | 1532003620809610 | 140 | NULL |
| 192.168.148.64 | 3306 | 1532003800821971 | 420 | NULL |
+----------------+------+------------------+----------------------+------------+
180 rows in set (0.00 sec)
#但是,read_only和replication_lag的监控日志都为空。
admin> select * from mysql_server_read_only_log;
Empty set (0.00 sec)
admin> select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)
#例如,指定写组的id为10,读组的id为20。
admin> insert into mysql_replication_hostgroups values(10,20,1);
在该配置加载到RUNTIME生效之前,先查看下各mysql server所在的组。
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+----------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------------+------+--------+--------+
| 10 | 192.168.148.62 | 3306 | ONLINE | 1 |
| 10 | 192.168.148.61 | 3306 | ONLINE | 1 |
| 10 | 192.168.148.64 | 3306 | ONLINE | 1 |
+--------------+----------------+------+--------+--------+
3 rows in set (0.00 sec)
#3个节点都在hostgroup_id=10的组中。
现在,将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。
admin> load mysql servers to runtime;
admin> save mysql servers to disk;
一加载,Monitor模块就会开始监控后端的read_only值,当监控到read_only值后,就会按照read_only的值将某些节点自动移动到读/写组。
例如,此处所有节点都在id=10的写组,slave1和slave2都是slave,它们的read_only=1,这两个节点将会移动到id=20的组。如果一开始这3节点都在id=20的读组,那么移动的将是Master节点,会移动到id=10的写组。
#看结果:
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+----------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------------+------+--------+--------+
| 10 | 192.168.148.62 | 3306 | ONLINE | 1 |
| 20 | 192.168.148.61 | 3306 | ONLINE | 1 |
| 20 | 192.168.148.64 | 3306 | ONLINE | 1 |
+--------------+----------------+------+--------+--------+
3 rows in set (0.00 sec)
admin> select * from mysql_server_read_only_log;
+----------------+------+------------------+-----------------+-----------+--------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+----------------+------+------------------+-----------------+-----------+--------+ |
192.168.148.64 | 3306 | 1532003708480193 | 1218 | 1 | NULL |
| 192.168.148.62 | 3306 | 1532003709979095 | 2369 | 0 | NULL |
| 192.168.148.61 | 3306 | 1532003709978587 | 3464 | 1 | NULL |
| 192.168.148.64 | 3306 | 1532003709981780 | 2562 | 1 | NULL |
| 192.168.148.61 | 3306 | 1532003711479179 | 2449 | 1 | NULL |
| 192.168.148.62 | 3306 | 1532003711479965 | 1918 | 0 | NULL |
+----------------+------+------------------+-----------------+-----------+--------+ |
上面的所有配置都是关于后端MySQL节点的,现在可以配置关于SQL语句的,包括:发送 SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。本小节是SQL请求所使用的用户配置,例如root用户。这要求我们 需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例。
#首先,在master节点上执行:(只需master执行即可,会复制给两个slave)
mysql> grant all on *.* to root@'192.168.148.%' identified by 'passwd';
mysql> grant all on *.* to sqlsender@'192.168.148.%' identified by 'P@ssword1!';
#然后回到ProxySQL,配置mysql_users表,将刚才的两个用户添加到该表中。
admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10);
admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
admin> load mysql users to runtime; admin> save mysql users to disk;
mysql_users表有不少字段,最主要的三个字段为username、password和default_hostgroup:
- username:前端连接ProxySQL,以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
- password:用户名对应的密码。可以是明文密码,也可以是hash密码。如果想使用hash密码,可以先在某个MySQL节点上执行select password(PASSWORD),然后将加密结果复制到该字段。
- default_hostgroup:该用户名默认的路由目标。例如,指定root用户的该字段值为10时,则使用root用户发送的SQL语句默认情况下将路由到hostgroup_id=10组中的某个节点。
admin> select * from mysql_users\G
*************************** 1. row ***************************
username: root
password: passwd
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
*************************** 2. row ***************************
username: sqlsender
password: P@ssword1!
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
2 rows in set (0.00 sec)
虽然本文不详细介绍mysql_users表,但上面标注了"注意本行"的两个字段 必须要引起注意。只有active=1的用户才是有效的用户。至于transaction_persistent字段,当它的值为1时,表示事务持久化: 当某连接使用该用户开启了一个事务后,那么在事务提交/回滚之前,所有的语句都路由到同一个组中,避免语句分散到不同组。在以前的版本中,默认值为0,不 知道从哪个版本开始,它的默认值为1。我们期望的值为1,所以在继续下面的步骤之前,先查看下这个值,如果为0,则执行下面的语句修改为1。
admin> update mysql_users set transaction_persistent=1 where username='root';
admin> update mysql_users set transaction_persistent=1 where username='sqlsender';
admin> load mysql users to runtime;
admin> save mysql users to disk;
然后,另开一个终端,分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据。
[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id"
+-------------+
| @@server_id | +-------------+ | 110 |
+-------------+
[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test"
[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema | | mysql | | performance_schema | | proxy_test | | sys |
+--------------------+
[root@s1 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e '\ use proxy_test;\ create table t(id int);'
[root@s1 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'
+-------------------------+ |
Tables_in_proxy_test | +-------------------------+ | t |
+-------------------------+
ProxySQL的路由规则非常灵活,可以基于用户、基于schema以及基于每个语句实现 路由规则的定制。本文作为入门文章,实现一个最简单的语句级路由规则,从而实现读写分离。必须注意,这只是实验,实际的路由规则绝不应该仅根据所谓的读、 写操作进行分离,而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。和查询规则有关的表有两个:mysql_query_rules和 mysql_query_rules_fast_routing,后者是前者的扩展表,1.4.7之后才支持该快速路由表。本文只介绍第一个表。插入两个 规则,目的是将select语句分离到hostgroup_id=20的读组,但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁,所以应该路由到hostgroup_id=10的写组。
admin>insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);
admin> load mysql query rules to runtime;
admin> save mysql query rules to disk;
select ... for update规则的rule_id必须要小于普通的select规则的rule_id,因为ProxySQL是根据rule_id的顺序进行规则匹配的。
#再来测试下,读操作是否路由给了hostgroup_id=20的读组。
[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' mysql:
[Warning] Using a password on the command line interface can be insecure.
+-------------+ | @@server_id | +-------------+ | 120 |
+-------------+
[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' mysql:
[Warning] Using a password on the command line interface can be insecure.
+-------------+ | @@server_id | +-------------+ | 130 |
+-------------+
#读操作已经路由给读组,再看看写操作。这里以事务持久化进行测试。
[root@s1 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e '\ start transaction;\ select @@server_id;\ commit;\ select @@server_id;'
+-------------+ | @@server_id | +-------------+ | 110 | +-------------+ +-------------+ | @@server_id | +-------------+ | 120 |
+-------------+
显然,一切都按照预期进行。最后,如果想查看路由的信息,可查询stats库中的stats_mysql_query_digest表。以下是该表的一个输出格式示例(和本文无关)。
admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+-------------------------------------------------------------+ | hg | sum_time | count_star | digest_text | +----+----------+------------+-------------------------------------------------------------+ | 2 | 14520738 | 50041 | SELECT c FROM sbtest1 WHERE id=? | | 1 | 3142041 | 5001 | COMMIT | | 1 | 2270931 | 5001 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | | 1 | 2021320 | 5003 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? | | 1 | 1768748 | 5001 | UPDATE sbtest1 SET k=k+? WHERE id=? | | 1 | 1697175 | 5003 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? | | 1 | 1346791 | 5001 | UPDATE sbtest1 SET c=? WHERE id=? | | 1 | 1263259 | 5001 | DELETE FROM sbtest1 WHERE id=? | | 1 | 1191760 | 5001 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) | | 1 | 875343 | 5005 | BEGIN | +----+----------+------------+-------------------------------------------------------------+
基于GTID主从复制+并行复制+增强半同步复制+读写分离结束