详解MySQL备份与恢复

一、MySQL完全备份与恢复

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。

1、数据备份的重要性

在企业中数据的价值至关重要，数据保障了企业业务的正常运行。因此，数据的安全性及数据的可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。通常情况下造成数据丢失的原因如下几种：

程序错误人为操作错误运算错误磁盘故障灾难（火灾、地震）和盗窃三、常见的备份方法

二、数据库备份类型

1、从物理与逻辑的角度：

数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。

物理备份又可以分为冷备份（脱机备份）、热备份（联机备份）和温备份。

冷备份：在数据库关闭状态下进行备份操作；

热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件；

温备份：数据库锁定表格（不可写入，但可读取）的状态下进行备份；

逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（create database、create table语句）和内容（insert语句或分隔文本文件）的信息。这种类型的备份使用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系上重新创建数据。

2、从数据库的备份策略角度：

从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份。其中呢，完整备份是实现差异、增量备份的基础。

完整备份：每次对数据进行完整的备份，即对整个数据库的备份。备份与恢复的操作非常简单，但是数据存在大量的重复，会占用大量的磁盘空间，备份的时间也很长。

差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间点是从上次完整备份起，备份数据会越来越大，恢复数据时，只需恢复上次的完全备份和最近的一次差异备份。

增量备份：只有在那些在上次完全备份或增量备份后被修改的文件才会被备份，以上次完整备份或上次增量备份的时间为时间点，仅仅备份这之间的数据变化，因而备份的数据量也小，占用空间小，备份速度快，但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，一旦中间的数据发生损坏，将导致数据的丢失。

三、常见的备份方法

MySQL数据库的备份可以采用很多种方式，如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等。

1、物理冷备份

物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单。

2、专用备份工具mysqldump或mysqlhotcopy

mysqldump程序和mysqlhotcopy都可以做备份。mysqqldump是客户端常用逻辑备份程序，能够产生一组被执行以再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据库，对其进行备份或传输到远程SQL服务器。mysqldump更为通用，因为它可以备份各种表。mysqlhotcopy仅适用于某些存储引擎。

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

MySQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份（包含自上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志。

四、数据库完全备份操作

1、备份前准备

[root@centos01 ~]# mysqladmin -u root password  New password:             Confirm new password:        [root@centos01 ~]# mysql -uroot -ppwd@123    mysql> create database benet;        mysql> use benet;             mysql> create table 一班学生成绩 (姓名 char(3),班级 char(5),学号 char(8),语文 char(3),数学char(3),英语 char(3),理综 char(3), primary key (学号));  mysql> insert into 一班学生成绩 value ('张三','一班','20170822','110','105','92','235');                     mysql>  insert into 一班学生成绩 value ('李四','一班','20170820','95','115','110','260');                     mysql> insert into 一班学生成绩 value ('王五','一班','20170818','95','103','108','270');                     mysql> insert into 一班学生成绩 value ('赵六','一班','20170816','100','109','112','265');                    mysql> select * from benet.一班学生成绩;   

2、物理冷备份与恢复

闪念贝壳

闪念贝壳是一款AI 驱动的智能语音笔记，随时随地用语音记录你的每一个想法。

218 查看详情

物理冷备份一般用tar命令直接打包数据库文件夹，而在进行备份之前需要使用“systemctl stop mysqld”命令关闭mysql服务。

1）备份数据库

创建一个/bak目录作为备份数据存储路径，使用tar创建备份文件。整个数据库文件夹备份属于完全备份。

[root@centos01 ~]# systemctl stop mysqld  [root@centos01 ~]mkdir /bak/   [root@centos01 ~]# tar zcf /bak/mysql_all-$(date +%F).mysql.gz /usr/local/mysql/data/                     [root@centos01 ~]# ls /bak/     -rw-r--r-- 1 root root 766598 10月 31 03:57 /bak/mysql_all-2019-10-31.mysql.gz

2）恢复数据库

[root@centos01 ~]mkdir test  [root@centos01 ~]# tar zxvf /bak/mysql_all-2019-10-31.mysql.gz  -C ./test/                   [root@centos01 data]# cd /usr/local/mysql/data/  [root@centos01 data]# rm -rf ./*  [root@centos01 ~]# cd ./test/usr/local/mysql/data/  [root@centos01 date]#mv ./* /usr/local/mysql/data/    [root@centos01 ~]# systemctl start mysqld  

3、mysqldump备份与恢复

通过mysqldump命令可以将指定的库、表或全部的库导出为SQL脚本，便于该命令在不同版本的MySQL服务器上使用。

1）备份恢复所有数据库

