Mysql删库跑路，如何恢复数据？

问题

      删库跑路，数据还能恢复吗？

       我们经常听说某某被领导训斥了，对领导心生痛恨，然后登录 Mysql 删库跑路。对于闲聊中经常听说过的一个段子，在现实生活中是否真的发生过，如果发生了，我们该如何解决，被删的数据还能恢复过来吗？

如何解决

        如果数据库所有的数据都删除了， 数据库没有备份，但所有的 binlog 日志都在的话，就可以使用 binlog 文件，从第一个开始逐个恢复每个 binlog 文件里的数据。但这种一般不会这么做，因为 binlog日志比较大，恢复起来比较耗时，而且 binlog 占用的磁盘空间也比较大，生产上都会定期删除的，所以一般不太可能使用 binlog 文件恢复整个数据库的。

        一般通用的做法是，每天凌晨后做一次全量数据库的备份，那么恢复数据库的时候，就可以使用最近一次全量备份再加上备份时间点后的 binlog 文件来恢复数据。

mysqldump备份数据库

        备份数据库我们一般使用 mysqldump 命令工具。

• 备份所有的数据库

        语法：mysqldump -u[用户名] -p[密码] --all-databases > /备份路径/备份文件名.sql

#备份全部数据库
mysqldump -uroot -p123456 --all-databases > backup_all_databases.sql

•  备份一个/多个数据库

        语法：mysqldump -u[用户名] -p[密码] --databases DB1 [DB2 DB3...]  > /备份路径/备份文件名.sql

#备份一个数据库
mysqldump -uroot -p123456 --databases database_test1 > backup_database_test1.sql#备份多个数据库
mysqldump -uroot -p123456 --databases database_test1 database_test2 > backup_database_test1_test2.sql

• 备份指定库中的指定表

        语法：mysqldump -u[用户名] -p[密码] [database] [table1] [table2] > /备份路径/备份文件名.sql

#备份库中的部分表
mysqldump -uroot -p123456 database_test1 table_test1 table_test2 > backup_tables.sql

mysqldump恢复数据 

• 恢复数据库

        备份所有数据库和备份指定库的恢复逻辑相同

        语法：mysql -u[用户名] -p[密码] < /备份文件路径/备份文件名.sql

#还原数据库
mysql -uroot -p123456 < backup_database_test1_test2.sql

•  恢复数据表

        恢复表的前提是表所在的库必须存在，且可任意指定库进行恢复操作

        语法：mysqldump -u[用户名] -p[密码] [database]  < /备份文件路径/备份文件名.sql

mysql -u root -p123456 database_test1 < backup_tables.sql

        当使用全量备份数据恢复了备份的数据，然后接下来就用 binlog 文件来恢复还没备份的数据，比如每天凌晨1点钟全量备份数据库数据，然后14:00点钟，数据都被删除了，此时用全量数据线恢复，再用凌晨1点钟到下午14:00的binlog 文件恢复剩下的数据。接下来，就介绍 binlog 日志文件。

binlog二进制归档日志

        binlog 二级制日志记录保存了所有执行过的修改操作语句，但不保存查询操作，二进制日志的主要用途包括数据恢复、数据备份和复制。

        MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

在配置文件中的[mysqld]部分增加如下配置:

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

查看binlog相关参数 

show variables like '%log_bin%';

 查询结果

• log_bin：binlog日志是否打开状态
• log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
• log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
• sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

查看有多少binlog文件

show binary logs;

查询结果

 

binlog 的日志格式

用参数 binlog_format 可以设置binlog日志的记录格式，mysql支持三种格式类型：

• STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。
• ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
• MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

binlog写入磁盘机制

binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。

• 为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
• 也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
• 还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

 

发生以下任何事件时, binlog日志文件会重新生成：

• 服务器启动或重新启动
• 服务器刷新日志，执行命令flush logs
• 日志文件大小达到 max_binlog_size 值，默认值为 1GB

删除 binlog 日志文件

删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000010';
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2024-09-21 14:00:00';

查看 binlog 日志文件

可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 # 查看bin-log二进制文件（带查询条件）
#根据时间日期
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime="2023-09-03 00:00:00" stop-datetime="2023-09-31 00:00:00"
#根据位置
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-position="5000" stop-position="20000"mysqlbinlog常见的选项有以下几个：
--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position：从二进制日志中读取指定position 事件位置作为开始。
--stop-position：从二进制日志中读取指定position 事件位置作为事件截至

