Server cluster (5) Database read-write separation by ProxySQL

1. Why do you need read-write separation?

Master-slave replication and read-write separation are generally used together. The purpose is very simple, it is to improve the concurrent performance of the database.
You think, assuming that it is a single machine, reading and writing are all done on a MySQL, and the performance is definitely not high.
If there are three MySQLs, one mater is only responsible for write operations, and two salves are only responsible for read operations, wouldn't the performance be greatly improved?
--SegmentFault

2, the way to achieve read-write separation

MySQL implements read-write separation in the following ways:

• Code layer implementation logic 1: parse read/write requests and distribute them to different databases in a targeted manner, so as to separate read and write;
• Code layer implementation logic 2: secondary verification method, the main library obtains the latest GTID, and the slave library verifies whether the application is applied, and the data is read successfully
• Based on ProxySQL, MyCat middleware to achieve the effect of read-write separation. Read-write separation is performed through different ports or mechanisms.

The middleware realizes read-write separation, which has obvious advantages. It only needs to be configured to achieve read-write separation. In addition, when the main library is down, the master-slave automatic switch can also be performed through configuration, so that even if the main library is down The entire cluster will not lose the ability to write.

• Multi-copy writing method: This method has many manifestations in domestic distributed databases, and it is confirmed that it is written to another replication cluster.

This series of articles takes the middleware method as an example to achieve MySQL load balancing and read-write separation.

3, MySQL middleware

1. Cetus (Open Source)

Project address
https://github.com/cetus-tools/cetus/releases

Centus is the middleware of relational database MySQL developed by C language. It is developed based on MySQL Proxy. It is divided into two versions: read-write separation version and sub-database version. The community currently has very few activity tracks, but there is still enterprise use.

2. MaxScale (closed source)

A middleware produced by MariaDB, which can realize read and write separation and read load balancing.
Although MaxScale is open source, it is not an "Open Source" project and uses a unique licensing protocol: BSL protocol (https://mariadb.com/projects-using-bsl-11/)

3. Vitess (Open Source)

The middleware used by Youtube is really complicated. Different from the previous middleware, the use of Vitess application changes is relatively large to use the API interface of the language provided by him. Support read-write separation, sub-database sub-table, failover and data backup.

4. MySQL Router (Open Source)

The read-write separation middleware officially recommended by MySQL, MySQL Router is an alternative to MySQL Proxy, which can realize read-write separation and cannot dynamically change the configuration. Act as a proxy in MGR.

5. ProxySQL (Open Source)

ProxySQL is developed by percona in C++ language. It is a lightweight open source software. Its performance and functions meet most of the functions required by read and write middleware. Its configuration data is based on SQLite storage and has now reached version v2.4.2.
It can realize the separation of reading and writing, and realize simple sharding based on the self-defined identification rules of statements. The configuration can be dynamically changed, and requests for SQL must go through the ProxySQL server, which has certain requirements on CPU and memory. Act as a proxy in MGR.

GitHub official website
https://github.com/sysown/proxysql/releases
Percona official website
https://www.percona.com/downloads/proxysql

6. MyCAT (Open Source)

An open source product based on Alibaba's open source Cobar product.
The application side is linked to the MyCAT server, which can support read-write separation and support a variety of sub-database sub-table algorithms.
Support MySQL, PostgreSQL, Oracle, SQL Server, performance loss 20%~50%
The overall design idea and implementation are very good, and it is also a relatively mature solution. In that era, the overall design idea and implementation method of MyCAT were relatively mature solutions.
However, the current status of the community is very bad, and it is basically being abandoned gradually. You can understand the reasons for yourself. DBLE is a derivative version currently maintained by AKSON.

6. ShardingSphere (Open Source)

ShardingSphere=Shardingjdbc+ShardingProxy+ShardingSidecar

Shardingjdbc: Lightweight Java architecture, the additional service provided in the JDBC layer of Java is that jdbc drives the secondary packaging jar, realizes read-write separation, complex sub-database sub-table logic, and performance loss of 7%
ShardingProxy: Similar to MyCAT, the middleware software realizes the separation of reading and writing, and the complex sub-database sub-table. 20% performance loss
It became a top-level project of the Apache Software Foundation on April 16, 2020.

In addition to those listed above, there are also commercial versions of Taobao's DRDS, civilian software OneProxy, etc.

This site adopts ProxySQL uniformly, taking the configuration on CentOS and Ubuntu as an example.

4. Install ProxySQL

Note:
Check the latest version on the official website first. The installation address provided on this site may be an old version.
You need to replace the URL of the installation instructions below.

CentOS:

1
2

wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql-2.4.2-1-centos7.x86_64.rpm
yum install proxysql-2.4.2-1-centos7.x86_64.rpm

Ubuntu:

1
2

wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb
dpkg -i proxysql_2.4.2-ubuntu20_amd64.deb

5. Configure ProxySQL

5.1, Early stage

First in /etc/proxysql.cnf, about line 37:

1
2
3
4
5
6
7
8

admin_variables=
{
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
#debug=true
}

Add your desired administrator account password after admin_credentials, format: account:password, separated by semicolon.
E.g:

1

admin_credentials="admin:admin;tsinbei:123456"

Note: If ProxySQL has already been started, this configuration will not take effect and needs to be deleted:
/var/lib/proxysql/proxysql.db
Restart, you can reload the configuration from the configuration file, but the previous configuration will be cleared.

Administrative commands:

1
2
3
4
5

systemctl start proxysql #Start
systemctl stop proxysql # stop
systemctl status proxysql # View status
systemctl enable proxysql # Set boot auto-start
systemctl disable proxysql # Cancel boot auto-start

After startup, execute

1

mysql -uadmin -padmin -h127.0.0.1 -P6032

Enter, admin can be replaced with the previously customized account password.

If there is no MySQL client on the server and remote configuration is required, then must configure the account password in front, and use the custom account and password to log in. Replace 127.0.0.1 in the command with the IP address.

5.2, public network cluster

If it is a public network cluster, you need to install ProxySQL on each server that has MySQL service (only the slave needs to be installed, the host does not need to be configured, keep it as it is). According to the method of the intranet cluster, SQL query will be caused. The delay has increased from 1 second to 10 seconds, which is not worth the loss.

In the following ProxySQL configuration, each server only needs to configure two mysql_servers, one is the local (127.0.0.1) read server, and the other is the host's write server.

5.3. Intranet cluster

If it is an intranet cluster, the network problem is no longer the bottleneck, so you only need to install ProxySQL on the host or a separate server, you need to configure a host-to-write server in mysql_servers, and all slave read servers .

5.4, read and write configuration

Note: In the following, the server and the corresponding HostGroup are:

Log in to the command line of ProxySQL and execute

1

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'x.x.x.x','3306',1,'Read Group');

