" name="sm-site-verification"/>
侧边栏壁纸
博主头像
PySuper 博主等级

千里之行,始于足下

  • 累计撰写 231 篇文章
  • 累计创建 15 个标签
  • 累计收到 2 条评论

目 录CONTENT

文章目录

MySQL 高可用 -- ProxySQL + MySQL(双主热备)

PySuper
2024-11-23 / 0 评论 / 0 点赞 / 59 阅读 / 0 字
温馨提示:
所有牛逼的人都有一段苦逼的岁月。 但是你只要像SB一样去坚持,终将牛逼!!! ✊✊✊

Keepalived & MySQL:https://zhengxingtao.com/archives/mysql-gao-ke-yong----keepalived-mysql-shuang-zhu-re-bei


准备工作:

  • 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 = ON

Worker

server-id=2
gtid_mode=ON
log-bin=mysql-bin
bind-address = 0.0.0.0
enforce_gtid_consistency = ON

2、创建复制用户

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;

# 跳过当前错误
STOP REPLICA;
SET GLOBAL sql_slave_skip_counter = 1;
START REPLICA;

# 启动复制
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;

# 跳过当前错误
STOP REPLICA;
SET GLOBAL sql_slave_skip_counter = 1;
START REPLICA;

5、复制状态

A61CDB4A-AA3C-433A-B175-11DE2ADEBAA9.png

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 -padmin

2、配置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()

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin

评论区