【DB宝42】MySQL高可用架构MHA+ProxySQL实

发表于

[toc]

一、MHA+ProxySQL架构

之前发过一篇MHA的文章，介绍了MHA相关的知识和功能测试，连接为：【DB宝19】在Docker中使用MySQL高可用之MHA 。今天这一篇给大家分享一下“MHA+中间件ProxySQL”来实现读写分离+负载均衡的相关知识。

我们都知道，MHA（Master High Availability Manager and tools for MySQL）目前在MySQL高可用方面是一个相对成熟的解决方案，是一套作为MySQL高可用性环境下故障切换和主从提升的高可用软件。它的架构是要求一个MySQL复制集群必须最少有3台数据库服务器，一主二从，即一台充当Master，一台充当备用Master，另一台充当从库。但是，如果不连接任何外部的数据库中间件，那么就会导致所有的业务压力流向主库，从而造成主库压力过大，而2个从库除了本身的IO和SQL线程外，无任何业务压力，会严重造成资源的浪费。因此，我们可以把MHA和ProxySQL结合使用来实现读写分离和负载均衡。所有的业务通过中间件ProxySQL后，会被分配到不同的MySQL机器上。从而，前端的写操作会流向主库，而读操作会被负载均衡的转发到2个从库上。

MHA+ProxySQL架构如下图所示：

二、快速搭建MHA环境

2.1 下载MHA镜像

1
2
3
4
5
6
7
8
9
10
11
sql复制代码-- 下载镜像
docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131
docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132
docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133
docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134

-- 重命名镜像
docker tag 	registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131  lhrbest/mha-lhr-master1-ip131
docker tag	registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132   lhrbest/mha-lhr-slave1-ip132 
docker tag	registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133   lhrbest/mha-lhr-slave2-ip133 
docker tag	registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134  lhrbest/mha-lhr-monitor-ip134

一共4个镜像，3个MHA Node，一个MHA Manager，压缩包大概3G，下载完成后：

1
2
3
4
5
sql复制代码[root@lhrdocker ~]# docker images | grep mha
registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134          latest              7d29597dc997        14 hours ago        1.53GB
registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133           latest              d3717794e93a        40 hours ago        4.56GB
registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132           latest              f62ee813e487        40 hours ago        4.56GB
registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131          latest              ae7be48d83dc        40 hours ago        4.56GB

2.2 编辑yml文件，创建MHA相关容器

编辑yml文件，使用docker-compose来创建MHA相关容器，注意docker-compose.yml文件的格式，对空格、缩进、对齐都有严格要求：

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
57
58
59
60
61
62
63
64
65
66
67
68
sql复制代码# 创建存放yml文件的路径
mkdir -p /root/mha

# 编辑文件/root/mha/docker-compose.yml
cat > /root/mha/docker-compose.yml <<"EOF"
version: '3.8'

services:
  MHA-LHR-Master1-ip131:
    container_name: "MHA-LHR-Master1-ip131"
    restart: "always"
    hostname: MHA-LHR-Master1-ip131
    privileged: true
    image: lhrbest/mha-lhr-master1-ip131
    ports:
      - "33131:3306"
      - "2201:22"
    networks:
      mhalhr:
        ipv4_address: 192.168.68.131

  MHA-LHR-Slave1-ip132:
    container_name: "MHA-LHR-Slave1-ip132"
    restart: "always"
    hostname: MHA-LHR-Slave1-ip132
    privileged: true
    image: lhrbest/mha-lhr-slave1-ip132
    ports:
      - "33132:3306"
      - "2202:22"
    networks:
      mhalhr:
        ipv4_address: 192.168.68.132

  MHA-LHR-Slave2-ip133:
    container_name: "MHA-LHR-Slave2-ip133"
    restart: "always"
    hostname: MHA-LHR-Slave2-ip133
    privileged: true
    image: lhrbest/mha-lhr-slave2-ip133
    ports:
      - "33133:3306"
      - "2203:22"
    networks:
      mhalhr:
        ipv4_address: 192.168.68.133

  MHA-LHR-Monitor-ip134:
    container_name: "MHA-LHR-Monitor-ip134"
    restart: "always"
    hostname: MHA-LHR-Monitor-ip134
    privileged: true
    image: lhrbest/mha-lhr-monitor-ip134
    ports:
      - "33134:3306"
      - "2204:22"
    networks:
      mhalhr:
        ipv4_address: 192.168.68.134

