操作系统 redhat 6.9

数据库 mysql 5.7.26

中间件 proxysql 1.4.14

已配置mysql一主一从，具体见下

编号服务器角色ip地址 端口

1 proxysql10.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]#