MySQL完全备份与恢复

---

引言

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

---

一、MySQL 日志

由于日志文件时掌握数据库运行状态的重要参考，因此日志文件的维护也有十分重要的意义。

MySQL 的日志默认存放位置： /usr/local/mysql/data

1. 类型和作用

redo 重做日志:达到事务一致性(每次重启会重做)
作用:确保日志的持久性，防止在发生故障，脏页未写入磁盘。
重启数据库会进行redolog执行重做，达到事务一致性

undo 回滚日志
作用:保证数据的原子性，记录事务发生之前的一一个版本，用于回滚，innodb事务可重复读和读取已提交隔离级别就是通过mvcc+undo实现

error log 错误日志 
作用: MySQL本身启动，停止，运行期间发生的错误信息

slow query log 慢查询日志
作用：记录执行时间过长的sql，时间阈值(10s) 可以配置，只记录执行成功
另一个作用:相当于提醒你是否优化此项

bin log二进制日志
作用:用于主从复制，实现主从同步；主从复制是另外两种核心架构的基础：读写分离和MHA(mysql ha);ha:高可用
记录的内容是:数据库中执行的sql语句

relay log中继日志
作用:用于数据库主从复制/同步，将主库发来的bin/log保存在本地，然后从库进行回放

general log普通日志
作用:记录数据库的操作明细，默认关闭，开启后会降低数据库性能

2. 日志配置文件

vim /etc/my.cnf

	[mysqld]	
	
#错误日志，用来记录当MySQL启动、 停止或运行时发生的错误信息，默认已开启
	log-error=/usr/local/mysql/data/mysql_error.log	#指定错误日志的保存位置和文件名
	general_log=ON	##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
	general_log_file=/usr/local/mysql/data/mysql_general.log
	
#二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
	log-bin=mysql-bin
或
	log_bin=mysql-bin
	
#中继日志
一般情况下它在Mysql主从同步(复制)、读写分离集群的从节点开启。主节点一般不需要这个日志

#慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的
	slow_query_log=ON
	slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
	long_query_time=5								#设置超过5秒执行的语句被记录，缺省时为10秒
	
systemctl restart mysqld
mysql -u root -P

3. 查询日志

符合	含义
xx%	以xx为开头的字段
%xx	以xx为结尾的字段
%xx%	只要出现xx字段的都会显示出来
xx	精准查询
variables	表示变量
like	表示模糊查询

• 查看通用查询日志是否开启（默认关闭）

show variables like 'general%';  
#general_log 对应的是通用查询日志   

• 查看二进制日志是否开启（默认开启）

#二进制日志用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，主要目的是在恢复数据时能够最大程度地恢复数据库

show variables like 'log_bin%'; 

• 查看慢查询功能是否开启（默认关闭）

#慢查询日志记录所有执行时间超过long_query_time 秒的SQL语句，用于查找哪些查询语句执行时间长，以便对其进行优化
show variables like '%slow%';         

二、MySQL 完全备份

1. 数据库备份的重要性

在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果。

• 数据库备份的重要性主要体现在：

① 提高系统的高可用性和灾难可恢复性，在数据库系统崩溃时，没有数据库备份就没法找到数据
② 使用数据库备份还原数据库，是数据库崩溃时提供数据恢复最小代价的最优方案，如果让用户重新添加数据，代价很大。
③ 没有备份就没有一切，数据库备份是一种防范灾难的强力手段

• 造成数据丢失的原因有很多种：

① 程序错误：指对数据库操作的程序难免有些错误，造成数据丢失
② 人为错误：指由于使用人员的误操作造成的数据被破坏，还有可能是黑客对系统攻击造成的数据丢失等
③ 计算机失败：指运行数据库的服务器操作系统或软件损坏，有可能造成数据的损坏
④ 磁盘失败：指硬盘等存储数据的硬件设备，长时间运行后可能损坏，造成数据丢失
⑤ 灾难（如火灾、地震）和偷窃：指自然灾害等的发生，有可能造成数据丢失

2. 数据库备份的分类

备份的方式分为很多种，从物理和逻辑的角度，备份可以分为以下几类

2.1 物理备份

物理备份是指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份，物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）

• 冷备份（脱机备份）：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性
• 热备份（联机备份）：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件

2.2 逻辑备份

逻辑备份是指对数据库逻辑组件（如表等数据库对象）的备份。

从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份

(1) 完全备份

每次对数据进行完整的备份 可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象
但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些

(2) 差异备份

备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分的内容
它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库
它的优点是存储和恢复速度快

(3) 增量备份

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

• 几种备份之间的区别总结

备份方式	完全备份	差异备份	增量备份
完全备份时的状态	表1、表2	表1、表2	表1、表2
第1次添加内容	创建表3	创建表3	创建表3
备份内容	表1、表2、表3	表3	表3
第2次添加内容	创建表4	创建表4	创建表4
备份内容	表1、表2、表3、表4	表3、表4	表4

2.3 小结

