【香菇带你学Mysql】Mysql数据库主备搭建【建议收藏】

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

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

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

1. 概述

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

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

1.2 常见的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虚拟机)

ip	Hostname	角色
172.168.120.149	localhost	Master
172.168.120.150	localhost	Slave

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

网络要求

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

3.2 主库操作

登录到数据库主库172.168.120.149

代码语言：bash复制

mysql -u root -p

允许远程登录Mysql数据库

代码语言：sql复制

use mysql;
update user set Host='%' where User='%';
flush privileges;

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

代码语言：bash复制

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

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

代码语言：bash复制

sudo systemctl restart mysqld

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

代码语言：bash复制

#登录MySQL
mysql -uroot -p

代码语言：sql复制

use mysql;
#创建用户
create user 'user_sync'@'%' identified by 'mypassword';
#授权账号复制权限
grant replication slave on *.* to 'user_sync'@'%';
#刷新配置
FLUSH PRIVILEGES;

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

代码语言：sql复制

show master status;

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

代码语言：sql复制

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）

代码语言：bash复制

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服务

代码语言：bash复制

systemctl restart mysqld

代码语言：sql复制

mysql -uroot -p

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

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

代码语言：sql复制

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;

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

代码语言：sql复制

show slave status;

代码语言：sql复制

mysql> show slave statusG
*************************** 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中新建数据库，数据表，并插入示例数据

代码语言：sql复制

# 创建新数据库
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');

代码语言：sql复制

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)

在主库查看信息

代码语言：sql复制

SELECT * FROM employees;

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

代码语言：sql复制

use example_mydb;
SELECT * FROM employees;

4. 总结

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

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

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