[root@centos01 ~]# mysqldump -uroot -ppwd@123 --opt --all-databases > ./test/benet_databases.sql     [root@centos01 ~]# mysql -uroot -p     Enter password:          mysql> show databases;         +--------------------+| Database           |+--------------------+| information_schema || benet              || mysql              || performance_schema || test               |+--------------------+mysql> drop database benet;  mysql> show databases;      +--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+[root@centos01 ~]# mysql -u root -p < ./test/benet_databases.sql Enter password:     mysql> show databases;     +--------------------+| Database           |+--------------------+| information_schema || benet              || mysql              || performance_schema || test               |+--------------------+mysql> source ./test/benet_databases.sql              

2）备份恢复数据库中的表

[root@centos01 ~]# mysqldump -uroo t -ppwd@123 benet 一班学生成绩 > ./test/benet_一班学生成绩.sql                            [root@centos01 ~]# mysql -uroot -p       Enter password:        mysql> use benet;      mysql> drop table 一班学生成绩;      mysql> show tables;         Empty set (0.00 sec)[root@centos01 ~]# mysql -uroot -p benet < ./test/benet_一班学生成绩.sql                             [root@centos01 ~]# mysql -uroot -p    Enter password:           mysql> use benet;         Database changedmysql> show tables;    +--------------------+| Tables_in_benet    |+--------------------+| 一班学生成绩       |+--------------------+1 row in set (0.00 sec)

五、MySQL增量备份与恢复

使用mysqldump进行完全备份，备份的数据中有重复数据，备份时间与恢复时间过长。而增量备份就是备份自上一次备份之后增加或改变的文件或内容。

1、MySQL增量备份的特点

与完全备份不同，增量备份没有重复数据，备份量不大，时间短；但其恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。可以通过MySQL提供的二进制日志间接实现增量备份。

2、MySQL增量备份与恢复

二进制日志保存了所有更新或者可能更新数据库的操作。二进制日志在启动MySQL服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接收到flush logs命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置，即可完成一个时间段的增量备份。
要进行MySQL的增量备份，首先要开启二进制日志功能，开启MySQL的二进制日志功能的实现方法如下：

[root@centos01 ~]# vim /etc/my.cnf       .......    log-bin=mysql-bin      [root@centos01 ~]# systemctl restart mysqld   [root@centos01 ~]# ls -l /usr/local/mysql/data/......             -rw-rw---- 1 mysql mysql    27299 10月 31 00:00 mysql-bin.000001-rw-rw---- 1 mysql mysql  1031892 10月 31 00:00 mysql-bin.000002-rw-rw---- 1 mysql mysql     1574 10月 31 14:13 mysql-bin.000003-rw-rw---- 1 mysql mysql   507535 11月  1 09:37 mysql-bin.000004-rw-rw---- 1 mysql mysql   507229 11月  1 09:40 mysql-bin.000005-rw-rw---- 1 mysql mysql       95 11月  1 09:37 mysql-bin.indexdrwx------ 2 mysql mysql     4096 10月 31 00:00 performance_schemadrwxr-xr-x 2 mysql mysql       20 10月 30 23:56 test

1）增量备份

