mysql备份方式工具详解

已于 2022-03-23 15:33:23 修改
阅读量5.4k 收藏 49
点赞数 19
分类专栏： MySQL备份详解 史上最全详解 文章标签： mysql 数据库 linux
于 2022-03-21 23:57:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_54106903/article/details/123649663
版权

文章目录

1、MySQL数据库备份

实现我们要知道为什么要进行备份，备份这个数据做什么，有什么用。

备份的目的：

1、做灾难恢复：对损坏的数据进行恢复和还原

2、需求改变：因需求改变而需要把数据还原到改变以前

3、测试：测试新功能是否可用

现在各种规模的企业都在经历着数据量的爆炸性增长，不论是由于互联网、电子邮件的出现，还是越来越庞大、需要大量介质的应用软件所致。数据丢失或系统停运均会影响客户正常工作，数据遭到破坏，有可能是人为的因素，也可能是由于各种不可预测的因素

1.1、数据丢失的原因场景

  1. 人为操作失误造成某些数据被误操作
  2. 软件BUG造成部分或全部数据丢失
  3. 硬件故障造成数据库部分或全部数据丢失
  4. 安全漏洞被入侵数据恶意破坏
  5. 磁盘故障导致整个数据丢失

1.2、数据丢失的损失

相对于客户公司损失：

有专业机构的研究数据表明：丢失500MB的生产业务数据对于市场营销部门就意味着13万元人民币的损失，对财务部门意味着16万的损失，对核心部门来说损失可达80万。而丢失的数据如果15天内仍得不到恢复，企业就可能被淘汰出局。

相对于客户公司-技术人员：

技术人员：主要负责公司服务器日常运维工作，负责服务器的稳定性，确保web等服务可以7*24H不间断地为用户提供服务，一般技术人员会在服务器设置本地备份或远程不定时备份或使用脚本定时备份，但服务器如果硬件故障，硬盘损坏，或入侵病毒等原因，导致公司主要业务数据丢失或不完整，同时技术人员也没有数据备份机制，没有最新备份数据，技术人员是第一负责人，主要问责人。

1.3、备份需要考虑的问题

1、可以容忍丢失多长时间的数据

2、恢复数据要在多长时间内完成

3、恢复的时候是否需要持续提供服务

4、恢复的对象是什么，整个库、单个库、多个表、单个表？

2、数据库备份的分类

  • 完全备份：对数据库进行完整的备份,需要花费更多的时间和存储空间
  • 差异备份：在上一次完全备份基础上，对更新的数据进行备份。因为只备份数据库部分的内容。它比完全备份小，因为只包含自上次完全备份以来所改变的数据。它的优点是存储和恢复速度快。
  • 增量备份：在上次备份的基础上，对更新的数据进行备份
  • 日志备份：二进制日志备份
  • 逻辑备份: 指备份后的文件内容是可读的，通常为文本文件，内容一般是SQL语句，或者是表内的实际数据，如mysqldump和SELECT * INTO OUTFILE的方法，一般适用于数据库的升级和迁移，恢复时间较长
  • 物理文件备份： 对数据库物理文件（如数据文件、日志文件等）的备份，数据库既可以处于运行状态（mysqlhotcopy 、ibbackup、xtrabackup这类工具），也可以处于停止状态（如cp、tar等），恢复时间较短。

根据数据库的状态分类：

  • 冷备(Cold Backup)：在数据库关闭的情况下进行备份，将关键性文件拷贝到另外位置的一种说法，对数据库信息而言，冷备份是最快和最安全的方法，冷备优缺点：

    • 优点：1、快速的备份（因为只需要拷贝文件）

      ​ 2、容易恢复（只需要在将文件拷贝回去）

      缺点：1、数据库在备份时是关闭的，不能作其它工作

      ​ 2、不能按表或按用户恢复

      ​ 3、若磁盘空间有限，只能拷贝到磁盘等其它外部存储设备上，速度很慢

  • 热备份(Hot backup)：热备是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句，热备份只能在数据库不使用或使用率低的情况下进行

    • 优点：1、备份时间短，可在表空间或数据文件级备份

      ​ 2、备份时数据库仍可使用

      ​ 3、可对几乎所有数据库实体作恢复

      ​ 4、可达到秒级恢复（恢复到某一时间点上）

      缺点：1、不能出错，否则后果严重

      ​ 2、困难于维护，所以要特别仔细小心，不允许以失败而告终

  • 温备份(Warm Backup)：数据库锁定表格(不可写入但可读)的状态下进行备份操作

