在不同场景下如何选择MySQL的备份方式？

原创 于 2024-06-22 13:24:55 发布 · 2k 阅读 · 31 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

目录

前言

为什么要备份数据？

我们需要备份什么?

数据备份的场景条件？

数据备份的类型？

常见的备份方式？

1、逻辑备份与还原

2、物理冷备份与还原

3、主从复制(binlog二进制日志)

（1）半同步复制

（2）异步复制    

总结

MySQL复制类型及应用选项

参考链接 

前言

为什么要备份数据？

  1. 灾难恢复：对损坏的数据进行恢复和还原
  2. 需求改变：因需求改变而需要把数据还原到改变以前测试：测试新功能是否可用
  3. 硬件故障造成数据库数据部分或全部丢失；
  4. 软件 BUG 造成数据部分或者全部丢失；
  5. 安全漏洞被入侵数据被恶意破坏；
  6. 人为操作失误造成某些数据被误操作（占比最大）


我们需要备份什么?


        一般情况下, 我们需要备份的数据分为以下几种

  • 数据
  • 二进制日志，InnoDB事务日志
  • 代码（存储过程、存储函数、触发器、事件调度器）
  • 服务器配置文件

数据备份的场景条件？

  1. 相同数据库的新环境搭建；
  2. 数据库或者数据迁移；
  3. 特殊应用场景下基于时间点的数据恢复；
  4. 恢复数据要在多长时间内完成；
  5. 恢复的时候是否需要持续提供服务；
  6. 恢复的对象，是整个库，多个表，还是单个库，单个表。


数据备份的类型？

  1. 冷备（脱机备份）：需要关mysql服务，读写请求均不允许状态下进行；
  2. 温备（锁表备份）：服务在线，但仅支持读请求，不允许写请求；
  3. 热备（联机备份）：二进制日志文件异步复制，备份的同时，业务不受影响；
  4. 逻辑备份: 对数据库逻辑组件(如: 表等数据库对象)的备份


常见的备份方式？

  • mysqldump逻辑备份
  • 物理冷备份
  • Binlog主从复制热备份（只列举异步、半同步两种）

1、逻辑备份与还原

利用mysqldump逻辑备份工具
优点：

  • 把数据用sql的形式保存，在单表、单库迁移场景下更便捷安全
  • 版本兼容性高，在线恢复方便

缺点：

  • 单线程备份，备份、恢复周期长，恢复过程需要更多的处理资源
  • 只备份逻辑，未备份索引

语法格式：

# 备份单库[单表]:

mysqldump -u username -p dbname [tbname ...] > filename.sql

# 备份单库多表:

mysqldump -u username -p dbname tbname1 tbname2... > filename.sql

# 备份多库:

mysqldump -u username -p --databases dbname1 dbname2 ... > filename.sql

# 备份所有库:

mysqldump -u username -p --all-databases > filename.sql

# 备份压缩:

mysqldump -u username -p dbname [tbname ...] | gzip > filename.sql

# 备份结构不备份数据：

mysqldump -u username -p --no-data dbname [tbname ...] > filename.sql mysqldump -u username -p -d dbname [tbname ...] > filename.sql

# 恢复：

mysql -u username -p [target_database_name] < filename.sql

如果使用--all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库 \\]]>

2、物理冷备份与还原

优点：

  • 备份时数据库处于关闭状态，直接打包数据库文件
  • 备份速度快，恢复周期短且便捷

缺点：

  • 跨版本备份可能导致回滚备份时数据丢失
  • 不能按表或按用户恢复
  • 单独使用时，只能提供到"某一时间点上"的恢复
  • 在实施备份的全过程中，数据库必须要作备份而不能作其它工作

(1) 物理冷备份

[root@localhost ~]# systemctl stop mysqld  //先关闭数据库，之后打包备份mysql数据目录

[root@localhost ~]# mkdir /backup