• 完全备份是对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是增量备份的基础
• 完全备份的优点是备份与恢复操作简单方便，缺点是数据存在大量重复，占用大量的备份空间，备份的时间长
• 在生产环境中，这两种备份方式都会使用，需要制定合理高效的方案达到备份数据的目的，避免数据丢失造成严重的后果

3. 常见的备份方法

• 物理冷备

备份时数据库处于关闭状态，直接打包数据库文件（tar） 备份速度快，恢复时也是最简单的
物理冷备关闭数据库的原因：因为数据库是由持续性的数据交互和数据频繁更新的特性，若不关闭数据库，tar就无法保证数据的更新与一致性

• 专用备份工具 mysqldump 或 mysqlhotcopy

mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅用于备份 MyISAM 和 ARCHIVE表（MyISAM、ARCHIVE指的是引擎表）

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

• 第三方工具备份

免费的MySQL 热备份软件 Percona、XtraBackup、mysqlbackup

三、MySQL 物理备份与恢复

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

• 优点：备份与恢复操作简单方便
• 缺点：数据存在大量的重复、占用大量的备份空间、备份与恢复时间长

1. 物理冷备份与恢复

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

• 具体操作如下：

#案例环境准备
mysql> select * from IT_salary;
+-----------------+-----------+--------+----------+--------+--------+----------+
| 岗位类别        | 姓名      | 年龄   | 员工ID   | 学历   | 年限   | 薪资     |
+-----------------+-----------+--------+----------+--------+--------+----------+
| JAVA工程师      | 小王      |     25 |        1 | 本科   |      2 |  3500.00 |
| 运维工程师      | 崔配文    |     28 |        2 | 本科   |      2 | 20000.00 |
| 网络工程师      | 孙空武    |     27 |       11 | 本科   |      3 |  4800.00 |
+-----------------+-----------+--------+----------+--------+--------+----------+
3 rows in set (0.00 sec)

-------------------------------------------------------------------------------------------------
#具体操作流程
[root@mysql ~]#systemctl stop mysqld.service 
[root@mysql ~]#cd /usr/local/mysql/

#数据备份打包；date +%F:当前时间
[root@mysql /usr/local/mysql]#tar Jcvf /opt/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/

#模拟故障
[root@mysql /usr/local/mysql]#rm -rf data
[root@mysql /usr/local/mysql]#systemctl start mysqld.service 
[root@mysql /usr/local/mysql]#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@mysql /usr/local/mysql]#cd /opt/
[root@mysql /opt]#ls
mysql_all_2021-10-24.tar.gz  rh  usr
[root@mysql /opt]#cd usr/local/mysql/
[root@mysql /opt/usr/local/mysql]#ls
data
#data目录移到/usr/local/mysql/目录下
[root@mysql /opt/usr/local/mysql]#mv data /usr/local/mysql/

2. mysqldump备份与恢复（温备份）

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

2.1 mysqldump备份

#-e：在命令行对表的内容进行操作
[root@mysql ~]#mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| IT                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

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

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql

案例
#备份名为IT的库到/opt目录下
[root@mysql ~]#mysqldump -uroot -p123456 --databases IT >/opt/IT.sql
#备份IT与sys两个库
mysqldump -uroot -p123456 --databases IT sys > /opt/IT-sys.sql    

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

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

案例
mysqldump -uroot -p123456 --all-databases > /opt/all.sql

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

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

mysqldump -uroot -p123456 IT class > /opt/IT_class.sql

• 查看备份文件

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

---

2.2 恢复数据库

• 方法① 使用source命令，登录到MySQL数据库，执行source备份sql脚本的路径

mysql> show databases;
mysql> drop database IT;
mysql> show databases;
mysql> source /opt/IT.sql

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

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

2.3 mysqldump总结

mysqldump 严格来说属于温备份，会需要对表进行写入的锁定

在全量备份与恢复案例中，假设现有class库，库中有一个gl表，需要注意：

• ① 当备份时加 --databases ，表示针对于class库

#备份时加 --databases ,表示针对于class库
mysqldump -uroot -p123456 --databases class > /opt/class_01.sql 

#恢复数据
mysql -uroot -p123456
drop database class;
exit
mysql -uroot -p123456 < /opt/class_01.sql

• ② 备份时不加 --databases，表示针对class库下的所有表

#备份
mysqldump -uroot -p123456 class > /opt/class_02.sql
#恢复
mysql -uroot -p123456
drop database class;
create database class;
exit
mysql -uroot -p123456 class < /opt/class_02.sql 

两种方式的备份,前者会从"create databases"开始,而后者则全是针对表格进行操作

总结

• 是从数据库的备份策略角度，备份可以氛围完全备份，差异备份和增量备份

• 从物理和逻辑角度，备份可以分为物理备份（冷、热备份）、逻辑备份

• 备份可以针对整库，某些库或某些表来进行

• 如何选择逻辑备份策略

首先是根据项目频率，其次关系备份的合理值区间值，以逻辑备份策略而言：
全备：一周一次的全备（全备的时间需要在业务暂停/空闲时进行）
增量：3天/2天一次增量备份
差异：选择特定的场景进行备份