Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to use MySQL to realize the separation of database read and write in ProxySQL

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people are at a loss about how to use MySQL to realize the separation of database reading and writing in ProxySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

ProxySQL introduction

1. Connection pool, and it is multiplexing

2. Limit the maximum number of connections between hosts and users

3. Auto logoff backend DB

Delay exceeds threshold

Ping delay exceeds threshold

Network failure or downtime

4. Powerful rule routing engine

Achieve the separation of reading and writing

Query rewriting

Sql traffic mirroring

5. Support prepared statement

6. Support Query Cache

7. Support load balancing and combine with gelera to automatically failover

Introduction to the overall environment

1. System environment

The system environment of the three servers is as follows

[root@db1 ~] # cat / etc/redhat-release CentOS Linux release 7.4.1708 (Core) [root@db1 ~] # uname-r 3.10.0-693.el7.x86_64

2. IP address and software version

Proxy 192.168.22.171

Db1 192.168.22.173

Db2 192.168.22.174

Mysql 5.7.17

Proxy sql 1.4.8

3. Turn off firewall and selinux

Systemctl stop firewalld # stop Firewall Service systemctl disable firewalld # disable boot self-starting sed-I / etc/selinux/conf & & reboot # try to modify the configuration file of selinux replaced by the sed command

4. Mysql installation is synchronized with master and slave.

Please refer to the following articles for installation

Practical Application of LAMP Architecture-- MySQL Service

Master-slave synchronization please refer to the following article

Master-Slave synchronization process of MySQL Database in Linux system

Install the deployment process

1. Master-slave synchronization of database

View master-slave synchronization status

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.22.173 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-log.000001 Read_Master_Log_Pos: 154 Relay_Log_File: db2-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-log.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_ DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 526 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_ File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 70a61633-63ae-11e8-ab86-000c29fe99ea Master_Info_File: / mysqldata/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for more updates Master_Retry_Count: 0 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (86400 sec)

Detect master-slave synchronization

[root@db1 ~] # mysql-uroot-p-e "create database testdb;" Enter password: [root@db1 ~] # mysql-uroot-p-e "show databases;" | grep testdb Enter password: testdb # db2 to see if mysql is synchronized > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +-+ 5 rows in set (0.01sec)

2. Prepare proxySQL software

[root@proxy ~] # wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm [root@proxy ~] # ll proxysql-1.4.8-1-centos7.x86_64.rpm-rw-r--r-- 1 root root 5977168 Apr 10 11:38 proxysql-1.4.8-1-centos7.x86_64.rpm

3. Installation and configuration

[root@proxy ~] # yum install-y proxysql-1.4.8-1-centos7.x86_64.rpm [root@proxy ~] # rpm-ql proxysql/ etc/init.d/proxysql # startup script / etc/proxysql.cnf # configuration file, valid only on the first startup (/ var/lib/proxysql/proxysql.db file does not exist). After starting #, you can modify the configuration and take effect by modifying the database in the proxysql management side (officially recommended). / usr/bin/proxysql # main program file / usr/share/proxysql/tools/proxysql_galera_checker.sh / usr/share/proxysql/tools/proxysql_galera_writer.pl

4. Detailed explanation of configuration file

[root@proxy ~] # egrep-v "^ # | ^ $" / etc/proxysql.cnf datadir= "/ var/lib/proxysql" # data directory admin_variables= {admin_credentials= "admin:admin" # user name and password of the management side mysql_ifaces= "0.0.0.0etc/proxysql.cnf datadir= 6032" # management port The management database used to connect to proxysql} mysql_variables= {threads=4 # specify the number of threads opened on the forwarding port max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces= "0.0.0.0 threads=4 6033" # specify the forwarding port for connecting to the backend mysql database Equivalent to default_schema= "information_schema" stacksize=1048576 server_version= "5.5.30" # specify the version of the backend mysql connect_timeout_server=3000 monitor_username= "monitor" monitor_password= "monitor" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 } mysql_servers = () mysql_users: () mysql_query_rules: () scheduler= () mysql_replication_hostgroups= () # so we use the officially recommended way to configure proxy sql

5. Start the service and view

[root@proxy ~] # / etc/init.d/proxysql startStarting ProxySQL: DONE! [root@proxy ~] # ss-lntup | grep proxy tcp LISTEN 0 128 *: 6032 *: * users: (("proxysql", pid=1199,fd=23)) tcp LISTEN 0 128 *: 6033 *: * users: (("proxysql", pid=1199,fd=22)) tcp LISTEN 0128 *: 6033 *: * users: ("proxysql", pid=1199) Fd=21)) tcp LISTEN 0128 *: 6033 *: * users: (("proxysql", pid=1199,fd=20)) tcp LISTEN 0128 *: 6033 *: * users: (("proxysql", pid=1199,fd=19)) # you can see that forwarding port 6033 starts four threads

