数据恢复实战案例
场景
1）启动数据库二进制日志重启数据库
`修改配置文件`
[root@expr01 ~]# vim /etc/my.cnf
[mysql]
port = 3306
socket = /apps/mysql/data/mysql.sock
[mysqld]
log-bin = /apps/mysql/logbin/mysql-bin # 二进制日志开启
port = 3306
mysqlx_port = 33060
mysqlx_socket = /apps/mysql/data/mysqlx.sock
basedir = /apps/mysql
datadir = /apps/mysql/data
socket = /apps/mysql/data/mysql.sock
pid-file = /apps/mysql/data/mysqld.pid
log-error = /apps/mysql/log/error.log
default-authentication-plugin = mysql_native_password
log_timestamps = SYSTEM
[root@expr01 ~]# service mysqld restart
2）人为造一些数据
[root@expr01 ~]# mysql -uroot -p123456
# 创建数据库db1
mysql> create database IF NOT EXISTS db1 CHARACTER SET 'utf8';
# 进入数据db1
mysql> use db1;
# 创建student表并且设置表的初始id为1，指定InnoDB存储引擎，指定utf8字符集
mysql> CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
gender ENUM('M','F') default 'M'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
# 插入三条数据
mysql> insert student (name,age)values('xiaoming',20);
mysql> insert student (name,age)values('wangwu',21);
mysql> insert student (name,age)values('lisi',18);
# 验证数据
mysql> select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | xiaoming | 20 | M |
| 2 | wangwu | 21 | M |
| 3 | lisi | 18 | M |
| 4 | tom | 20 | M |
+----+----------+------+--------+
4 rows in set (0.00 sec)
# 创建teacher表并且设置表的初始id为1，指定InnoDB存储引擎，指定utf8字符集
mysql> CREATE TABLE teacher (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
gender ENUM('M','F') default 'M'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
# 插入一条数据
mysql> insert teacher (name,age)values('jack',20);
# 验证数据
mysql> select * from teacher;
+----+------+------+--------+
| id | name | age | gender |
+----+------+------+--------+
| 1 | jack | 20 | M |
+----+------+------+--------+
1 row in set (0.00 sec)
3）通过mysqldump对所有数据库备份
[root@expr01 ~]# mkdir /backup/
[root@expr01 ~]# mysqldump -uroot -p123456 -A --master-data=2 -S /apps/mysql/data/mysql.sock > /backup/all_2.sql
4）删除student表
[root@expr01 ~]# mysql -uroot -p123456
mysql> drop table student;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| teacher |
+---------------+
1 row in set (0.00 sec)
5）对teacher表插入数据
mysql> insert teacher (name,age)values('tanghao',21);
mysql> select * from teacher;
+----+---------+------+--------+
| id | name | age | gender |
+----+---------+------+--------+
| 1 | jack | 20 | M |
| 2 | tanghao | 21 | M |
+----+---------+------+--------+
2 rows in set (0.00 sec)
6）恢复数据
6.1）禁止用户访问
# 修改配置文件添加 skip-networking=1 或者 配置防火墙策略
# 我这里是添加 skip-networking=1
[root@expr01 ~]# vim /etc/my.cnf
[mysqld]
.....
skip-networking=1
.....
[root@expr01 ~]# service mysqld restart
6.2）查看备份的二进制文件记录的位置
[root@expr01 ~]# grep "CHANGE MASTER TO MASTER_LOG_FILE" /backup/all_2.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=2618;
6.3）提取二进制文件索引全量备份后的数据
[root@expr01 ~]# mysqlbinlog /apps/mysql/logbin/mysql-bin.000001 --start-position=2618 > /backup/inc.sql
6.4）在提取的数据中删除误操作drop
`# 查看DROP信息`
[root@expr01 ~]# sed -n '/^DROP/p' /backup/inc.sql
DROP TABLE `student` /* generated by server */
`# 将DROP开头的信息都删除`
[root@expr01 ~]# sed -i.bak '/^DROP/d' /backup/inc.sql
6.5）恢复数据
[root@expr01 ~]# mysql -uroot -p123456
# 临时关闭二进制记录
mysql> set sql_log_bin=0;
# 恢复数据
mysql> source /backup/all_2.sql
mysql> source /backup/inc.sql
# 开启二进制记录
mysql> set sql_log_bin=1;
6.6）验证数据是否恢复
mysql> use db1
Database changed
# 验证表是否恢复
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
| teacher |
+---------------+
2 rows in set (0.00 sec)
# 验证表中的数据是否恢复
mysql> select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | xiaoming | 20 | M |
| 2 | wangwu | 21 | M |
| 3 | lisi | 18 | M |
| 4 | tom | 20 | M |
+----+----------+------+--------+
4 rows in set (0.00 sec)
6.7）恢复数据访问
[root@expr01 ~]# sed -i '/^skip-networking/d' /etc/my.cnf
[root@expr01 ~]# service mysqld restart
可以看出数据已经全部恢复。
注意：我们要时常备份数据库，二进制日志开启也十分重要也需要备份
注：这样恢复只适用于丢失的表和其他表没有关联联系。如果有关联关系的表，例如（订单表和库存表）就不适用这样恢复数据，只能恢复到drop以前，然后在通过手动录入的方式进行恢复，所有丢失了数据恢复是很麻烦的