networks:
  mhalhr:
    name: mhalhr
    ipam:
      config:
         - subnet: "192.168.68.0/16"

EOF

2.3 安装docker-compose软件（若已安装，可忽略）

1
2
3
4
5
6
7
8
sql复制代码[root@lhrdocker ~]# curl --insecure -L https://github.com/docker/compose/releases/download/1.28.4/docker-compose-Linux-x86_64 -o /usr/local/bin/docker-compose
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   638  100   638    0     0    530      0  0:00:01  0:00:01 --:--:--   531
100 11.6M  100 11.6M    0     0  1994k      0  0:00:06  0:00:06 --:--:-- 2943k
[root@lhrdocker ~]# chmod +x /usr/local/bin/docker-compose
[root@lhrdocker ~]# docker-compose -v
docker-compose version 1.28.4, build cabd5cfb

2.4 创建MHA容器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sql复制代码# 启动mha环境的容器，一定要进入文件夹/root/mha/后再操作
-- docker rm -f MHA-LHR-Master1-ip131 MHA-LHR-Slave1-ip132 MHA-LHR-Slave2-ip133 MHA-LHR-Monitor-ip134
[root@lhrdocker ~]# cd /root/mha/
[root@lhrdocker mha]#
[root@lhrdocker mha]# docker-compose up -d
Creating network "mhalhr" with the default driver
Creating MHA-LHR-Monitor-ip134 ... done
Creating MHA-LHR-Slave2-ip133  ... done
Creating MHA-LHR-Master1-ip131 ... done
Creating MHA-LHR-Slave1-ip132  ... done
[root@docker35 ~]# docker ps | grep "mha\|COMMAND" 
CONTAINER ID        IMAGE                           COMMAND                  CREATED             STATUS              PORTS                                                                                           NAMES
2978361198b7        lhrbest/mha-lhr-master1-ip131   "/usr/sbin/init"         2 minutes ago       Up 2 minutes        16500-16599/tcp, 0.0.0.0:2201->22/tcp, 0.0.0.0:33131->3306/tcp                                  MHA-LHR-Master1-ip131
a64e2e86589c        lhrbest/mha-lhr-slave1-ip132    "/usr/sbin/init"         2 minutes ago       Up 2 minutes        16500-16599/tcp, 0.0.0.0:2202->22/tcp, 0.0.0.0:33132->3306/tcp                                  MHA-LHR-Slave1-ip132
d7d6ce34800b        lhrbest/mha-lhr-monitor-ip134   "/usr/sbin/init"         2 minutes ago       Up 2 minutes        0.0.0.0:2204->22/tcp, 0.0.0.0:33134->3306/tcp                                                   MHA-LHR-Monitor-ip134
dacd22edb2f8        lhrbest/mha-lhr-slave2-ip133    "/usr/sbin/init"         2 minutes ago       Up 2 minutes        16500-16599/tcp, 0.0.0.0:2203->22/tcp, 0.0.0.0:33133->3306/tcp                                  MHA-LHR-Slave2-ip133

2.5 主库131添加VIP

1
2
3
4
5
6
7
8
9
sh复制代码# 进入主库131
docker exec -it MHA-LHR-Master1-ip131 bash

# 添加VIP135
/sbin/ifconfig eth0:1 192.168.68.135/24
ifconfig

