Shulou(Shulou.com)06/01 Report--

Brothers, Lao Zhang has seen you again many days later. Every time I meet with you, there will be good news to tell you, and this time is no exception. Some time ago, the book "the Road to Promotion of the King of MySQL" was published, and the response was good. Strive to publish another practical book on MongoDB operation and maintenance this year, which can be used as a reference for those students who want to learn NoSQL.

Just in time for the World Cup, Lao Zhang's favorite team is Portugal-he loves Ronaldo best and likes his spirit of not admitting defeat in the game. It's the same with our technology. Don't give up your dream just because of a little difficulty. Only by making continuous efforts to improve yourself, can we achieve self-value on a better platform.

Today, Lao Zhang introduces a middleware product of MySQL-ProxySQL, which is a flexible and powerful MySQL proxy layer. As powerful as Ronaldo, it can achieve read-write separation, support Query routing, support dynamically specifying a SQL for cache, and support dynamic loading configuration, failover and some SQL filtering functions. There are also some similar products such as DBproxy, MyCAT, OneProxy and so on. However, after repeated comparison and testing, I decided to introduce a MySQL middleware product ProxySQL, which is not familiar with the performance.

More details about ProxySQL can be found at:

Https://github.com/sysown/proxysql/wiki .

Next through the actual combat to fully understand the characteristics and usage scenarios of ProxySQL, first introduce the environment, our system is CentOS6.7,MySQL version 5.7.14, prepare one master and two slaves architecture to cooperate with ProxySQL.

Environment configuration: 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 middleware server-id:3306103

Note: read_only=on should be enabled for both slave libraries.

Lab architecture:

Installation and startup of ProxySQL

First of all, you need to install some dependent software packages and configure the Yum source for installation.

On 192.168.56.103, do the following:

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

Two download addresses for the ProxySQL package.

GitHub official 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 and 6033 is the port number of external services.

The administrative user name and password are the default admin.

Turn off ProxySQL:

Service proxysql stop

View the installation version:

Administrator login command:

/ usr/local/mysql/bin/mysql-uadmin-padmin-h 127.0.0.1-P 6032

You can see that there are four libraries: main, disk, stats and monitor. Explain the role of these four libraries respectively.

Main: memory configuration database, namely MEMORY, in which backend db instances, user authentication, routing rules and other information are stored in the table. The following information is available in the main library:

The main tables under the library:

A list of MySQL servers that can be connected to the mysql_servers- backend.

Mysql_users- configures the account of the backend database and the account of monitoring.

Mysql_query_rules- specifies the list of rules that Query routes to different backend servers.

Note: the table name that begins with runtime_ indicates the configuration content that ProxySQL is currently running and cannot be modified by the DML statement. You can only modify the corresponding table that does not start with runtime, then "LOAD" to make it effective, and "SAVE" to save it to the hard disk for the next restart to load.

Disk library-persist the configuration of the disk.

Stats Library-Summary of statistics.

Monitor Library-some monitored information collected, including the health status of the database, etc.

Configure ProxySQL Monitoring

First, create and grant permissions to the monitoring account and external access account of ProxySQL on master (192.168.56.100).

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

Multi-layer configuration system of ProxySQL

ProxySQL has a complete configuration system to facilitate the online operation of DBA. The whole configuration system is divided into three layers, the top layer is RUNTIME, the middle layer is MEMORY and the lowest layer, that is, DISK and CONFIG FILE of the persistence layer.

Configuration structure:

RUNTIME: represents the configuration currently in use in ProxySQL. The configuration here cannot be modified directly, but must be entered from the next layer "load".

The MEMORY:MEMORY layer connects the RUNTIME layer above and the persistence layer below. In this layer, you can operate the ProxySQL configuration normally and modify it at will, without affecting the production environment. The modification of a configuration is usually done at the MEMORY layer, and then loaded into RUNTIME and persisted to disk after it is confirmed to be normal.

DISK and CONFIG FILE: persist the configuration information. The configuration information in memory will be lost after restart, so you need to keep the configuration information on disk. When rebooting, it can be quickly loaded back from disk.

After introducing the three-tier configuration system, use super-managed users to log in to ProxySQL to add the list of master and slave servers.

The command is as follows:

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

After logging in to ProxySQL, whatever you do, you need to run load to runtime to load from memory to runtime. Then perform save to disk persistence to disk.

After loading, all three machines are in ONLINE state.

