ProxySQL read/write splitting and proxysql read/write

ProxySQL read/write splitting and proxysql read/write
First, let's 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 located 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.logmanager_workdir=/var/log/mha/app1.logmaster_binlog_dir=/home/mysql/db3306/log/master_ip_failover_script=/usr/local/bin/master_ip_failovermaster_ip_online_change_script=/usr/local/bin/master_ip_online_changepassword=123456ping_interval=1remote_workdir=/tmprepl_password=123456repl_user=rootreport_script=/usr/local/bin/send_reportshutdown_script=""ssh_user=rootuser=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 will build read/write splitting based on such an MHA environment. 1: Install the ProxySQL software. We deploy it on 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

 

The following prompt is displayed:

Installed:proxysql.x86_64 0:1.3.7-1.1.el6Complete!

 

That is, the installation is complete. Check the specific file:

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

 

It is found that ProxySQL has been successfully installed. 2: Start ProxySQLTake a 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 proxysqlexport CLUSTER_APP_USERNAME="proxysql_user"export CLUSTER_APP_PASSWORD="passw0rd" # ProxySQL read/write hostgroupexport WRITE_HOSTGROUP_ID="10"export READ_HOSTGROUP_ID="11" # ProxySQL read/write configuration mode.export MODE="singlewrite"

 

Start:

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

 

Now the ProxySQL route has been started, prompting us to start the proxysql service

[root@localhost bin]# service proxyproxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor[root@localhost bin]# service proxysql startStarting ProxySQL: DONE![root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032mysql: [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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

 

We can see that we have successfully logged on. The default configuration file of proxysql is as follows:

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

 

Next we start to configure ProxySQL:

[root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032mysql: [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 itsaffiliates. Other names may be trademarks of their respectiveowners. 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 adminDatabase changedmysql> 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 following Master/Slave information:

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 the read/write ing (this mainly ensures that ProxySQL can automatically switch during MHA backend switching ):

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 the 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: if the status of runtime_mysql_servers is not ONLINE, you can view the specific error information in the table monitor. mysql_server_ping_log. Mysql> select * from monitor. mysql_server_ping_log;Then configure the program account. simply use the root: 123456 highest permission for Configuration:

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 complete, reload and save 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, configure the 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)

 

Now the configuration is complete. 3: Test read/write splittingOn 172.16.16.35, connect proxySQL port 6033 and perform the simple select Operation:

[root@localhost ~]# mysql -uroot -p123456 -h172.16.16.34 -P6033mysql: [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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 let's look at the query:

mysql> select * from stats_mysql_query_digest;

The read/write splitting has been completed.

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;

It has been automatically routed to the master database. So far, the ProxySQL + MySQL MHA read/write splitting test has been completed... To be continued 5: Thinking About ProxySQL and simple commandsProxySQL can achieve read/write splitting through the above method, but is this really no problem? If it is a SQL statement that requires a high real-time performance, such as querying order status, it seems that a BUG will occur when it is routed to the slave database. We can choose to control these parameters on the program side. ProxySQL is only used as a server Load balancer instance and multiple accounts, one read/write and one read-only account are created for ProxySQL. Then the program implements read/write splitting. ProxySQL is designed to run in three layers: RUNTIME, MEMORY, and DISK: RUNTIME, which indicates the current configuration of ProxySQL, including global_variables, mysql_servers, mysql_users, and mysql_query_rules. You cannot directly modify the configuration here. You must load the configuration from the next layer. MEMORY is usually configured in the main modified by the mysql command line. It can be considered as the persistent storage configuration of the SQLite database in the MEMORY image DISK/config file, usually at $ (DATADIR) /proxysql. db, which will be loaded from the hard disk during restart. /Etc/proxysql. the cnf file is only used during the first initialization. After the cnf file is complete, 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 Common commands on the hard disk: load mysql servers to runtime -- make the modified configuration take effect, that is, LOAD the parameter from memory, equivalent to load mysql users from memory. the syntax of this statement is relatively single, FROM indicates load from the upper layer, and TO indicates FROM the current layer TO a certain layer. For example, we set the MySQL Monitoring account, but we still need to execute LOAD and SAVE to SAVE the variables and make them take effect.