如何删库以后不跑路

我们都知道事务的基本特性ACID，事务具有持久性，一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

经常会听到哪个哪个程序员，删库跑路了，如果真的把数据库删了，有什么办法可以拯救一下这些操作或者在此事件发生前，有什么途径预防一下呢。

首先我们要先了解二进制日志

参考MySQL二进制日志总结

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。

二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。

对于事务表的操作，二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)，提交前的每个二进制日志记录都先cache，提交时写入。

所以，对于事务表来说，一个事务中可能包含多条二进制日志事件，它们会在提交时一次性写入。而对于非事务表的操作，每次执行完语句就直接写入。

可通过命令show variables like '%log_bin%';查看二进制日志是否开启，可以查看到目前我的二进制日志是开启的

MariaDB/MySQL默认没有启动二进制日志，需要在配置文件中的[mysqld]部分设置log-bin也可以。注意：对于mysql 5.7，直接启动binlog可能会导致mysql服务启动失败，这时需要在配置文件中的mysqld为mysql实例分配server_id。

添加配置如下几个参数

server-id=1
log-bin=[on|filename]
log_bin_index=filename.index 
#配置信息为log-bin的filename加上.index,如我这边log-bin配置为mysql-bin,这边配置就为mysql-bin.index
binlog_format=[mixed|row|statement]
#STATEMENT模式（SBR）
#每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

#ROW模式（RBR）
#不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

# MIXED模式（MBR）
#以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

查看二进制日志

通过命令show binary logs;可查看二进制日志文件

在查看二进制日志之前，我们先使用命令flush logs;清空一下二进制日志内容

再使用show master status查看当前正在运行的二进制日志文件，可以查看到当前正在运行的二进制日志文件为mysql-bin.000056;

再通过命令show binlog events in 'mysql-bin.000056';查看二进制文件事件内容，如下图所示

其中Log_name为当前二进制文件名，Pos为当前事件开始偏移量，Event_type为当前事件类型，Server_id为配置文件中配置的server-id值，End_log_pos为当前事件结束偏移量，Info为对事件的相关描述（如事务开始结束，执行sql语句等）

当然我们还可以使用mysqlbinlog工具

要查看二进制文件具体内容，可执行mysqlbinlog [option] log-file1 log-file2...读取解析二进制文件

以下是常用的几个选项：

-d,--database=name：只查看指定数据库的日志操作

-o,--offset= #：忽略掉日志中的前n个操作命令

-r,--result-file=name：将输出的日志信息输出到指定的文件中，使用重定向也一样可以。

-s,--short-form：显示简单格式的日志，只记录一些普通的语句，会省略掉一些额外的信息如位置信息和时间信息以及基于行的日志。可以用来调试，生产环境千万不可使用