6. Configure the account and authorize it on mysql

Mysql > GRANT ALL ON *. * TO 'proxysql'@'192.168.22.%' IDENTIFIED BY' 123456; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql > flush privileges; Query OK, 0 rows affected (0.02 sec)

7. Proxysql default database description

[root@proxy] # yum install mysql-y [root@proxy] # mysql-uadmin-padmin-h227.0.0.1-P6032 Welcome to the MariaDB monitor. Commands end with; or\ g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2017, 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) |

Main: memory configuration database, where backend db instances, user authentication, routing rules and other information are stored in the table. The current configuration of proxysql whose table name starts with runtime_ cannot be modified by dml statement, only the corresponding table that does not start with runtime_ (in memory) can be modified, then LOAD makes it effective, and SAVE saves it to the hard disk for next restart to load.

Disk: is the configuration that persists to the hard disk, sqlite data file.

Stats: is the statistics of proxysql running crawls, including the number of commands executed to the backend, traffic, processlist, query category summary / execution time, and so on.

Monitor: the library stores the information collected by the monitor module, mainly for health / latency checks on the back-end db.

8. The configuration system of proxysql

ProxySQL has a complex but easy-to-use configuration system that meets the following requirements:

Allow easy dynamic configuration updates (this is so that ProxySQL users can use it in large infrastructures that require zero downtime configurations). A MySQL-compatible management interface can be used for this purpose.

2. Allow as many configuration items as possible to be dynamically modified without restarting the ProxySQL process

3. Invalid configuration can be rolled back effortlessly

4. This is achieved through a multi-level configuration system, where settings are moved from runtime to memory and persisted to disk as needed.

Level 3 configuration consists of the following layers:

Reference article: https://github.com/sysown/pro...

9. Configure proxysql administrative users

The default table information for proxysql is as follows

MySQL [main] > show tables +-+ | tables | +-+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | 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) # here is to use the insert into statement to dynamically configure There is no need to restart MySQL [(none)] > insert into mysql_servers (hostgroup_id,hostname,port,weight,comment) values. Query OK, 1 row affected (0.01 sec) MySQL [(none)] > insert intomysql_servers (hostgroup_id,hostname,port,weight,comment) values (2 row affected sec) MySQL [(none)] > select * from mysql_servers + -+-+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +- -+-+ | 1 | db1 | 3306 | ONLINE | 1 | 0 | | 1000 | 0 | 0 | Write Group | | 2 | db2 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group | +-- | -+- -- + 2 rows in set (0.00 sec) # next, write the user you just created on the mysql client to the mysql_users table of the proxy sql host It is also used for proxysql clients to access the database, and the default group is the write group. When there is a problem with the read-write separation rule, it directly accesses the database of the default group. MySQL [main] > INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('proxysql','123456',1); Query OK, 1 row affected (0.00 sec) MySQL [main] > select * from mysql_users +- +-+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +-- -+- -+-+ | proxysql | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | + -+ -+ 1 row in set (0.00 sec)

Users who add monitoring on mysql

Mysql > GRANT SELECT ON *. * TO 'monitor'@'192.168.22.%' IDENTIFIED BY' monitor'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > flush privileges; Query OK, 0 rows affected (0.00 sec) # configure monitoring user MySQL [main] > set mysql-monitor_username='monitor'; Query OK on proxysql host, 1 row affected (0.00 sec) MySQL [main] > set mysql-monitor_password='monitor' Query OK, 1 row affected (0.00 sec) # reference article: https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration

10. Configure the forwarding rules of proxysql

MySQL [main] > insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values; Query OK, 1 row affected (0.01 sec) MySQL [main] > insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values Query OK, 1 row affected (0.00 sec) MySQL [main] > select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules +-- +-- +-+ | rule_id | active | match_digest | destination_hostgroup | apply | + -+ | 1 | 1 | ^ SELECT.*FOR UPDATE$ | 1 | 1 | 2 | 1 | ^ SELECT | 2 | 1 | +- -+-+-- +-- + 2 rows in set (0.00 sec) # configure the request to query select to be forwarded to the hostgroup_id=2 group (read group) # to modify statements such as select * from table_name for update We need to forward the request to the write group, that is, hostgroup_id=1# forwards all other requests that are not matched by the rules to the default group (default_hostgroup in the mysql_ users table)