查询结果如下

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230823 23:40:58 server id 1  end_log_pos 123 CRC32 0xed27440b  Start: binlog v 4, server v 5.7.43-log created 230823 23:40:58 at startup
ROLLBACK/*!*/;
# at 123
#230823 23:40:58 server id 1  end_log_pos 154 CRC32 0x57d3c47c  Previous-GTIDs
# [empty]
# at 154
#230903 21:45:18 server id 1  end_log_pos 219 CRC32 0xba0e6d16  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230903 21:45:18 server id 1  end_log_pos 404 CRC32 0xdb84c89b  Query   thread_id=2     exec_time=0     error_code=0
use `test1`/*!*/;
SET TIMESTAMP=1693748718/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER TABLE `test1`.`train_user`
DROP INDEX `idx_name`,
DROP INDEX `idx_telephone`,
DROP INDEX `idx_mail`
/*!*/;
# at 404
#230903 21:48:27 server id 1  end_log_pos 469 CRC32 0x95afb5c7  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 469
#230903 21:48:02 server id 1  end_log_pos 542 CRC32 0x361725c8  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1693748882/*!*/;
BEGIN
/*!*/;
# at 542
#230903 21:48:02 server id 1  end_log_pos 609 CRC32 0x2d1b1977  Table_map: `test1`.`train_user` mapped to number 121
# at 609
#230903 21:48:02 server id 1  end_log_pos 801 CRC32 0x280ab5da  Update_rows: table id 121 flags: STMT_END_F
### UPDATE `test1`.`train_user`
### WHERE
###   @1=1
###   @2='test'
###   @3='25D55AD283AA400AF464C76D713C07AD'
###   @4='123456789'
###   @5='[e-mail]'
###   @6=1
### SET
###   @1=1
###   @2='test'
###   @3='25D55AD283AA400AF464C76D713C07AD'
###   @4='13202013849'
###   @5='[e-mail]'
###   @6=1
# at 801
#230903 21:48:27 server id 1  end_log_pos 832 CRC32 0x8fd4a4df  Xid = 156
COMMIT/*!*/;
# at 832
#230903 21:48:42 server id 1  end_log_pos 897 CRC32 0x995be1d1  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 897
#230903 21:48:10 server id 1  end_log_pos 970 CRC32 0x0dda090a  Query   thread_id=3     exec_time=17    error_code=0
SET TIMESTAMP=1693748890/*!*/;
BEGIN
/*!*/;
# at 970
#230903 21:48:10 server id 1  end_log_pos 1037 CRC32 0x3333b17b         Table_map: `test1`.`train_user` mapped to number 121
# at 1037
#230903 21:48:10 server id 1  end_log_pos 1231 CRC32 0xef64abb3         Update_rows: table id 121 flags: STMT_END_F
### UPDATE `test1`.`train_user`
### WHERE
###   @1=1
###   @2='test'
###   @3='25D55AD283AA400AF464C76D713C07AD'
###   @4='13202013849'
###   @5='[e-mail]'
###   @6=1
### SET
###   @1=1
###   @2='test'
###   @3='25D55AD283AA400AF464C76D713C07AD'
###   @4='17666031514'
###   @5='[e-mail]'
###   @6=1
# at 1231
#230903 21:48:42 server id 1  end_log_pos 1262 CRC32 0xc8db8a4d         Xid = 171
COMMIT/*!*/;
# at 1262
#230903 21:52:21 server id 1  end_log_pos 1327 CRC32 0xa92e5799         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1327
#230903 21:52:21 server id 1  end_log_pos 1400 CRC32 0x145371dd         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1693749141/*!*/;
BEGIN
/*!*/;
# at 1400
#230903 21:52:21 server id 1  end_log_pos 1467 CRC32 0xfc342a84         Table_map: `test1`.`train_user` mapped to number 121
# at 1467
#230903 21:52:21 server id 1  end_log_pos 1545 CRC32 0x312e239c         Write_rows: table id 121 flags: STMT_END_F
### INSERT INTO `test1`.`train_user`
### SET
###   @1=2
###   @2='lisan'
###   @3='Aa123456'
###   @4='18814094148'
###   @5=''
###   @6=0
# at 1545
#230903 21:52:21 server id 1  end_log_pos 1576 CRC32 0x0302a530         Xid = 204
COMMIT/*!*/;
# at 1576
#230903 21:53:16 server id 1  end_log_pos 1641 CRC32 0xff5518fe         Anonymous_GTID  last_committed=4        sequence_number=5       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1641
#230903 21:53:16 server id 1  end_log_pos 1714 CRC32 0xbe05b40c         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1693749196/*!*/;
BEGIN
/*!*/;
# at 1714
#230903 21:53:16 server id 1  end_log_pos 1781 CRC32 0xc4a966ca         Table_map: `test1`.`train_user` mapped to number 121
# at 1781
#230903 21:53:16 server id 1  end_log_pos 1916 CRC32 0xceb8fa72         Update_rows: table id 121 flags: STMT_END_F
### UPDATE `test1`.`train_user`
### WHERE
###   @1=2
###   @2='lisan'
###   @3='Aa123456'
###   @4='18814094148'
###   @5=''
###   @6=0
### SET
###   @1=2
###   @2='lisan'
###   @3='Aa123456'
###   @4='18814094148'
###   @5='[e-mail]'
###   @6=0
# at 1916
#230903 21:53:16 server id 1  end_log_pos 1947 CRC32 0x75d7152d         Xid = 212
COMMIT/*!*/;
# at 1947
#230903 21:53:21 server id 1  end_log_pos 2012 CRC32 0xaaad4f1d         Anonymous_GTID  last_committed=5        sequence_number=6       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2012
#230903 21:53:21 server id 1  end_log_pos 2085 CRC32 0xeefb9dd0         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1693749201/*!*/;

binlog日志文件恢复数据

        根据自己需要恢复数据的起始位置,进行定位，来恢复数据。每条sql的上下都有BEGIN和COMMIT, 我们可以根据位置来定位，也可以根据时间日期来定位。

        看上面的 binlog 日志，我们先根据位置来定位，找到需要恢复数据的BEGIN, 可以看到BEGIN前 的 at 1641 的起始位置，和COMMIT 后的 at 1947 的结束位置。

        此时我们可以根据mysqlbinlog 命令的起始位置来恢复数据。如下：

mysqlbinlog  --no-defaults --start-position=1641 --stop-position=1947 --database=test C:/ProgramData/MySQL/data/mysqlbinlog.000007 | mysql -uroot -padmin -v gorgor_user

        我们也可以根据时间日期来定位，进行数据的恢复。我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1693749196，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1693749201，转成datetime格式。

mysqlbinlog  --no-defaults --start-datetime="2023-9-3 21:53:16" --stop-datetime="2023-9-3 21:53:21" --database=test C:/ProgramData/MySQL/data/mysqlbinlog.000007 | mysql -uroot -padmin -v gorgor_user