--set-charset=char_name：在输出日志信息到文件中时，在文件第一行加上``set` `names char_name`

--start-datetime,--stop-datetime：指定输出开始时间和结束时间内的所有日志信息

--start-position= #,--stop-position=#：指定输出开始位置和结束位置内的所有日志信息

-v,-vv：显示更详细信息，基于row的日志默认不会显示出来，此时使用-v或-vv可以查看

我通过执行mysqlbinlog /www/server/data/mysql-bin.000056 > /root/binlog.txt将输出内容重定向到binlog.txt文件中

以下为二进制文件输出的相关内容

# at 52746
#201019 14:33:53 server id 1  end_log_pos 52843 CRC32 0xfa71c68d     Query    thread_id=41332    exec_time=0    error_code=0
SET TIMESTAMP=1603089233/*!*/;
BEGIN
/*!*/;
# at 52843
#201019 14:33:53 server id 1  end_log_pos 53228 CRC32 0xac4385c3     Query    thread_id=41332    exec_time=0    error_code=0
SET TIMESTAMP=1603089233/*!*/;
update `goods` set `min_price_attr_id` = 66938, `max_price_attr_id` = 66938, `goods`.`updated_at` = '2020-10-18 23:33:53' where `id` = 666 and `goods`.`deleted_at` is null and (`store_id` = 3) and (`is_store` = 1 and `max_price` is not null and `min_price` is not null) order by `position` asc
/*!*/;
# at 53228
#201019 14:33:53 server id 1  end_log_pos 53259 CRC32 0x75346610     Xid = 1227571
COMMIT/*!*/;
# at 53259
#201019 14:36:25 server id 1  end_log_pos 53324 CRC32 0x19d0106c     Anonymous_GTID    last_committed=17    sequence_number=18    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 53324
#201019 14:36:25 server id 1  end_log_pos 53421 CRC32 0xb8303b88     Query    thread_id=41341    exec_time=0    error_code=0
SET TIMESTAMP=1603089385/*!*/;
BEGIN
/*!*/;
# at 53421
#201019 14:36:25 server id 1  end_log_pos 53806 CRC32 0xe0a65551     Query    thread_id=41341    exec_time=0    error_code=0
SET TIMESTAMP=1603089385/*!*/;
update `goods` set `min_price_attr_id` = 66938, `max_price_attr_id` = 66938, `goods`.`updated_at` = '2020-10-18 23:36:25' where `id` = 666 and `goods`.`deleted_at` is null and (`store_id` = 3) and (`is_store` = 1 and `max_price` is not null and `min_price` is not null) order by `position` asc
/*!*/;
# at 53806
#201019 14:36:25 server id 1  end_log_pos 53837 CRC32 0x79445d95     Xid = 1227807
COMMIT/*!*/;

内容说明参考mysql binlog之一 binlog输出内容的说明

• 以上信息中有许多# at开头的行，用于标示每个事件在binlog中的偏移量，例如# at 52746表示该事件在binlog的位置是52746字节之后。两个# at之间标示了mysql中的一个事件，而且在事件开始时也会通过end_log_pos标示事件结束的位置

• server id 1表示产生该事务服务器id号为1，这是在my.cnf文件中定义的，该id在集群内不能重复。一来是对事务产生的server做标识，二来是在类似双主模式下的集群环境里用于中断binlog循环复制重做的问题

• CRC32用于校验binlog文件的完整性

• GTID表示启动了GTID属性

• last_committed=17事务组提交标识、同一组提交的事务具备相同的last_committed值，可以在从库并行重放，以减少同步延迟。

• sequence_number=18，事务对应的顺序号，该值单调递增，同时也标识了同一组提交事务的顺序，在从库设置slave_preserve_commit_order=1时，依赖该值模拟主库的提交顺序，在从库提交。以达到数据落盘过程完全一致

• SET @@SESSION.GTID_NEXT=’ANONYMOUS’,标识该事务使用的GTID序列号

• SET TIMESTAMP=1603089385/!/;标识了该事务产生的时间，在一些特殊情况下保证数据一致性。比如使用了now函数的sql，可能延迟了很长时间才传到备库，此时now函数取该值插入，保证了与主库执行时间的一致。

• begin开始执行事务

• Table_map: test.testxxxx mapped to number 185，标识要操作哪个库的哪张表

• update goods set min_price_attr_id = 66938, max_price_attr_id = 66938, goods.updated_at = ‘2020-10-18 23:36:25’ where id = 666 and goods.deleted_at is null and (store_id = 3) and (is_store = 1 and max_price is not null and min_price is not null) order by position asc为具体执行的sql语句

• Xid = 1227807是保证数据完整性的一个标识，每个DML事务提交并完全写入binlog中结尾处一定会有Xid（DDL是没有的）。在redolog中也会记录该Xid值。在2PC提交机制中用此标识在redolog和binlog进行对比，崩溃时如果redolog没有commit，在redolog和binlog都有该值时，则事务重做，如果binlog没有找到该值时，则事务回滚。如果redolog也没有该值时，无需查找binlog，事务直接回滚

• 最后由COMMIT标识提交事务

删库前的准备工作

开启二进制日志

在配置文件中启动相应的二进制日志

定期备份数据库

可编写数据备份脚本，定期执行定时任务保存备份

#! /bin/bash

user="root"
passwd="123456"
database="test"

BCK_DIR="/root/mysql-backups"  #备份文件目录
DATE=`date +%F`

# 创建文件
mkdir -p $BCK_DIR/$DATE

mysqldump -u$user -p$passwd --databases $database > $BCK_DIR/$DATE/$database.sql

#crontab -e
#设置每天凌晨一点备份 
0 1 * * * /home/sh/dump.sh

实践删库

在数据库中创建名字为binlog的数据库

其中数据库存在一张名为test的表

在删除数据库之前，我们首先要对数据库全库进行全量备份mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

因为在执行二进制日志文件撤回前，数据库需要存在相关数据，比如相应的表结构数据等。使我们可以恢复到上一次备份前的数据，再通过增量日志，使我们可以恢复到误删之前的环境

然后我们执行命令drop database binlog命令删除该数据库

首先我们通过备份的数据，回退到上一次备份的数据中，使用命令cat /backup/mysqldump/all.db > mysql -uroot -p

执行命令mysqlbinlog /www/server/data/mysql-bin.000058 > /root/mysql-bin.000058.txt,将二进制日志文件内容输出到txt文件中

查找二进制文件内容，并定位到drop database binlog语句的位置中

# at 331017
# at 331049
#201020 16:58:58 server id 1  end_log_pos 331049 CRC32 0x639270f4     Intvar
SET INSERT_ID=1603/*!*/;
#201020 16:58:58 server id 1  end_log_pos 331552 CRC32 0xf87e2911     Query    thread_id=51712    exec_time=0    error_code=0
SET TIMESTAMP=1603184338/*!*/;
insert into `trackers` (`type`, `type_name`, `value`, `currency`, `content`, `uuid`, `store_id`, `time`, `from_host`, `from_path`, `url_host`, `url_path`, `updated_at`, `created_at`) values (102, 'VIEW_GOODS_CONTENT', '{{$selectAttr[\'price\']}}', 'USD', NULL, '034DF7A7E1E3E1590C5FCF28BBB0A975', 3, 1603184338, 'test.belment.cn', '/', 'test.belment.cn', '/product/ODQ2', '2020-10-20 01:58:58', '2020-10-20 01:58:58')
/*!*/;
# at 331552
#201020 16:58:58 server id 1  end_log_pos 331583 CRC32 0xca2c5c6a     Xid = 1495527
COMMIT/*!*/;
# at 331583
#201020 16:59:35 server id 1  end_log_pos 331648 CRC32 0x57c85e3d     Anonymous_GTID    last_committed=319    sequence_number=320    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 331648
#201020 16:59:35 server id 1  end_log_pos 331746 CRC32 0xd22d3ecb     Query    thread_id=51675    exec_time=0    error_code=0
SET TIMESTAMP=1603184375/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
drop database binlog
/*!*/;
# at 331746
#201020 16:59:48 server id 1  end_log_pos 331811 CRC32 0xf21596d0     Anonymous_GTID    last_committed=320    sequence_number=321    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 331811
#201020 16:59:48 server id 1  end_log_pos 331908 CRC32 0x8acc8fd4     Query    thread_id=51719    exec_time=0    error_code=0
SET TIMESTAMP=1603184388/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;

由此可见，我们应该将二进制文件偏移量重置到331583

mysqlbinlog /www/server/data/mysql-bin.000058 --stop-position=331583 | mysql -u root -p

本作品采用《CC 协议》，转载必须注明作者和本文链接