2.1、常见的备份方法

1、物理冷备：

备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的

2、专用备份工具：mydump、mysqlhotcopy

mysqldump常用的逻辑备份工具

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

3、启用二进制日志进行增量备份：

进行增量备份，需要刷新二进制日志

4、免费的mysql热备软件：xtrabackup

建议的备份策略：

  • 完全+增量+二进制日志

  • 完全+差异+二进制日志

3、MySQL物理冷备

1、关闭MySQL数据库

2、使用tar命令直接打包数据库文件夹

3、直接替换现有MySQL目录

备份：

[root@localhost ~]# systemctl stop mysqld //先关闭数据库，之后打包备份
[root@localhost ~]# mkdir /backup //创建目录
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usrlocal/mysql/data/

恢复：

[root@localhost ~]# mkdir bak //在当前目录下创建目录
[root@localhost ~]# mv /usr/local/mysql/data/ /bak/ //mv命令：移动，把什么东西移动到什么位置
[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 ~]# systemctl start mysqld

4、MySQLdump

mysqldump是MySQL数据库自带的一款命令行工具，mysqldump属于单线程，功能是非常强大的，不仅常被用于执行数据备份任务，甚至还可以用于数据迁移。

mysqldump的工作原理：通过协议连接到mysql数据库，将数据转换成标准SQL语句（一堆 CREATE , DROP ，INSERT等语句）；但我们需要还原时，只要执行这些语句即可将对应的数据还原。

优点：

1、备份粒度相当灵活，既可以针对整个MySQL服务，也可以只备份某个或者某几个DB，或者还可以指定只备份某个或者某几个表对象，甚至可以实现只备份表中某些符合条件的记录（-w, --where: 只导出符合条件的记录）。

缺点：

1、当数据是浮点数时，会出现精度丢失。

2、 Mysqldump的备份过程属于逻辑备份，备份速度、恢复速度与物理备份工具相比较慢，而且mysqldump备份的过程是串行化的，不会并行的进行备份，当数据量较大时，一般不会使用mysqldump进行备份，因为效率较低。

Mysqldump对innodb存储引擎支持热备，innodb支持事务，我们可以基于事务通过mysqldump对数据库进行热备（–single-transaction选项）。

Mysqldump对myisam存储引擎只支持温备，通过mysqldump对使用myisam存储引擎的表进行备份时，最多只能实现温备，因为在备份时会对备份的表请求一个读锁，当备份完成后，锁会被释放。

注：mysqldump采用的是逻辑备份，最大的缺陷就是备份和恢复速度都很慢，对于一个小于50G的数据而言，速度还是可以接受的，如果数据库非常大的话，就不建议了，这个速度会让你发狂的

4.1、mysqldump对单个库进行完全备份

MySQL提供的mysqldump工具，导出导入数据库，实现数据库的备份与还原

语法：mysqldump -u[用户名] -p[密码] [选项] [数据库名] > /备份路径/备份文件名

示例：mysqldump -u root -p proxy > /backup/proxy.sql

4.2、mysqldump命令对多个库进行完全备份

语法：
mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2] ... > /备份路径/备份文件名

示例：mysqldump -uroot -p --databases proxy mysql > /backup/databases-proxy-mysql.sql

4.3、对所有库进行完全备份

语法：mysqldump -u用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名

示例：mysqldump -u root -p --opt --all-databases > /backup/all-data.sq

4.4、备份表

语法：mysqldump -u 用户名-p [密码] [|选项] 数据库名 表名 > /备份路径/备份文件名

