MySQL uses proxysql for read-write separation

Proxysql Introduction:

• Proxysql is a high-performance MySQL middleware with a powerful rules engine.

• Detailed Chinese Introduction: Other people write, basically with the official no difference

• Official Document: https://github.com/sysown/proxysql/wiki/
• : https://github.com/sysown/proxysql/releases

Preparatory work

• The environment used in this article:
  • The system is CentOS7.4 and the firewall and SELinux are turned off
  • Proxysql version: proxysql-1.4.8-1-centos7.x86_64.rpm
  • MySQL version: Mariadb 5.5.56-2.el7
  • Proxysql Host ip:192.168.100.2
  • MySQL Main library ip:192.168.100.3
  • MySQL from library ip:192.168.100.4
• Prerequisites: MySQL master/slave has been configured for synchronization

Start installing Proxysql

• Install Proxysql:

wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpmyum install -y proxysql-1.4.8-1-centos7.x86_64.rpm#proxysql需要依赖一些perl库，所以使用yum安装#安装生成的文件：[[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

• Start Proxysql:

/etc/init.d/proxysql start#proxysql客户端监听在6033端口上，管理端监听6032端口[[email protected] ~]#/etc/init.d/proxysql startStarting ProxySQL: DONE![[email protected] ~]#ss -tanlState      Recv-Q Send-Q           Local Address:Port                          Peer Address:PortLISTEN     0      128                          *:6032                                     *:*LISTEN     0      128                          *:6033                                     *:*    LISTEN     0      128                          *:6033                                   *:*LISTEN     0      128                          *:6033                                     *:*LISTEN     0      128                          *:6033                                     *:*

• Connect the Proxysql management side for configuration:

mysql -uadmin -padmin -h127.0.0.1 -P6032#默认的管理端账号密码都是admin，登录进去之后可以修改变量进行修改账号密码[[email protected] ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server 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)]>

• To add a back-end MySQL host:

Insert into Mysql_servers (hostgroup_id,hostname,port,weight,comment) VALUES (1, ' 192.168.100.3 ', 3306,1, ' Write Group ' Insert into Mysql_servers (hostgroup_id,hostname,port,weight,comment) VALUES (2, ' 192.168.100.4 ', 3306,1, ' Read Group #使用insert语句添加主机到mysql_servers表中, where: hostgroup_id 1 represents a write group, and 2 represents a read group. MySQL [(None)]> select * from mysql_servers;+--------------+---------------+------+--------+--------+----------- --+-----------------+---------------------+--| hostgroup_id | hostname | Port | Status | Weight | Compression | max_connections | Max_replication_lag | u+--------------+---------------+------+--------+--------+-------------+-----------------+--------------------- +--| 1 | 192.168.100.3 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0| 2 | 192.168.100.4 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+--2 rows in Set (0.00 sec) 

• To add an account that can access the backend host:

  GRANT all on *. proxysql ' @ ' 192.168.100.% ' identified by ' 123456 '; #在后端mysql中添加可以增删改查的账号insert into Mysql_ Users (Username,password,default_hostgroup,transaction_persistent) VALUES (' Proxysql ', ' 123456 ', 1, 1); Add the account you just created in the Mysql_users table of the Proxysql host, Proxysql client needs to use this account to access the database. #default_hostgroup默认组设置为写组, that is, # when a read-write separated routing rule does not meet, the default group of databases is accessed MySQL [(none)]> INSERT into mysql_users (username, password,default_hostgroup,transaction_persistent) VALUES (' Proxysql ', ' 123456 ', 1, 1); Query OK, 1 row Affected (0.00 sec) MySQL [(None)]> select * from mysql_users\g*************************** 1.               Row *************************** Username:proxysql password:123456 active:1  use_ssl:0 default_hostgroup:1 default_schema:null schema_locked:0transaction_persistent: 1 fast_forward:0 backend:1 frontend:1 max_connections:100001 row in set (0. XX sec)  

• Add a health monitoring account:

GRANT SELECT ON *.* TO ‘monitor‘@‘192.168.100.%‘ IDENTIFIED BY ‘monitor‘;#在后端主机中添加可以访问数据库的账号,SELECT权限即可set mysql-monitor_username=‘monitor‘set mysql-monitor_password=‘monitor‘#在proxysql管理端中修改变量设置健康检测的账号

• To add a read-write separated routing rule:

Insert into Mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES ("^select.*for" Insert into Mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1, ' update$ ^select ', n '); #将select语句全部路由至hostgroup_id =2 Group (that is, read group) #但是select * from TB for update such statements are modified data, so they need to be defined separately, Route it to Hostgroup_id=1 Group (that is, write group) #其他没有被规则匹配到的组将会被路由至用户默认的组 (Default_hostgroup in mysql_users table) MySQL [(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 mysql users to runtime;load mysql servers to runtime;load mysql query rules to runtime;load mysql variables to runtime;load admin variables to runtime;#load进runtime，是配置生效save mysql users to disk;save mysql servers to disk;save mysql query rules to disk;save mysql variables to disk;save admin variables to disk;#save到磁盘(/var/lib/proxysql/proxysql.db)中，永久保存配置MySQL [(none)]> load mysql users to runtime;Query OK, 0 rows affected (0.00 sec)... ...... ...MySQL [(none)]> save admin variables to disk;Query OK, 31 rows affected (0.01 sec)

Test read/write separation

• To connect the Proxysql client:

mysql -uproxysql -p123456 -h127.0.0.1 -P6033#登录用户是刚才我们在mysql_user表中创建的用户，端口为6033[[email protected] ~]#mysql -uproxysql -p123456 -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:

Create DATABASE bigboss;create database Weijinyun;select user,host from Mysql.user; #创建两个数据库和查个表. 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) 

• verifies that the read-write separation was successful:

#proxysql有个类似审计的功能, you can view the execution of various types of SQL. Execute on proxysql management: SELECT * from Stats_mysql_query_digest; #从下面的hostgroup和digest_text值来看, all writes are routed to 1 groups, and read operations are routed to 2 groups, # 1 groups are write groups, 2 groups are read groups! #读写分离成功!!! MySQL [(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 |+-----------+--------------------+----------+--------------------+----------------------------------+ ------------+------------+------------+----------+----------+----------+| 1 | Information_schema | Proxysql | 0XA6212D89D814BAC5 | Create Database Weijinyun | 1 | 1523658457 | 1523658457 | 1244 | 1244 | 1244 | | 2 | Information_schema | Proxysql | 0x0f02b330c823d739 | Select User,hosT from Mysql.user | 1 | 1523658520 | 1523658520 | 12538 | 12538 | 12538 | | 1 | Information_schema | Proxysql | 0x02033e45904d3df0 | Show Databases | 5 | 1523658103 | 1523658486 | 24852 | 1263 | 17592 | | 1 | Information_schema | Proxysql | 0xa175fd2982ec6396 | Create Database Bigboss | 1 | 1523658437 | 1523658437 | 1833 | 1833 | 1833 | | 1 | Information_schema | Proxysql | 0x226cd90d52a2ba0b | SELECT @ @version_comment limit? | 3 | 1523658098 | 1523658473 | 0 | 0 | 0 |+-----------+--------------------+----------+--------------------+----------------------------------+-------- ----+------------+------------+----------+----------+----------+6 rows in Set (0.00 sec)

MySQL uses proxysql for read-write separation