# 如果删除的话
ip addr del 192.168.68.135/24 dev eth1

添加完成后：

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
sh复制代码[root@MHA-LHR-Master1-ip131 /]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.68.131  netmask 255.255.0.0  broadcast 192.168.255.255
        ether 02:42:c0:a8:44:83  txqueuelen 0  (Ethernet)
        RX packets 220  bytes 15883 (15.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 189  bytes 17524 (17.1 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.68.135  netmask 255.255.255.0  broadcast 192.168.68.255
        ether 02:42:c0:a8:44:83  txqueuelen 0  
lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 5  bytes 400 (400.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 5  bytes 400 (400.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        
# 管理节点已经可以ping通VIP了
[root@MHA-LHR-Monitor-ip134 /]# ping 192.168.68.135
PING 192.168.68.135 (192.168.68.135) 56(84) bytes of data.
64 bytes from 192.168.68.135: icmp_seq=1 ttl=64 time=0.172 ms
64 bytes from 192.168.68.135: icmp_seq=2 ttl=64 time=0.076 ms
^C
--- 192.168.68.135 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1000ms
rtt min/avg/max/mdev = 0.076/0.124/0.172/0.048 ms

到这一步就可以验证主从复制是否正确，若正确，则可以直接测试MHA了。

1
2
3
4
5
6
7
8
9
10
sql复制代码mysql -uroot -plhr -h192.168.68.131 -P3306
show slave hosts;
mysql> show slave hosts;
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host           | Port | Master_id | Slave_UUID                           |
+-----------+----------------+------+-----------+--------------------------------------+
| 573306133 | 192.168.68.133 | 3306 | 573306131 | d391ce7e-aec3-11ea-94cd-0242c0a84485 |
| 573306132 | 192.168.68.132 | 3306 | 573306131 | d24a77d1-aec3-11ea-9399-0242c0a84484 |
+-----------+----------------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

三、配置ProxySQL环境

3.1 申请ProxySQL主机并安装ProxySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
sql复制代码docker rm -f MHA-LHR-ProxySQL-ip136
docker run -d --name MHA-LHR-ProxySQL-ip136 -h MHA-LHR-ProxySQL-ip136 \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --network mhalhr --ip 192.168.68.136 \
  -p 26032:6032 -p 26033:6033 -p 26080:6080 \
  --privileged=true lhrbest/lhrcentos76:8.0 \
  /usr/sbin/init

docker network connect bridge MHA-LHR-ProxySQL-ip136
docker restart MHA-LHR-ProxySQL-ip136

docker cp proxysql2-2.0.15-1.1.el7.x86_64.rpm MHA-LHR-ProxySQL-ip136:/
docker exec -it MHA-LHR-ProxySQL-ip136 bash
rpm -ivh proxysql2-2.0.15-1.1.el7.x86_64.rpm


systemctl start proxysql
systemctl status proxysql

3.2 添加远程登录用户

1
2
3
4
5
6
7
8
9
10
sql复制代码-- 添加远程登录用户
mysql -uadmin -padmin -h127.0.0.1 -P6032
select @@admin-admin_credentials;
set admin-admin_credentials='admin:admin;root:lhr';
select @@admin-admin_credentials;
load admin variables to runtime;
save admin variables to disk;

-- 远程登录
mysql -uroot -plhr -h192.168.66.35 -P26032

执行过程：

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
sql复制代码-- ProxySQL本地登录
[root@MHA-LHR-ProxySQL-ip136 /]# mysql -uadmin -padmin -h127.0.0.1 -P6032
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 162
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin;lhr:lhr       |
+---------------------------+
1 row in set (0.05 sec)

mysql> set admin-admin_credentials='admin:admin;root:lhr';
Query OK, 1 row affected (0.00 sec)

mysql> select @@admin-admin_credentials;
+---------------------------+
| @@admin-admin_credentials |
+---------------------------+
| admin:admin;root:lhr      |
+---------------------------+
1 row in set (0.00 sec)

mysql> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save admin variables to disk;
Query OK, 35 rows affected (0.13 sec)

mysql> 

-- 远程登录
C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'root'@'172.17.0.1' (using password: YES)

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032
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 163
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 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.05 sec)

3.3 开启ProxySQL的web监控功能

1
2
3
4
5
6
7
8
9
10
11
12
sql复制代码-- 开启web监控功能
SET admin-web_enabled='true';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;
select * from global_variables where variable_name LIKE 'admin-web_enabled';
select @@admin-web_enabled;

lsof -i:6080

-- 浏览器访问
https://192.168.66.35:26080
用户名和密码：stats:stats

3.4 配置被监控的数据库

3.4.1 向ProxySQL插入被监控数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
sql复制代码-- 1、向ProxySQL插入被监控数据库
select * from mysql_servers;
insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.131',3306);
insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.132',3306);
insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.133',3306);
load mysql servers to runtime;
save mysql servers to disk;
select * from mysql_servers;
MySQL [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.68.131 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.68.133 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.07 sec)

3.4.2 在所有被监控MySQL服务器上创建监控帐户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sql复制代码-- 2、在所有被监控MySQL服务器上创建帐户，注意：新版本中，这里的密码必须为monitor，可参考配置文件/etc/proxysql.cnf
mysql -uroot -plhr -h192.168.66.35 -P33131
create user 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT all privileges ON *.* TO 'monitor'@'%' with grant option;
select user,host from mysql.user;

mysql> select user,host from mysql.user;
+---------------+--------------+
| user          | host         |
+---------------+--------------+
| mha           | %            |
| monitor       | %            |
| repl          | %            |
| root          | %            |
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
+---------------+--------------+
7 rows in set (0.00 sec)

3.4.3 在所有被监控MySQL服务器上创建对外访问账户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
sql复制代码-- 3、 在所有被监控MySQL服务器上创建对外访问账户：
create user 'wr'@'%' IDENTIFIED BY 'lhr';
GRANT all privileges ON *.* TO 'wr'@'%' with grant option; 

-- 配置到ProxySQL中
insert into mysql_users(username,password,default_hostgroup) values('wr','lhr',10);
update mysql_users set transaction_persistent=1 where username='wr';
load mysql users to runtime;
save mysql users to disk;
select * from mysql_users;

MySQL [(none)]> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| wr       | lhr      | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.05 sec)

3.4.4 配置监控

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
sql复制代码-- 4、在ProxySQL端执行下列SQL语句：
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';
load mysql servers to runtime;
save mysql servers to disk;
select * from global_variables where variable_name in('mysql-monitor_username','mysql-monitor_password');
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor        |
| mysql-monitor_username | monitor        |
+------------------------+----------------+
2 rows in set (0.05 sec)


-- 检查连接到MySQL的日志
select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
MySQL [(none)]> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
+----------------+------+------------------+----------------------+------------+
| hostname       | port | time_start_us    | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.168.68.132 | 3306 | 1614050308827202 | 252                  | NULL       |
| 192.168.68.133 | 3306 | 1614050308716530 | 370                  | NULL       |
| 192.168.68.131 | 3306 | 1614050308605853 | 542                  | NULL       |
| 192.168.68.131 | 3306 | 1614050298778908 | 334                  | NULL       |
| 192.168.68.133 | 3306 | 1614050298690947 | 297                  | NULL       |
| 192.168.68.132 | 3306 | 1614050298605725 | 344                  | NULL       |
+----------------+------+------------------+----------------------+------------+
6 rows in set (0.06 sec)

MySQL [(none)]> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
+----------------+------+------------------+-------------------------+---------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.68.131 | 3306 | 1614050285481316 | 1173                    | NULL          |
| 192.168.68.133 | 3306 | 1614050284894846 | 1008                    | NULL          |
| 192.168.68.132 | 3306 | 1614050284309124 | 970                     | NULL          |
| 192.168.68.131 | 3306 | 1614050225194575 | 1108                    | NULL          |
| 192.168.68.133 | 3306 | 1614050224751771 | 987                     | NULL          |
| 192.168.68.132 | 3306 | 1614050224309026 | 1294                    | NULL          |
+----------------+------+------------------+-------------------------+---------------+
6 rows in set (0.05 sec)

3.4.5 配置MySQL主机组

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
sql复制代码-- 5、实验使用10作为写入组，20作为读取组。
show create table mysql_replication_hostgroups\G;
writer_hostgroup 写入组的编号
reader_hostgroup 读取组的编号


-- 注意：需要配置从库的read_only=1
show variables like 'read_only';
set global read_only=1;

insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;
select * from mysql_replication_hostgroups;
select * from mysql_server_read_only_log  order by time_start_us desc limit  3;
select * from mysql_servers;

MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | proxy   |
+------------------+------------------+------------+---------+
1 row in set (0.05 sec)

MySQL [(none)]> select * from mysql_server_read_only_log  order by time_start_us desc limit  3;
+----------------+------+------------------+-----------------+-----------+-------+
| hostname       | port | time_start_us    | success_time_us | read_only | error |
+----------------+------+------------------+-----------------+-----------+-------+
| 192.168.68.133 | 3306 | 1614050367153351 | 611             | 1         | NULL  |
| 192.168.68.131 | 3306 | 1614050367136396 | 490             | 0         | NULL  |
| 192.168.68.132 | 3306 | 1614050367119511 | 531             | 1         | NULL  |
+----------------+------+------------------+-----------------+-----------+-------+
3 rows in set (0.05 sec)

MySQL [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.68.131 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.68.132 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.68.133 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.05 sec)

注意，此时mysql_servers表中的hostgroup_id值已发生变化。

3.4.6 配置读写分离策略

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
sql复制代码-- 6、配置读写分离策略
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',20,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- 配置查询select的请求转发到hostgroup_id=2组上（读组）
-- 针对select * from table_name  for update这样的修改语句，我们是需要将请求转到写组，也就是hostgroup_id=1
-- 对于其它没有被规则匹配的请求全部转发到默认的组（mysql_users表中default_hostgroup）
select * from  mysql_query_rules;
select username,password,default_hostgroup from mysql_users;
MySQL [(none)]> select * from  mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern        | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 1       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT.*FOR UPDATE$ | 0                    | CASELESS     | NULL    | NULL            | 10                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 2       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT              | 0                    | CASELESS     | NULL    | NULL            | 20                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
2 rows in set (0.05 sec)

MySQL [(none)]> select username,password,default_hostgroup from mysql_users;
+----------+----------+-------------------+
| username | password | default_hostgroup |
+----------+----------+-------------------+
| wr       | lhr      | 10                |
+----------+----------+-------------------+
1 row in set (0.05 sec)

至此，ProxySQL读写分离和负载均衡已配置完成，接下来我们进行测试。

About Me

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在个人微 信公众号（DB宝）上有同步更新

● QQ群号： 230161599 、618766405，微信群私聊

● 个人QQ号（646634621），微 信号（db_bao），注明添加缘由

● 于 2021年3月 在西安完成

● 最新修改时间：2021年3月

● 版权所有，欢迎分享本文，转载请保留出处

●小麦苗的微店： weidian.com/?userid=793…

●小麦苗出版的数据库类丛书： blog.itpub.net/26736162/vi…

●小麦苗OCP、OCM、高可用、DBA学习班（Oracle、MySQL、NoSQL）： blog.itpub.net/26736162/vi…

●数据库笔试面试题库及解答： mp.weixin.qq.com/s/Vm5PqNcDc…

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（DB宝）及QQ群（DBA宝典）、添加小麦苗微信， 学习最实用的数据库技术。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%