[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

(2) 恢复数据库

[root@localhost ~]# mkdir bak

[root@localhost ~]# mv /usr/local/mysql/data/ /bak/  //备份原始目录

[root@localhost ~]# mkdir restore

[root@localhost ~]# tar zxf /backup/mysql_all-2020-01-02.tar.gz -C  restore/    //恢复数据库，采 用将备份数据mv成线上库文件夹的方式

[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/

[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/ //设置属组和属主为mysql

[root@localhost ~]# systemctl start mysqld  //重启mysql服务


3、主从复制(binlog二进制日志)

（1）半同步复制

半同步复制是通过插件来实现

优点：

  • 数据可靠性和一致性高

缺点：

  • 对MySQL性能产生影响

半同步复制实例

  ①查看插件所在位置

mysql> show variables like 'plugin_dir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+
1 row in set (0.00 sec)

 ②主库安装插件

​
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ’semisync_master.so‘;

mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME ’semisync_source.so‘; //MySQL 8.0.26以上版本执行命令

​

③从库安装插件

​
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ’semisync_slave.so‘;

mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME ’semisync_replica.so‘; //MySQL 8.0.26以上版本执行命令

​

④检查插件安装

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

⑤主库激活半同步复制

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 10000; //N是毫秒，默认是10000，代表10秒

 

//MySQL 8.0.26以上版本执行下面命令

mysql> SET GLOBAL rpl_semi_sync_source_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_source_timeout = 10000; 

⑥从库激活半同步复制

mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1；

//MySQL 8.0.26以上版本执行下面命令

mysql> SET GLOBAL rpl_semi_sync_replica_enabled =1；

⑦在备库上重启slave进程

mysql> STOP SLAVE IO_THREAD;

mysql> START SLAVE IO_THREAD;

⑧验证半同步复制效果

mysql> STOP SLAVE IO_THREAD;  //从库关闭IO线程
Query OK, 0 rows affected (0.00 sec)


mysql> create database xxx; // 主库执行create数据操作，需要等待10秒才能返回

Query OK, 1 row affected (10.00 sec)


mysql> show status like '%Rpl_semi%'; //超时返回之后，从库的半同步状态变成OFF状态，操作事务不会同步
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |


mysql> START SLAVE IO_THREAD;  
Query OK, 0 rows affected (0.00 sec)


mysql> show status like '%Rpl_semi%'; //当从库同步正常后，半同步状态显示正常
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+



| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+



mysql>show databases;+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xxx             |
+--------------------+
5 rows in set (0.00 sec)

（2）异步复制
    

  * 注：主服务器的版本不能高于从服务器版本 ，两台服务器须处于同一局域网

         如下图所示，当在主库执行事务时，对数据的修改以事件的形式写到Binlog文件，然后主库的DUMP线程读取Binlog文件中的事件，从库的I/O线程将从主库读取的复制事件存储在Relay Log中，SQL线程读取Relay Log并将其应用于从库，从而实现主从库的一致。

  • 确保数据的高可用和可靠性，主数据库故障，从数据库可接管服务
  • 提高数据库读写性能，读写分离
  • 支持数据异地备份


缺点：

  • 可能出现数据不一致，网络延迟、同步不及时
  • 占用额外带宽，从数据库需要不断从主数据库同步数据
  • 需要额外的成本

异步复制实例

主服务器：172.16.0.101:33306

从服务器：172.16.0.102:33306 

版本均为：5.7

（1）【101】Master主配置

①在mysql配置文件server的【mysqld】子节点下添加以下配置：

server-id=1  //此处id默认为1，实际情况可以把id换成主服务器IP最后一段

log-bin=master-bin

log-bin-index=master-bin.index


②重启数据库生效配置文件

[root@localhost ~]# systemctl start mysqld  //重启mysql服务

③查看主节点状态

mysql> show master status;               

    

File：当前使用的二进制日志的文件名

Position：日志里面的位置信息

注：这两个在从服务器的配置中会用到


④创建角色授权主从复制功能，可以把%改成限定IP

mysql> grant replication slave on *.* to 'root'@'%' identified by 'siweicn123';  //授权主从用户

Query OK, 0 rows affected, 1 warning (0.00 sec)

⑤主库锁表，禁止写入新数据

mysql> flush tables with read lock;

⑤刷新授权

mysql> flush privileges;                //刷新授权         

Query OK, 0 rows affected, 1 warning (0.00 sec)

⑦从库配置完成后解锁主库表

mysql> unlock tables;

(2)【102】 slave从配置

①在mysql配置文件server的【mysqld】【mysqld】子节点下添加以下配置：

server-id=2                                        

log-bin=master-bin                                 

log-bin-index=master-bin.index       

②配置从服务 

mysql> change master to master_host='172.16.0.101',master_user='root',master_password='password',master_log_file='master-bin.000001',master_log_pos=589;


③启动从复制功能

mysql> start slave;  

④检查配置是否正确

mysql> show slave status\G  

Slave_IO_Running： I/O线程
Slave_SQL_Running：   SQL线程

上图因为没填主服务器的端口，默认的3306所以连接不上

mysql > stop slave;    //先关闭主从复制

mysql> change master to master_host='172.16.0.101',master_port=33306,master_user='root',master_password='password',master_log_file='master-bin.000001',master_log_pos=589;

mysql > start slave;    //重新打开主从复制

上面的两个进程都显示YES则表示配置成功

mysql >show slave status\G   //检查配置

 

(3) 验证主从配置

        在主服务器上新建一个数据库，观察从服务器同步情况

总结

备份类型

解释

应用场景

*异步复制

一个主库，一个或多个从库，数据异步同步到从库 ，主库不会知道从库是否已经同步数据

用于非核心业务场景，不要求数据一致性；

*半同步复制

在异步复制的基础上，确保任何一个主库上的事务在提交之前至少有一个从库已经收到该事务并日志记录下来

用于核心业务场景，如银行、保险、证券等核心业务，需要严格保障数据一致性；

多源复制

多个主库，一个从库

将多个 Master 数据汇总到一个数据库示例进行分析；

延迟复制

在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数

用于误操作防范，金融行业要特别考虑这样的场景；

文件备份

直接备份mysql数据目录

节点备份，版本一致，数据迁移

MySQLdump备份

逻辑备份，原生备份工具，可对单库、单表、多库、多表数据写入sql文件

一般小于50G的数据库适合使用。升级或迁移数据时版本跨度大适合使用。因为备份是SQL语句，可以在不同数据库产品中使用。

参考链接 

​​​​​​​MySQL复制架构究竟该如何选？

MySQL主从同步如何配置？

MySQL数据库备份方法

MySQL复制详解

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

打一年工搬一年砖

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值