2021-04-07

第四章 Mysql备份与恢复
实验案例：增量备份与恢复
>实验环境
某公司近期在进行期中考试，要求数据库管理员负责一班，二班学生的考试成绩录入。为保证数据的可靠性，数据库管理员在录入学生成绩后均要做数据库备份，并且为了测试备份数据是否可用，模拟数据丢失故障，进行数据还原。
>需求描述
>在数据库表中，分三次录入学生考试成绩，具体的考试成绩信息分别参见表4-2~表4-4
表4-2 一班学生成绩
姓名 班级 学号 语文 数学 英语 理综
张三 一班 20170822 110 105 92 235
李四 一班 20170820 95 115 110 260
王五 一班 20170818 95 103 108 270
赵六 一班 20170816 100 109 112 265
表4-3 二班学生成绩（一）
姓名 班级 学号 语文 数学 英语 理综
李宁 二班 20170824 92 98 105 235
陈铭 二班 20170826 111 107 96 204
表4-4 二班学生成绩（二）
姓名 班级 学号 语文 数学 英语 理综
付杰 二班 20170828 115 118 116 268
郭尚 二班 20170830 111 99 80 259

****>首次录入成绩后，做该表的完全备份。后两次成绩的录入之后，分别做增量备份。
**>模拟数据丢失，并使用增量备份分别基于位置和时间点恢复一班所有学生成绩与二班陈铭与付杰的成绩

推荐步骤**
>创建数据库与表
分三次录入学生成绩，并分别进行完全备份与增量备份
模拟数据丢失，进行数据恢复
1、设置root密码登录数据库
[root@centos01 ~]mkdir /bak/
[root@centos01 ~]# mkdir ./test/
[root@centos01 ~]# mysqladmin -u root password ‘pwd@123’
[root@centos01 ~]# mysql -u root -p
Enter password:
2、创建库
mysql> create database bdqn;
Query OK, 1 row affected (0.00 sec)
3、创建表格录入成绩
mysql> create table 一班成绩 (姓名 char (3),班级 char (2),学号 char (8),语文 char(3),数学 char(3),英语 char(3),理综 char(3),primary key (学号));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into 一班成绩 value (‘张三’,‘一班’,‘20170822’,‘110’,‘105’,‘92’,‘235’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into 一****班成绩 value (‘李四’,‘一班’,‘20170820’,‘95’,‘115’,‘110’,‘260’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into 一班成绩 value (‘王五’,‘一班’,‘20170818’,‘95’,‘103’,‘108’,‘270’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into 一班成绩 value (‘赵六’,‘一班’,‘20170816’,‘100’,‘109’,‘112’,‘265’);
Query OK, 1 row affected (0.00 sec)

配置冷备份
1、停止数据库服务器
[root@centos01 ~]# systemctl stop mysqld
2、备份数据库文件
[root@centos01 ~]# tar zcf /bak/mysql_all-$(date +%F).mysql.gz /usr/local/mysql/data/
tar: 从成员名中删除开头的“/”
[root@centos01 ~]# ls -ld /bak/mysql_all-2019-10-31.mysql.gz
-rw-r–r-- 1 root root 766598 10月 31 03:57 /bak/mysql_all-2019-10-31.mysql.gz
使用mysqldump备份表和库
[root@centos01 ~]# systemctl start mysqld
1、备份表
[root@centos01 ~]# mysqldump -uroot -ppwd@123 bdqn 一班成绩 > ./test/bdqn_一班成绩.sql
2、备份库
[root@centos01 ~]# mysqldump -uroot -ppwd@123 --databases bdqn > ./test/bdqn.sql
查看二进制文件
[root@centos01 ~]# cd /usr/local/mysql/data/
[root@centos01 data]# ls

刷新二进制日志
[root@centos01 data]# mysqladmin -u root -p flush-log
Enter password:
[root@centos01 data]# ls

录入二班成绩
[root@centos01 data]# mysql -u root -p
Enter password:
mysql> use bdqn;
Database changed

mysql> create table 二班成绩 (姓名 char(3),班级 char(2),学号 char(8),语文 char(3),数学 char(3),英语 char(3),理综 char(3),primary key (学号));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into 二班成绩 value (‘李宁’,‘二班’,‘20170824’,‘92’,‘98’,‘105’,‘235’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into 二班成绩 value (‘陈铭’,‘二班’,‘20170826’,‘111’,‘107’,‘96’,‘204’);
Query OK, 1 row affected (0.00 sec)

增量备份
1、查看二进制文件
[root@centos01 data]# cat mysql-bin.000006

2、复制二进制文件
[root@centos01 data]# cp mysql-bin.000006 /root/test/
3、刷新二进制文件
[root@centos01 data]# mysqladmin -u root -p flush-log
Enter password:
[root@centos01 data]# ls

录入二班成绩2
[root@centos01 data]# mysql -u root -p
Enter password:
mysql> use bdqn;
Database changed
mysql> insert into 二班成绩 value (‘付杰’,‘二班’,‘20170828’,‘115’,‘118’,‘116’,‘268’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into 二班成绩 value (‘郭尚’,‘二班’,‘20170830’,‘111’,‘99’,‘80’,‘259’);
Query OK, 1 row affected (0.01 sec)

增量备份
1、查看二进制文件
[root@centos01 data]# cat mysql-bin.000007

2、复制二进制文件
[root@centos01 data]# cp mysql-bin.000007 /root/test/
查看test目录

删除表、库、
mysql> drop table 一班成绩;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table 二班成绩;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> drop database bdqn;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;

恢复数据
mysql> source /root/test/bdqn.sql;
查看库以及表

使用增量备份基于id恢复二班陈铭成绩
1、查看备份的二进制日志id
[root@centos01 ~]# mysqlbinlog --no-defaults ./test/mysql-bin.000006

2、使用id恢复二班陈铭成绩
1）恢复二班成绩表格
[root@centos01 ~]# mysqlbinlog --stop-position=‘391’ ./test/mysql-bin.000006 | mysql -u root -p
Enter password:
2）恢复陈铭数据
[root@centos01 ~]# mysqlbinlog --start-position=‘632’ ./test/mysql-bin.000006 | mysql -u root -p
Enter password:
3、查看恢复

使用增量备份基于时间恢复二班付杰成绩
.1、查看备份的二进制日志id
[root@centos01 ~]# mysqlbinlog --no-defaults ./test/mysql-bin.000007

2、使用时间恢复二班付杰成绩
[root@centos01 ~]# mysqlbinlog --stop-datetime=‘2019-10-31 17:02:20’ ./test/mysql-bin.000007 | mysql -u root -p
Enter password:
3、查看恢复