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

This article mainly introduces how ProxySQL helps MySQL implement the separation of reading and writing. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone's reference. The following is to understand with the author how ProxySQL helps MySQL implement the separation of reading and writing.

ProxySQL is a high-performance lightweight product based on C++, is a powerful mysql middleware, it can achieve a variety of ways to read and write separation.

Master IP:172.16.75.4 CentOS 7.5D server_id:401

Slave IP:172.16.75.3 CentOS 7.5C server_id:301

1.

[root@slave2 ~] # ss-tnlState Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *: 3306 *: * LISTEN 0 128 *: 111l *: * LISTEN 0128 *: 6032 *: * LISTEN 0128 *: 6033 *: * LISTEN 0128 *: 6033 *: * LISTEN 0128 *: 6033 *: * LISTEN 0 128 *: 6033 *: *

two。 Use the mysql client tool to log in to proxysql. The user name and password are admin and the port is 6032. Localhost login is not allowed by default, so log in with 127.0.0.1IP address.

[root@slave2] # mysql-uadmin-padmin-h227.0.0.1-P6032Welcome to the MariaDB monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement. MySQL [(none)] > show databases +-+ | seq | name | file | +- -+ | 0 | main | | 2 | disk | / var/lib/proxysql/proxysql.db | | 3 | stats | | 4 | monitor | | | 5 | stats_history | / var/lib/proxysql/proxysql_stats.db | +-- + 5 rows in set (0.00 sec) |

3. This paper mainly introduces the mian library and monitor library.

MySQL [(none)] > show tables from main +-+ | tables | +-+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | insert the monitoring node | Master node and slave node | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_ Hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +-- -+ 20 rows in set (0.00 sec)

4. There are several important properties in the mysql_ sessions table:

Hostgroup_id: group ID to distinguish between master and slave

Hostname: the IP address of the backend master and slave

Port: IP port of backend master and slave. Default is 3306.

MySQL [main] > insert into mysql_servers (hostgroup_id,hostname,port) values (10 record172.16.75.4), (20) Query OK, 2 rows affected (0.02 sec) MySQL [main] > load mysql servers to runtime;Query OK, 0 rows affected (0.01 sec) MySQL [main] > save mysql servers to disk;Query OK, 0 rows affected (0.06 sec)

Note:

1. The hostgroup_id of the set master is 10 (write group)

two。 Set the hostgroup_id of slave to 20 (read group)

3. After each operation in proxysql, it needs to be manually loaded into memory, and then manually saved to disk, and the "_" in the table name is changed to a space; mysql_servers à mysql servers

5. Authorize a monitoring user on master to monitor the backend node (Note: it is the mysql of master, not Proxysql)

The permissions required by this user: replication client and replication slave

MariaDB [(none)] > grant replication client,replication slave on *. * to 'monitor'@'%' identified by' 123456 query OK, 0 rows affected (0.02 sec)

6. Add this node to the proxysql

MySQL [main] > set mysql-monitor_username='monitor';Query OK, 1 row affected (0.00 sec) MySQL [main] > set mysql-monitor_password='123456';Query OK, 1 row affected (0.00 sec) MySQL [main] > load mysql variables to runtime;Query OK, 0 rows affected (0.00 sec) MySQL [main] > save mysql variables to disk;Query OK, 95 rows affected (0.03 sec)

Check the table mysql_server_ping_log to understand the status information of backend CVM.

MySQL [main] > select * from mysql_server_ping_log limit 1 10 + -+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +- 4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505686592082 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server | | 172.16.75.4 | 3306 | 1541505686594872 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505696592635 | 0 | | Host '172.16.75.4' is not allowed to connect to this MariaDB server | | 172.16.75.4 | 3306 | 1541505696595442 | 0 | Access denied for user' monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505706593101 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server | | 172.16.75 | .4 | 3306 | 1541505706596427 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505716593471 | 0 | Host' 172.16.75.4'is not allowed to connect to this MariaDB server | | 172.16.75.4 | 3306 | 1541505716596416 | 0 | Access denied for user 'monitor'@'172. 16.75.4'(using password: YES) | | 172.16.75.3 | 3306 | 1541505726593810 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server | +-+ -+ 10 rows in set (0.00 sec)

7. Set the read group and write group for the grouping of the backend defined CVM. You can add the definition to the mysql_replication_hostgroups table. Check whether the backend node has read_only permission by viewing the mysql_server_read_only_ log table in the monitor library.

