proxySQL+mysql8.0一主双从+springboot配置

Good kid.

已于 2024-02-25 14:55:13 修改

阅读量1.3k

点赞数 23

文章标签： spring boot 后端 mysql

于 2024-01-09 15:48:10 首次发布

本文链接：https://blog.csdn.net/MTQ851/article/details/135481232

版权

准备环境:
Ubuntu 22.04.3 LTS(四台虚拟机)
mysql8.0.35
proxySQL2.5.5

要在 Ubuntu 上为 MySQL 8.0 搭建主从复制，你需要两台 Ubuntu 服务器：一台作为主（Master）服务器，另一台作为从（Slave）服务器。以下是详细步骤来设置 MySQL 主从复制。

第 1 步：在两个服务器上安装 MySQL 8.0
更新包索引：

sudo apt update

安装 MySQL 服务器：

sudo apt install mysql-server

第 2 步：配置主服务器
以 root 用户登录到 MySQL：

sudo mysql -u root -p

编辑 MySQL 的配置文件 my.cnf 或 mysqld.cnf：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

在 [mysqld] 部分中，进行以下设置：

server-id = 1
bind-address = 0.0.0.0
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = DBname(数据库名称)

注意：bind-address 的值要设为主服务器的实际 IP 地址或 0.0.0.0 以允许远程连接。
重启 MySQL 服务使改变生效：

sudo systemctl restart mysql

创建用于复制的 MySQL 用户：

CREATE USER 'replicator'@'%' IDENTIFIED BY 'your_replicator_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

注意 8.0.x 用户认证的方式需要修改为 mysql_native_password
需要在my.cnf 加上这个用户认证方式，再来创建用户

[mysqld]
default_authentication_plugin=mysql_native_password

查找初始复制位置：
SQL
SHOW MASTER STATUS;
记录下 File 和 Position 的值（如: mysql-bin.000001 和 154），稍后配置从服务器时会用到。

第 3 步：配置从服务器
按照相同的方式安装 MySQL 并访问 MySQL Shell。

编辑 MySQL 的配置文件：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

在 [mysqld] 部分中，进行以下设置：
server-id = 2（设置 server-id 为唯一的值，不同于主服务器的 server-id，例如设置为 2。）
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1 （只读）
binlog_do_db = DBname(数据库名称)

重启 MySQL 服务：

sudo systemctl restart mysql

为复制配置从服务器：

CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='replicator',
MASTER_PASSWORD='your_replicator_password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;

将 master_ip 换成主服务器的 IP 地址，recorded_log_file_name 和 recorded_log_position 替换为之前记下的值。

启动复制进程：

START SLAVE;

检查从服务器的复制状态：

SHOW SLAVE STATUS\G

查看 Slave_IO_Running 和 Slave_SQL_Running 是否都是 Yes。

完成以上步骤后，你现在应该有一个运行在 Ubuntu 上并配置了 MySQL 8.0 主从复制的环境。务必在实际的生产部署前进行充分的测试和调整，以满足具体需求和性能标准。

proxySQL准备

配置 APT 存储库(这里我两个都运行了）

apt-get update && apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | tee /etc/apt/sources.list.d/proxysql.list

或对于没有 apt-key 的发行版注意：在较旧的 EOL 发行版（Ubuntu 14.04、Debian 8）上，由于 CA 证书过期，您可能需要使用 http:// 而不是 https:// 或手动更新 CA 证书

apt-get update && apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates
wget -nv -O /etc/apt/trusted.gpg.d/proxysql-2.5.x-keyring.gpg 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key.gpg'
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | tee /etc/apt/sources.list.d/proxysql.list

安装软件包

apt-get -y update
apt-get -y install proxysql

开启proxysql

sudo systemctl start  proxysql

在这台proxySQL的虚拟机上也需要安装个mysql才能到proxysql管理层（这边不详细说mysql安装过程了）

连接到ProxySQL管理界面：

mysql -u admin -padmin -h 127.0.0.1 -P 6032

这里使用了默认的用户名（admin）和密码（admin），请按实际情况替换它们。

配置后端MySQL服务器：

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'<主库IP>',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'<从库1_IP>',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'<从库2_IP>',3306);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

配置mysql_users 表，将proxysql用户添加到该表中。

insert into mysql_users (username,password,default_hostgroup) values ('replicator','your_replicator_password',10);
load mysql users to runtime;
save mysql users to disk;

这里的hostgroup_id是用来区分主库和从库的标识符，通常10用于主库，20用于从库。

c. 配置监控用户：

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('monitor', '<监控用户密码>', 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

d. 配置复制主从规则：

INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup) VALUES (10, 20);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

e. 配置查询规则（可选）：

INSERT INTO mysql_query_rules(active, match_digest, destination_hostgroup, apply) VALUES(1, '^SELECT.*FOR UPDATE$', 10, 1);
INSERT INTO mysql_query_rules(active, match_digest, destination_hostgroup, apply) VALUES(1, '^SELECT', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
以上配置将所有的SELECT ... FOR UPDATE查询发送到主库，而普通的SELECT查询则会被发送到从库。

查询连接信息

mysql> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host       | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 1         | 192.168.120.13 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 0          |
| 2         | 192.168.120.14 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 0          |
| 2         | 192.168.120.15 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 0          |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
3 rows in set (0.01 sec)

注意大坑
proxysql目前安装最新的内置的mysql版本也才是5.5.30的所以如果你的数据库是8.0及以上的

一定要记得修改proxysql内置mysql的版本号登上proxysql的应用端执行以下命令

update global_variables set variable_value="8.0.4 (ProxySQL)" where variable_name='mysql-server_version';

#那个版本号8.0.4 你可以随意改只要是8.0以上的就行

load mysql variables to run;
save mysql variables to disk;

配置Spring Boot应用程序
在Spring Boot应用程序中，您的application.properties或application.yml配置文件需要指定连接到ProxySQL的实例而不是直接连接MySQL服务器。

另外还有个大坑
由于mysql5.7后引入了默认认证插件caching_sha2_password，所以要换成mysql_native_password，不然navicat连接proxysql就会一直报密码错误，主从数据库都执行下面操作

ALTER USER 'username'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'newpassword';
FLUSH PRIVILEGES;

application.yml

spring:
  datasource:
    url: jdbc:mysql://<ProxySQL-IP>:6033/dbname
    username: <数据库用户名>
    password: <数据库密码>
    driver-class-name: com.mysql.jdbc.Driver

Good kid.

关注

23
点赞
踩
22

收藏

觉得还不错? 一键收藏
0
评论
proxySQL+mysql8.0一主双从+springboot配置

准备环境:Ubuntu 22.04.3 LTS(四台虚拟机)要在 Ubuntu 上为 MySQL 8.0 搭建主从复制，你需要两台 Ubuntu 服务器：一台作为主（Master）服务器，另一台作为从（Slave）服务器。以下是详细步骤来设置 MySQL 主从复制。第 1 步：在两个服务器上安装 MySQL 8.0第 2 步：配置主服务器在 [mysqld] 部分中，进行以下设置：binlog_do_db = DBname(数据库名称)
复制链接

扫一扫