Add a read server. If there are multiple read servers, you can modify the penultimate number (1, corresponding to the field weight) and set the weight.

implement

1

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'x.x.x.x','3306',2,'Write Group');

Add write server.

implement

1
2
3

INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);

INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);

It means that the read operation is assigned to the server whose HostGroup is 2, and the write operation is assigned to the server whose HostGroup is 1.

implement

1

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES('username','password',1);

to add a MySQL account password.

Note:
Here is the account secret of the database corresponding to the MySQL master-slave database. Generally, the master-slave database account secret is the same.
The back-end (such as PHP) uses which account and password to connect to the database. It is best not to use root.

Finally, execute

1
2
3
4
5
6
7

load mysql servers to runtime;
load mysql query rules to runtime;
load mysql users to runtime;

save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;

Save the data added above.

If you need to view the added data later, log in to the database as well, and execute

1
2
3

select * from mysql_servers;
select * from mysql_query_rules;
select * from mysql_users;

to list the above configuration,

implement

1
2
3

delete from mysql_servers;
delete from mysql_query_rules;
delete from mysql_users;

to clear the above configuration.

5.5, monitoring configuration

Log in to the ProxySQL command line as above and execute:

1
2
3

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

Replace monitor with your desired account password.

then execute

1
2
3

load mysql variables to runtime;
save mysql variables to disk;
select * from monitor.mysql_server_connect_log;

Save and view the log, if the last few lines are NULL it means success.

Log in to the server MySQL database and execute

1
2

CREATE USER 'monitor' IDENTIFIED BY 'monitor';
grant replication client on *.* to monitor@'%';

If it is not convenient to log in to the command line, you can manually create a user with phpMyAdmin, allow % (all hosts) to connect, and authorize all permissions.

6, configure the backend

After completing the above configuration, you can change the database address of the backend to ProxySQL's IP: 6033, and no other changes are required.
That's where middleware comes in handy (laughs).