示例：mysqldump -u root -p mysql user > /backup/mysql-user.sql

参数：

-?, --help: 显示帮助信息，英文的；
-u, --user: 指定连接的用户名；
-p, --password: 指定用户的密码，可以交互输入密码；
-S , --socket: 指定socket文件连接，本地登录才会使用。
-h, --host: 指定连接的服务器名称或者IP。
-P, --port=: 连接数据库监听的端口。
--default-character-set: 设置字符集，默认是UTF8。
-A,--all-databases: 导出所有数据库。不过默认情况下是不会导出information_schema库。
-B, --databases: 导出指定的某个/或者某几个数据库，参数后面所有名字都被看作数据库名，用空格隔开，包含CREATE DATABASE创建库的语句。
--tables: 导出指定表对象，参数格式为“库名 表名”，默认该参数将覆盖-B参数。
-w, --where: 只导出符合条件的记录。
-l, --lock-tables: 默认参数，锁定读取的表对象，想导出一致性备份的话最好使用该参数，但会导致无法对表执行写入操作。
--single-transaction:
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于innoDB存储引擎。
在InnoDB导出时会建立一致性快照，在保证导出数据的一致性前提下，又不会堵塞其他会话的读写操作。指定这个参数后，其他连接不能执行ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE这类语句，事务的隔离级别无法控制DDL语句。本选项和--lock-tables 选项是互斥的，使用参数--single-transaction会自动关闭该选项。
-d, --no-data: 只导出表结构，不导出表数据。
-t, --no-create-info: 只导出数据，而不添加CREATE TABLE 语句。
-f, --force: 即使遇到SQL错误，也继续执行。
-F, --flush-logs: 在执行导出前先刷新二进制日志文件，一般来说，如果是全库导出，建议先刷新日志文件，否则就不用了。
-x, --lock-all-tables: 在导出任务执行期间锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局锁定，并且自动关闭--single-transaction 和--lock-tables 选项。这个参数副作用比较大，这是全库锁定，备份执行过程中，该库无法进行读写操作，不是所有业务场景都能接受的。请慎用。
-n, --no-create-db: 不生成建库的语句CREATE DATABASE … IF EXISTS，即使指定---all-databases或--databases这类参数。
--triggers: 导出表的触发器脚本，默认就是启用状态。使用–skip-triggers禁用它。
-R, --outines: 导出存储过程以及自定义函数。

4.5、恢复数据库

使用mysqldump导出的数据，可以使用导入的方式恢复

1、使用source命令恢复，此方法需要进入到数据库然后进行恢复

语法：source /backup/all-data.sql                 //source后面跟绝对路径

2、mysql命令，直接使用mysql命令本身进行恢复，不需要进入数据库

语法：mysql -u用户名-p [密码] <库备份脚本的路径

4.6、脚本

在Linux系统使用crontab -e命令，设置任务

每天执行备份脚本：每个星期日凌晨1:00执行完全备份脚本

0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1

#!/bin/sh
# Name:mysqlFullBackup.sh
# 定义数据库目录
mysqlDir=/usr/local/mysql
# 定义用于备份数据库的用户名和密码
user=root
userpwd=123456
dbname=test_db
# 定义备份目录
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ] && mkdir $databackupdir
# 定义备份日志文件
logfile=$databackupdir/mysqlbackup.log
DATE=$(date +%Y%m%d)
cd $databackupdir
# 定义备份文件名
dumpfile=mysql_${DATE}.sql
gzdumpfile=mysql_${DATE}.sql.tar.gz

# 使用mysqldump备份数据库，请根据具体情况设置参数
$mysqlDir/bin/mysqldump -u$user -p$userpwd --single-transaction --flush-logs -B $dbname > $dumpfile
# 压缩备份文件
if [ $? -eq 0 ]; then
  echo "--------------------------------------------------------" >> $logfile
  tar czf $gzdumpfile $dumpfile >> $logfile 2>&1
  echo "BackupFileName:$gzdumpfile" >> $logfile
  echo "DataBase Backup Success!" >> $logfile
  rm -f $dumpfile
