MySQL数据库中各种备份方法的介绍，以及进行误操作后如何进行恢复

一、数据库备份的分类

1.1 数据备份的重要性

• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果
• 造成数据丢失的原因
  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难(如火灾、地震)和盗窃

1.2 数据库备份的分类

从物理与逻辑的角度，备份可分为

物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份

物理备份方法

• 冷备份(脱机备份) :是在关闭数据库的时候进行的
• 热备份(联机备份) :数据库处于运行状态,依赖于数据库的日志文件
• 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作

从数据库的备份策略角度，备份可分为

• 完全备份:每次对数据库进行完整的备份
• 差异备份:备份自从上次完全备份之后被修改过的文件
• 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

1.3 常见的备份方法

物理冷备

• 备份时数据库处于关闭状态，直接打包数据库文件
• 备份速度快，恢复时也是最简单的

专用备份工具mydump或mysqlhotcopy

• mysqldump常用的逻辑备份工具
• mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

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

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

第三方工具备份

• 免费的MySQL热备份软件Percona XtraBackup

二、MySQL完全备份

• 是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础

优点

• 备份与恢复操作简单方便

缺点

• 数据存在大量的重复
• 占用大量的备份空间
• 备份与恢复时间长

2.1 数据库完全备份分类

物理冷备份与恢复

• 关闭MySQL数据库
• 使用tar命令直接打包数据库文件夹
• 直接替换现有MySQL目录即可

mysqldump备份与恢复

• MySQL自带的备份工具，可方便实现对MySQL的备份
• 可以将指定的库、表导出为SQL脚本
• 使用命令mysq|导入备份的数据

2.2 MySQL物理冷备份及恢复

物理冷备份