Next, continue to configure the monitoring account for ProxySQL with the following command:

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 and there are no errors reported.

Configure ProxySQL master-slave packet information

A table mysql_replication_hostgroups will be used here:

The writer_hostgroup is the number of the write group, and the reader_hostgroup is the number of the read group. In the experiment, 10 is used as the write group and 20 as the read group number.

Insert into mysql_replication_hostgroups values (10, 10, 20, 10, 10, 10, 10, 10, 10, 10, 10, 10, 20, load mysql servers to runtime;save mysql servers to disk.)

ProxySQL groups servers according to the value of server's read_only. The server,master of read_only=0 is assigned to the write group numbered 10, and the server,slave of read_only=1 is assigned to the reading group of 20.

Configure the external access account, specify the main library by default, and enable transaction persistence protection for this user.

Note: the transaction_persistent field in the mysql_ users table defaults to 0

It is recommended to set it to 1 after the user is created to avoid dirty reading, phantom reading and other phenomena. The commands are as follows:

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 logged in is the main library:

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

Configure read-write separation policy

Configuring the read-write separation policy requires the use of the mysql_query_ rules table. The match_pattern field in the table represents the set rule, the destination_hostgroup field represents the default specified grouping, and the apply represents the actual execution of the application rule.

Assign all statements that begin with select to the read group numbered 20. Select for update will produce a write lock, which requires high effectiveness of the data query. Assign it to the write group numbered 10, and all other operations will be routed to the write group by default.

The command is as follows:

Insert into mysql_query_rules (active,match_pattern,destination_hostgroup, apply) VALUES (1Magi'^ SELECT.*FOR UPDATE$',10,1); insert into mysql_query_rules (active,match_pattern,destination_hostgroup, apply) VALUES (1Magi'^ SELECT',20,1); load mysql query rules to runtime;save mysql query rules to disk; ```* Test read-write separation * * Log in to the database through the created external account zs. The command is as follows: `/ usr/local/mysql/bin/mysql-uzs-pzs-h 192.168.56.103-P 6033`` * * View the data of tt under the zs library: * *! [] (https://s1.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=) and then log in to the management port To monitor the query status by querying the stats_mysql_query_digest table, the command is as follows: select * from stats_mysql_query_digest ! [] (https://s1.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=) can see that this select statement is automatically routed to the reader group numbered 20, that is, the slave library. And then continue the test. Log in to the database through ProxySQL: `/ usr/local/mysql/bin/mysql-uzs-pzs-h 192.168.56.103-P 6033` to execute the statement operations of select * from zs.tt for update and update tt set name='ff' where score=100:! [] (https://s1.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=)! [] (https://s1.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=) logs in to the management port at this time Monitor the status of the query and find that it has been successfully routed to the write group numbered 10, that is, the main database, which proves that the read-write separation setting is successful. ! [] (https://s1.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=)! [] (https://s1.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, After successfully setting the separation of read and write, We can adjust the weight to allow a machine to take more read operations. These techniques can be used in the operation and maintenance of ProxySQL. Adjust the query weight of the 192.168.56.102 node2 node so that more read requests are 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 to diskscape! [] (https://s1.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 those of us who are struggling with DBA, whether we are beginners or "old drivers" who have been working for many years, we should not rush to build each MySQL cluster architecture. In the process of learning, some students always have a misunderstanding, that is, I think I can build all the database architecture is very good. In fact, this is not the case. Architecture construction is not our ultimate goal. As a DBA, we should first understand the existing business of our company, see what kind of architecture the company's business scenario is suitable for, and do a good job in designing the corresponding database architecture. Understand the advantages and disadvantages of the architecture, as well as the problems that may arise in the future application, and make a plan that can solve the problem in advance. Only by knowing ourselves and knowing each other and paying attention to details can we avoid working overtime day and night to deal with problems that should not happen.

Here are five lessons learned from MySQL architecture design.

(1) to design a reasonable structure according to the company's existing business.

(2) choose a mature architecture scheme.

(3) adjust measures to local conditions and make choices according to the actual equipment conditions.

(4) consider the feasibility of the scheme.

(5) the simpler the better, the more suitable for the company, the better.

Lao Zhang, I hope that anything I can write can give you some inspiration. In the work, can help you all is enough! Technology needs to be shared, we work hard together to make our family better!

Finally, as a digression, Lao Zhang was recently invited to practice a set of authentic MySQL dragon eighteen palms in the online column of 51CTO blog. If you are interested, you can poke the link to learn about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.