# proxysql - Dockerfile
https://abc.htmltoo.com/thread-45917.htm
docker run -d --net=host --name proxysql --restart=always -v /data/docker/mysql/proxysql/proxysql.cnf:/etc/proxysql.cnf -v /etc/localtime:/etc/localtime:ro -v /data/file:/data/file proxysql/proxysql:latest
-v /data/docker/mysql/proxysql/proxysql.db:/var/lib/proxysql/proxysql.db
-如果已存在"proxysql.db"文件(在/var/lib/proxysql目录下)，则ProxySQL服务只有在第一次启动时才会去读取proxysql.cnf文件并解析;后面启动会就不会读取proxysql.cnf文件了
-所以你先要删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启/启动proxysql服务。
# mysql服务器: 创建 ProxySQL 的监控账户和对外访问账户
grant all on *.* to 'monitor'@'%' identified by 'wdqdmm@p';
grant all on *.* to 'proxysql'@'%' identified by 'wdqdmm@p';
---连接proxysql管理端进行配置
mysql -A -uadmin -pwdqdmm@p -h127.0.0.1 -P6032 --prompt='Admin>';
---客户端连接, mysql
mysql -A -uihunter -pwdqdmm@m -h127.0.0.1 -P6033 --prompt='Client>';
应用: database -> 添加服务: proxysql
镜像: proxysql/proxysql:latest
卷:
/etc/localtime:/etc/localtime:ro
/data/file:/data/file
/data/docker/mysql/proxysql/proxysql.cnf:/etc/proxysql.cnf
/data/docker/mysql/proxysql/proxysql.db:/var/lib/proxysql/proxysql.db
端口:
6032-6032
6033-6033
保存ip: 升级或替换 不变
主机名: 使用容器名称
apt-get update -y
apt-get install -y wget vim net-tools curl git cron axel zip unzip
apt-get install -y cmake golang python
apt-get install -y build-essential autoconf automake libtool
apt-get dist-upgrade -y
apt-get upgrade -y
apt autoremove
apt clean
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
# 查看版本
proxysql --version
# 查看端口
netstat -lnt
# 安装mysql客户端
apt-get install -y mysql-client
# 读写规则：以select开头的且不以for update结尾的语句一律路由到slave节点，其他的路由到master节点。
# 更改语句:
UPDATE mysql_servers SET port = '3306' WHERE port = '3333';
DELETE FROM mysql_servers;
# 修改密码:
UPDATE mysql_users SET password='wdqdmm@m' WHERE password='wdqdmm@p';
# 查看用户:
SELECT * FROM mysql_users;
# 查看所有数据库
show databases;
# 显示数据表
show tables;
1. 每台server信息分组
use main;
INSERT INTO mysql_servers
( hostgroup_id, hostname, port, weight, max_connections, max_replication_lag, comment )
VALUES
( 1, 'mysql1', 3306, 1, 1000, 10, 'mysql' ),
( 1, 'mysql2', 3306, 1, 1000, 10, 'mysql' ),
( 2, 'mysql3', 3306, 1, 1000, 10, 'mysql' ),
( 2, 'mysql4', 3306, 1, 1000, 10, 'mysql' );
SELECT * FROM mysql_servers;
-- 特别注意这里两条数据的hostgroup_id是一样的，权重weight也是一样的，前者保证同一个group以便负载均衡，后者权重可以影响负载均衡结果
2.接下来将刚刚在mysql客户端创建的用户写入到proxy sql主机的mysql_users表中，它也是用于proxysql客户端访问数据库，默认组是写组，当读写分离规则出现问题时，它会直接访问默认组的数据库。
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root', 'wdqdmm@r',1);
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql', 'wdqdmm@p',2);
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('ihunter', 'wdqdmm@m',1);
SELECT * FROM mysql_users;
-- 这里需要注意，default_hostgroup需要和上面的mysql_servers.hostgroup_id对应
# 使配置生效
# Memory -> Runtime:
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;
# Memory -> Disk
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;
3.转发规则
#将insert这样的修改语句路由到1组（写组）
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^INSERT.*',1,0);
#将select语句全部路由至hostgroup_id=2的组(也就是读组)
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,0);
#将update语句全部路由至hostgroup_id=1的组(也就是写组)
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(3,1,'^UPDATE',1,0);
#将show语句全部路由至hostgroup_id=2的组(也就是读组)
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(4,1,'^SHOW',2,0);
----------------------------
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
SELECT rule_id,active,match_digest,match_pattern,destination_hostgroup,apply FROM mysql_query_rules;
---配置查询select的请求转发到hostgroup_id=2组上（读组）
---征对select * from table_name for update这样的修改语句，我们是需要将请求转到写组，也就是hostgroup_id=1
---对于其它没有被规则匹配的请求全部转发到默认的组（mysql_users表中default_hostgroup）
---DELETE 语句替换为 select 语句:
INSERT INTO mysql_query_rules (rule_id, active, match_pattern,replace_pattern, apply) VALUES (4,1,'^DELETE FROM t1$','select * from test.t1',1);
---禁止泛查询
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) VALUES (2,1,'^SELECT \* from t1$','Query not allowed',1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) VALUES (3,1,'^DELETE FROM t1$','Query not allowed',1);
---mysql_query_rules 的 apply 字段
-当设置为1后，当匹配到该规则后，将立即应用该规则，不会再评估其它的规则
---match_digest：通过正则表达式匹配digest
---match_pattern：通过正则表达式匹配查询语句的文本内容
---replace_pattern：将匹配到的内容替换为此字段值。它使用的是RE2正则引擎的Replace。注意，这是可选的，当未设置该字段，查询处理器将不会重写语句，只会缓存、路由以及设置其它参数
---timeout：被匹配或被重写的查询执行的最大超时时长(单位毫秒)。如果一个查询执行的时间太久(超过了这个值)，该查询将自动被杀掉
---retries：当在执行查询时探测到故障后，重新执行查询的最大次数
---digest：将参数化后的语句进行hash运算得到一个hash值digest，可以对这个hash值进行精确匹配。匹配效率最高。
---match_digest：对digest值进行正则匹配。
---match_pattern：对原始SQL语句的文本内容进行正则匹配。
-如果要进行SQL语句的重写(即正则替换)，或者对参数值匹配，则必须采用match_pattern。如果可以，尽量采用digest匹配方式，因为它的效率更高。
---查看规则
SELECT rule_id,active,match_digest,match_pattern,destination_hostgroup,apply FROM mysql_query_rules;
DELETE FROM mysql_query_rules;
UPDATE mysql_query_rules SET match_digest='^SELECT' WHERE match_digest='^SELECT';
-----------------------------
# 读写映射, 这里配置主从自动切换：互为主从，自动切换，保证高可用。
# 如果你没有做到互为主从，请跳过此项。
# mysql_replication_groups，控制组复制集群中的读、写组。此配置用于与MHA结合实现高可用。
insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,2,'MHA高可用');
select * from mysql_replication_hostgroups;
---writer_hostgroup - 默认情况下将发送所有流量的主机组，MySQL中read_only = 0的节点将分配给该主机组。
---reader_hostgroup - 应该发送读取流量的主机组，应该定义查询规则或单独的只读用户将流量路由到此主机组，将read_only = 1的节点分配给该主机组。
---check_type - 执行只读检查时检查的MySQL变量，默认情况下为read_only（也可以使用super_read_only）。
---comment - 提醒, 可用于用户定义的任何目的的文本字段。
#写入RUNTIME层, 磁盘
load mysql users to runtime; # 将内存数据库中的配置加载到 runtime 数据结构
load mysql servers to runtime; # 将MySQL server 从内存数据库中加载到 runtime
load mysql query rules to runtime; # 将 MySQL query rules 从内存数据库加载到 runtime 数据结构
load mysql variables to runtime; # 将 MySQL variables 从内存数据库加载到 runtime 数据结构
load admin variables to runtime; # 将 admin variables 从内存数据库加载到 runtime 数据结构
save mysql users to disk; # 将内存数据库中的 MySQL 用户持久化到磁盘数据库中
save mysql servers to disk; # 从内存数据库中将 MySQL server 持久化到磁盘数据库中
save mysql query rules to disk; # 将 MySQL query rules 从内存数据库中持久化到磁盘数据库中
save mysql variables to disk; # 将 MySQL variables 从内存数据库中持久化到磁盘数据库中
save admin variables to disk; # 将 admin variables 从内存数据库中持久化到磁盘数据库
5.验证负载均衡
proxysql的6032端口是管理入口，6033端口就是客户端入口。
# mysql -h 192.168.20.6 -P 6033 -uihunter -pproxysql < test_proxysql_lb.sql
mysql -uproxysql -pwdqdmm@p -P6033 -h127.0.0.1 --prompt='Client>'; # 客户端连接, mysql
show databases;
# 创建数据与表，测试读写分离情况
MySQL [(none)]> create database test_db;
MySQL [(none)]> use test_db;
MySQL [test_db]> create table test_tables(name varchar(20),age int(4));
MySQL [test_db]> insert into test_tables values('test','1');
MySQL [test_db]> select * from test_tables;
# 在proxysql管理端查看读写分离
mysql -uproxysql -pwdqdmm@p -h 127.0.0.1 -P 6032 --prompt='Admin>';
# 查看相关路由规则的匹配情况
select active,hits, mysql_query_rules.rule_id, schemaname, match_digest, match_pattern, replace_pattern,destination_hostgroup hostgroup,s.comment,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules JOIN mysql_servers s on destination_hostgroup=hostgroup_id ORDER BY mysql_query_rules.rule_id;
# 可以查看各类SQL的执行分离情况
# select * from stats_mysql_query_digest;
select hostgroup,schemaname,username,digest,digest_text,count_star from stats_mysql_query_digest;
6.测试MHA高可用故障转移
---stop mysqld1 # 停止了主1,备3
# ProxySQL上的节点状态
select hostgroup_id, hostname, port,status from runtime_mysql_servers;
-结果显示mysqld1的状态为SHUNNED，表示该节点被ProxySQL避开了。且mysqld2节点移到了hg=1的组中，说明该节点被选举为了新的Master节点。
---start mysqld1 mysql1 # 恢复
7.Mysql 集群部署 - 高可用高负载方案
https://abc.htmltoo.com/thread-45907.htm
# 监视
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='wdqdmm@p' WHERE variable_name='mysql-monitor_password';
# 配置监视间隔：
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
#检查监控状态
-连接状态, 看最后如果出现 connect_error = NULL 则表示正常。
select * from mysql_server_connect_log LIMIT 10;
-ping状态, 看最后如出现NULL则表示正常
select * from mysql_server_ping_log LIMIT 10;
-查询sql请求路由信息
select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest LIMIT 10;
-统计各种SQL类型的执行次数和时间
select * from stats_mysql_commands_counters LIMIT 10;
-查看连接后端MySQL的连接池信息
select * from stats_mysql_connection_pool LIMIT 10;
vim /data/docker/mysql/proxysql/proxysql.cnf
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:wdqdmm@p"
mysql_ifaces="0.0.0.0:6032"
refresh_interval=2000
debug=true
admin-web_enabled=true
admin-web_port=6080
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="10.5.11"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="wdqdmm@p"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
# defines all the mysql_servers
mysql_servers =
(
# {
# address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
# port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
# hostgroup = 0 # no default, required
# status = "ONLINE" # default: ONLINE
# weight = 1 # default: 1
# compression = 0 # default: 0
# max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
# },
# {
# address = "/var/lib/mysql/mysql.sock"
# port = 0
# hostgroup = 0
# },
# {
# address="127.0.0.1"
# port=21891
# hostgroup=0
# max_connections=200
# },
{ address="127.0.0.1" , port=3360 , hostgroup=1 , weight=1 , max_connections=8000 },
{ address="127.0.0.1" , port=3361 , hostgroup=2 , weight=1 , max_connections=8000 },
{ address="127.0.0.1" , port=3362 , hostgroup=2 , weight=2 , max_connections=8000 },
{ address="127.0.0.1" , port=3363 , hostgroup=2 , weight=3 , max_connections=8000 },
{ address="127.0.0.1" , port=3364 , hostgroup=2 , weight=4 , max_connections=8000 }
)
# defines all the MySQL users
mysql_users:
(
# {
# username = "username" # no default , required
# password = "password" # default: ''
# default_hostgroup = 0 # default: 0
# active = 1 # default: 1
# },
# {
# username = "root"
# password = ""
# default_hostgroup = 0
# max_connections=1000
# default_schema="test"
# active = 1
# },
{ username = "root" , password = "wdqdmm@r" , default_hostgroup = 1 , active = 1 },
{ username = "proxysql" , password = "wdqdmm@p" , default_hostgroup = 2, active = 1 },
{ username = "ihunter" , password = "wdqdmm@m" , default_hostgroup = 1, active = 1 }
)
#defines MySQL Query Rules
mysql_query_rules:
(
{
rule_id=1
active=1
match_digest="^INSERT.*"
destination_hostgroup=1
apply=1
},
{
rule_id=2
active=1
match_digest="^SELECT"
destination_hostgroup=2
apply=1
},
{
rule_id=3
active=1
match_digest="^UPDATE"
destination_hostgroup=1
apply=1
},
{
rule_id=4
active=1
match_digest="^SHOW"
destination_hostgroup=2
apply=1
}
)
scheduler=
(
# {
# id=1
# active=0
# interval_ms=10000
# filename="/var/lib/proxysql/proxysql_galera_checker.sh"
# arg1="0"
# arg2="0"
# arg3="0"
# arg4="1"
# arg5="/var/lib/proxysql/proxysql_galera_checker.log"
# }
)
mysql_replication_hostgroups=
(
# {
# writer_hostgroup=30
# reader_hostgroup=40
# comment="test repl 1"
# },
# {
# writer_hostgroup=50
# reader_hostgroup=60
# comment="test repl 2"
# }
)https://my.oschina.net/cart/blog/2907232