准备条件

操作系统  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         33063         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; +--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 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 -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 北京云和恩墨有限公司数据库技术顾问 北京神州新桥科技有限公司数据库咨询顾问        参与过的重点项目 四川达州商业银行核心系统建设项目 中国联通数据库系统运维项目 贵州移动数据库系统运维项目 京东方数据库运维项目 拉卡拉数据库系统运维项目 新疆汇和银行核心系统建设项目 新疆银行新核心系统建设项目 邢台银行影印平台数据库建设项目 保定银行核心系统建设项目 重庆富民银行数据中心建设项目 吉林亿联银行数据中心建设项目 云南工行数据库建设项目 江西裕民银行数据中心建设项目 湖北发改委数据库运维项目 武汉众邦银行数据中心建设项目 辽宁振兴银行数据库运维项目

总结

联系方式  提供oracle及mysql的技术服务，安装部署，性能优化，故障诊断，教育培训。 微信公众号 微信