如何使用proxysql 1.4.14中间件实现mysql 5.7.26主从的读写分离
这篇文章将为大家详细讲解有关如何使用proxysql 1.4.14中间件实现mysql 5.7.26主从的读写分离，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
准备条件
操作系统 redhat 6.9
数据库 mysql 5.7.26
中间件 proxysql 1.4.14
已配置mysql一主一从，具体见下
数据库读写分离整体架构
编号 服务器角色 ip地址 端口
1 proxysql 10.0.0.13 6032,6033(注:6032是proxysql的管理端口，6033是proxysql对外服务的端口）
2 mysql主库 10.0.0.11 3306
3 mysql从库 10.0.0.12 3306
配置数据库读写分离
1,登陆中间件proxysql
[root@mysqlclient ~]# mysql -uadmin -padmin -h227.0.0.1 -P6032
2,配置用于读写分离的不同的主机组，10用于mysql主库的主机组，20用于mysql从库的主机组
mysql> insert into mysql_replication_hostgroups values(10,20,'use for msyql primary replication');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+-----------------------------------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+-----------------------------------+
| 10 | 20 | use for msyql primary replication |
+------------------+------------------+-----------------------------------+
1 row in set (0.00 sec)
3,登陆mysql主库创建用于监控mysql主从库read_only是否只读的数据库用户monitor
mysql>grant replication client on *.* to 'monitor'@'10.0.0.13' identified by 'monitor';
Query OK, 0 rows affected (0.02 sec)
4,登陆mysql从库确认read_only=on只读
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
5,登陆中间件proxysql配置用于监控mysql的数据库用户及密码
（注：对应上述第3步配置的数据库用户）
mysql> select * from main.global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+----------------+
| variable_name | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor |
| mysql-monitor_username | monitor |
+------------------------+----------------+
2 rows in set (0.00 sec)
--如果配置的数据库用户密码不对，根据实际情况进行调整
mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)
--持久化
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 97 rows affected (0.02 sec)
6，配置mysql主从节点与主机组的对应关系
（注：不同的mysql节点对应不同的主机组，实现读写分离）
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.0.0.11',3306);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'10.0.0.12',3306);
Query OK, 1 row affected (0.00 sec)
持久化
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 10.0.0.11 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 10.0.0.12 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
7,登陆mysql主库创建用于读写分离的数据库用户
--读写数据库用户
mysql> grant all on *.* to rwuser@'10.0.0.11' identified by 'system';
Query OK, 0 rows affected (0.02 sec)
--只读数据库用户
mysql> grant all on *.* to rouser@'10.0.0.12' identified by 'system';
Query OK, 0 rows affected (0.01 sec)
8,登陆中间件proxysql配置数据库用户与主机组的对应关系，即不同的数据库用户可以导流到不同的mysql主从的节点上
mysql> insert into mysql_users(username,password,default_hostgroup) values('rwuser','system',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_users(username,password,default_hostgroup) values('rouser','system',20);
Query OK, 1 row affected (0.00 sec)
--持久化
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| rwuser | system | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
| rouser | system | 1 | 0 | 20 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)
9，客户端使用不同的数据库用户登陆中间件，实现mysql主从库的读写分离
（注：rwuser数据库用户访问mysql主库，rouser数据库用户访问mysql从库）
[root@mysqlclient proxydir]# mysql -urwuser -psystem -h227.0.0.1 -P6033 -e 'select @@server_id'
Warning: Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
[root@mysqlclient proxydir]# mysql -urouser -psystem -h227.0.0.1 -P6033 -e 'select @@server_id'
Warning: Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
[root@mysqlclient proxydir]#
关于"如何使用proxysql 1.4.14中间件实现mysql 5.7.26主从的读写分离"这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
