如何恢复MySQL数据库数据

共计 5651 个字符，预计需要花费 15 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章将为大家详细讲解有关如何恢复 MySQL 数据库数据，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

一、恢复方案

1、数据量不是特别大，可以将 mysqldump 命令备份的数据使用 mysql 客户端命令或者 source 命令完成数据的恢复；
2、使用 Xtrabackup 完成数据库的物理备份恢复，期间需要重启数据库服务；
3、使用 LVM 快照卷完成数据库物理备份恢复，期间需要重启数据库服务；

二、使用 mysqlbinlog 进行时间点恢复

1、介绍

mysqlbinlog 是一个从二进制日志中读取语句的工具，在 mysql 安装完成之后自带的。

2、二进制日志恢复原理

当使用 mysqldump 对数据库进行备份时，生成的备份文件中包含了数据库 DML 操作时的时间点以及备份时的二进制日志位置信息，如果单库，可以从某个时间点开始，进行时间点恢复；如果是主从架构，可以根据备份时的 –master-data= 2 和 –single-transaction，完成根据时间点或者位置点的恢复。

3、二进制日志恢复示例

（1）单库恢复示例

创建数据库，并插入测试数据

mysql  SHOW CREATE DATABASE test_db;
mysql  CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `age` tinyint(4) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
mysql  INSERT INTO student (name,age) VALUES(Jack ,23),(Tomcat ,24),(XiaoHong ,22),(ZhangFei ,29);

使用 mysqldump 进行全量备份，备份时滚动日志，同时记住二进制日志文件名称和日志的位置点

[root@WB-BLOG ~]# mysqldump -uroot -proot -h227.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events   /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e  show binary logs    bin_pos_`date +%F`.out

此时查看二进制日志文件名称和日志点位置如下

mysql  SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1497 |
| mysql-bin.000002 | 397 |
+------------------+-----------+
2 rows in set (0.00 sec)

使用了一段时间，不小心误操作，执行了如下的语句，将数据库中的数据全部修改了

mysql  UPDATE STUDENT SET name =  admin

过了一段时间，可能是几分钟，也可能是几个小时，有人反映网站登录有问题了，查看发现好多数据被误修改，而这段时间内，还一直有写入操作，如又新增了如下的记录

mysql  INSERT INTO student(name,age) VALUES(Hbase ,23),(BlackHole ,30);

此时需要恢复数据，首先为了防止数据继续写入，可以先锁表，暂停写入业务，通知用户系统维护，然后执行如下操作：

# 登录数据库，锁表，此时表只能读，不能写
mysql  USE test_db;
mysql  LOCK TABLE student READ;
#然后重新（注意是重新打开）打开一个 session 窗口，否则会话处出之后，锁就会释放。然后压缩备份现有数据和二进制日志文件
[root@WB-BLOG mysql_logs]# tar zcvf mysql_data.tar.gz /mysql_data/*
[root@WB-BLOG mysql_logs]# tar zcvf mysql_bin.tar.gz /mysql_logs/*
#导入最近备份的一次全备数据
[root@WB-BLOG ~]# mysql -uroot -proot -h227.0.0.1 -P3306   /tmp/test_db.sql 
#查看全备时的二进制日志文件和日志点
[root@WB-BLOG ~]# cat bin_pos_2018-06-24.out 
 Log_name File_size
 mysql-bin.000001 1497
 mysql-bin.000002 397
#将 861 这个点之后的二进制日志文件转换为一个 sql 文件
[root@WB-BLOG bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397   /tmp/tmp.sql
#使用 vim 编辑器编辑这个 sql 文件，找到其中的未加条件的 UPDATE 语句，然后将其删掉，然后将删掉 UPDATE 语句之后的 sql 脚本内容导入到数据库中
[root@WB-BLOG bin]# vim /tmp/tmp.sql
 use `test_db`/*!*/;
 SET TIMESTAMP=1522088753/*!*/;
 update student set name =  admin  # 删掉这一句
[root@WB-BLOG bin]# mysql -uroot -proot -h227.0.0.1 -P3306   /tmp/tmp.sql
#登录数据库查询数据是否恢复，可以查看被误修改的数据是否还原，然后对表执行解锁，再次全备数据
mysql  UNLOCK TABLES;

（2）主从架构数据恢复示例

环境

主库：192.168.199.10(node01)
从库：192.168.199.11(node02)

首先停止从库的 SQL 线程，然后在从库上全备数据，并输入 SHOW SLAVE STATUS 信息到备份文件中，SHOW SLAVE STATUS 的输出信息中记录了当前应用到了主库的哪个位置点的信息

# 登录从库，然后关闭 SQL 线程
mysql  STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)
#然后记录从库中当前应用的主库的二进制日志文件信息
[root@node02 mysql_data]# mysql -e  SHOW SLAVE STATUS \G    slave_`date +%F`.info
[root@node02 mysql_data]# mysqldump -uroot -proot -h227.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction   /tmp/mysql_test_db_`date +%F`.sql

