MySQL 数据库备份（一）（完全备份与恢复）

前言

随着办公自动化和电子商务的飞速发展，企业对信息系统的依赖性越来越高，数据库作为信息系统的核心，担当者重要的角色，数据库备份，是在数据丢失的情况下，能及时恢复重要数据，防止数据丢失的一种重要手段，一个合理的数据库备份方案，能够在数据丢失时，有有效地恢复数据，而且也需要考虑技术实现难度和有效地利用资源

一、MySQL数据库备份

1.数据备份的重要性

备份的主要目的就是灾难恢复
在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难（天灾）和盗窃

2.数据库备份的分类

①物理备份（对磁盘块进行备份）
物理备份：对数据操作系统的物理文件（数据文件、日志文件等）的备份，方法有：
冷备份（脱机备份）：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性
热备份（联机备份）：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
②逻辑备份（对文件进行备份）
对数据库逻辑组件（如：表等数据库对象）的备份

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

3.常见的备份方法

①物理冷备

备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也是最简单的
②专用备份工具mydump或mysqlhotcopy

mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
③启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志
④第三方工具备份

免费的MySQL热备软件Percona XtraBackup

二、完全备份

①物理冷备份与恢复

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

②mysqldump备份与恢复

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

systemctl stop mysqld
yum -y install xz
#数据库文件很大，可以使用压缩率较大的xz格式压缩，首选需要安装xz压缩格式工具

tar Jcvf /opt/mysql-$(date +%%F).tar.xz /usr/local/mysql/data/
#对数据库文件夹进行打包操作

ls /opt
#查看打包命令是否运行成功，已经生成了备份文件

du -sh /opt/mysql-2021-10-24.tar.xz 
#对比打包前后的文件大小，可以看到压缩的备份文件所占用空间很小

tar -Jxvf /opt/mysql-2021-10-24.tar.xz -C /usr/local/mysql/data/
systemctl restart mysql
#如果数据库文件损坏数据丢失，可以解压缩文件，相当于数据恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.使用 mysqldump 工具备份

①完全备份一个或多个完整的库

#开始之前，创建库和表，用作例子

mysql -uroot -p123123
#输入密码进入数据库，以上为我的密码

create database puxinL;
#创建库
use puxin;

create table club ( id int not null auto_increment, name varchar(10) not null, age char(5) not null, hobby varchar(10), primary key (id));
#创建表结构
#字段一：id，最大显示长度2，不能为空，自动递增从1开始
#字段二：name，可变长度字段，10，不能为空
#字段三：sex，固定长度5，不能为空
#字段四：hobby，可变长度，最多10字符
#字段四：主键（id）

insert into club values(1,'大潘','22','洗脚');
insert into club values(2,'徐舔舔','23','舔狗');
insert into club values(3,'汪汪','24','弹琴');
insert into club values(4,'吴工','25','买买买');
insert into club values(5,'姜姜','26','骂人');
#插入2条数据

select * from club;
#检查一下

quit
#退出数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

#使用 mysqldump 对某些表进行完全备份，命令格式如下：
mysqldump -u[用户名] -p[密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名.sql
#导出的为数据库脚本文件

例：对库puxin中的表club进行备份至/opt/目录下
mysqldump -uroot -p[密码] puxin club > /opt/xjj.sql

#对某些库进行完全备份
mysqldump -uroot -p[密码] [数据库名] > /目录/备份文件名.sql

例：
mysqldump -uroot -p123123 puxin > /opt/xjj


#对多个库进行完全备份
mysqldump -uroot -p123123 --databases puxin sys > /opt/xjj1.sql


#对所有库进行完全备份
mysqldump -uroot -p123123 --all-databases > /opt/xjj2.sql


#直接备份表结构
mysqldump -uroot -p123123 -d puxin club > /opt/xjj3.sql
#-d：只备份表的结构
#不加-d：表结构和表内容全复制
#-e:免交互，可以执行各种sql的(创建，删除，增，删，改、查)等各种操作


ls /opt/
#查看打包命令是否运行成功，已经生成了备份文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

cat /opt/xjj3.sql
#分析 mysqldump 工具生成的备份文件的内容

1
2
3

上面使用了 -d 选项，说明只保存数据库的表结构，且表中只有一个库，可以看到先删除了表（同名），再创建了它
删除和创建都是最普通的 MySQL 命令，任何一条在 MySQL 中都是可以执行的语句，有了这些语句就可以创建出和现在的表结构相同的表
如果不使用 -d 选项，会把数据也进行备份，下面看看数据时如何保存的

cat /opt/xjj1

1
2

可以看到与使用 -d 参数的差别是最后出现了 insert into 语句，往数据库中插入了两条数据，也就是说，数据的备份是保存的插入语句操作
通过以上分析，很容易理解，备份的本质就是保存数据库当前表结构的创建语句和现有数据的插入语句，有了它们就可以直接恢复数据库的状态

三、完全恢复

前面介绍了数据库的完全备份，使用 mysqldump 工具可以把数据的创建语句保存在备份的脚本文件中
当出现错误时，可以使用以下几种方法恢复它们
1.恢复整库操作

①source命令整库恢复

mysql -uroot -p123123

show databases;
drop database puxin;
show databases;

source /opt/xjj1.sql
#之前备份过的，该备份为SCHOOL和sys两个库的完整备份
#注意！选项得加“--databases”才是完整的备份，不然会恢复不了

1
2
3
4
5
6
7
8
9
10

②MySQL 命令整库恢复

quit

mysql -uroot -p123123 -e 'drop database puxin;'
mysql -uroot -p123123 -e 'SHOW DATABASES;'

mysql -uroot -p123123 < /opt/xjj2.sql

 mysql -uroot -p123123 -e 'SHOW DATABASES;'

1
2
3
4
5
6
7
8
9

2.恢复表

恢复数据表同样可以使用 source 命令和 mysql 命令操作
①使用source命令恢复表

mysql -uroot -p123123
use puxin;
show tables;
drop table club;
 
source /opt/xjj.sql
#恢复表

show tables;
select * from club;
#查看

1
2
3
4
5
6
7
8
9
10
11
12

②使用 mysql 命令恢复表

quit

mysql -uroot -p123123 -e 'SHOW TABLES FROM puxin;'

mysql -uroot -p123123 -e 'DROP TABLES puxin.club;'
mysql -uroot -p123123 -e 'SHOW TABLES FROM puxin;'

mysql -uroot -p123123 puxin < /opt/xjj.sql
mysql -uroot -p123123 -e 'SHOW TABLES FROM puxin;'

1
2
3
4
5
6
7
8
9
10

总结
①MySQL 需要定期实施备份，指定合适的备份计划或策略，并严格遵守

②除了进行完全备份，开启 MySQL 服务器的日志功能也很重要，完全备份加上日志，可以对 MySQL 进行最大化还原

③备份文件的名字还需要使用统一的易于理解的名称，推荐使用库名或表名加上时间的命名规则，在需要恢复数据库时能很容易的定位到相应的所需备份文件