proxysql! As strong as Cristiano Ronaldo!

Brothers, after a few days old Zhang and meet you again. Every time we meet, there will be good news to tell you, this time is no exception. Some time ago published the "MySQL King Promotion Road," a book, the response is good. Try to publish a book on MongoDB OPS this year that will serve as a reference for those students who want to learn about NoSQL.

Now in the World Cup, Lao Zhang's favorite team is Portugal-love Cristiano Ronaldo, like his kind in the game of the spirit of strong and unyielding. We do technology is the same, do not because of a little difficulty, give up the original dream. Only by continuous efforts to enhance their own, in order to achieve a better platform for self-worth.

Today, Lao Zhang to introduce a MySQL middleware product--proxysql, it is a flexible and powerful MySQL agent layer. As powerful as Cristiano Ronaldo, it enables read-write separation, supports query routing, supports dynamic assignment of a SQL for cache, supports dynamic load configuration, failover, and some SQL filtering capabilities. There are some similar products such as Dbproxy, Mycat, Oneproxy and so on. But after repeated comparison and testing, decided to introduce a performance is not familiar with MySQL middleware product proxysql.
More details about Proxysql are available at:
Https://github.com/sysown/proxysql/wiki.
Then through the actual combat to fully understand the characteristics of proxysql and use of the scene, first introduce the environment, our system is the Centos6.7,mysql version is 5.7.14, prepare a master two from the architecture to match proxysql.

环境配置：192.168.56.100  Master（node1）   server-id：3306100192.168.56.101  Slave1（node2）   server-id：3306101192.168.56.102  Slave2（node3）   server-id：3306102192.168.56.103  Proxysql中间件 server-id：3306103

Note: Two from the library to open the Read_only=on.

Experimental architecture:

Proxysql installation and start-up
First of all, install some dependent packages and configure the Yum source for installation.
On 192.168.56.103, perform the following actions:

yum -y install perl-DBD-MySQLyum -y install perl-DBIyum -y install perl-Time-HiResyum -y install perl-IO-Socket-SSL

Two of Proxysql software packages.
GitHub website: https://github.com/sysown/proxysql/releases.
Percona official website: https://www.percona.com/downloads/proxysql/.
Install Proxysql:

rpm -ivh proxysql-1.3.9-1-centos67.x86_64.rpm

The configuration file path is/etc/proxysql.cnf.

Start Proxysql:

service proxysql start

Note: 6032 is the management port number of Proxysql, 6033 is the port number of the external service.

Admin user name and password are the default admin.

Close Proxysql:
Service Proxysql Stop

To view the installation version:

Administrator Login Command:
/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032

There are four libraries visible: Main, disk, stats, and monitor. Explain the role of these four libraries separately.
Main: Memory configuration database, that is, storage, the table store back-end DB instances, user authentication, routing rules and other information. The main library has the following information:

Main tables under the library:
The mysql_servers-backend can connect to the list of MySQL servers.
mysql_users-Configure the backend database account and monitor the account.
mysql_query_rules-specifies a list of rules that query routes to different servers on the backend.

Note: The table name starts with Runtime_ and proxysql the currently running configuration and cannot be modified through DML statements. You can only modify the corresponding table that does not start with runtime, and then "load" makes it effective and "save" saves it to the hard disk for the next reboot to load.

Disk Library-The configuration of the persisted disks.
Stats Library-Summary of statistical information.
Monitor Library-Some of the monitored information collected, including the health status of the database.

Configuring Proxysql Monitoring
First create a Proxysql monitoring account and an external access account on Master (192.168.56.100) and grant permissions.
The command is as follows:

create user ‘monitor‘@‘192.168.56.%‘ identified by ‘monitor‘;grant all privileges on *.* to ‘monitor‘@‘192.168.56.%‘ with grant option;create user ‘zs‘@‘192.168.56.%‘ identified by ‘zs‘;grant all privileges on *.* to ‘zs‘@‘192.168.56.%‘ with grant option;flush privileges;

Proxysql multi-layer configuration system
The Proxysql has a complete configuration system to facilitate DBA-to-line operations. The entire configuration system is divided into three layers, the top layer is runtime, the middle layer is memory and the lowest level, that is, the persistent layer of disk and config FILE.

Configuration structure:

RUNTIME: Represents the currently in use configuration of proxysql and cannot directly modify the configuration here, it must come in from the next layer of "load".
Memory:memory this layer is connected to the runtime layer, the following connection persistence layer. In this layer can be normal operation Proxysql configuration, random modification, will not affect the production environment. Modifying a configuration is usually done first in the memory layer, then confirmed normal and then loaded into runtime and persisted to disk.
Disk and config FILE: Persistent configuration information, the configuration information in memory is lost after reboot, so the configuration information needs to be kept on disk. When you restart, you can quickly load it back from disk.
After this three-tier configuration system is introduced, use the Proxysql to log in to add a master-slave server list.
The command is as follows:

insert into mysql_servers(hostgroup_id,hostname,port) values (10,‘192.168.56.100‘,3306);insert into mysql_servers(hostgroup_id,hostname,port) values (10,‘192.168.56.101‘,3306);insert into mysql_servers(hostgroup_id,hostname,port) values (10,‘192.168.56.102‘,3306);load mysql servers to runtime;save mysql servers to disk;

