MySQL备份与恢复（二）---完全备份与恢复（温、冷备）

---

前言

每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长
每次都进行完全备份，会导致备份文件占用空间巨大，并且有大量的重复数据，恢复时，直接使用完全备份的文件即可

---

一、MySQL完全备份介绍

• 是对整个数据库、数据库结构和文件结构的备份

• 保存的是备份完成时刻的数据库

• 是差异备份与增量备份的基础

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

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

二、思路

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

三、环境准备

mysql> use test;
mysql> create table if not exists info1 ( id int(4) not null auto_increment, name varchar(10) not null, age char(10) not nul
l, hobby varchar(50), primary key (id));

mysql> insert into info1 values(1,'user1',20,'running');
mysql> insert into info1 values(2,'user2',30,'singing');

mysql> select * from info1;
+----+-------+-----+---------+
| id | name  | age | hobby   |
+----+-------+-----+---------+
|  1 | user1 | 20  | running |
|  2 | user2 | 30  | singing |
+----+-------+-----+---------+
2 rows in set (0.00 sec)

四、物理备份与恢复（冷备）

mysql> select * from info1;		//查看原有数据
+----+-------+-----+---------+
| id | name  | age | hobby   |
+----+-------+-----+---------+
|  1 | user1 | 20  | running |
|  2 | user2 | 30  | singing |
+----+-------+-----+---------+
2 rows in set (0.00 sec)

[root@c7-2 ~]#systemctl stop mysqld		//停止mysql
[root@c7-2 ~]#yum install -y xz			//安装xz
#把data压缩备份
[root@c7-2 ~]#tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

##模拟故障
[root@c7-2 ~]#cd /usr/local/mysql/
[root@c7-2 /usr/local/mysql]#rm -rf data/		//删除数据目录

[root@c7-2 ~]#systemctl start mysqld
[root@c7-2 ~]#mysql -uroot -p123456			//再次登陆就登录不上了
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


##解压恢复数据
[root@c7-2 ~]#systemctl stop mysqld
[root@c7-2 ~]#tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/data/
[root@c7-2 ~]#cd /usr/local/mysql/data
[root@c7-2 ~]#mv /usr/local/mysql/data/* ./

五、mysqldump 备份与恢复（温备份）

• MySQL自带的备份工具，可方便实现对MySQL的备份
• 可以将指定的库、表导出为SQL 脚本
• 使用命令mysq|导入备份的数据
• 冷备份使用tar打包，需要先关闭数据库是因为数据库仍在持续写入，不关闭会导致数据丢失

1.完全备份一个或多个完整的库 (包括其中所有的表)

###格式：
mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql    #导出的就是数据库脚本文件

#####例：
mysqldump -u root -p --databases zone > /opt/zone.sql       #备份一个zone库
mysqldump -u root -p --databases mysql zone > /opt/mysql-zone.sql    #备份mysql与 zone两个库 

2.完全备份 MySQL 服务器中所有的库

####格式：
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

例:
mysqldump -u root -p --all-databases > /opt/all.sql

3.完全备份指定库中的部分表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
例:
mysqldump -u root -p [-d] zone info1 info2 > /opt/zone_info1.sql
#使用“-d”选项，说明只保存数据库的表结构 
#不使用“-d"选项，说明表数据也进行备份
#做为一个表结构模板

4.查看备份文件

grep -v "^--" /opt/zone_info1.sql | grep -v "^/" | grep -v "^$"

六、完全恢复

• 方法一：使用source恢复数据库的步骤
  　　　　１.登录到MySQL数据库
  　　　　２.执行source备份sql脚本的路径

###模拟故障
use zone;
delete from dsj; 

###恢复
source /opt/zone.sql

• 方法二：使用musql命令，无须登录MySQL数据库
  　　　　使用 -e 删除数据表，执行mysql备份sql脚本的路径

[root@c7-2 ~]#mysqldump -uroot -p123456 zone > /opt/zone.sql
[root@c7-2 ~]#mysql -uroot -p123456 -e 'drop table zone.dsj;'
[root@c7-2 ~]#mysql -uroot -p123456 -e 'show tables from zone;'
[root@c7-2 ~]#mysql -uroot -p123456 zone < /opt/zone.sql 
[root@c7-2 ~]#mysql -uroot -p123456 -e 'show tables from zone;'

---

总结

• 冷备份 (脱机备份) ：是在关闭数据库的时候进行的（tar）
• 温备份 ：数据库锁定表格(不可写入但可读)的状态下进行备份操作（mysqldump）