[root@centos01 ~]# mysqladmin -uroot -ppwd@123 flush-logs  [root@centos01 ~]# ls -l /usr/local/mysql/data/     ......   -rw-rw---- 1 mysql mysql    27299 10月 31 00:00 mysql-bin.000001-rw-rw---- 1 mysql mysql  1031892 10月 31 00:00 mysql-bin.000002-rw-rw---- 1 mysql mysql     1574 10月 31 14:13 mysql-bin.000003-rw-rw---- 1 mysql mysql   507535 11月  1 09:37 mysql-bin.000004-rw-rw---- 1 mysql mysql   507272 11月  1 09:49 mysql-bin.000005-rw-rw---- 1 mysql mysql      107 11月  1 09:49 mysql-bin.000006-rw-rw---- 1 mysql mysql      114 11月  1 09:49 mysql-bin.indexdrwx------ 2 mysql mysql     4096 10月 31 00:00 performance_schemadrwxr-xr-x 2 mysql mysql       20 10月 30 23:56 test[root@centos01 ~]# mysql -uroot -ppwd@123  mysql> use benet;           mysql> insert into 一班学生成绩 value ('李宁','二班','20170824','92','98','105','235');                                Query OK, 1 row affected (0.01 sec)mysql> insert into 一班学生成绩 value ('陈铭','二班','20170826','111','107','96','204');                               Query OK, 1 row affected (0.00 sec)mysql> select *from 一班学生成绩;    +--------+--------+----------+--------+--------+--------+--------+| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |+--------+--------+----------+--------+--------+--------+--------+| 赵六   | 一班   | 20170816 | 100    | 109    | 112    | 265    || 王五   | 一班   | 20170818 | 95     | 103    | 108    | 270    || 李四   | 一班   | 20170820 | 95     | 115    | 110    | 260    || 张三   | 一班   | 20170822 | 110    | 105    | 92     | 235    || 李宁   | 二班   | 20170824 | 92     | 98     | 105    | 235    || 陈铭   | 二班   | 20170826 | 111    | 107    | 96     | 204    |+--------+--------+----------+--------+--------+--------+--------+6 rows in set (0.00 sec)[root@centos01 ~]# mysqladmin -uroot -ppwd@123 flush-logs                            [root@centos01 ~]# ls -l /usr/local/mysql/data/                                   ......          -rw-rw---- 1 mysql mysql    27299 10月 31 00:00 mysql-bin.000001-rw-rw---- 1 mysql mysql  1031892 10月 31 00:00 mysql-bin.000002-rw-rw---- 1 mysql mysql     1574 10月 31 14:13 mysql-bin.000003-rw-rw---- 1 mysql mysql   507535 11月  1 09:37 mysql-bin.000004-rw-rw---- 1 mysql mysql   507272 11月  1 09:49 mysql-bin.000005-rw-rw---- 1 mysql mysql      649 11月  1 09:58 mysql-bin.000006-rw-rw---- 1 mysql mysql      107 11月  1 09:58 mysql-bin.000007-rw-rw---- 1 mysql mysql      133 11月  1 09:58 mysql-bin.indexdrwx------ 2 mysql mysql     4096 10月 31 00:00 performance_schemadrwxr-xr-x 2 mysql mysql       20 10月 30 23:56 test[root@centos01 ~]# cp /usr/local/mysql/data/mysql-bin.000006 /root/test/                               

2）模拟误操作删除一班学生成绩表

