[toc]
MHA+ProxySQL
Using MySQL high availability MHA in Docker In this article, WE will share the knowledge of “MHA+ middleware ProxySQL” to achieve read/write separation and load balancing.
As we all know, MHA (Master High Availability Manager and Tools for MySQL) is a relatively mature solution for MySQL High Availability. MySQL is a set of high availability software for failover and master/slave promotion in MySQL high availability environment. Its architecture requires that a MySQL replication cluster must have at least three database servers, one Master, one standby Master, and one slave. However, if you do not connect to any external database middleware, all the business pressure will flow to the master library, resulting in too much pressure on the master library, while the two slave libraries have no business pressure except their OWN IO and SQL threads, which will seriously cause a waste of resources. Therefore, we can use MHA in combination with ProxySQL to achieve read/write separation and load balancing. All business is allocated to different MySQL machines after passing ProxySQL middleware. Thus, front-end writes flow to the master library, while reads are load-balanced to the two slave libraries.
The MHA+ProxySQL architecture is shown in the following figure:
2. Build MHA environment quickly
2.1 Downloading an MHA Image
- The address of wheat Seedling Docker Hub: hub.docker.com/u/lhrbest
Download image
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
-- Renames the image
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
Copy the codeThere are 4 images, 3 MHA nodes and 1 MHA Manager. The compressed package is about 3G. After downloading:
[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
Copy the code2.2 Edit yML files to create MHA-related containers
Docker-compose: docker-compose: docker-compose: docker-compose: docker-compose: docker-compose: docker-compose: docker-compose: docker-compose: docker-compose: docker-compose:
Create path mkdir for yML files-p /root/Mha # Edit file/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.16868.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.16868.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.16868.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.16868.134.
networks:
mhalhr:
name: mhalhr
ipam:
config:
-Subnet configures: "192.168.68.0/16" EOFCopy the code2.3 Installing docker-compose software (if it has been installed, ignore it)
-
Docker Compose installation official documentation: docs.docker.com/compose/
-
Edit the docker – compose. Yml file official document: docs.docker.com/compose/com…
[root@lhrdocker ~]# curl https://github.com/docker/compose/releases/download/1.28.4/docker-compose-Linux-x86_64 - insecure - L - 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.284., build cabd5cfb
Copy the code2.4 Creating an MHA Container
To start the container of the MHA environment, be sure to enter the folder/root/mha/After the operation-- 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
Copy the code2.5 Adding VIP to Primary Library 131
Enter main library 131
docker exec -it MHA-LHR-Master1-ip131 bash
# add VIP135/ sbin/ifconfig eth0:1 192.168.68.135/24 ifconfig# If deletedIP addr del 192.168.68.135/24 dev eth1Copy the codeAfter adding:
[root@MHA-LHR-Master1-ip131 /]# ifconfigeth0: 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.5kib) 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.0b) RX errors 0 dropped 0 Overruns 0 Frame 0 TX packets 5 bytes 400 (400.0b) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0The management node can ping the VIP
[root@MHA-LHR-Monitor-ip134 /]# ping 192.168.68.135PING 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.075ms ^C -- 192.168.30.745 ping statistics -- 3 packets transmitted, 2 received 0% packet loss, time 1000ms RTT min/avg/ Max /mdev = 0.076/0.124/0.172/0.048msCopy the codeAt this point, you can verify that the master-slave replication is correct, and if so, you can test the MHA directly.
mysql -uroot -plhr -h192168.68.131. -P3306
show slave hosts;
mysql> show slave hosts;
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+----------------+------+-----------+--------------------------------------+
| 573306133 | 192.16868.133. | 3306 | 573306131 | d391ce7e-aec3- 11ea- 94.cd- 0242.c0a84485 |
| 573306132 | 192.16868.132. | 3306 | 573306131 | d24a77d1-aec3- 11ea- 9399.- 0242.c0a84484 |
+-----------+----------------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
Copy the code
Configure the ProxySQL environment
3.1 Apply for a ProxySQL host and install ProxySQL
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 \
- ring = true lhrbest/lhrcentos76:8.0 \
/usr/sbin/init
docker network connect bridge MHA-LHR-ProxySQL-ip136
docker restart MHA-LHR-ProxySQL-ip136
docker cp proxysql22.015.1.1.el7.x86_64.rpm MHA-LHR-ProxySQL-ip136:/
docker exec -it MHA-LHR-ProxySQL-ip136 bash
rpm -ivh proxysql22.015.1.1.el7.x86_64.rpm
systemctl start proxysql
systemctl status proxysql
Copy the code3.2 Adding a Remote Login User
-- Add a remote login user
mysql -uadmin -padmin -h127. 0. 01. -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;
-- Remote login
mysql -uroot -plhr -h192168.66.35. -P26032
Copy the codeExecution process:
-- ProxySQL Local login
[root@MHA-LHR-ProxySQL-ip136 /]# mysql -uadmin -padmin -h127. 0. 01. -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.530. (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>
-- Remote login
C:\Users\lhrxxt>mysql -uroot -plhr -h192168.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 -h192168.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.530. (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)
Copy the code3.3 Enabling the Web Monitoring function in ProxySQL
- Enable the Web monitoring function
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
-- Browser access
https://192.16866.35.:26080Username and password :stats :statsCopy the code
3.4 Configuring a Monitored Database
3.4.1 Insert the monitored database into ProxySQL
1. Insert the monitored database into 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.16868.131. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 192.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 192.16868.133. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
3 rows in set (0.07 sec)
Copy the code3.4.2 Create monitoring accounts on all monitored MySQL servers
In the new version, the password must be monitor. Please refer to the /etc/proxysql.cnf configuration file
mysql -uroot -plhr -h192168.66.35. -P33131
create user 'monitor'@The '%' IDENTIFIED BY 'monitor';
GRANT all privileges ON *.* TO 'monitor'@The '%' 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)
Copy the code3.4.3 Creating external access accounts on all monitored MySQL servers
MySQL > create external access account on MySQL server
create user 'wr'@The '%' IDENTIFIED BY 'lhr';
GRANT all privileges ON *.* TO 'wr'@The '%' with grant option;
Configure into 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)
Copy the code3.4.4 Configuring Monitoring
SQL = "ProxySQL";
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)
Check the log for connection to 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.16868.132. | 3306 | 1614050308827202 | 252 | NULL |
| 192.16868.133. | 3306 | 1614050308716530 | 370 | NULL |
| 192.16868.131. | 3306 | 1614050308605853 | 542 | NULL |
| 192.16868.131. | 3306 | 1614050298778908 | 334 | NULL |
| 192.16868.133. | 3306 | 1614050298690947 | 297 | NULL |
| 192.16868.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.16868.131. | 3306 | 1614050285481316 | 1173 | NULL |
| 192.16868.133. | 3306 | 1614050284894846 | 1008 | NULL |
| 192.16868.132. | 3306 | 1614050284309124 | 970 | NULL |
| 192.16868.131. | 3306 | 1614050225194575 | 1108 | NULL |
| 192.16868.133. | 3306 | 1614050224751771 | 987 | NULL |
| 192.16868.132. | 3306 | 1614050224309026 | 1294 | NULL |
+----------------+------+------------------+-------------------------+---------------+
6 rows in set (0.05 sec)
Copy the code3.4.5 Configuring a MySQL Host Group
5. Experiment uses 10 as write group and 20 as read group.
show create tablemysql_replication_hostgroups\G; Writer_hostgroup Number of the write group reader_hostgroup Number of the read group-- Note that read_only=1 is required for the slave library
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.16868.133. | 3306 | 1614050367153351 | 611 | 1 | NULL |
| 192.16868.131. | 3306 | 1614050367136396 | 490 | 0 | NULL |
| 192.16868.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.16868.131. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.133. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
3 rows in set (0.05 sec)
Copy the codeNote that the hostgroup_id value in the mysql_Servers table has changed.
3.4.6 Configuring a Read/write Separation Policy
- 6. Configure a read/write separation policy
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 * from hostgroup_id=2;
Select * from table_name for update; select * from table_name for update; select hostgroup_id=1
Mysql_users table default_hostgroup (mysql_users)
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)
Copy the codeWith ProxySQL read/write separation and load balancing configured, let’s test.
About Me
● The author of this article: Wheat Seedling, part of the content is arranged from the network, if there is infringement, please contact wheat seedling to delete ● This article in personal wechat public number (DB treasure) has been updated simultaneously ● QQ group number: 230161599, 618766405, wechat group private chat ● Personal QQ number (646634621), wechat account (db_bao), indicate the reason for adding ● Completed in Xi ‘an in March 2021 ● Updated date: March 2021 ● All rights reserved
● Wheat Seedling wechat store: weidian.com/?userid=793… Low wheat seedlings in the publication of the database class series: blog.itpub.net/26736162/vi… Low wheat seedlings OCP, OCM, high availability, the DBA course (Oracle, MySQL and no () : blog.itpub.net/26736162/vi… Low database written interview question and answer: mp.weixin.qq.com/s/Vm5PqNcDc…
Use wechat client to scan the following TWO-DIMENSIONAL code to follow the wechat public account of Wheat Seedling (DBbao) and QQ group (DBA Baodian), add Wheat seedling wechat, learn the most practical database technology.