15.Proxysql读写分离搭建

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 |
+---------+