mysqld:表mysql.plugin不存在_如何删库以后不跑路

我们都知道事务的基本特性 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 SHOP6666', '/', 'TEST SHOP6666', '/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

原文作者：oliver-l

转自链接：如何删库以后不跑路 | MySQL 技术论坛