ProxySQL 配置MySQL主从
#查看各库的表 show tables from stats; #若使用show tables; 仅显示main库中的表，无论是否使用了use stats;
一、配置ProxySQL主从分组信息
1.用到这个表：mysql_replication_hostgroup，表结构如下：
mysql> show create table mysql_replication_hostgroups\G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
注：writer_hostgroup 和reader_hostgroup 写组和读组都要大于等于0且不能相同
2.创建组：(定义写为1，读为0)
mysql> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
Query OK, 1 row affected (0.00 sec)
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.00 sec)
ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server，master被分到编号为1的写组，read_only=1的server，slave则分到编号为0的读组
3.添加主从服务器节点：
insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.1.1',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.1.2',3306);
load mysql servers to runtime;
save mysql servers to disk;
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 |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.1.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 0 | 192.168.1.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4.为ProxySQL监控MySQL后端节点
admin@127.0.0.1 [monitor]>set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 [monitor]>set mysql-monitor_password='Monitor@123.com';
Query OK, 1 row affected (0.00 sec)
在main下修改：
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Monitor@123.com' WHERE variable_name='mysql-monitor_password';
admin@127.0.0.1 [monitor]>load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)
admin@127.0.0.1 [monitor]>save mysql variables to disk;
Query OK, 139 rows affected (0.00 sec)
admin@127.0.0.1 [monitor]>select * from monitor.mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.168.1.1 | 3306 | 1620289005261149 | 973 | NULL |
| 192.168.1.2 | 3306 | 1620289006053633 | 910 | NULL |
| 192.168.1.1 | 3306 | 1620289065261228 | 939 | NULL |
| 192.168.1.1 | 3306 | 1620289066428641 | 1062 | NULL |
| 192.168.1.1 | 3306 | 1620289125261308 | 1537 | NULL |
| 192.168.1.1 | 3306 | 1620289125914110 | 867 | NULL |
| 192.168.1.1 | 3306 | 1620289185261356 | 825 | NULL |
| 192.168.1.2 | 3306 | 1620289186047972 | 952 | NULL |
| 192.168.1.1 | 3306 | 1620289245261496 | 1087 | NULL |
| 192.168.1.1 | 3306 | 1620289246197323 | 885 | NULL |
| 192.168.1.2 | 3306 | 1620289305261660 | 871 | NULL |
| 192.168.1.2 | 3306 | 1620289306045663 | 817 | NULL |
| 192.168.1.2 | 3306 | 1620289365261669 | 956 | NULL |
| 192.168.1.1 | 3306 | 1620289366242017 | 791 | NULL |
| 192.168.1.2 | 3306 | 1620289425261898 | 910 | NULL |
| 192.168.1.1 | 3306 | 1620289425990912 | 947 | NULL |
| 192.168.1.2 | 3306 | 1620289485261833 | 918 | NULL |
| 192.168.1.2 | 3306 | 1620289486053107 | 837 | NULL |
+---------------+------+------------------+-------------------------+---------------+
验证监控信息，ProxySQL 监控模块的指标都保存在monitor库的log表中
以下是连接是否正常的监控，对connect指标的监控 ，在前面可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常
5.对心跳信息的监控：
mysql> select * from mysql_server_ping_log limit 10;
+---------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------+
| 192.168.1.1 | 3306 | 1620289215302013 | 362 | NULL |
| 192.168.1.2 | 3306 | 1620289215450055 | 162 | NULL |
| 192.168.1.1 | 3306 | 1620289225301381 | 141 | NULL |
| 192.168.1.1 | 3306 | 1620289225425428 | 287 | NULL |
| 192.168.1.2 | 3306 | 1620289235302177 | 329 | NULL |
| 192.168.1.1 | 3306 | 1620289235435745 | 216 | NULL |
| 192.168.1.2 | 3306 | 1620289245301490 | 241 | NULL |
| 192.168.1.1 | 3306 | 1620289245402414 | 262 | NULL |
| 192.168.1.2 | 3306 | 1620289255302418 | 158 | NULL |
| 192.168.1.1 | 3306 | 1620289255472625 | 295 | NULL |
+---------------+------+------------------+----------------------+------------+
6.查看read_only日志监控：
mysql> select * from mysql_server_read_only_log limit 5;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 192.168.1.1 | 3306 | 1620290427336532 | 354 | 0 | NULL |
| 192.168.1.2 | 3306 | 1620290427357500 | 359 | 1 | NULL |
| 192.168.1.2 | 3306 | 1620290428834707 | 526 | 1 | NULL |
| 192.168.1.1 | 3306 | 1620290428850551 | 358 | 0 | NULL |
| 192.168.1.1 | 3306 | 1620290430334764 | 456 | 0 | NULL |
+---------------+------+------------------+-----------------+-----------+-------+
Monitor 模块就会开始监控后端的read_only值，当监控到read_only值，就会按照read_only的值将某些节点自动移到读写组
一些监控的状态斗志在log相关，都在monitor库下面的 global_variables 变量。
7.ProxySQL配置对外访问账号：（要在MySQL节点上创建）
mysql_users表结构如下：
mysql> show create table mysql_users\G
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)
将对外访问账号添加到mysql_users表中：
insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('yoon','*AD04915810305F8091B9C67A0CCE68566BB80611’,1,1);
load mysql users to runtime;
save mysql users to disk;
注：transaction_persistent 如果为1，则一个完整的SQL只可能路由到一个节点；这点非常重要，主要解决这种情况：一个事务有混合的读操作和写操作组成，事务未提交前，如果事务中的读操作和写操作路由到不同节点，那么读取到的结果必然是脏数据。所以一般情况下，该值应该设置为1，尤其是业务中使用到事务机制的情况（默认为0）
mysql_users 表有不少字段，最主要的三个字段username,password,default_hostgroup
A.username: 前端链接ProxySQL ，以及ProxySQL 将SQL 语句路由给MySQL所使用的的用户名
B.password：用户名对应的密码，。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行select password(PASSWORD)，然后将加密结果复制到该字段。
C.default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为1时，则使用 proxysql 用户发送的SQL语句默认情况下将路由到hostgroup_id=1 组中的某个节点。
在从库端192.168.1.2上通过对方访问账号yoon连接，测试是否路由能默认到hostgroup_id=1，它是一个写组
# mysql -h192.168.1.1 -uyoon -p'Yoon123!@#' -P 6033
mysql: [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 63
Server version: 5.7.28-31-log Percona Server (GPL), Release 31, Revision d14ef86
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yoon |
+--------------------+
5 rows in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 113306 |
+-------------+
1 row in set (0.00 sec)
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
2019-06-27 运行python脚本后台执行