在从库上备份完成之后，重新启动从库的 SQL 线程

mysql  START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

启动 SQL 线程之后，备份这段时间内在主库上的 DML 操作会重新同步到从库上。假如在主库上发生了一个误操作，没加条件更新了 student 表中的所有数据，导致了表中所有数据被修改，此时由于同步操作，从库也被修改了

# 登录主库，修改数据库的对外用户，使其暂不提供服务，然后滚动日志
mysql  UPDATE mysql.user SET Host =  127.0.0.1  WHERE User= tomcat 
Query OK, 1 rows affected (0.00 sec)
#刷新权限表
mysql  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#滚动日志
mysql  FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)
#将从库备份的数据及备份时刻的从库 slave 信息传到主库上
[root@node02 mysql_data]# scp /tmp/mysql_test_db_2018-06-24.sql 192.168.199.10:/root/
[root@node02 mysql_data]# scp slave_2018-06-24.info node01:/root/

备份主库的数据目录和二进制日志文件目录

[root@node01 mysql_logs]# tar zcvf mysql_master_data.tar.gz /mysql_data/*
[root@node01 mysql_logs]# tar zcvf mysql_logs.tar.gz /mysql_logs/*

导入从库最近一次备份的数据

[root@node01 mysql_logs]# mysql -uroot -proot -h227.0.0.1 -P3306   /root/mysql_test_db_2018-03-26.sql 
#注意：上述的操作不能锁主库的表，否则全备数据无法导入。

查看备份时刻的从库中应用到的主库二进制日志文件名称及位置点

[root@node01 mysql_logs]# cat /root/slave_2018-03-26.info
 Master_Log_File: master-bin.000002 # 备份时所应用的主库二进制日志文件名称
 Read_Master_Log_Pos: 395 # 备份时所应用的主库二进制日志文件的位置 

从该日志文件及日志点开始，将 395 日志点之后的日志文件转换为 sql 脚本，如果有多个二进制日志文件可以同时转换为 sql 脚本，如下所示

[root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.000002 --start-position=395   /tmp/tmp.sql
#将 master-bin.000003,master-bin.000004,master-bin.000005 合并到 /tmp.sql 文件中
[root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.00000{3,4,5} --start-position=395   /tmp/tmp.sql

找到误操作的 update 语句，然后删除该语句，并将增量的 sql 脚本导入数据库

[root@node01 mysql_logs]# vim /tmp/tmp.sql
 use `test_db`/*!*/;
 update student set name =  admin  # 删掉这一句
[root@node01 mysql_logs]# mysql -uroot -proot -h227.0.0.1 -P3306   /tmp/tmp.sql

登录数据库，查看数据是否正常，被误修改的数据是否已经恢复，如果恢复，则在主库上全备数据，然后传到从库，完成从库恢复

[root@node01 mysql_data]# mysqldump -uroot -proot -h227.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction --master-date=1   /tmp/master_test_db_`date +%F`.sql
[root@node01 mysql_data]# scp /tmp/master_test_db_2018-06-24.sql node01:/root/
#如果从库设置了只读，需要先去掉只读限制
mysql  SET GLOBAL read_only = OFF;
Query OK, 0 rows affected (0.00 sec)
#将数据导入从库
[root@node02 mysql_logs]# mysql -uroot -proot -h227.0.0.1 -P3306   /root/master_test_db_2018-06-24.sql
#开启从库的只读
mysql  SET GLOBAL read_only = ON;
Query OK, 0 rows affected (0.00 sec)

由于在主库上备份时添加了 –master-date= 1 参数，所以从库导入之后，不需要重新执行 change master 操作。

登录从库，查看 SHOW SLAVE STATUS 信息是否正常，如果正常，登录主库，重新修改授权表，然后对外提供服务

mysql  UPDATE mysql.user set Host =  192.168.0.%  WHERE User =  tomcat 
mysql  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

关于如何恢复 MySQL 数据库数据就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

向 AI 问一下细节