环境说明:
IP | 角色 | 应用 | 系统平台 |
---|---|---|---|
192.168.92.131 | 读写分离解析主机 | proxysql | rhel8 |
192.168.92.129 | master | mysql | rhel8 |
192.168.92.130 | slave | mysql | rhel8 |
准备工作:
- 关闭防火墙
- 关闭SELINUX
- 安装mysql并配置主从
安装mysql并配置主从
//授权(master)[root@master ~]# mysql -uroot -p123456 mysql> grant replication slave on *.* to 'repl'@'192.168.92.130' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)//连接测试(slave)[root@slave ~]# mysql -urepl -p123456 -h192.168.92.129mysql: [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 7Server version: 5.7.31 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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. mysql>//更改配置文件(master)[root@master ~]# vi /etc/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/opt/mydata socket=/tmp/mysql.sock port=3306pid-file=/opt/mydata/mysql.pid user=mysql skip-name-resolve server-id = 10log-bin = mysql_bin [root@master ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!//更改配置文件(slave)[root@slave ~]# vi /etc/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/opt/mydata socket=/tmp/mysql.sock port=3306pid-file=/opt/mydata/mysql.pid user=mysql skip-name-resolve server-id = 20relay-log = myrelay [root@slave ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!//设置改变属主(slave)mysql> change master to -> master_host='192.168.92.129', -> master_user='repl', -> master_password='123456', -> master_log_file='mysql_bin.000001', -> master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G 。。。。。。 Slave_IO_Running: Yes Slave_SQL_Running: Yes 。。。。。。
配置ProxySQL
mysql主库添加proxysql可以增删改查的账号
//授权(master)mysql> grant all on *.* to 'proxysql'@'192.168.92.131' ideentified by 'porxysql'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)//连接测试(proxy SQL)[root@proxysql ~]# mysql -uproxysql -pproxysql -h192.168.92.129ERROR 1045 (28000): Access denied for user 'proxysql'@'192.168.92.131' (using password: YES) [root@proxysql ~]# mysql -uproxysql -pporxysql -h192.168.92.129Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 9Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> exit Bye [root@proxysql ~]# mysql -uproxysql -pporxysql -h192.168.92.130Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 5Server version: 5.7.31 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
登录proxysql管理端
[root@proxysql ~]# export MYSQL_PS1="(\u@\h:\p) [\d]> "[root@proxysql ~]# mysql -uadmin -padmin -P7777 -h127.0.0.1Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (admin@127.0.0.1:7777) [(none)]>
proxysql管理端添加后端连接mysql主从数据库的配置
(admin@127.0.0.1:7777) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(100,'192.168.92.129',3306,1,'Write Group'); Query OK, 1 row affected (0.001 sec) (admin@127.0.0.1:7777) [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(200,'192.168.92.130',3306,1,'Read Group'); Query OK, 1 row affected (0.000 sec) (admin@127.0.0.1:7777) [(none)]> select * from mysql_servers;+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+ | 100 | 192.168.92.129 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group | | 200 | 192.168.92.130 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+2 rows in set (0.000 sec)//修改后,需要加载到RUNTIME,并保存到disk(admin@127.0.0.1:7777) [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.006 sec) (admin@127.0.0.1:7777) [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.071 sec)
在 proxysql 主机的 mysql_users 表中添加刚才在 master 上创建的账号 proxysql,proxysql 客户端需要使用这个账号来访问数据库
default_hostgroup 默认组设置为写组,也就是100;
当读写分离的路由规则不符合时,会访问默认组的数据库;
(admin@127.0.0.1:7777) [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('proxysql','proxysql',100,1); Query OK, 1 row affected (0.000 sec) (admin@127.0.0.1:7777) [(none)]> select * from mysql_users \G*************************** 1. row *************************** username: proxysql 用户名 password: proxysql 密码 active: 1 是否启用账户 use_ssl: 0 default_hostgroup: 100 默认主 default_schema: NULL schema_locked: 0transaction_persistent: 1 fast_forward: 0 backend: 1 frontend: 1 max_connections: 10000 最大连接数 attributes: comment: 1 row in set (0.000 sec)//保存到dixk(admin@127.0.0.1:7777) [(none)]> load mysql users to runtime; Query OK, 0 rows affected (0.001 sec) (admin@127.0.0.1:7777) [(none)]> save mysql users to disk; Query OK, 0 rows affected (0.010 sec)
添加健康检测的帐号
在mysql的 master 端添加属于proxysql的只读账号
mysql> grant select on *.* to 'monitor'@'192.168.92.%' identified by 'monitor'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
在proxysql主机端修改变量设置健康检测的账号
(admin@127.0.0.1:7777) [(none)]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.000 sec) (admin@127.0.0.1:7777) [(none)]> set mysql-monitor_password='monitor'; Query OK, 1 row affected (0.000 sec) (admin@127.0.0.1:7777) [(none)]> load mysql variables to runtime; Query OK, 0 rows affected (0.002 sec) (admin@127.0.0.1:7777) [(none)]> save mysql variables to disk; Query OK, 140 rows affected (0.005 sec)
添加读写分离的路由规则
需求:
- 将 select 查询语句全部路由至 hostgroup_id=2 的组(也就是读组)
- 但是 select * from tb for update 这样的语句是会修改数据的,所以需要单独定义,将它路由至 hostgroup_id=1 的组(也就是写组)
- 其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users 表中的 default_hostgroup)
(admin@127.0.0.1:7777) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(100,1,'^SELECT.*FOR UPDATE$',100,1); Query OK, 1 row affected (0.001 sec) (admin@127.0.0.1:7777) [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(200,1,'^SELECT',200,1); Query OK, 1 row affected (0.000 sec) (admin@127.0.0.1:7777) [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;+---------+--------+----------------------+-----------------------+-------+ | rule_id | active | match_digest | destination_hostgroup | apply | +---------+--------+----------------------+-----------------------+-------+ | 100 | 1 | ^SELECT.*FOR UPDATE$ | 100 | 1 | | 200 | 1 | ^SELECT | 200 | 1 | +---------+--------+----------------------+-----------------------+-------+2 rows in set (0.000 sec) (admin@127.0.0.1:7777) [(none)]> load mysql query rules to runtime; Query OK, 0 rows affected (0.001 sec) (admin@127.0.0.1:7777) [(none)]> save mysql query rules to disk; Query OK, 0 rows affected (0.034 sec)
验证读写分离
登录 proxysql 客户端
登录用户是刚才我们在 mysql_user 表中创建的用户,端口为6033
[root@proxysql ~]# mysql -uproxysql -pproxysql -h127.0.0.1 -P6033 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 5Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (proxysql@127.0.0.1:6033) [(none)]> show databases;+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+5 rows in set (0.006 sec)
尝试修改数据库和查询
创建2个数据库并查询一下表
(proxysql@127.0.0.1:6033) [(none)]> create database jinxinruntian; Query OK, 1 row affected (0.007 sec) (proxysql@127.0.0.1:6033) [(none)]> create database wangqing; Query OK, 1 row affected (0.004 sec) (proxysql@127.0.0.1:6033) [(none)]> select user,host from mysql.user;+---------------+----------------+ | user | host | +---------------+----------------+ | monitor | 192.168.92.% | | repl | 192.168.92.130 | | proxysql | 192.168.92.131 | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+----------------+6 rows in set (0.003 sec)
验证读写分离是否成功
proxysql有个类似审计的功能,可以查看各类SQL的执行情况,其需要在proxysql管理端执行
(admin@127.0.0.1:7777) [(none)]> select * from stats_mysql_query_digest\G*************************** 1. row *************************** hostgroup: 200 schemaname: information_schema username: proxysql client_address: digest: 0x0F02B330C823D739 digest_text: select user,host from mysql.user count_star: 1 first_seen: 1623022307 last_seen: 1623022307 sum_time: 2953 min_time: 2953 max_time: 2953sum_rows_affected: 0 sum_rows_sent: 6*************************** 2. row *************************** hostgroup: 100 schemaname: information_schema username: proxysql client_address: digest: 0xCE6BB7764BA98E04 digest_text: create database jinxinruntian count_star: 1 first_seen: 1623022295 last_seen: 1623022295 sum_time: 6590 min_time: 6590 max_time: 6590sum_rows_affected: 1 sum_rows_sent: 0*************************** 3. row *************************** hostgroup: 100 schemaname: none username: proxysql client_address: digest: 0x99531AEFF718C501 digest_text: show tables count_star: 1 first_seen: 1623022281 last_seen: 1623022281 sum_time: 28595 min_time: 28595 max_time: 28595sum_rows_affected: 0 sum_rows_sent: 0*************************** 4. row *************************** hostgroup: 200 schemaname: school username: proxysql client_address: digest: 0x620B328FE9D6D71A digest_text: SELECT DATABASE() count_star: 1 first_seen: 1623022281 last_seen: 1623022281 sum_time: 2763 min_time: 2763 max_time: 2763sum_rows_affected: 0 sum_rows_sent: 1
从上面的 hostgroup 和 digest_text 值来看,所有的写操作都被路由至100组,读操作都被路由至200组,其中100组为写组,200组为读组!