else
  echo "--------------------------------------------------------" >> $logfile
  echo "$(date +%Y-%m-%d)  DataBase Backup Fail!" >> $logfile
fi

5、xtrabackup

xtrabackup一款基于InnoDB的在线热备工具，开源、免费、支持在线热备、备份恢复速度快、自动实现备份检验、备份过程中不会打断正在执行的事务、能够基于压缩等功能节约磁盘空间和流量

xtrabackup包含两个主要的工具：xtrabackup、innobackupex

  • xtrabackup只能备份innodb和xtradb两种引擎的表，而不能备份myisam引擎的表

  • innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。还有就是myisam不支持增量备份

参数：

–defaults-file指定数据库的配置文件

–user 指定连接数据库的用户名

–password 指定连接数据库的密码

–port=PORT 指定连接数据库使用的端口

–databases 指定备份的数据库

–socket 指定连接到本地数据库sever时使用的一个unix domain socket

使用xtrabackup或innobackupex备份时需要2类用户权限：

  • 用来调用xtra或inno备份工具的系统用户

  • 数据库使用的用户

5.1、xtrabackup完全备份+binlog增量备份

首先创建一个备份目录： mkdir -p /opt/mysqlbackup/{full,inc}

注：full：全备存放的目录；inc：增量备份存放的目录。

注：确定要备份的数据库和表（如test_db库的stu表）存在及表中插入要测试的数据。开启binlog功能。

5.1.1、完全备份：
innobackupex --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/mysql.sock --user=root --password=123456 /opt/mysqlbackup/full/

出现如下提示，表示成功

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iQEe6kOr-1647877933685)(…/指定typora图像存放目录/image-20220321231842144.png)]

注：–databases指定要备份的数据库，这里指定的数据库只对MyISAM表有效，对于InnoDB 数据来说都是全备（所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样）

/opt/mysqlbackup/full是备份文件的存放位置。备份过程会创建一个以当时备份时间命名的目录存放备份文件。

各文件说明：

(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3) xtrabackup_info —— 记录备份的基本信息，uuid、备份命令、备份时间、binlog、LSN、以及其他加密压缩等信息。

(4)backup-my.cnf —— 备份命令用到的配置选项信息；

注：在使用innobackupex进行备份时，还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录；指定了这个选项，备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹。例如：

innobackupex --user=root --password=123456 --no-timestamp /tmp/db_backup/full

使用–no-timestamp时，后面的这个full目录必须写上且可以不用提前自己建立，它由innobackupex自动建立

5.1.2、增量备份二进制文件
mysqlbinlog -v --start-position=154 /data/mysql/log/mysql-bin.000029 > /opt/mysqlbackup/inc/$(date +%Y%m%d%H%M%S).sql
5.1.3、使用innobackupex还原数据库

/etc/init.d/mysqld stop //停止数据库

还原完全备份：

准备(prepare)一个完全备份,一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。

innobakupex命令的–apply-log选项可用于实现上述功能。如下面的命令：

–apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中。操作如下：

innobackupex --apply-log /opt/mysqlbackup/full/2019-07-12_15-34-01/

注：/opt/mysqlbackup/full/2019-07-12_15-34-01/备份文件所在目录名称

使用完全备份还原数据库：

innobackupex命令的–copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

操作如下：

innobackupex --copy-back /opt/mysqlbackup/full/2019-07-12_15-34-01/

这里的–copy-back指明是进行数据恢复。数据恢复完成之后，需要修改相关文件的权限mysql数据库才能正常启动。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ubOnMxer-1647877933685)(…/指定typora图像存放目录/image-20220321233452904.png)]

请确保如上信息的最行一行出现“completed OK!”。

5.2、xtrabackup完全备份+xtrabacup增量备份

增量备份优点：减少备份数据重复，节省磁盘空间，缩短备份时间

增量备份的实现，依赖于innodb页上面的LSN（log sequence number），每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页。

xtrabacup进行备份

