准备条件

操作系统 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 -h127.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;

+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

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

+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

| 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 -h127.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 -h127.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]#

本人的职业经历

中国普天数据库技术顾问

北京科蓝软件系统数据库DBA

北京云和恩墨有限公司数据库技术顾问

北京神州新桥科技有限公司数据库咨询顾问

参与过的重点项目

四川达州商业银行核心系统建设项目

中国联通数据库系统运维项目

贵州移动数据库系统运维项目

京东方数据库运维项目

拉卡拉数据库系统运维项目

新疆汇和银行核心系统建设项目

新疆银行新核心系统建设项目

邢台银行影印平台数据库建设项目

保定银行核心系统建设项目

重庆富民银行数据中心建设项目

吉林亿联银行数据中心建设项目

云南工行数据库建设项目

江西裕民银行数据中心建设项目

湖北发改委数据库运维项目

武汉众邦银行数据中心建设项目

辽宁振兴银行数据库运维项目

总结

配置mysql主从读写分离，其实就是在proxysql中间件中，配置不同的主机组，不同的主机组各自对应不同的数据库节点，这个不同的主机呢又是对应各自不同的数据库用户，通过不同的数据库访问中间件proxysql实现读写分离
配置主机组是对应proxysql中间件表 mysql_replication_hostgroups
配置mysql节点与主机组的隶属关系是对应proxysql中间件表 mysql_servers
配置数据库用户与主机组的隶属关系是对应proxysql中间件表 mysql_users

联系方式

提供oracle及mysql的技术服务，安装部署，性能优化，故障诊断，教育培训。

微信公众号

微信

ITPub博客

使用proxysql 1.4.14中间件实现mysql 5.7.26主从的读写分离

准备条件

数据库读写分离整体架构

配置数据库读写分离

本人的职业经历

总结

最新文章