[root@localhost mysql]# systemctl stop mysqld.service
[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# tar zcvf /opt/mysql_all-$(date +%F).tar.gz data/
[root@localhost mysql]# cd /opt
[root@localhost opt]# ls
mysql_all-2020-08-18.tar.gz

恢复数据库

[root@localhost opt]# cd /usr/local/mysql/
[root@localhost mysql]# rm -rf data/     '删除数据库文件'
[root@localhost mysql]# mysql -uroot -p

mysql> show databases;             '此时数据库文件消失'
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

[root@localhost mysql]# cd /opt
[root@localhost opt]# tar zxvf mysql_all-2020-08-18.tar.gz -C   /usr/local/mysql/  '将备份的文件恢复'
[root@localhost mysql]# systemctl start mysqld.service 
[root@localhost mysql]# mysql -uroot -p

mysql> show databases;      '数据恢复'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

2.3 mysqkdump备份数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| apple              |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use mydatabase;
Database changed

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| mytable              |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 80.00 | sh      |
|  2 | lisi     | 77.00 | nj      |
+----+----------+-------+---------+
2 rows in set (0.00 sec)

mysqldump命令对单个库进行完全备份

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

[root@localhost ~]# mysqldump -uroot -p123456 mydatabase > /opt/mydatabase.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost opt]# ls
mydatabase.sql  

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

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

[root@localhost ~]# mysqldump -uroot -p123456 --databases mydatabase apple > /opt/mydatabase-apple.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost opt]# ls
mydatabase-apple.sql  mydatabase.sql

对所有库进行完全备份

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

[root@localhost ~]# mysqldump -uroot -p123456 --all-databases > /opt/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost opt]# ls
all.sql  mydatabase-apple.sql  mydatabase.sql

mysqldump可针对库内特定的表进行备份

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

[root@localhost opt]# mysqldump -uroot -p123456 mydatabase mytable > /opt/mydatabase.mytable.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost opt]# ls
all.sql           mydatabase.sql          mydatabase-apple.sql  
mydatabase.mytable.sql

2.4 恢复数据库

使用mysqldump导出的脚本，可使用导入的方法

• source命令，用在mysq|中, 只可以使用绝对路径
• mysq|命令，用在linux下面

使用source恢复数据库的步骤

• 登录到MySQL数据库
• 执行source备份sq|脚本的路径

source恢复的示例

mysql > source /backup/all-data.sql

mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> source /opt/mydatabase.sql

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| mytable              |
+----------------------+
1 row in set (0.00 sec)

mysql> drop database apple;
Query OK, 1 row affected (0.00 sec)

mysql> drop database mydatabase;
Query OK, 1 row affected (0.01 sec)

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

mysql> source /opt/mydatabase-apple.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| apple              |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

使用mysql命令恢复数据库

mysql -u 用户名 -p 密码 < 库备份脚本的路径

例：

[root@localhost opt]# mysql -uroot -p123456 < /opt/mydatabase-apple.sql

恢复表的操作

• 恢复表时同样可以使用source或者mysql命令
• source恢复表的操作与恢复库的操作相同
• 当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在

mysql -u 用户名 -p 密码 < 表备份脚本的路径

[root@localhost opt]# mysql -uroot -p123456 < /opt/mydatabase.info.sql

在生产环境中，可以使用shell脚本自动实现定时备份

三、MySQL增量备份

使用mysqldump进行完全备份存在的问题

• 备份数据中有重复数据
• 备份时间与恢复时间过长

是自上一次备份后增加/变化的文件或者内容

特点

• 没有重复数据，备份量不大，时间短
• 恢复需要_上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

MySQL没有提供直接的增量备份方法

可通过MySQL提供的二进制日志间接实现增量备份

MySQL二进制日志对备份的意义

• 二进制日志保存了所有更新或者可能更新数据库的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
• 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

[root@localhost opt]# vim /etc/my.cnf
.......
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
log-bin=mysql-bin      '添加以mysql-bin为开头的二进制文件'
server-id = 1

[root@localhost data]# ls
apple           ibdata1      ibtmp1      mysql-bin.000001  '二进制日志文件'
auto.cnf        ib_logfile0  mydatabase  mysql-bin.index
ib_buffer_pool  ib_logfile1  mysql

3.1 MySQL数据库增量恢复

一般恢复

• 将所有备份的二进制日志内容全部恢复

mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p

基于位置恢复

• 数据库在某一时间点可能既有错误的操作也有正确的操作
• 可以基于精准的位置跳过错误的操作

恢复数据到指定位置
mysqlbinlog --stop-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码

从指定的位置开始恢复数据
mysqlbinlog --start-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码

基于时间点恢复

• 跳过某个发生错误的时间点实现数据恢复

从日志开头截止到某个时间点的恢复
mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码

从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码

从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码

3.2 增量备份及恢复的具体操作

操作前先进行完整备份

[root@localhost ~]# mysqldump -uroot -p123456 mydatabase mytable > /opt/mytable.sql

开启日志文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
log-bin=mysql-bin         '开启二进制日志文件'

[root@localhost data]# systemctl restart mysqld.service 
[root@localhost data]# ls
apple           ibdata1      ibtmp1      mysql-bin.000001  '二进制日志文件'
auto.cnf        ib_logfile0  mydatabase  mysql-bin.index
ib_buffer_pool  ib_logfile1  mysql

进行模拟误操作

mysql> select * from mytable;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 22  |
|  2 | lisi     | 26  |
|  3 | wangwu   | 30  |
+----+----------+-----+
3 rows in set (0.00 sec)

mysql> insert into mytable values (4,'zhaoliu','20');  '执行正确操作'
Query OK, 1 row affected (0.02 sec)

mysql> delete from mytable where id=1;              '进行误操作'
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytable values (5,'qiqi','21');  '执行正确操作'
Query OK, 1 row affected (0.01 sec)

mysql> select * from mytable;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  2 | lisi    | 26  |
|  3 | wangwu  | 30  |
|  4 | zhaoliu | 20  |
|  5 | qiqi    | 21  |
+----+---------+-----+
4 rows in set (0.00 sec)

进行增量备份

[root@localhost data]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

解码二进制日志文件

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/bk01.txt

[root@localhost data]# vim /opt/bk01.txt
# at 584           '正常操作结束'
#200822 15:06:12 server id 1  end_log_pos 646 CRC32 0x591ad99d  Table_map: `mydatabase`.`mytable` mapped to number 108
# at 646            '执行的误操作'
#200822 15:06:12 server id 1  end_log_pos 698 CRC32 0x4ddb56be  Delete_rows: table id 108 flags: STMT_END_F
### DELETE FROM `mydatabase`.`mytable`
### WHERE
###   @1=1
###   @2='zhangsan'
###   @3='22'
# at 698           '正常操作开始'
#200822 15:06:12 server id 1  end_log_pos 729 CRC32 0x30b1bed7  Xid = 7
COMMIT/*!*/;         
#200822 15:06:30 server id 1  end_log_pos 794 CRC32 0x576f94b9  Anonymous_GTID  last_committed=2        sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

先删除错误的数据表，进行恢复

mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

mysql> source /opt/mytable.sql;

mysql> select * from mytable;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 22  |
|  2 | lisi     | 26  |
|  3 | wangwu   | 30  |
+----+----------+-----+
3 rows in set (0.00 sec)

使用增量备份的断点恢复

[root@localhost data]# mysqlbinlog --no-defaults --stop-position='584' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from mytable;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 22  |
|  2 | lisi     | 26  |
|  3 | wangwu   | 30  |
|  4 | zhaoliu  | 20  |
+----+----------+-----+
4 rows in set (0.00 sec)

[root@localhost data]# mysqlbinlog --no-defaults --start-position='698' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from mytable;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 22  |
|  2 | lisi     | 26  |
|  3 | wangwu   | 30  |
|  4 | zhaoliu  | 20  |
|  5 | qiqi     | 21  |
+----+----------+-----+
5 rows in set (0.00 sec)

按照增量备份中的时间点恢复

mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

mysql> source /opt/mytable.sql;

mysql> select * from mytable;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 22  |
|  2 | lisi     | 26  |
|  3 | wangwu   | 30  |
+----+----------+-----+
3 rows in set (0.00 sec)

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-8-22 15:06:12' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from mytable;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 22  |
|  2 | lisi     | 26  |
|  3 | wangwu   | 30  |
|  4 | zhaoliu  | 20  |
+----+----------+-----+
4 rows in set (0.00 sec)

[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-8-22 15:06:30' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from mytable;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan | 22  |
|  2 | lisi     | 26  |
|  3 | wangwu   | 30  |
|  4 | zhaoliu  | 20  |
|  5 | qiqi     | 21  |
+----+----------+-----+
5 rows in set (0.00 sec)