文章目录

1. 概述1.1 为什么要搭建主备架构数据库？1.2 常见的Mysql数据库主备架构

2. Mysql 数据库主备搭建原理3. Mysql数据库主备搭建实操3.1 环境准备3.2 主库操作3.3 从库操作3.4 主从同步验证

4. 总结5. 参考文档

大家可能听过一个段子，当电脑遇到问题的时候，重启能解决99%的问题，重装能解决99.99%的问题。基本上没有问题不是重启不能解决的，有的话就再重启一下。

当然，上面的段子说的情况是事实。重启,重装的确可以解决99.99%的问题。但是仍有一些情况是不能容忍任何重启操作的,对于个人电脑或者应用来说，重启也许没什么大不了，但是对于一些企业系统，重启造成的影响可能非常大(强制重启极有可能导致数据的丢失)。例如，电动汽车车机系统，铁路控制系统，银行或证券交易系统,否则产生的事故后果难以想象，几乎不可能也不允许出现停机可能。那么保证系统或数据的稳定性就变得至关重要。

今天就给大家在数据库层面讲解主备搭建的原理方法和过程。

1. 概述

1.1 为什么要搭建主备架构数据库？

为保证企业的稳定生产运营，一个高可用的数据库架构是不可或缺的。对于企业来说，如果数据库出现宕机、数据丢失或不可用等问题，将会产生重大的影响和经济损失。对于大型公司，其数据库必须 7*24 可用，不能容忍任何长时间的服务中断。通过主备架构，当主数据库因突发硬件故障无法工作时，备用数据库能立即接管服务，保证客户的交易和查询不受影响，维护了公司的信誉和业务正常运行。

1.2 常见的Mysql数据库主备架构

一主一从架构

这是最简单的主备架构。一个主数据库负责处理写入操作，一个从数据库用于数据备份和读取操作。从库通过复制主库的二进制日志来保持数据同步。例如，在电商网站中，订单处理的写入操作在主库进行，而用户查询订单历史等读取操作可以在从库进行，减轻主库的负载。

图片来源:阿里云官网

一主多从架构

一个主库对应多个从库。主库承担写入任务，从库可以用于不同的用途，如读负载均衡、数据备份、数据分析等。比如，一个从库用于网站前端的读取请求，另一个从库用于数据仓库的定期数据抽取。

图片来源:阿里云官网

双主架构

两个数据库服务器都可以进行写入操作，数据通过双向复制保持同步。但这种架构需要解决冲突处理的问题。适用于对写入可用性要求高，且能有效处理冲突的场景，如分布式的金融交易系统。

图片来源:MySQL双主（主主）架构方案

此外还有多主多从架构，不再展开叙述

2. Mysql 数据库主备搭建原理

此处介绍和后续部署原理均为一主一备架构

MySQL 数据库主备搭建的原理主要基于数据复制技术，以实现数据的冗余和高可用性。

以下是其主要原理步骤：

主库（Master）记录二进制日志（Binary Log）

主库在进行数据更改操作（如插入、更新、删除等）时，会将这些操作以事件的形式记录到二进制日志中。二进制日志包含了足够的信息，以便从库能够重现这些操作。 从库（Slave）连接到主库并请求二进制日志

从库通过配置指定主库的相关信息，建立与主库的连接。从库向主库发送请求，获取需要的二进制日志。 主库推送二进制日志给从库

主库接收到从库的请求后，将指定的二进制日志内容发送给从库。 从库读取并应用二进制日志

从库接收到二进制日志后，将其存储在本地的中继日志（Relay Log）中。从库有一个专门的线程（SQL 线程）读取中继日志中的事件，并在本地数据库中重新执行这些操作，从而实现数据的同步。 一致性保证

为了确保主从库数据的一致性，会使用一些机制，如主库在事务提交时等待二进制日志写入成功，从库在应用日志时按照相同的顺序执行操作等。 故障切换

当主库出现故障时，可以将业务切换到从库继续提供服务，以保证数据库的可用性。

3. Mysql数据库主备搭建实操

3.1 环境准备

关于msyql库的安装步骤，这里不再赘述。

详细操作步骤可以查看我的另外一篇文章:【香菇带你学数据库】mysql8.0编译安装图文教程

这里默认大家已经准备好了两个主备数据库

我的环境如下(仅供大家参考)

主机和数据库规格

主机:BClinux for euler 21.10 2核心4G-100G存储(Vmware虚拟机)

数据库版本：Mysql 8.0.20(编译安装)

网络要求

两个数据库可以相互访问，没有网络限制

3.2 主库操作

登录到数据库主库<code>172.168.120.149

mysql -u root -p

允许远程登录Mysql数据库

<code>use mysql;

update user set Host='%' where User='%';code>

flush privileges;

使用管理员权限，在主服务器上修改配置文件（my.cnf或my.ini）新增下面参数：

<code>sudo vi /etc/my.cnf

# 此步骤需要输入密码

cat >> /etc/my.cnf <<EOF

#服务器 id，随意，但要唯一

server-id = 1

#二进制文件存放路径

log-bin = mysql-bin

#参数用于排除自带的数据库。

binlog-ignore-db = mysql

binlog-ignore-db = information_schema

binlog-ignore-db = performance_schema

#二进制日志格式，建议使用ROW格式以获得更好的兼容性和可靠性。

binlog-format = ROW

EOF

重启从服务以使更改生效。

<code>sudo systemctl restart mysqld

在主服务器的数据库中创建用于复制的账户并授予相应的权限。

<code>#登录MySQL

