Rocky8.9部署MySQL主主同步

安装MySQL

在两台服务器上执行以下命令安装MySQL Server及相关库

dnf install mysql-server mysql mysql-libs -y

启用并启动 MySQL 服务

systemctl enable mysqld
systemctl restart mysqld
systemctl status mysqld

MySQL初始化安全设置

mysql_secure_installation

配置MySQL双主

第一台服务器

vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]

server-id=1
log-bin=mysql-bin
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
expire_logs_days = 30
max_binlog_size = 512M
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

  • server-id=1：设置服务器的唯一 ID。

  • log-bin=mysql-bin：启用二进制日志。

  • log-slave-updates：使从服务器也记录二进制日志。

  • sync_binlog=1：控制二进制日志的写入方式。

  • auto_increment_increment=2 和 auto_increment_offset=1：用于防止主键冲突。

  • expire_logs_days = 30：设置日志过期天数。

  • max_binlog_size = 512M：设置二进制日志的最大大小。

重启服务

systemctl enable mysqld
systemctl restart mysqld
systemctl status mysqld

第二台服务器

vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]

server-id=2
log-bin=mysql-bin
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
expire_logs_days = 30
max_binlog_size = 512M
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

重启服务

systemctl enable mysqld
systemctl restart mysqld
systemctl status mysqld

两台服务器

执行以下MySQL命令创建复制用户

mysql -uroot -p
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'repl'@'%';
SHOW MASTER STATUS;

第二台服务器

mysql -uroot -p
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.123.1', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=842;
start slave;

第一台服务器

mysql -uroot -p
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.123.2', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=827;
start slave;

两台服务器

执行以下MySQL命令检查复制状态

show slave status \G