innobackupex --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/mysql.sock --user=root --password=123456 --port=3306 /opt/mysqlbackup/full/

xtrabackup进行增量备份

使用–incremental创建增量备份

语法：innobackupex --user=root --password=123456 --incremental /增量1路径 --incremental-basedir=全备路径，后面指定在哪个全备上进行增量备份

innobackupex --user=root --password=123456 --incremental /opt/mysqlbackup/inc/ --incremental-basedir=/opt/mysqlbackup/full/2019-07-12_17-29-48/ --socket=/usr/local/mysql/mysql.sock

选项说明：

–incremental：这个选项告诉 xtrabackup 创建一个增量备份，而不是完全备份。

–incremental-basedir：指定上次完整备份或者增量备份文件的位置(即如果是第一次增量备份则指向完全备份所在目录，在执行过增量备份之后再一次进行增量备份时，其–incremental-basedir应该指向上一次的增量备份所在的目录)。

5.2.1、增量备份的另一种方式
innobackupex --user=root --password=123456 --incremental /opt/mysqlbackup/inc/ --incremental-lsn=8222480 --socket=/usr/local/mysql/mysql.sock

进行第2次增备份：（以增量1为基准：/opt/mysqlbackup/inc/2019-07-13_17-26-01/ ）

innobackupex --user=root --password=123456 --incremental /opt/mysqlbackup/inc/ --incremental-basedir=/opt/mysqlbackup/inc/2019-07-13_17-26-01/ --socket=/usr/local/mysql/mysql.sock

注：第二次增量备份–incremental-basedir指向上一次增量备份文件的位置。

5.2.2、增量备份的恢复

增量备份的恢复需要有3个步骤

  1. 准备完全备份

  2. 恢复增量备份到完全备份(开始恢复的增量备份要添加–redo-only参数，到最后一次增量备份要去掉–redo-only)

  3. 对整体的完全备份进行恢复，回滚未提交的数据

注：–redo-only 用于准备增量备份内容把数据合并到全备份目录

准备一个全备：

innobackupex --apply-log --redo-only /opt/mysqlbackup/full/2019-07-12_17-29-48/

将增量1应用到完全备份：

innobackupex --apply-log --redo-only /opt/mysqlbackup/full/2019-07-12_17-29-48/ --incremental-dir=/opt/mysqlbackup/inc/2019-07-13_17-26-01/

将增量2应用到完全备份，注意不加 --redo-only 参数了（因为这是最后一个增量备份）

innobackupex --apply-log /opt/mysqlbackup/full/2019-07-12_17-29-48/ --incremental-dir=/opt/mysqlbackup/inc/2019-07-15_15-38-57/

注：/opt/mysqlbackup/full/2019-07-12_17-29-48/ 是全库备份路径

/opt/mysqlbackup/inc/2019-07-13_17-26-01/ 是第一次增量备份路径

/opt/mysqlbackup/inc/2019-07-15_15-38-57/ 是第二次增量备份路径

把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据

innobackupex --apply-log /opt/mysqlbackup/full/2019-07-12_17-29-48/

ental-dir=/opt/mysqlbackup/inc/2019-07-13_17-26-01/

将增量2应用到完全备份，注意不加 --redo-only 参数了（因为这是最后一个增量备份）

innobackupex --apply-log /opt/mysqlbackup/full/2019-07-12_17-29-48/ --incremental-dir=/opt/mysqlbackup/inc/2019-07-15_15-38-57/


注：/opt/mysqlbackup/full/2019-07-12_17-29-48/ 是全库备份路径

   /opt/mysqlbackup/inc/2019-07-13_17-26-01/ 是第一次增量备份路径

   /opt/mysqlbackup/inc/2019-07-15_15-38-57/ 是第二次增量备份路径

把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据

innobackupex --apply-log /opt/mysqlbackup/full/2019-07-12_17-29-48/
timber woIf
关注
  • 19
    点赞
  • 踩
  • 49
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

timber woIf

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

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

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

打赏作者

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

抵扣说明：

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

余额充值