After logging in to Proxysql, any operation is required to run load to runtime, from memory load to runtime. The save to disk is then persisted to the diskette.

After the load is complete, three machines are online.

Next continue to configure the monitoring account for Proxysql, the command is as follows:

set mysql-monitor_username=‘monitor‘;set mysql-monitor_password=‘monitor‘;load mysql variables to runtime;save mysql variables to disk;

Then verify the monitoring information:


The monitoring information is normal, without any error.

Configuring Proxysql Master-Slave grouping information
A table mysql_replication_hostgroups is used here:

The writer_hostgroup inside is the number of the write group, and the Reader_hostgroup is the number of the read group. The experiment uses 10 as the write group and 20 as the read group number.

insert into mysql_replication_hostgroups values (10,20,‘proxy‘);load mysql servers to runtime;save mysql servers to disk;

Proxysql servers are grouped according to the value of the server's read_only. The server,master of read_only=0 is divided into a write group numbered 10, and the server,slave of Read_only=1 is divided into a read group numbered 20.

Configure the external access account, specify the main library by default, and turn on transaction persistence protection for that user.

Note: The Transaction_persistent field in the Mysql_users table defaults to 0

It is recommended that you set it to 1 after the user has been created, to avoid dirty reads, Phantom reads, and so on the following commands:

insert into mysql_users(username,password,default_hostgroup) values (‘zs‘,‘zs‘,10);update mysql_users set transaction_persistent=1 where username=‘zs‘;load mysql users to runtime;save mysql users to disk;

Verify that the server that is logged on is the main library:

Note: The external port number needs to be specified as 6033.

Configuring a read-write detach policy
Configuring a read-write detach policy requires the use of the Mysql_query_rules table. The Match_pattern field in the table is the rule that represents the setting, the Destination_hostgroup field represents the default specified grouping, and the apply represents the actual execution of the application rule. The
assigns all statements that begin with select to the read group numbered 20. The Select for update produces a write lock that has a high demand for data queries, assigns it to a write group numbered 10, and all other operations are routed to the write group by default. The
command is as follows:

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; "* * * * Read/write separation * * By creating the external Account ZS, connect the proxysql login database. The command is as follows: '/usr/local/mysql/bin/mysql-uzs-pzs-h 192.168.56.103-p 6033 ' * * View data for TT under ZS Library: **! [] (http://i2.51cto.com/images/blog/201806/18/0e4e67cca5bb59d343b3872a51cd613d.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Then log in to the management port, the query stats_mysql_query_digest this table to monitor the query status, the command is as follows: SELECT * FROM stats_mysql_query_digest;! [] (http://i2.51cto.com/images/blog/201806/18/5c7dfa40460715c314c1742c708bf0cf.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) It is visible that this SELECT statement is automatically routed to the read group numbered 20, which is on the slave library. Then continue the test by logging into the database via proxysql: '/uSr/local/mysql/bin/mysql-uzs-pzs-h 192.168.56.103-p 6033 ' Execute select * from ZS.TT for update and update TT set name= ' FF ' wher E-score=100 statement operation:! [] (http://i2.51cto.com/images/blog/201806/18/3fe8f7324f55a65b717047fae2b71107.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) ! [] (http://i2.51cto.com/images/blog/201806/18/3120aece6efc2fe3715115bfc78675e0.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Then login to the management port, monitoring the status of the query, the discovery has been successfully routed to the number 10 write group, that is, the main library, proof read and write separation settings successfully. [] (http://i2.51cto.com/images/blog/201806/18/266ee481d4751223124bac4326879ac9.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) ! [] (http://i2.51cto.com/images/blog/201806/18/e4c27fb9757e4408afba6609499d00e1.png?x-oss-process=image/ Watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) After the read/write separation setting is successful, we can adjust the weights to allow a machine to tolerate more read operations. These techniques can be used on operations proxysql. Adjust the query weights of the 192.168.56.102 node2 node to allow more read requests to be routed to this machine. The command is as follows: Update mysql_servers set weight=10 where hostname= ' 192.168.56.102 '; load MySQL servers to runtime;save mysql servers t o disk;! [] (http://i2.51cto.com/images/blog/201806/18/1934fb4e0d0b457e718937176abf20b5.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) As a bitter dba, we, both beginners and the "old drivers" who have been working for years, should not rush to build each MySQL cluster architecture. In the process of learning, some students always have a misunderstanding, that is, I would like to build all the database architecture is very powerful. In fact, this is not the case, the structure is not our ultimate goal, as a DBA to understand the company's existing business, to see what the company's business scenario is suitable for the architecture, to do the corresponding database architecture design. Understand the advantages and disadvantages of the architecture, and the problems that may arise in the future application, and make a plan to solve the problem in advance. Knowing each other, paying attention to details, can avoid overtime and night to deal with the problems that should not happen. The following summarizes the experience of five MySQL architecture designs. (1) According to the company's existing business design reasonable structure. (2) Select the mature architecture scheme. (3) To adapt to local conditions, according to the actual equipment to make choices. (4) Consider the feasibility of the scheme. (5) The more simple the better, the better for the company. Lao Zhang I would like to write anything, can give you some inspiration. In the work, can help to everyone is enough! Technology needs to be shared, we work together to make our families better!

proxysql! As strong as Cristiano Ronaldo!