MySQL备份与恢复详述_linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix

三、常见的备份方法

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

1、物理冷备份

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

2、专用备份工具mysqldump或mysqlhotcopy

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

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

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

四、数据库完全备份操作 1、备份前准备

[root@CentOS01 ~]# mysqladmin -u root password !--mysql数据库设置密码-- 
New password: !--输入密码-- 
Confirm new password: !--确认密码-- 
[root@centos01 ~]# mysql -uroot -ppwd@123 !--登录MySQL数据库-- 
mysql create database benet; !--创建benet数据库-- 
mysql use benet; !--切换到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、物理冷备份与恢复

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

1）备份数据库

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

[root@centos01 ~]# systemctl stop mysqld !--停止mysql服务-- 
[root@centos01 ~]mkdir /bak/ !--创建存储备份目录-- 
[root@centos01 ~]# tar zcf /bak/mysql_all-$(date +%F).mysql.gz /usr/local/mysql/data/ 
 !--直接tar打包数据库文件-- 
[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 !--启动mysql服务--

3、mysqldump备份与恢复

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

1）备份恢复所有数据库

[root@centos01 ~]# mysqldump -uroot -ppwd@123 --opt --all-databases ./test/benet_databases.sql !--备份所有库,opt选项是优化执行速度-- 
[root@centos01 ~]# mysql -uroot -p !--登录数据库-- 
Enter password: !--数据密码-- 
mysql show databases; !--查看所有数据库-- 
+--------------------+
| Database |
+--------------------+
| information_schema |
| benet |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql drop database benet; !--删除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; !--切换到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; !--切换到benet数据库-- 
Database changed
mysql 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 !--进入MySQL配置文件-- 
....... !--此处省略部分内容-- 
log-bin=mysql-bin !--开启二进制日志功能-- 
[root@centos01 ~]# systemctl restart mysqld !--重启MySQL服务-- 
[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.index
drwx------ 2 mysql mysql 4096 10月 31 00:00 performance_schema
drwxr-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.index
drwx------ 2 mysql mysql 4096 10月 31 00:00 performance_schema
drwxr-xr-x 2 mysql mysql 20 10月 30 23:56 test
[root@centos01 ~]# mysql -uroot -ppwd@123 !--登录mysql数据库-- 
mysql use benet; !--切换到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.index
drwx------ 2 mysql mysql 4096 10月 31 00:00 performance_schema
drwxr-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 !--at就是我们称之为操作ID，下面紧跟的是时间标记-- 
#191101 9:55:33 server id 1 end_log_pos 329 Query thread_id=9 exec_time=0 error_code=0
use 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 = 278
COMMIT/*!*/;
# at 356
#191101 9:55:43 server id 1 end_log_pos 425 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1572573343/*!*/;
BEGIN
/*!*/;
# at 425 !--at就是我们称之为操作ID，下面紧跟的是时间标记-- 
#191101 9:55:43 server id 1 end_log_pos 579 Query thread_id=9 exec_time=0 error_code=0
SET 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 !--基于ID恢复增量备份-- 
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 !--基于ID恢复增量备份-- 
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 |
+--------+--------+----------+--------+--------+--------+--------+

CentOS安装MySQL数据库 MySQL Slave库恢复实录