Proxysql读写分离搭建
1）环境准备
这里分别准备四台虚拟机，192.168.10.129(server_id:1293306) 192.168.10.130(server_id:1303306) 192.168.10.131(server_id:1313306) 192.168.10.132，
192.168.10.129~131 这三台都装好mysql服务端，且配置好主从复制，我这里主库是129，其余两个是从库，192.168.10.132这台装proxysql软件，注意装proxysql软件的这台虚拟机上一定要装上mysql客户端。
2）开始
2.1 连接proxysql，管理节点端口号是6032，6033是访问后端数据库端口，这里先进管理节点中。
[root@db04 ~]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin> ' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ProxySQL Admin>
2.2 在proxysql中创建监控用户
ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.00 sec) ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec)
load mysql global variables to runtime
save mysql global variables to disk
这里在proxysql中创建了一个监控用户monitor,密码也是monitor,该监控用户主要负责远程连接mysql服务器或者其实时状态信息。
2.3 在mysql主库中(129)中创建该用户接受来自proxysql的监控
create user monitor@'%' identified by 'monitor'; grant all on *.* to monitor@'%'; # 这里权限可以给一个replica client flush privileges;
2.4 在proxysql中添加组管理信息(mysql_replication_hostgroups)
这里主要是对proxysql中的mysql_replication_hostgroups表中添加组管理，该组主要是用来管理mysql servers里面的节点，可将里面的节点进行分类(读写?),先看看这个表的结构
ProxySQL Admin> show create table mysql_replication_hostgroups\G; *************************** 1. row *************************** table: mysql_replication_hostgroups Create Table: CREATE TABLE mysql_replication_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0), check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only', comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup)) 1 row in set (0.00 sec)
从上面可以看出有写主机组、读主机组、还有一个check_type字段，该字段主要用来进行判断主机是读还是写，这里我添加一行数据,
ProxySQL Admin>insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type) values (10,20,'read_only') ProxySQL Admin> select * from mysql_replication_hostgroups; +------------------+------------------+------------+---------+ | writer_hostgroup | reader_hostgroup | check_type | comment | +------------------+------------------+------------+---------+ | 10 | 20 | read_only | proxy | +------------------+------------------+------------+---------+ 1 row in set (0.00 sec)
load mysql replication hostgroups to runtime;
save mysql replication hostgroups to disk;
2.5 添加主机信息(mysql_servers)
mysql_servers表是来用管理mysql服务端节点，
ProxySQL Admin> show create table mysql_servers\G; *************************** 1. row *************************** table: mysql_servers Create Table: CREATE TABLE mysql_servers ( hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0, hostname VARCHAR NOT NULL, port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306, gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0, status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE', weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1, compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0, max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000, max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0, use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0, max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0, comment VARCHAR NOT NULL DEFAULT '', PRIMARY KEY (hostgroup_id, hostname, port) ) 1 row in set (0.00 sec)
这时就可以把mysql服务端节点信息都写入到这个表中
ProxySQL Admin> select * from mysql_servers; +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.10.129 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.10.130 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.10.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.01 sec)
load mysql servers to runtime;
save mysql servers to disk;
2.6 添加读写分离规则(mysql_query_rules)
proxysql支持正则，这里添加两条匹配规则, 1) 表示像select * from xxx for update这种语句都会分到到写组，2）表示像select这种语句都会被分配到读组。
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',10,1); insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',20,1)
load mysql query rules to runtime;
save mysql query rules to disk;
2.7 添加应用root(在proxysql中) 这里就是mysql users表
ProxySQL Admin> select * from mysql_users; +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ | root | 123 | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ 1 row in set (0.00 sec)
这里在proxysql添加了root用户，密码是123，然后该用户在mysql服务器中也要一定存在才可以被访问。
3.测试读写分离
[root@db04 ~]# mysql -uroot -p123 -h 192.168.10.132 -P 6033 -e "select @@server_id" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 1313306 | +-------------+ [root@db04 ~]# mysql -uroot -p123 -h 192.168.10.132 -P 6033 -e "select @@server_id" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 1303306 | +-------------+ [root@db04 ~]# mysql -uroot -p123 -h 192.168.10.132 -P 6033 -e "begin;select @@server_id commit;" mysql: [Warning] Using a password on the command line interface can be insecure. +---------+ | commit | +---------+ | 1293306 | +---------+
官方文档：https://proxysql.com/documentation/