MySQL uses proxysql for read-write separation

Proxysql Introduction:

ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。官方文档：https://github.com/sysown/proxysql/wiki/：https://github.com/sysown/proxysql/releases/

Environment:

系统：CentOS7.5ProxySQL版本：proxysql-1.4.8-1-centos7.x86_64.rpmMysql版本：MySQL 5.7.22ProxySQL主机IP：192.168.1.2Mysql主库IP：192.168.1.3Mysql从库IP：192.168.1.4

Prerequisites:

防火墙和selinux已关闭；Mysql主从同步已经配置完成；

Install Proxysql: Install

[[email protected] ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm[[email protected] ~]# yum install proxysql-1.4.8-1-centos7.x86_64.rpm -y# proxysql需要依赖一些perl库，所以使用yum安装# 依赖：    perl-DBD-MySQL    perl-DBI#安装生成的文件：[[email protected] ~]# rpm -ql proxysql/etc/init.d/proxysql    #启动脚本/etc/proxysql.cnf       #配置文件，仅在第一次(/var/lib/proxysql/proxysql.db文件不存在)启动时有效                        #启动后可以在proxysql管理端中通过修改数据库的方式修改配置并生效(官方推荐方式。)/usr/bin/proxysql       #主程序文件/usr/share/proxysql/tools/proxysql_galera_checker.sh/usr/share/proxysql/tools/proxysql_galera_writer.pl

Configuration file Description:

[[email protected] ~]# egrep-v "^#|^$"/etc/proxysql.cnfdatadir= "/var/lib/proxysql" admin_variables={admin_ Credentials= "Admin:admin" # defines the user name and password for the connection management port mysql_ifaces= "0.0.0.0:6032" # defines management port 6032; used to connect Proxysql management database, modify PR Settings for the Oxysql service and Routing policy}mysql_variables={threads=4 # define how many threads to open per forwarding port max_connections=2048 defaul               T_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces= "0.0.0.0:6033" # defines the forwarding port 6033, which is used to connect the backend MySQL instance, acting as proxy forwarding; default_schema= "Information_schema" stacksize=1048576 server _version= "5.7.22" # Sets the version number of the backend MySQL instance, only plays the role of comment connect_timeout_server=3000 monitor_username= "Monit or "monitor_password=" monitor "monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=100 XX 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= () [[email protected] ~]#

Start Proxysql:

[[email protected] ~]# /etc/init.d/proxysql start   # 启动Starting ProxySQL: DONE![[email protected] ~]# ss -lntup |grep proxysql # 查看：proxysql客户端监听在6033端口上，管理端监听6032端口；tcp    LISTEN     0      128                    *:6032                  *:*      users:(("proxysql",1322,20))tcp    LISTEN     0      128                    *:6033                  *:*      users:(("proxysql",1322,19))tcp    LISTEN     0      128                    *:6033                  *:*      users:(("proxysql",1322,18))tcp    LISTEN     0      128                    *:6033                  *:*      users:(("proxysql",1322,16))tcp    LISTEN     0      128                    *:6033                  *:*      users:(("proxysql",1322,15))# 可以看到转发端口的6033开启了4个线程，线程数由全局变量"threads"控制，受cpu物理核心数的影响（每个端口下的线程数<=cpu物理核心数）

Set up MySQL add Proxysql can be added or deleted to change the account:

mysql> GRANT ALL ON *.* TO ‘proxysql‘@‘192.168.1.%‘ IDENTIFIED BY ‘pwproxysql‘;

Connect the Proxysql management side for configuration:

[[email protected] ~]# yum install mysql-y # installation of MySQL client; dependency: mysql-libs[[email protected] ~]# export MYSQ  L_ps1= "(\[email protected]\h:\p) [\d]>" [[email protected] ~]# mysql-uadmin-padmin-h127.0.0.1-p6032 # The default user name password is admin.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 1Server version:5.5.30 (proxysql Admin Module) Copyright (c) +, Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. ([email protected]:6032) [(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) Library Description: Main memory configuration database, table storage backend DB instance, user authentication, routing rules and other information. The table name begins with Runtime_, proxysql the currently running configuration, cannot be modified by DML statements, only the corresponding table that does not start with runtime_ (in memory), and load makes it effective, save to save it to the hard disk for the next reboot to load. Disk is a persistent to hard disk configuration, SQLite data file. Stats is the statistics of Proxysql run crawl, including the number of executions, traffic, processlist, query type summary/execution time, etc. to the backend commands. The Monitor library stores the information collected by the Monitor module, primarily for health/latency checks of the backend db. # Use the INSERT statement to add the MySQL host to the Mysql_servers table, where: hostgroup_id 1 represents the Write group, and 2 for the read group. ([email protected]:6032) [(None)]> insert into mysql_servers (hostgroup_id,hostname,port,weight,comment) VALUES (1, ' 192.168.20.68 ', 3306, 1, ' Write Group '); Query OK, 1 row Affected (0.00 sec) ([email protected]:6032) [(none)]> insert into mysql_servers (hostgroup_id, Hostname,port,weight,comment) VALUES (2, ' 192.168.20.69 ', 3306,1, ' Read Group '); Query OK, 1 row Affected (0.00 sec) ([email protected]:6032) [(None)]> select * from mysql_servers;+-------------- +---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----- -----------+-------------+| hostgroup_id | hostname | Port | Status | Weight | Compression | max_connections | Max_replication_lag | Use_ssl | Max_latency_ms | Comment |+--------------+---------------+------+--------+--------+-------------+-----------------+-------------- -------+---------+----------------+-------------+| 1 | 192.168.20.68 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group | | 2 | 192.168.20.69 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |+--------------+---------------+------+--------+--------+-------------+-----------------+----------- ----------+---------+----------------+-------------+2 rows in Set (0.00 sec) # Add the account you just created in the Mysql_users table of the Proxysql host The Proxysql client needs to use this account to access the database. # default_hostgroup Default group is set to write group, that is, 1;# the default group's database is accessed when the routing rule for read-write separation is not met ([email protected]:6032) [(none)]> INSERT INTO Mysql_users (username,password,default_hostgroup,transaction_persistent) VALUES (' Proxysql ', ' Pwproxysql ', 1, 1); Query OK, 1 row Affected (0.00 sec) ([email protected]:6032) [(none)]> 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) ([email  protected]:6032) [(None)]> select * from Mysql_users \g*************************** 1.      Row *************************** Username:proxysql # backend MySQL instance username password:pwproxysql # The password for the backend MySQL instance active:1 # active=1 indicates that the user is in effect and 0 is not in effect use_ssl:0 Defau Lt_hostgroup:1 # User Default login to which hostgroup_id under the instance Default_schema:nuLL # User Default login to backend MySQL instance when connected to the database, this place is null, then the global variable Mysql-default_schema, default is Information_schema Schema_locke d:0 Transaction_persistent:1 # If set to 1, after connecting to the Proxysql session, if the transaction on a hostgroup, then the subsequent SQL will continue to maintain on this hostgroup, not LUN will Match the other routing rules until the end of the transaction. Although the default is 0 fast_forward:0 # ignores the query rewrite/cache layer, the user's request is transmitted directly to the backend db. The equivalent of only using its connection pooling function, generally not, routing rules. * Just backend:1 frontend:1 max_connections:10000 # Maximum number of connections allowed for this user 1 row in Set (0.00 sec)

Add a health monitoring account:

mysql端添加proxysql只能查的账号:    mysql> GRANT SELECT ON *.* TO ‘monitor‘@‘192.168.1.%‘ IDENTIFIED BY ‘monitor‘;proxysql端修改变量设置健康检测的账号：    ([email protected]:6032) [(none)]> set mysql-monitor_username=‘monitor‘;    Query OK, 1 row affected (0.00 sec)    ([email protected]:6032) [(none)]> set mysql-monitor_password=‘monitor‘;    Query OK, 1 row affected (0.00 sec)

To add a read-write separated routing rule:

# route The SELECT statement to the hostgroup_id=2 group (that is, the read group) # but the SELECT * from TB for UPDATE statement is a modification of the data, so it needs to be defined separately and routed to the Hostgroup_id=1 group ( That is, write groups) # Other groups that are not matched to the rule will be routed to the user's default group (Default_hostgroup in the Mysql_users table) ([email protected]:6032) [(None)]> Insert into Mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES ("^select.*for" update$ ', 1, 1); Query OK, 1 row Affected (0.00 sec) ([email protected]:6032) [(none)]> insert into Mysql_query_rules (rule_id, active,match_digest,destination_hostgroup,apply) VALUES (2,1, ' ^select ', 2, 1); Query OK, 1 row Affected (0.00 sec) ([email protected]:6032) [(None)]> 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)

Load the data we have just modified into runtime (refer to Proxysql's multilayer configuration structure):

# load进runtime，是配置生效([email protected]:6032) [(none)]> load mysql users to runtime;([email protected]:6032) [(none)]> load mysql servers to runtime;([email protected]:6032) [(none)]> load mysql query rules to runtime;([email protected]:6032) [(none)]> load mysql variables to runtime;([email protected]:6032) [(none)]> load admin variables to runtime;# save到磁盘(/var/lib/proxysql/proxysql.db)中，永久保存配置([email protected]:6032) [(none)]> save mysql users to disk;([email protected]:6032) [(none)]> save mysql servers to disk;([email protected]:6032) [(none)]> save mysql query rules to disk;([email protected]:6032) [(none)]> save mysql variables to disk;([email protected]:6032) [(none)]> save admin variables to disk;([email protected]:6032) [(none)]> load mysql users to runtime;

Test read-Write detach connection Proxysql client:

#登录用户是刚才我们在mysql_user表中创建的用户，端口为6033[[email protected] ~]#mysql -uproxysql -ppwproxysql -h127.0.0.1 -P6033Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server 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 || test               |+--------------------+4 rows in set (0.00 sec)MySQL [(none)]>

Try to modify the database and query:

#创建两个数据库和查个表。MySQL [(none)]> create database bigboss;Query OK, 1 row affected (0.01 sec)MySQL [(none)]> create database weijinyun;Query OK, 1 row affected (0.00 sec)MySQL [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || bigboss            || mysql              || performance_schema || test               || weijinyun          |+--------------------+6 rows in set (0.01 sec)MySQL [(none)]> select user,host from mysql.user;+-------------+---------------+| user        | host          |+-------------+---------------+| root        | 127.0.0.1     || monitor     | 192.168.100.% || proxysql    | 192.168.100.% || repliaction | 192.168.100.% || root        | ::1           ||             | centos7       || root        | centos7       ||             | localhost     || root        | localhost     |+-------------+---------------+9 rows in set (0.01 sec)

Verify that the read-write separation is successful:

# Proxysql has an audit-like feature that allows you to view the execution of various types of SQL. In the Proxysql management side execution: # from the following hostgroup and Digest_text values, all the write operations are routed to 1 groups, read operations are routed to 2 groups, # where 1 groups are write groups, 2 groups are read groups! ([email protected]:6032) [(None)]> 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 | Information_schema | Proxysql | 0x3ea85877510ac608 | SELECT * FROM Stats_mysql_query_digest | 2 | 1527233735 | 1527233782 | 4092 | 792| 3300 | | 1 | Information_schema | Proxysql | 0x594f2c744b698066 |       Select USER ()                   | 1 | 1527233378 | 1527233378 | 0 | 0| 0 | | 1 | Information_schema | Proxysql | 0x02033e45904d3df0 | Show Databases | 2 | 1527233202 | 1527233495 | 5950 | 1974| 3976 | | 1 | Information_schema | Proxysql | 0x226cd90d52a2ba0b |       SELECT @ @version_comment limit? | 2 | 1527233196 | 1527233378 | 0 | 0| 0 |+-----------+--------------------+----------+--------------------+----------------------------------------+-- ----------+------------+------------+----------+----------+----------+4 rows in Set (0.00 sec) ([email  protected]:6032) [(none)]># read and write separation success!!!

Reference:

http://seanlook.com/2017/04/10/mysql-proxysql-install-config/http://blog.51cto.com/bigboss/2103290

MySQL uses proxysql for read-write separation