MariaDB 10两主一从数据同步复制配置

最新推荐文章于 2025-11-12 01:31:59 发布

原创最新推荐文章于 2025-11-12 01:31:59 发布 · 478 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mariadb

Linux系统专区同时被 2 个专栏收录

39 篇文章

订阅专栏

LNMP 架构

19 篇文章

订阅专栏

本文详细介绍了如何配置MariaDB 10的两主一从数据同步复制。首先，分别在三台服务器上进行环境准备和配置文件修改。接着，设置主从环境并重启服务。然后，通过授权和配置从库，实现DB1、DB2之间的主主复制，以及DB2到DB3的主从复制。最后，验证同步状态并确保从库处于只读模式，完成配置。

1.准备环境
Server 1:CentOS 7.9 64位 IP:172.23.100.99
Server 2:Debian 10.7 64位 IP:172.23.100.97
Server 3:Debian 10.7 64位 IP:172.23.100.90
每台服务器的防火墙开启数据库端口（默认3306）
MariaDB版本：皆为10.4.17，其中Server 1和Server 2上为编译安装mariadb,Server 3上为apt安装mariadb。
节点DB1：172.23.100.99
节点DB2：172.23.100.97
节点DB3：172.23.100.90
防火墙开放端口3306。
2.修改MariaDB的配置文件（my.cnf）
Server 1中，在/etc/my.cnf中添加以下选项：
server-id = 99 #服务器节点ID,一定要保证唯一值, 不妨设为服务器IP地址最后一位数。
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=1
auto-increment-increment=2
binlog_format=mixed
expire_logs_days = 7
Server 2中，在/etc/my.cnf中添加以下选项：
server-id = 97 #服务器节点ID,一定要保证唯一值
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days = 7
Server 3中，在/etc/mysql/my.cnf中添加以下选项：
server-id = 90
关闭log-bin
#log-bin = mysql-bin
#log_bin = /var/log/mysql/mysql-bin
#binlog_format = mixed
#log_bin_index = /var/log/mysql/mysql-bin.index
#sync_binlog = 1
#expire_logs_days = 7
#max_binlog_size = 100M
3.主从环境
172.23.100.99 node1 主
172.23.100.97 node2 主
172.23.100.90 node3 从
4.重启MariaDB服务
Server 1及Server 2中：systemctl restart mysqld
Server 3中：systemctl restart mariadb
5.锁定主机(此步骤应在以下过程中授权用户后才能用，再次授权之前应解锁)
配置过程中为了防止突然的主机数据写入导致配置主从失败可以将机器锁定只允许读（注意千万不要将从机配成锁定否则无法同步数据了）。
FLUSH TABLES WITH READ LOCK
配置完成后再解锁，执行：
UNLOCK TABLES
6.DB1为master,DB2为slave配置
（1）DB1 作为master 授权给DB2
Server 1中登录Mariadb终端：
[root@centos-moodle:~]#mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.4.17-MariaDB-log Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> grant replication slave,replication client on *.* to ‘tongbu97’@‘172.23.100.97’ identified by ‘123456’;
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000154 | 1403602 | | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.000 sec)
（2）DB2作为slave从库
Server 2中登录Mariadb终端：
[root@debian-moodle:~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 63
Server version: 10.4.17-MariaDB-log Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> change master to master_host=‘172.23.100.99’,master_user=‘tongbu97’,master_password=‘123456’,master_log_file=‘mysql-bin.000154’,master_log_pos=1403602,master_connect_retry=30;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.23.100.99
Master_User: tongbu97
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000154
Read_Master_Log_Pos: 1683329
Relay_Log_File: mysql-relay-bin.000088
Relay_Log_Pos: 657
Relay_Master_Log_File: mysql-bin.000154
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1683329
Relay_Log_Space: 1265
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

7.DB2为master,DB1为slave配置
（1）DB2作为master 授权给DB1
[root@debian-moodle:~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 72
Server version: 10.4.17-MariaDB-log Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> grant replication slave,replication client on *.* to ‘tongbu99’@‘172.23.100.99’ identified by ‘12345678’;
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000029 | 1844275 | | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.000 sec)
（2）DB1作为slave从库
Server 1中登录Mariadb终端：
MariaDB [(none)]> change master to master_host=‘172.23.100.97’,master_user=‘tongbu99’,master_password=‘12345678’,master_log_file=‘mysql-bin.000029’,master_log_pos=1844275,master_connect_retry=30;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.23.100.97
Master_User: tongbu99
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000029
Read_Master_Log_Pos: 1959682
Relay_Log_File: mysql-relay-bin.000085
Relay_Log_Pos: 1959981
Relay_Master_Log_File: mysql-bin.000029
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1959682
Relay_Log_Space: 1960589
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 10
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 3186
1 row in set (0.000 sec)
我们看到两台服务器的数据同步信息中Slave_IO_Running和Slave_SQL_Running 都是Yes，说明我们配置成功了！
到此，主主配置已成功！
8.DB2为master,DB3为slave配置
(1)DB2作为master 授权给DB3
[root@debian-moodle:~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 344
Server version: 10.4.17-MariaDB-log Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> grant replication slave,replication client on . to ‘tongbu90’@‘172.23.100.90’ identified by ‘123456789’;
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000033 | 8880536 | | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.000 sec)
(2)DB3作为slave从库
Server 3中登录Mariadb终端：
MariaDB [(none)]> change master to master_host=‘172.23.100.97’,master_user=‘tongbu90’,master_password=‘123456789’,master_log_file=‘mysql-bin.000033’,master_log_pos=8880536,master_connect_retry=30;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status \G
…
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
至此，主从搭建成功。为了防止从库写入，应在/etc/mysql/my.cnf中加入：
read_only = ON。再重启数据库。
综上，实现了MariaDB 10两主一从同步复制配置。