MySQL [main] > insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10 main 20); Query OK, 1 row affected (0.00 sec) MySQL [main] > load mysql servers to runtime;Query OK, 0 rows affected (0.00 sec) MySQL [main] > save mysql servers to disk;Query OK, 0 rows affected (0.03 sec) MySQL [main] > select * from monitor.mysql_server_read_only_log limit 3 +-+ | hostname | port | time_start_us | success_time_us | read_only | error | +-+-- -+ | 172.16.75.4 | 3306 | 1541506648164762 | 0 | NULL | | 172.16.75.3 | 3306 | 1541506648162822 | 3585 | 1 | NULL | | 172.16. 75.3 | 3306 | 1541506649664049 | 993 | 1 | NULL | +-+-+ 3 rows in set (0.00 sec)

8.

Master mysql:

MariaDB [(none)] > grant all on *. * to 'reader'@'%' identified by' 123456 question OK, 0 rows affected (0.01 sec) MariaDB [(none)] > grant all on *. * to 'writer'@'%' identified by' 123456 question OK, 0 rows affected (0.00 sec)

Proxysql:

MySQL [main] > insert into mysql_users (username,password,default_hostgroup) values ('reader','123456',20), (' writer','123456',10); Query OK, 2 rows affected (0.00 sec) MySQL [main] > load mysql users to runtime;Query OK, 0 rows affected (0.01 sec) MySQL [main] > save mysql users to disk Query OK 0 rows affected (0.03 sec) MySQL [main] > select * from mysql_users\ gateway * 1. Row * * username: reader password: 123456 active: 1 use_ssl: 0 Default_hostgroup: 20 default_schema: NULL schema_locked: 0transaction_persistent: 1 fast_forward: 0 backend: 1 frontend: 1 max_connections: 10000 row * * * username: writer password: 0 active: 1 use_ssl: 0 default_hostgroup: 10 default_schema: NULL schema_locked: 0transaction_persistent: 1 fast_forward: 0 backend: 1 frontend: 1 max_connections: 100002 rows in set (123456 sec)

8. Test the user-based read-write separation mechanism on any host

[root@slave2 ~] # mysql-uwriter-h272.16.75.4-P6033-p123456-e 'select @ @ server_id';+-+ | @ @ server_id | +-+ | 401 | +-+ [root@slave2 ~] # mysql-ureader-h272.16.75.4-P6033-p123456-e' select @ @ server_id' +-+ | @ @ server_id | +-+ | 301 | +-+

9. Separation of read and write based on SQL statement

You need to add two regular expression rules to the mysql_query_rules table

MySQL [main] > insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values, (2 rows affected 1 sec) MySQL [main] > load mysql query rules to runtime;Query OK, 0 rows affected (0 sec) MySQL [main] > save mysql query rules to disk;Query OK, 0 rows affected (0.02 sec)

10. The test of any host is based on the read-write separation mechanism of SQL statements.

[root@slave2 ~] # mysql-ureader-h272.16.75.4-P6033-p123456-e'set @ @ autocommit=0;\ start transaction;\ use hellodb;\ insert into coc (ID,ClassID,CourseID) values (100100100);\ select @ @ server_id;\ commit;'+-+ | @ @ server_id | +-+ | 301 | +-+

View the hellodb database in Master

MariaDB [hellodb] > select * from coc +-+ | ID | ClassID | CourseID | +-+ | 1 | 1 | 2 | 2 | 1 | 5 | 3 | 2 | 2 | 4 | 2 | 6 | 5 | 3 | 1 | | 6 | | 3 | 7 | 7 | 4 | 5 | 8 | 4 | 2 | 9 | 5 | 1 | 10 | 5 | 9 | 11 | 6 | 3 | 12 | 6 | 4 | 13 | 7 | 4 | 14 | 7 | 3 | + | -+-+ 14 rows in set (0.00 sec) MariaDB [hellodb] > select * from coc +-+ | ID | ClassID | CourseID | +-+ | 1 | 1 | 2 | 2 | 1 | 5 | 3 | 2 | 2 | 2 | 4 | 2 | 6 | 5 | 3 | | 1 | | 6 | 3 | 7 | 7 | 4 | 5 | 8 | 4 | 2 | 9 | 5 | 1 | 10 | 5 | 9 | 11 | 6 | 3 | 12 | 6 | 4 | 13 | 7 | 4 | 14 | 7 | 3 | 100 | | 100 | 100 | +-+ 15 rows in set (0.00 sec)

After reading the above about how ProxySQL helps MySQL implement the separation of reading and writing, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.