主从数据库同步配置详解（MySQL/MariaDB）

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

在数据库管理中，主从同步是保障数据安全性和提高读写性能的关键技术。通过设置主从架构，可以实现数据的自动备份、负载均衡、甚至高可用性，特别是在业务扩展和容灾恢复中起到重要作用。本文将详细介绍如何配置MariaDB和MySQL的主从数据库同步，并讨论主从复制的原理和应用场景。

原理：
MariaDB和MySQL的主从同步基于二进制日志（binlog）实现。主数据库负责记录所有数据更改操作（插入、更新、删除等）到二进制日志中，而从数据库通过IO线程读取这些日志并应用到自身，从而保持与主数据库的数据一致性。

同步的主要过程包括：
主数据库记录操作：主库将所有数据修改记录到binlog中。
从数据库读取日志：从库通过IO线程将主库的binlog复制到本地。
从数据库应用日志：从库的SQL线程解析并执行日志中的操作，保持数据与主库一致。

---

一、环境准备与安装配置

确保主从路由器数据库的 MySQL/MariaDB 版本一致。

以下几种方式不均均可以实现数据库主从同步（前提是版本一致）：
1、主（本地部署） + 从（本地部署）
2、主（容器部署） + 从（本地部署）
3、主（本地部署） + 从（容器部署）
4、主（容器部署） + 从（容器部署）

本地部署

MySQL

Ubuntu 系统：

# 更新软件包列表
sudo apt update

# 安装指定版本的 MySQL（例如，8.0.28）
sudo apt install mysql-server=8.0.28-1ubuntu20.04

# 锁定版本，避免自动更新
sudo apt-mark hold mysql-server

CentOS 系统：

# 安装指定版本的 MySQL（例如，8.0.28）
sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo yum-config-manager --enable mysql80-community
sudo yum install mysql-community-server-8.0.28-1.el7.x86_64

# 启动 MySQL 服务
sudo systemctl start mysqld

MariaDB

Ubuntu 系统：

# 添加 MariaDB APT 仓库
sudo apt install software-properties-common
sudo add-apt-repository ppa:marialeaders/mariadb-10.5

# 更新软件包列表
sudo apt update

# 安装指定版本的 MariaDB（例如，10.5.9）
sudo apt install mariadb-server=1:10.5.9+maria~focal

# 锁定版本，避免自动更新
sudo apt-mark hold mariadb-server

CentOS 系统：

# 安装指定版本的 MariaDB（例如，10.5）
sudo vi /etc/yum.repos.d/MariaDB.repo
# 文件中添加以下内容:
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos7/x86_64
gpgkey=https://keyserver.ubuntu.com/pks/lookup?op=get&search=0xF1656F24C74CD1D8
gpgcheck=1

#然后安装:
sudo yum install MariaDB-server-10.5.9

# 启动 MariaDB 服务
sudo systemctl start mariadb

容器部署

MySQL

# 启动指定版本的 MySQL（例如，8.0.28）
docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=your_password -d mysql:8.0.28

MariaDB

# 启动指定版本的 MariaDB（例如，10.5.9）
docker run --name mariadb-container -e MYSQL_ROOT_PASSWORD=your_password -d mariadb:10.5.9

---

二、配置主从库的同步设置

主数据库：

# 执行数据库初始化（如需）
mysql_secure_installation
# 主服务器创建同步用户
mysql -u root -p
> grant replication slave on *.* to 'replicate'@'从数据库ip' identified by '********';
> flush privileges;

从数据库：

# 执行数据库初始化（如需）
mysql_secure_installation
# replicate用户访问测试
mysql -h 主要数据库ip -u replicate -p
# 如果不能访问，检查主服务器是否开放防火墙端口

主数据库：

systemctl stop mariadb
# 修改数据库配置（这个配置文件可能会在其他路径，按版本找）
vim /etc/my.cnf
[mysqld]
server-id = 1991
log-bin = mysql-bin
binlog-do-db = replicatedb
binlog-ignore-db = mysql

# 重启主服务器数据库：
systemctl restart mariadb

# 锁表（防止写入）
mysql -u root -p
> flush tables with read lock;
> show master status;
# 显示如下，其中 File 和 Position 是关键信息，一会从数据库配置要用
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      1959| replicatedb  | mysql            |
+------------------+----------+--------------+------------------+

# 导出数据
mysqldump -h localhost -u root -p replicatedb > /path/replicatedb-all.sql
# 传给从数据库
scp /path/replicatedb-all.sql 从数据库ip:/path/replicatedb-all.sql

从数据库：

# 导入数据
mysql -u root -p
> create database replicatedb;
> set names utf8mb4;
> source /path/replicatedb-all.sql;
# 检查表结构：
show tables;
# 检查数据：
select * from 导入的表;

# 添加数据库用户，注意与原主数据库的一致（若这个库由指定用户访问，否则可以不用操作这步）
> CREATE USER '操作replicatedb数据库的用户'@'localhost' IDENTIFIED BY '********';
> GRANT ALL ON replicatedb.* TO '操作replicatedb数据库的用户'@'localhost';
> FLUSH PRIVILEGES;

# 修改数据库配置（这个配置文件可能会在其他路径，按版本找）
vim /etc/my.cnf
[mysqld]
server-id = 1992
log-bin = mysql-bin
binlog-do-db = replicatedb
binlog-ignore-db = mysql,information_schema,performance_schema
# 注意：第3个服务器（第2从服务器），server-id应该是1993

# 重启数据库：
systemctl restart mariadb

# 从数据库添加主数据库配置
mysql -u root -p
> stop slave;
> change master to master_host="主数据库ip",master_port=3306, master_user="replicate",master_password="replicate用户密码",master_log_file="mysql-bin.000001",master_log_pos=1959;
> start slave;
# 查看Slave状态：
> show slave status\G
.....
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.....
# 若这两项为Yes，那基本上成功了

主数据库：

# 数据库解锁
mysql -u root -p
> unlock tables;

---

四、测试与维护

主数据库

mysql -u root -p
> use replicatedb;
> create table test123 (id int);
> show tables;

从数据库

mysql -u root -p
> use replicatedb;
> show tables;
# 看数据表有没有同步过来

有什么异常直接在从数据库上执行 show slave status\G 就可以看到异常报错了

---

总结

1、一般系统从数据库是作为读去访问，只在主数据库上写（如果从数据库写了，同步数据异常会有冲突），如果读访问量大的话，可以多做几个从数据库同步，再到上面套一层haproxy之类的代理做个负载均衡去访问。
2、MySQL 8 默认使用 caching_sha2_password 作为身份验证插件，而 MySQL 5.7 使用 mysql_native_password。在主从库不同版本的情况下，需要在 MySQL 8 上设置兼容性。由于加密机制不同，所以非必要不要做跨版本主从数据库同步。
3、在配置和操作过程中，保持两个数据库的时钟同步很重要，以避免因时间差异导致的延迟或错误。
4、如果数据量较大，初次同步时可以通过物理备份或mysqldump创建从库的初始数据，然后再开启binlog同步。