Keepalived & MySQL：
准备工作：
Master：192.168.1.201
Worker：192.168.1.202
都安装MySQL容器
两台服务器之间可网络连接
MySQL 双主热备
1、修改配置文件
Master
server-id=1
gtid_mode=ON
log-bin=mysql-bin
bind-address = 0.0.0.0
enforce_gtid_consistency = ONWorker
server-id=2
gtid_mode=ON
log-bin=mysql-bin
bind-address = 0.0.0.0
enforce_gtid_consistency = ON2、创建复制用户
Master
CREATE USER 'replicator'@'%' IDENTIFIED BY 'PySuper';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
ALTER USER 'replicator'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'PySuper';
FLUSH PRIVILEGES;
# 查看创建的用户
SELECT user, host, plugin FROM mysql.user WHERE user = 'replicator';Worker
CREATE USER 'replicator'@'%' IDENTIFIED BY 'PySuper';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
ALTER USER 'replicator'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'PySuper';
FLUSH PRIVILEGES;
SELECT user, host, plugin FROM mysql.user WHERE user = 'replicator';3、确认状态
Master
# 停止复制
STOP REPLICA;
# 重置所有复制配置
RESET REPLICA ALL;
# 停止IO线程
STOP REPLICA IO_THREAD FOR CHANNEL '';
# 查看当前二进制日志状态
SHOW BINARY LOG STATUS;Worker
STOP REPLICA;
RESET REPLICA ALL;
STOP REPLICA IO_THREAD FOR CHANNEL '';
SHOW BINARY LOG STATUS;
4、配置复制源
主库主机、端口、用户名、密码、日志文件和位置
这里的
SOURCE_LOG_FILE，SOURCE_LOG_POS，就是上面 LOG 状态中的File、Position
Master
# 配置复制源信息
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='103.36.222.30',
SOURCE_PORT=33006,
SOURCE_USER='replicator',
SOURCE_PASSWORD='PySuper',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=1201;
# 启动复制
START REPLICA;
# 查看复制状态
SHOW REPLICA STATUS;Worker
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='156.238.247.211',
SOURCE_PORT=3306,
SOURCE_USER='replicator',
SOURCE_PASSWORD='PySuper',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=880;
START REPLICA;
SHOW REPLICA STATUS;5、复制状态
6、其他命令
# 停止复制
STOP REPLICA;
# 跳过一个复制错误
SET GLOBAL sql_slave_skip_counter = 1;
# 重新启动复制
START REPLICA;
# 查看GTID模式是否启用
SHOW VARIABLES LIKE 'gtid_mode';
# 查看是否强制GTID一致性
SHOW VARIABLES LIKE 'enforce_gtid_consistency';ProxySQL 故障转移
1、Proxy Docker
外界访问ProxySQL进行数据库操作时，通常使用6033端口
6032端口则用于管理和配置ProxySQL
6032端口：
这是ProxySQL的管理接口端口。你可以通过这个端口连接到ProxySQL的管理控制台，执行管理和配置命令。
这个端口通常用于管理目的，不用于处理客户端的数据库请求。
6033端口：
这是ProxySQL的默认数据端口。客户端应用程序通过这个端口连接到ProxySQL，以访问后端的MySQL数据库。
这个端口用于处理实际的数据库查询和请求。
# 创建 ProxySQL 容器
docker run -dit \
-h ProxySQL \
--name ProxySQL \
-p 4000:6032 \
-p 4001:6033 \
--restart=always \
proxysql/proxysql
# 进入容器
docker exec -it ProxySQL /bin/bash
# 连接，容器内部直接使用映射前的端口
mysql -h 127.0.0.1 -P 6032 -u admin -padmin2、配置MySQL服务器
这里的读写分离，酌情添加
# 配置 MySQL 服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (1, '103.36.222.30', 33006, 2);
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (1, '156.238.247.211', 3306, 1);
# 配置立即生效
LOAD MYSQL SERVERS TO RUNTIME;
# 将当前运行时内存中的配置, 保存到磁盘上的配置文件中
SAVE MYSQL SERVERS TO DISK;
# 查看MySQL服务器的状态
SELECT hostgroup_id, hostname, port, status, weight FROM mysql_servers;3、添加访问用户
这里添加的用户，就是后面 访问 ProxySQL 6033端口的用户
ProxySQL
# 添加一个新的MySQL用户，该用户可以通过ProxySQL进行数据库连接
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('proxy', 'PySuper', 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;MySQL
访问ProxySQL之后，还是要访问MySQL数据库的，所以MySQL中也要创建，而且是两个节点都要有
但是上面已经配置MySQL的双主热备，这里只需要在一个节点执行就可以
# 创建ProxySQL用户, 允许从任意主机连接('%')
CREATE USER 'proxy'@'%' IDENTIFIED BY 'PySuper';
# 这里注意分配 相应的库、表 的权限！，这里写的是sys，SQL的基础库，可以指定为项目使用的数据库
GRANT ALL PRIVILEGES ON sys.* TO 'proxy'@'%';
# 下面这句是赋予所有库的权限
GRANT ALL PRIVILEGES ON *.* TO 'proxy'@'%';
FLUSH PRIVILEGES;
# 查看用户
SELECT user, host, plugin FROM mysql.user;4、配置健康检查
ProxySQL
# 设置监控用户名
SET mysql-monitor_username='pingpong';
# 设置监控用户密码
SET mysql-monitor_password='pingpong_PySuper';
# 设置监控间隔时间（毫秒）
SET mysql-monitor_interval_ms=2000;
# 加载MySQL变量到运行时
LOAD MYSQL VARIABLES TO RUNTIME;
# 将MySQL变量保存到磁盘
SAVE MYSQL VARIABLES TO DISK;MySQL
这里需要用户从 ProxySQL 所在节点，访问到数据库
创建一个用户，给一个比较低的权限，能查询就行
# 创建心跳检测用户pingpong, 允许从任意主机连接('%')
CREATE USER 'pingpong'@'%' IDENTIFIED BY 'pingpong_PySuper';
GRANT SELECT ON *.* TO 'pingpong'@'%';
FLUSH PRIVILEGES;5、读写分离
# 定义读写分离的主机组
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES (1, 2, 'cluster1');
# 配置读写规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT.*FOR UPDATE$', 1, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^SELECT', 2, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;6、其他命令
# 测试容器连接，在ProxySQL中执行
wget --spider http://192.168.1.201:3306
wget --spider http://192.168.1.202:3306
# 测试连接
# 内网
mysql -h 127.0.0.1 -P 6033 -u Username -pPassword
# 公网
mysql -h 192.168.1.201 -P 3306 -u Username -pPassword# 查看MySQL服务器的状态
SELECT hostgroup_id, hostname, port, weight FROM mysql_servers;
# 查看运行时的MySQL服务器状态
SELECT hostgroup_id, hostname, port, status, weight FROM runtime_mysql_servers;
# 查看监控信息
SELECT * FROM stats_mysql_connection_pool;
# 查看运行时MySQL服务器状态
SELECT hostgroup_id, hostname, port, status, weight, max_replication_lag FROM runtime_mysql_servers;
# 查看健康检查统计信息
SELECT hostgroup, srv_host, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_mysql_connection_pool;7、本地使用
# 用wget从github直接下载
wget https://github.com/sysown/proxysql/releases/download/v2.5.4/proxysql_2.5.4-ubuntu22_amd64.deb
# 使用dpkg安装
dpkg -i proxysql_2.5.4-ubuntu22_amd64.deb
# 进入控制台
mysql -uroot -pAffect_PySuper -h127.0.0.1 -P13306 --prompt='Admin>'
# 查看：https://blog.csdn.net/benshu_001/article/details/142062299连接测试
import pymysql
def connect_to_mysql():
try:
# 创建数据库连接
connection = pymysql.connect(
host="103.36.222.30", # 替换为你的MySQL服务器地址
port=4001, # 替换为你的MySQL服务器端口
user="proxy", # 替换为你的用户名
password="PySuper", # 替换为你的密码
database="sys", # 替换为你的数据库名称
)
with connection.cursor() as cursor:
# 执行一个查询
cursor.execute("SELECT DATABASE();")
# 获取查询结果
record = cursor.fetchone()
print("当前连接的数据库是: ", record)
except pymysql.MySQLError as e:
print("连接数据库时发生错误: ", e)
if __name__ == "__main__":
connect_to_mysql()
评论区