[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;'                           [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                         ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist

3）恢复误删除的表

[root@centos01 ~]# mysql -uroot -ppwd@123 < ./test/benet_databases.sql                        [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                                +--------+--------+----------+--------+--------+--------+--------+| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |+--------+--------+----------+--------+--------+--------+--------+| 赵六   | 一班   | 20170816 | 100    | 109    | 112    | 265    || 王五   | 一班   | 20170818 | 95     | 103    | 108    | 270    || 李四   | 一班   | 20170820 | 95     | 115    | 110    | 260    || 张三   | 一班   | 20170822 | 110    | 105    | 92     | 235    |+--------+--------+----------+--------+--------+--------+--------+[root@centos01 ~]# mysqlbinlog --no-defaults /root/test/mysql-bin.000006 |mysql -u root -p                    Enter password:       [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                           +--------+--------+----------+--------+--------+--------+--------+| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |+--------+--------+----------+--------+--------+--------+--------+| 赵六   | 一班   | 20170816 | 100    | 109    | 112    | 265    || 王五   | 一班   | 20170818 | 95     | 103    | 108    | 270    || 李四   | 一班   | 20170820 | 95     | 115    | 110    | 260    || 张三   | 一班   | 20170822 | 110    | 105    | 92     | 235    || 李宁   | 二班   | 20170824 | 92     | 98     | 105    | 235    || 陈铭   | 二班   | 20170826 | 111    | 107    | 96     | 204    |+--------+--------+----------+--------+--------+--------+--------+

3、基于位置恢复

[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;'                   [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                   ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist[root@centos01 ~]# mysql -uroot -ppwd@123 < ./test/benet_databases.sql                    [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                    +--------+--------+----------+--------+--------+--------+--------+| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |+--------+--------+----------+--------+--------+--------+--------+| 赵六   | 一班   | 20170816 | 100    | 109    | 112    | 265    || 王五   | 一班   | 20170818 | 95     | 103    | 108    | 270    || 李四   | 一班   | 20170820 | 95     | 115    | 110    | 260    || 张三   | 一班   | 20170822 | 110    | 105    | 92     | 235    |+--------+--------+----------+--------+--------+--------+--------+[root@centos01 ~]# mysqlbinlog --no-defaults /root/test/mysql-bin.000006                       ......         # at 176                  #191101  9:55:33 server id 1  end_log_pos 329   Query   thread_id=9 exec_time=0 error_code=0use benet/*!*/;SET TIMESTAMP=1572573333/*!*/;insert into 一班学生成绩 value ('李宁','二班','20170824','92','98','105','235')/*!*/;# at 329#191101  9:55:33 server id 1  end_log_pos 356   Xid = 278COMMIT/*!*/;# at 356#191101  9:55:43 server id 1  end_log_pos 425   Query   thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1572573343/*!*/;BEGIN/*!*/;# at 425       #191101  9:55:43 server id 1  end_log_pos 579   Query   thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1572573343/*!*/;insert into 一班学生成绩 value ('陈铭','二班','20170826','111','107','96','204')/*!*/;[root@centos01 ~]# mysqlbinlog --no-defaults --stop-position='425' /root/test/mysql-bin.000006 |mysql -uroot -p       Enter password:       [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                   +--------+--------+----------+--------+--------+--------+--------+| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |+--------+--------+----------+--------+--------+--------+--------+| 赵六   | 一班   | 20170816 | 100    | 109    | 112    | 265    || 王五   | 一班   | 20170818 | 95     | 103    | 108    | 270    || 李四   | 一班   | 20170820 | 95     | 115    | 110    | 260    || 张三   | 一班   | 20170822 | 110    | 105    | 92     | 235    || 李宁   | 二班   | 20170824 | 92     | 98     | 105    | 235    |+--------+--------+----------+--------+--------+--------+--------+

上述命令中“–stop-position”指定的是停止的位置，如果仅恢复“陈铭”的信息，跳过“李宁”的信息恢复，可以使用“–start-position”选项指定开始恢复数据的位置。这时所恢复的数据是从指定位置开始直到二进制日志文件的最后。

[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;'                       [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                    ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist[root@centos01 ~]# mysql -uroot -ppwd@123 < ./test/benet_databases.sql                                [root@centos01 ~]# mysqlbinlog --no-defaults --start-position='425' /root/test/mysql-bin.000006 |mysql -uroot -p       Enter password:        [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                     +--------+--------+----------+--------+--------+--------+--------+| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |+--------+--------+----------+--------+--------+--------+--------+| 赵六   | 一班   | 20170816 | 100    | 109    | 112    | 265    || 王五   | 一班   | 20170818 | 95     | 103    | 108    | 270    || 李四   | 一班   | 20170820 | 95     | 115    | 110    | 260    || 张三   | 一班   | 20170822 | 110    | 105    | 92     | 235    || 陈铭   | 二班   | 20170826 | 111    | 107    | 96     | 204    |+--------+--------+----------+--------+--------+--------+--------+

4、基于时间点恢复

基于时间点恢复数据所使用的选项是“–stop-datetime”，指定的时间同样也是查询二进制日志所得。执行一下操作可以实现仅恢复到9:55:43之前的数据，即不恢复“陈铭”的信息。

[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;'                       [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                   ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist[root@centos01 ~]# mysql -uroot -ppwd@123 < ./test/benet_databases.sql                       [root@centos01 ~]# mysqlbinlog --no-defaults --stop-datetime='2019-11-01  9:55:43' /root/test/mysql-bin.000006 |mysql -uroot -p                              Enter password:       [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                               +--------+--------+----------+--------+--------+--------+--------+| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |+--------+--------+----------+--------+--------+--------+--------+| 赵六   | 一班   | 20170816 | 100    | 109    | 112    | 265    || 王五   | 一班   | 20170818 | 95     | 103    | 108    | 270    || 李四   | 一班   | 20170820 | 95     | 115    | 110    | 260    || 张三   | 一班   | 20170822 | 110    | 105    | 92     | 235    || 李宁   | 二班   | 20170824 | 92     | 98     | 105    | 235    |+--------+--------+----------+--------+--------+--------+--------+

执行以下操作可以实现仅恢复“陈铭”的信息，跳过“李宁”的信息恢复

[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;'                         [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                     ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist[root@centos01 ~]# mysql -uroot -ppwd@123 < ./test/benet_databases.sql                              [root@centos01 ~]# mysqlbinlog --no-defaults --start-datetime='2019-11-01 9:55:43'/root/test/mysql-bin.000006 |mysql -uroot -p                            Enter password:        [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;'                           +--------+--------+----------+--------+--------+--------+--------+| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |+--------+--------+----------+--------+--------+--------+--------+| 赵六   | 一班   | 20170816 | 100    | 109    | 112    | 265    || 王五   | 一班   | 20170818 | 95     | 103    | 108    | 270    || 李四   | 一班   | 20170820 | 95     | 115    | 110    | 260    || 张三   | 一班   | 20170822 | 110    | 105    | 92     | 235    || 陈铭   | 二班   | 20170826 | 111    | 107    | 96     | 204    |+--------+--------+----------+--------+--------+--------+--------+

—————— 本文至此结束，感谢阅读 ——————

推荐学习：MySQL教程

以上就是详解MySQL备份与恢复的详细内容，更多请关注创想鸟其它相关文章！