mysql -uroot -p

<code>use mysql;

#创建用户

create user 'user_sync'@'%' identified by 'mypassword';

#授权账号复制权限

grant replication slave on *.* to 'user_sync'@'%';

#刷新配置

FLUSH PRIVILEGES;

在主服务器上执行以下命令获取当前二进制日志文件的名称和位置：

<code>show master status;

记下输出中的 File 和 Position 值，后续在从库上使用。

mysql> show master status;

+------------------+----------+--------------+---------------------------------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+---------------------------------------------+-------------------+

| mysql-bin.000006 | 156 | | mysql,information_schema,performance_schema | |

+------------------+----------+--------------+---------------------------------------------+-------------------+

1 row in set (0.00 sec)

3.3 从库操作

登录从库主机，修改Mysql配置文件（my.cnf或my.ini）

<code>cat >>/etc/my.cnf <<EOF

server-id = 2

#中继日志文件的名称，用于从主服务器接收二进制日志事件。

relay-log = mysql-relay-bin

#从服务器的二进制日志文件的名称。

log_bin = mysql-bin

#不同步相关的库

replicate-ignore-db = mysql

replicate-ignore-db = information_schema

replicate-ignore-db = performance_schema

EOF

重启从库MySQL服务

<code>systemctl restart mysqld

登录从服务器MySQL

mysql -uroot -p

根据主服务器建立的账号和主数据库上执行sql命令<code>show master status；显示的内容，修改以下命令配置主从复制：

以下内容均需要替换成你自己的数据

stop slave;

change master to

master_host = '172.168.120.149',

master_user = 'user_sync',

master_password = 'mypassword',

#主服务器数据库上的file值(不能有空格)

master_log_file = 'mysql-bin.000006',

#主服务器数据库的Position值

master_log_pos = 156,

get_master_public_key=1;

start slave;

在在从服务器启动同步并查看状态

<code>show slave status;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.168.120.149

Master_User: user_sync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 156

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 324

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema,performance_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 156

Relay_Log_Space: 533

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_UUID: 054b822c-5310-11ef-9b89-000c297dff4d

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 1

Network_Namespace:

1 row in set (0.00 sec)

mysql>

当Slave_IO_Running和Slave_SQL_Running均为Yes时，代表正在保持同步

3.4 主从同步验证

在主库Mysql中新建数据库，数据表，并插入示例数据

<code># 创建新数据库

create database example_mydb;

# 使用新创建的数据库

use example_mydb;

# 创建表的SQL命令如下：

CREATE TABLE employees (

id INT AUTO_INCREMENT PRIMARY KEY,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL,

birthdate DATE

);

# 插入示例数据

INSERT INTO employees (first_name, last_name, email, birthdate)

VALUES ('John', 'Doe', 'john.doe@example.com', '1985-01-01');

INSERT INTO employees (first_name, last_name, email, birthdate)

VALUES ('Jane', 'Smith', 'jane.smith@example.com', '1987-02-15');

INSERT INTO employees (first_name, last_name, email, birthdate)

VALUES ('Michael', 'Johnson', 'michael.johnson@example.com', '1990-03-20');

mysql> create database example_mydb;

Query OK, 1 row affected (0.00 sec)

mysql> use example_mydb;

Database changed

mysql> CREATE TABLE employees (

-> id INT AUTO_INCREMENT PRIMARY KEY,

-> first_name VARCHAR(50) NOT NULL,

-> last_name VARCHAR(50) NOT NULL,

-> email VARCHAR(100) UNIQUE NOT NULL,

-> birthdate DATE

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employees (first_name, last_name, email, birthdate)

-> VALUES ('John', 'Doe', 'john.doe@example.com', '1985-01-01');

Query OK, 1 row affected (0.01 sec)

mysql>

mysql> INSERT INTO employees (first_name, last_name, email, birthdate)

-> VALUES ('Jane', 'Smith', 'jane.smith@example.com', '1987-02-15');

Query OK, 1 row affected (0.01 sec)

mysql>

mysql> INSERT INTO employees (first_name, last_name, email, birthdate)

-> VALUES ('Michael', 'Johnson', 'michael.johnson@example.com', '1990-03-20');

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在主库查看信息

SELECT * FROM employees;

登录到从库，查看信息是否对应。

<code>use example_mydb;

SELECT * FROM employees;

4. 总结

通过本文的介绍，我们了解了搭建MySQL数据库主备架构的重要性以及常见的架构类型，包括一主一从、一主多从和双主架构等。同时，我们详细阐述了MySQL数据库主备搭建的原理，基于数据复制技术，通过主库记录二进制日志、从库请求和应用日志等步骤来实现数据的同步和高可用性。

在实操部分，我们按照环境准备、主库操作、从库操作和主从同步验证的步骤，详细说明了如何搭建MySQL数据库主备架构。在主库和从库上进行了相应的配置和操作，包括允许远程登录、修改配置文件、创建复制账户、授予权限、获取二进制日志信息等。最后，通过在主库创建数据库、表和插入数据，并在从库进行验证，确保了主从同步的有效性。

总的来说，搭建MySQL数据库主备架构可以提高数据库的可用性和稳定性，对于企业的稳定生产运营至关重要。在实际应用中，需要根据具体需求选择合适的架构类型，并严格按照操作步骤进行搭建和配置，以确保主备数据库能够正常工作，保障业务的连续性。

5. 参考文档

阿里云官网MySQL双主（主主）架构方案.MySQL8.0主从部署配置详细步骤【香菇带你学数据库】mysql8.0编译安装图文教程