跳至主要內容

mysql 双主同步配置

Change Lee2024年7月17日...大约 3 分钟数据库mysqlmariadb

机器规划

主机IP操作系统port账号密码（base64）节点ID软件
Master01192.168.1.20Debian123306replicatorTWFyaWFkYkAyMDI01mariadb-server-10.5/
Master02192.168.8.20Debian123306replicatorTWFyaWFkYkAyMDI02mariadb-server-10.5/

软件安装

apt update
apt install mariadb-server

修改配置

/etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf

开启 bin-log 和 server-id

提示

binlog_do_db 标识哪个数据库需要做同步，如果不指定则所有数据都做同步，这样bin-log 会比较乱，不利用于做数据恢复

Master01

[mysqld]
sserver-id         = 1
log_bin            = /mysql/mysql-bin.log
expire_logs_days   = 10
max_binlog_size    = 200M
binlog_do_db       = your_db_name

Master02

[mysqld]
server-id          = 2
log_bin            = /mysql/mysql-bin.log
expire_logs_days   = 10
max_binlog_size    = 200M
binlog_do_db       = your_db_name

配置循环复制和数据冲突

Master01

auto_increment_increment = 2
auto_increment_offset = 1

Master02

auto_increment_increment = 2
auto_increment_offset = 2

修改文件目录相关的配置（2台都要改）

#user                   = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp

修改 log 相关的配置（2台都要改）

general_log_file    = /var/log/mysql/mysql.log
general_log         = 1
log_error           = /var/log/mysql/error.log

修改超时相关的配置（2台都要改）

# *Timeout Setting
#
net_read_timeout             = 120  # 网络读取超时时间（秒）
net_write_timeout            = 120  # 网络写入超时时间（秒）
slave_net_timeout            = 300  # 从服务器网络超时时间（秒）
master_retry_count           = 86400  # 从服务器在重试连接主服务器之前尝试的次数
rpl_semi_sync_master_timeout = 30000  # 半同步复制超时时间（毫秒）

查看默认 timeout 设置

mysql> SHOW VARIABLES LIKE 'net_read_timeout';
mysql> SHOW VARIABLES LIKE 'net_write_timeout';
mysql> SHOW VARIABLES LIKE 'slave_net_timeout';
mysql> SHOW VARIABLES LIKE 'master_retry_count';
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync_master_timeout';

创建相关目录并授权（2台都要改）

# 常规日志目录
sudo mkdir -m 2750 /var/log/mysql
sudo chown mysql /var/log/mysql
# bin-log 目录
sudo mkdir -m 2750 /mysql
sudo chown mysql /mysql

重启服务（2台都要改）

systemctl restart mariadb

查询 Master状态 （2台都要改）

SHOW MASTER STATUS;

结果参考

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 | your_db_name |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

创建复制用户（2台都要改）

登录数据库，创建用于复制的用户

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

设置 Replication

Master01

CHANGE MASTER TO 
MASTER_HOST='192.168.8.20',
MASTER_USER='replicator',
MASTER_PASSWORD='TWFyaWFkYkAyMDI0',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=328;

START SLAVE;

Master02

CHANGE MASTER TO 
MASTER_HOST='192.168.1.20',
MASTER_USER='replicator',
MASTER_PASSWORD='TWFyaWFkYkAyMDI0',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=328;

START SLAVE;

验证 Replication

SHOW SLAVE STATUS\G;

查看 Slave_IO_Running 和 Slave_SQL_Running 是否都显示 Yes。
插入数据验证
执行任意创建、修改、删除操作，验证数据是否同步

参考：

# Master01 上执行
USE your_db_name;
INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2');
# Master02 上验证
USE your_db_name;
SELECT * FROM your_table;

完整配置参考

详情
# this is only for the mysqld standalone daemon
[mysqld]
# *Timeout Setting
#
net_read_timeout = 120  # 网络读取超时时间（秒）
net_write_timeout = 120  # 网络写入超时时间（秒）
slave_net_timeout = 300  # 从服务器网络超时时间（秒）
master_retry_count = 86400  # 从服务器在重试连接主服务器之前尝试的次数
rpl_semi_sync_master_timeout = 30000  # 半同步复制超时时间（毫秒）

#
# * Basic Settings
#

#user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0


#
# * Logging and Replication
#
auto_increment_increment = 2
auto_increment_offset = 1

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
general_log_file       = /var/log/mysql/mysql.log
general_log            = 1

# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
log_error = /var/log/mysql/error.log

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id              = 1
log_bin                = /mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 200M
binlog_do_db           = frp
binlog_do_db           = frp_new

#
# * Character sets
#

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

贡献者

change
最近更新：