环境准备
master 192.168.0.141
slave1 192.168.0.137
slave2 192.168.0.138
proxysql 192.168.0.186
从库设置只读
在实现主从复制时从节点必须要设置read_only,这是ProxySQL区分是用来作为读服务器还是写服务器的依据
mysql> set global read_only=1;
mysql创建服务账号和monitor账号
create user 'proxysql'@'192.168.0.%' identified by 'proxysql';
GRANT ALL ON *.* TO 'proxysql'@'192.168.0.%';
create user 'monitor'@'192.168.0.%' identified by 'monitor';
GRANT SELECT ON *.* TO 'monitor'@'192.168.0.%';
proxysql yum源
vim /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/latest
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
proxysql安装
yum install proxysql
proxysql配置文件说明
rpm -ql proxysql
/etc/init.d/proxysql #管理脚本
/etc/logrotate.d/proxysql
/etc/proxysql.cnf #配置文件
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
修改proxysql端口
vim /etc/proxysql.cnf
...
interfaces="0.0.0.0:3306" #6033改为3306
...
启动
systemctl start proxysql.service
登录到proxysql管理端
mysql -uadmin -padmin -h127.0.0.1 -P6032
查看数据库信息
mysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
main:默认数据库,存放用户验证、路由规则等信息。我们要做的配置都是针对这个库的
disk:持久化到硬盘的配置
stats:proxysql运行抓取的统计信息,如各命令的执行次数、查询执行时间等
monitor:monitor模块收集的信息,db的健康情况、各种检查等
查看配置表
mysql> show tables;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_galera_hostgroups |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
24 rows in set (0.01 sec)
global_variables:各种变量,包括监听的端口、管理账号、是否禁用monitor等,详情可以参考官方文档
mysql_:mysql开头的表就是我们配置要操作的表,具体都是干什么的还是看官方文档吧,介绍的很细,后面我会针对读写分离的配置做介绍
runtime_:runtime开头的表是运行时读的表,不能通过DML语句修改,我们针对mysql开头的表做完配置修改之后,要执行load mysql xxx to runtime以将对应的配置加载到运行时环境
注意:当执行完load语句将配置加载到运行时环境后,还要执行save mysql xxx to disk将配置存到硬盘上,以便下次重启时加载,如果忘记执行,当重启时本次修改的配置会丢失
添加DB实例
一共有三个节点(一个master两个slave),要进行读写分离,将master设为写节点,两个slave设为读节点
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.0.141',3306,1,100,10,'write group');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(2000,'192.168.0.137',3306,1,100,10,'read group');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(2000,'192.168.0.138',3306,1,100,10,'read group');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1000 | 192.168.0.141 | 3306 | 0 | ONLINE | 1 | 0 | 100 | 10 | 0 | 0 | write group |
| 2000 | 192.168.0.137 | 3306 | 0 | ONLINE | 1 | 0 | 100 | 10 | 0 | 0 | read group |
| 2000 | 192.168.0.138 | 3306 | 0 | ONLINE | 1 | 0 | 100 | 10 | 0 | 0 | read group |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
3 rows in set (0.00 sec)
hostgroup_id:一个角色一个id,该表的主键是hostgroup_id+hostname+port
hostname:db实例IP
port:db实例端口
weight:权重,如果有多个相同角色的实例,会优先选择权重高的
status:状态
-ONLINE 正常
-SHUNNED 临时被剔除
-OFFLINE_SOFT 软离线状态,不再接受新的连接,已建立的连接会等待
-OFFLINE_HARD 离线,不接收新连接, 已建立的连接也会强制断开(宕机或者网络不可用)
max_connections:最大连接数
max_replication_lag:允许的最大延迟
添加服务账号
mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1000);
Query OK, 1 row affected (0.01 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 | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------+
| proxysql | proxysql | 1 | 0 | 1000 | NULL | 0 | 1 | 0 | 1 | 1 | 10000
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------+
1 row 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> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1000,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2000,1);
Query OK, 1 row affected (0.00 sec)
mysql> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | 1000 | 1 |
| 2 | 1 | ^SELECT | 2000 | 1 |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)
完善配置
我们的mysql集群是基于MHA的,master挂掉之后,slave会提升为新的master,这个时候我们希望proxysql的规则自动变更,在mysql_servers中增加一条记录,将新的master的hostname和port添加到写的hostgroup中
proxysql是支撑这种配置的,根据mysql_replication_hostgroups中的数据,proxysql通过检测到各server的read_only值来自动为server设置hostgroup_id
mysql> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values(1000,2000,'Reading and Writing Separation');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+--------------------------------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+--------------------------------+
| 1000 | 2000 | read_only | Reading and Writing Separation |
+------------------+------------------+------------+--------------------------------+
1 row in set (0.00 sec)
将配置加载到运行时
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
将配置保存到硬盘
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.15 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.33 sec)
mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.36 sec)
mysql> save mysql variables to disk;
Query OK, 96 rows affected (0.09 sec)
mysql> save admin variables to disk;
Query OK, 32 rows affected (0.09 sec)
到此读写分离的相关配置就大功告成了!之后我们的应用可以通过配置中配置proxysql用户连接proxysql服务操作我们的mysql集群
mysql -uproxysql -pproxysql -h 192.168.0.186
mysql -uproxysql -pproxysql -h192.168.0.186 -e "select @@server_id;"
mysql -uproxysql -pproxysql -h192.168.0.186 -e "begin;insert into test.t1 value(3);SELECT @@server_id;commit;"
proxysql应用(wordpress)相关操作
MySQL(主库)创建库,用户,并授权
create database wordpress charset utf8;
create user 'wordpress'@'192.168.0.%' identified by '123456';
GRANT ALL ON wordpress.* TO 'wordpress'@'192.168.0.%';
proxysql添加MySQL用户
mysql -uadmin -padmin -h127.0.0.1 -P6032
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('wordpress','123456',1000);
select * from mysql_users;
load mysql users to runtime;
save mysql users to disk;
mysql -uwordpress -p123456 -h192.168.0.186