MySQL备份与恢复

1、数据库完全备份操作

冷备份：在数据库关闭状态下进行备份操作

热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

1.1、物理冷备份与恢复

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

1）备份数据库

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

2）恢复数据库

? ? ? ? 执行下面操作将数据库文件/usr/local/mysql/data/转移至bak目录下，模拟故障。??

????????执行下面操作恢复数据

?1.2、mysqldump备份与恢复

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

1）备份数据库

????????使用mysqldump命令导出数据时，默认会直接在字段显示，若要保存到文件，还需要结合shell的">"重定向输出操作。

? ? ? ? 格式一：备份指定库中的部分表

?????????格式二：备份一个或多个完整的库（包括其中所有的表）

????????格式三：备份MySQL服务器中所有的库

?将msql库中的user表导出为mysql-user.sql，将整个auth库导出为auth.sql文件，所有操作均以root用户身份验证

创建备份文件all-data.sql，其中包括MySQL服务器中的所有库，使用--opt选项优化执行速度?

2）查看备份文件

? ? ? ? 通过mysqldump工具导出的SQL脚本是文本文件

[root@yang ~]# grep -v "^--" bak/auth.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_name` char(16) NOT NULL,
  `user_passwd` char(48) DEFAULT '',
  PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('lisi','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
UNLOCK TABLES;

3）恢复数据库

????????使用mysqldump命令导出的SQL备份脚本，在需要恢复时可以通过mysql命令对其进行导入操作

使用备份文件mysql-user.sql中将表导入test库，其中"-e"选项是用于指定连接MySQL后执行的命令，命令执行完后自动退出?

?若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名

?2、MySQL增量备份与恢复

????????使用mysqldump进行完全备份，备份的数据中有重复数据，备份时间与恢复时间过长。而增量备份就是备份自上一次备份之后增加或改变的文件或内容。MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志间接实现增量备份

2.1、MySQL二进制日志

????????二进制日志保存了所有更新或者可能更新数据库的操作。二进制日志在启动MySQL服务器后开始记录，并在文件达到二进制日志所设置的最大值或接收到"flush logs"命令后重新创建新的日志文件，生成二进制文件系列。max_binlog_size配置项可以设置二进制日志文件的最大值

开启二进制日志功能

?

2.2、MySQL增量恢复

一般恢复：将所有备份的二进制日志内容全部恢复?

基于位置的恢复：--stop（到此位置结束的操作）、--start（此位置开始的操作）

基于时间点的恢复：跳过某个发生错误的时间点实现数据恢复?

3、实验案例

实验环境

????????某学校近期在进行其中考试，要求数据库管理员负责一班、二班学生的考试成绩录入，为保证数据的可靠性，数据库管理员在路人学生成绩后均要做数据库备份，并且为了测试备份数据是否可用，模拟数据丢失故障，进行数据还原。

需求描述

? ? ? ? 1、在数据库表中，分三次录入学生考试成绩

? ? ? ? 2、首次录入成绩后，做该表的完全备份，后两次成绩的录入之后，分别做增量备份

? ? ? ? 3、模拟数据丢失，并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩

?1、第一次录入数据，并做完全备份

//创建chengji表
create table chengji(姓名 char(50), 班级 char(10), 学号 int(20), 语文 int(10), 数学 int(10), 英语 int(10), 理综 int(10))；

mysql> insert into chengji values ('张三','一班',20170822,110,105,92,235);
mysql> insert into chengji values ('李四','一班',20170820,95,115,110,260);
mysql> insert into chengji values ('王五','一班',20170818,95,103,108,270);
mysql> insert into chengji values ('赵六','一班',20170816,100,109,112,265);

mysql> select * from chengji;
+--------+--------+----------+--------+--------+--------+--------+
| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |
+--------+--------+----------+--------+--------+--------+--------+
| 张三   | 一班   | 20170822 |    110 |    105 |     92 |    235 |
| 李四   | 一班   | 20170820 |     95 |    115 |    110 |    260 |
| 王五   | 一班   | 20170818 |     95 |    103 |    108 |    270 |
| 赵六   | 一班   | 20170816 |    100 |    109 |    112 |    265 |
+--------+--------+----------+--------+--------+--------+--------+

//创建一个backup文件夹用于存放备份文件
[root@yang ~]# mkdir backup

/使用mysqldump创建一个bdqn库中chengji表的备份文件
[root@yang ~]# mysqldump -u root -p bdqn chengji > backup/bdqn-chengji.sql
Enter password: 

2、插入第二条数据并做增量备份

mysql> insert into chengji values ('李宁','二班',20170824,92,98,105,235);

mysql> insert into chengji values ('陈铭','二班',20170826,111,107,96,204);

[root@yang ~]# mysqladmin -u root -p flush-logs
Enter password:       
[root@yang ~]# ll /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 3443 12月 14 17:34 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  120 12月 14 17:34 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql   68 12月 14 17:34 /usr/local/mysql/mysql-bin.index

3、插入第三条数据并做增量备份

mysql> insert into chengji values ('付杰','二班',20170828,115,118,116,268);

mysql> insert into chengji values ('郭尚','二班',20170830,111,99,80,259);

[root@yang ~]# mysqladmin -u root -p flush-logs
Enter password: 
[root@yang ~]# ll /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 3443 12月 14 17:34 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  675 12月 14 17:36 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql  120 12月 14 17:36 /usr/local/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql  102 12月 14 17:36 /usr/local/mysql/mysql-bin.index

4、使用完全备份恢复一班的信息

[root@yang ~]# mysql -u root -p bdqn < backup/bdqn-chengji.sql 
Enter password: 
[root@yang ~]# mysql -uroot -p -e 'select * from bdqn.chengji;'
Enter password: 
+--------+--------+----------+--------+--------+--------+--------+
| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |
+--------+--------+----------+--------+--------+--------+--------+
| 张三   | 一班   | 20170822 |    110 |    105 |     92 |    235 |
| 李四   | 一班   | 20170820 |     95 |    115 |    110 |    260 |
| 王五   | 一班   | 20170818 |     95 |    103 |    108 |    270 |
| 赵六   | 一班   | 20170816 |    100 |    109 |    112 |    265 |
+--------+--------+----------+--------+--------+--------+--------+

5、使用位置备份恢复二班陈铭与付杰的成绩

[root@yang ~]# mysqlbinlog --no-defaults --start-position='3221' /usr/local/mysql/mysql-bin.000001 | mysql -uroot -p
Enter password: 
[root@yang ~]# mysqlbinlog --no-defaults --stop-position='344' /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p

[root@yang ~]# mysql -uroot -p -e 'select * from bdqn.chengji;'
Enter password: 
+--------+--------+----------+--------+--------+--------+--------+
| 姓名   | 班级   | 学号     | 语文   | 数学   | 英语   | 理综   |
+--------+--------+----------+--------+--------+--------+--------+
| 张三   | 一班   | 20170822 |    110 |    105 |     92 |    235 |
| 李四   | 一班   | 20170820 |     95 |    115 |    110 |    260 |
| 王五   | 一班   | 20170818 |     95 |    103 |    108 |    270 |
| 赵六   | 一班   | 20170816 |    100 |    109 |    112 |    265 |
| 陈铭   | 二班   | 20170826 |    111 |    107 |     96 |    204 |
| 付杰   | 二班   | 20170828 |    115 |    118 |    116 |    268 |