ProxySQL read-write separation from configuration to use

The purpose of use has been explained in Meituan-Dianping’s DBProxy read-write separation usage instructions article. This article introduces how to use ProxySQL and the performance difference with DBProxy. For a detailed introduction, please see the relevant instructions on the official website, and this middleware is also a middleware recommended by percona. Its features are not much different from other middleware that separates reading and writing. The details will be introduced in the article. This article briefly introduces some instructions during use. You can also check the official wiki for help.

Environment:

Distributor ID: Ubuntu
Description: Ubuntu 14.04.5 LTS
Release: 14.04Codename: trusty

Download

percona site:

https://www.percona.com/downloads/proxysql/

github/Official website:

https://github .com/sysown/proxysql/releases

##Let’s first take a look at our environment:

MHA has been set up:

master：172.16.16.35:3306slave：172.16.16.35:3307slave：172.16.16.34:3307

MHA manager is at 172.16.16.34, the configuration file is as follows:

[root@localhost bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456ping_interval=1remote_workdir=/tmp
repl_password=123456repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""ssh_user=root
user=root
[server1]
hostname=172.16.16.35port=3306[server2]
candidate_master=1check_repl_delay=0hostname=172.16.16.34port=3306[server3]
hostname=172.16.16.35port=3307

Next we build read-write separation based on such an MHA environment.

1: Install the ProxySQL software, which we deploy to 172.16.16.34

##

[root@localhost bin]# sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm[root@localhost bin]# yum install proxysql

At the end there is the following prompt:

Installed:
proxysql.x86_64 0:1.3.7-1.1.el6
Complete!

That is, the installation is complete. Then check the specific files:

[root@localhost bin]# find / -name proxysql/var/lib/proxysql/var/run/proxysql/etc/rc.d/init.d/proxysql/usr/bin/proxysql

I found that ProxySQL has been installed successfully

2 : Start configuring ProxySQL

Look at the configuration file:

[root@localhost bin]# cat /etc/proxysql-admin.cnf
# proxysql admin interface credentials.
export PROXYSQL_USERNAME="admin"export PROXYSQL_PASSWORD="admin"export PROXYSQL_HOSTNAME="localhost"export PROXYSQL_PORT="6032"
 # PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME="admin"export CLUSTER_PASSWORD="admin"export CLUSTER_HOSTNAME="localhost"export CLUSTER_PORT="3306"
 # proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME="monitor"export MONITOR_PASSWORD="monit0r"
 # Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME="proxysql_user"export CLUSTER_APP_PASSWORD="passw0rd"
 # ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID="10"export READ_HOSTGROUP_ID="11"
 # ProxySQL read/write configuration mode.
export MODE="singlewrite"

Startup:

[root@localhost bin]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
ProxySQL is not running; please start the proxysql service

Now that ProxySQL routing has been started, we are prompted to start proxysql service

[root@localhost bin]# service proxy
proxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor
[root@localhost bin]# service proxysql start
Starting ProxySQL: DONE![root@localhost bin]# 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 1Server version: 5.7.14 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, 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>

You can see that we have logged in successfully. What I want to explain here is that the default configuration file of proxysql is:

[root@localhost bin]# find / -name proxysql.cnf/etc/proxysql.cnf

Next we start configuring ProxySQL:

[root@localhost bin]# 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 2Server version: 5.7.14 (ProxySQL Admin Module)
 
Copyright (c) 2000, 2016, 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> show databases;+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+4 rows in set (0.00 sec)
 
mysql> use admin
Database changed
mysql> show tables;+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| mysql_collations |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| runtime_global_variables |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_scheduler |
| scheduler |
+--------------------------------------+13 rows in set (0.00 sec)

Add the master-slave information below:

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'172.16.16.35',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.34',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.35',3307,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_servers;+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 172.16.16.35 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.34 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.35 | 3307 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)

Then add read-write mapping (mainly to ensure that ProxySQL can automatically switch when the MHA backend is switched):

mysql> insert into mysql_replication_hostgroups values(100,101,'masterha') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+----------+
| 100 | 101 | masterha |
+------------------+------------------+----------+1 row in set (0.00 sec)

Add a monitoring account for ProxySQL:

mysql> GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'172.16.16.%' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

Configure monitoring Account (configured in proxySQL):

mysql> set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 74 rows affected (0.02 sec)

PS: Sometimes it is OK if the status of runtime_mysql_servers is not ONLINE Check the specific error information by looking at the monitor.mysql_server_ping_log table.

mysql> select * from monitor.mysql_server_ping_log;

Then configure the program account. Simply use root: 123456 to configure the highest authority:

mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('root','123456',1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123456 | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (0.00 sec)

After the configuration is completed, start reloading and saving our configuration:

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.08 sec)
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)

Next, start configuring routing rules:

mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',101,1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)

The configuration has been completed so far

3: Test read-write separation

Connect to proxySQL port 6033 on 172.16.16.35, and perform a simple select operation:

[root@localhost ~]# mysql -uroot -p123456 -h172.16.16.34 -P6033
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 22Server version: 5.7.14 (ProxySQL)
Copyright (c) 2000, 2016, 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 * from maxiangqian.test;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.04 sec)

Then check and check:

mysql> select * from stats_mysql_query_digest;

You can see that it has been completed Reading and writing are separated.

mysql> select @@server_id;+-------------+
| @@server_id |
+-------------+
| 353307 |
+-------------+1 row in set (0.01 sec)

Check that the server ID has been routed to the slave database 172.16.16.35:3307.

Test for update:

mysql> select * from maxiangqian.test for update;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.00 sec)

Check again

mysql> select * from stats_mysql_query_digest;

has been automatically routed to the main library.

At this point, the ProxySQL+MySQL MHA read and write separation test is completed. . . To be continued

5: Thoughts on ProxySQL and simple commands

ProxySQL can achieve read-write separation through the above method, but is there really no problem with this method? If it is some SQL that requires very high real-time performance, such as querying order status, it seems to be routed from BUG will appear in the library. We can choose to control these parameters on the terminal. ProxySQL is only used as a load balancer. Create multiple accounts for ProxySQL, one for reading and writing, and one for reading only. Then the program implements the separation of reading and writing.

ProxySQL is designed and run in three layers, namely RUNTIME, MEMORY, and DISK:

RUNTIME represents the currently effective configuration of ProxySQL, including global_variables, mysql_servers, mysql_users, mysql_query_rules. The configuration here cannot be modified directly, it must be loaded from the next layer.

MEMORY is the configuration in main that is usually modified on the mysql command line. It can be considered as the image of the SQLite database in memory.

DISK / CONFIG FILE The configuration for persistent storage is usually in $(DATADIR )/proxysql.db, which will be loaded from the hard disk during restart. The /etc/proxysql.cnf file is only used during the first initialization. After that, if you want to modify the listening port, you still need to modify it in the management command line and then save it to the hard disk.

Common commands:

LOAD MYSQL SERVERS TO RUNTIME -- Make the modified configuration take effect, that is, load the parameters from MEMORY, which is equivalent to LOAD MYSQL USERS FROM MEMORY. The syntax of this statement is relatively simple. FROM means LOAD from the upper layer. TO represents from this layer to a certain layer. For example, we set up a MySQL monitoring account earlier, but we still need to execute LOAD and SAVE to save the variables and make them effective.

The above is the detailed content of ProxySQL read-write separation from configuration to use. For more information, please follow other related articles on the PHP Chinese website!