11. Update the configuration to RUNTIME

From the above configuration system hierarchy, we can see that all incoming requests first go through the RUNTIME layer.

MySQL [main] > load mysql users to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main] > load mysql servers to runtime; Query OK, 0 rows affected (0.02 sec) MySQL [main] > load mysql query rules to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main] > load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec) MySQL [main] > load admin variables to runtime; Query OK, 0 rows affected (0.00 sec)

12. Save all configurations to disk

All configuration data is saved to disk, that is, permanently written to the file / var/lib/proxysql/proxysql.db

MySQL [main] > save mysql users to disk; Query OK, 0 rows affected (0.03 sec) MySQL [main] > save mysql servers to disk; Query OK, 0 rows affected (0.04 sec) ySQL [main] > save mysql query rules to disk; Query OK, 0 rows affected (0.03 sec) MySQL [main] > save mysql variables to disk; Query OK, 94 rows affected (0.02 sec) MySQL [main] > save admin variables to disk Query OK, 31 rows affected (0.02 sec) MySQL [main] > load mysql users to runtime; Query OK, 0 rows affected (0.00 sec)

13. Test the separation of read and write

[root@proxy] # mysql-uproxysql-p123456-h 127.0.0.1-P 6033 Welcome to the MariaDB monitor.Commands end with; or\ g. Your MySQL connection id is 2Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement. MySQL [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +-+ 5 rows in set (0. 02 sec) # this is our real database

Create data and tables and test the separation of read and write

MySQL [(none)] > create database test_proxysql; Query OK, 1 row affected (0.02 sec) MySQL [(none)] > use test_proxysql; Database changed MySQL [test_proxysql] > create table test_tables (name varchar (20), age int (4)); Query OK, 0 rows affected (0.07 sec) MySQL [test_proxysql] > insert into test_tables values ('zhao','30') Query OK, 1 row affected (0.09 sec) MySQL [test_proxysql] > select * from test_tables; +-+ | name | age | +-+-+ | zhao | 30 | +-+-+ 1 row in set (0.02 sec)

View read-write separation on the proxysql management side

MySQL [main] > select * from stats_mysql_query_digest + -+ | hostgroup | schemaname | username | digest | digest_text | count_star | | first_seen | last_seen | sum_time | min_time | max_time | +-| -+ | 2 | test_proxysql | proxysql | 0x57CF7EC26C91DF9A | select * from test_tables | 1 | 1527667635 | 1527667635 | 14253 | 14253 | 14253 | | 1 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @ @ version_comment limit? | 1 | 1527667214 | 1527667214 | 0 | 0 | 1 | test_proxysql | proxysql | 0xFF9877421CFBDA6F | insert into test_tables values (? | 1 | 1527667623 | 1527667623 | 89033 | 89033 | 89033 | | 1 | information_schema | proxysql | 0xE662AE2DEE853B44 | create database test-proxysql | 1 | 1527667316 | 1527667316 | 8470 | 8470 | 8470 | 1 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | | 1 | 1527667222 | 1527667222 | 19414 | 19414 | 19414 | 1 | information_schema | proxysql | 0xB9EF28C84E4207EC | create database test_proxysql | 1 | 1527667332 | 1527667332 | 15814 | 15814 | 15814 | 2 | information_schema | proxysql | 0x620B328FE9D6D71A | SELECT DATABASE () | 1 | 1527667342 | 1527667342 | 23386 | 23386 | 23386 | | 1 | test_proxysql | proxysql | 0x02033E45904D3DF0 | show databases | 1 | 1527667342 | 1527667342 | 2451 | 2451 | 2451 | 1 | test_proxysql | proxysql | 0x59F02DA280268525 | create table test_tables | 1 | 1527667360 | 1527667360 | 9187 | | 9187 | 9187 | | 1 | test_proxysql | proxysql | 0x99531AEFF718C501 | show tables | 1 | 1527667342 | 1527667342 | 1001 | 1001 | 1 | test_proxysql | proxysql | 0xC745E37AAF6095AF | create table test_tables (name varchar (?) | Age int (?) | 1 | 1527667558 | 1527667558 | 68935 | 68935 | 68935 | +- -- + 11 rows in set (0.01sec) # from the above results, we can see that the read-write separation configuration is successful. Read requests are forwarded to group 2, and write requests are forwarded to group 1

The whole read-write separation architecture configuration is complete, but this architecture needs to be optimized, that is, there is a single point of problem with this architecture. It can be used in the actual production environment.

After reading the above, have you mastered how to use MySQL to achieve database read-write separation in ProxySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report