Mariadb 通过binlog恢复删除（drop table）的数据
首先要在数据库中开启binlog，在配置文件中加入这一句。
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mariadb/mariadb.log pid-file=/run/mariadb/mariadb.pid log-bin=mysql-bin
用此命令可以查看是否启用：show variables like '%sql_log_bin%';
在数据库中添加测试数据库及测试表：
MariaDB [test]> create table t3(id int ,name varchar(50)); Query OK, 0 rows affected (0.008 sec) MariaDB [test]> insert into t3 values(1,'11111111111'); Query OK, 1 row affected (0.002 sec) MariaDB [test]> insert into t3 values(2,'11111111'); Query OK, 1 row affected (0.013 sec) MariaDB [test]> insert into t3 values(3,'11aa111111'); Query OK, 1 row affected (0.002 sec) MariaDB [test]> insert into t3 values(4,'11aa111'); Query OK, 1 row affected (0.002 sec) MariaDB [test]> insert into t3 values(4,'11aa111'); Query OK, 1 row affected (0.001 sec) MariaDB [test]> select * from t3; +------+-------------+ | id | name | +------+-------------+ | 1 | 11111111111 | | 2 | 11111111 | | 3 | 11aa111111 | | 4 | 11aa111 | | 4 | 11aa111 | +------+-------------+ 5 rows in set (0.001 sec) MariaDB [test]> delete from t3 where id=4; Query OK, 2 rows affected (0.002 sec) MariaDB [test]> select * from t3; +------+-------------+ | id | name | +------+-------------+ | 1 | 11111111111 | | 2 | 11111111 | | 3 | 11aa111111 | +------+-------------+ 3 rows in set (0.000 sec)
查看binlog记录的详细信息：
MariaDB [test]> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 2782 | | mysql-bin.000002 | 385 | +------------------+-----------+ 2 rows in set (0.002 sec) MariaDB [test]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 385 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [test]> show binlog events in 'mysql-bin.000002'; +------------------+-----+-------------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------------+-----------+-------------+------------------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.27-MariaDB-log, Binlog ver: 4 | | mysql-bin.000002 | 256 | Gtid_list | 1 | 299 | [0-1-654] | | mysql-bin.000002 | 299 | Binlog_checkpoint | 1 | 342 | mysql-bin.000001 | | mysql-bin.000002 | 342 | Binlog_checkpoint | 1 | 385 | mysql-bin.000002 | +------------------+-----+-------------------+-----------+-------------+------------------------------------------------+ 4 rows in set (0.001 sec) MariaDB [test]> show binlog events in 'mysql-bin.000001'; +------------------+------+-------------------+-----------+-------------+-------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------------+-----------+-------------+-------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.27-MariaDB-log, Binlog ver: 4 | | mysql-bin.000001 | 256 | Gtid_list | 1 | 299 | [0-1-643] | | mysql-bin.000001 | 299 | Binlog_checkpoint | 1 | 342 | mysql-bin.000001 | | mysql-bin.000001 | 342 | Gtid | 1 | 384 | BEGIN GTID 0-1-644 | | mysql-bin.000001 | 384 | Annotate_rows | 1 | 439 | insert into t2 values(2,'aaaaa') | | mysql-bin.000001 | 439 | Table_map | 1 | 487 | table_id: 20 (test.t2) | | mysql-bin.000001 | 487 | Write_rows_v1 | 1 | 531 | table_id: 20 flags: STMT_END_F | | mysql-bin.000001 | 531 | Xid | 1 | 562 | COMMIT /* xid=9 */ | | mysql-bin.000001 | 562 | Gtid | 1 | 604 | BEGIN GTID 0-1-645 | | mysql-bin.000001 | 604 | Annotate_rows | 1 | 659 | insert into t2 values(3,'bbaaa') | | mysql-bin.000001 | 659 | Table_map | 1 | 707 | table_id: 20 (test.t2) | | mysql-bin.000001 | 707 | Write_rows_v1 | 1 | 751 | table_id: 20 flags: STMT_END_F | | mysql-bin.000001 | 751 | Xid | 1 | 782 | COMMIT /* xid=10 */ | | mysql-bin.000001 | 782 | Gtid | 1 | 824 | BEGIN GTID 0-1-646 | | mysql-bin.000001 | 824 | Annotate_rows | 1 | 882 | insert into t2 values(4,'baaabaaa') | | mysql-bin.000001 | 882 | Table_map | 1 | 930 | table_id: 20 (test.t2) | | mysql-bin.000001 | 930 | Write_rows_v1 | 1 | 977 | table_id: 20 flags: STMT_END_F | | mysql-bin.000001 | 977 | Xid | 1 | 1008 | COMMIT /* xid=11 */ | | mysql-bin.000001 | 1008 | Gtid | 1 | 1050 | BEGIN GTID 0-1-647 | | mysql-bin.000001 | 1050 | Annotate_rows | 1 | 1098 | delete from t2 where id=1 | | mysql-bin.000001 | 1098 | Table_map | 1 | 1146 | table_id: 20 (test.t2) | | mysql-bin.000001 | 1146 | Delete_rows_v1 | 1 | 1190 | table_id: 20 flags: STMT_END_F | | mysql-bin.000001 | 1190 | Xid | 1 | 1221 | COMMIT /* xid=13 */ | | mysql-bin.000001 | 1221 | Gtid | 1 | 1263 | GTID 0-1-648 | | mysql-bin.000001 | 1263 | Query | 1 | 1371 | use `test`; create table t3(id int ,name varchar(50)) | | mysql-bin.000001 | 1371 | Gtid | 1 | 1413 | BEGIN GTID 0-1-649 | | mysql-bin.000001 | 1413 | Annotate_rows | 1 | 1474 | insert into t3 values(1,'11111111111') | | mysql-bin.000001 | 1474 | Table_map | 1 | 1522 | table_id: 21 (test.t3) | | mysql-bin.000001 | 1522 | Write_rows_v1 | 1 | 1572 | table_id: 21 flags: STMT_END_F | | mysql-bin.000001 | 1572 | Xid | 1 | 1603 | COMMIT /* xid=23 */ | | mysql-bin.000001 | 1603 | Gtid | 1 | 1645 | BEGIN GTID 0-1-650 | | mysql-bin.000001 | 1645 | Annotate_rows | 1 | 1703 | insert into t3 values(2,'11111111') | | mysql-bin.000001 | 1703 | Table_map | 1 | 1751 | table_id: 21 (test.t3) | | mysql-bin.000001 | 1751 | Write_rows_v1 | 1 | 1798 | table_id: 21 flags: STMT_END_F | | mysql-bin.000001 | 1798 | Xid | 1 | 1829 | COMMIT /* xid=24 */ | | mysql-bin.000001 | 1829 | Gtid | 1 | 1871 | BEGIN GTID 0-1-651 | | mysql-bin.000001 | 1871 | Annotate_rows | 1 | 1931 | insert into t3 values(3,'11aa111111') | | mysql-bin.000001 | 1931 | Table_map | 1 | 1979 | table_id: 21 (test.t3) | | mysql-bin.000001 | 1979 | Write_rows_v1 | 1 | 2028 | table_id: 21 flags: STMT_END_F | | mysql-bin.000001 | 2028 | Xid | 1 | 2059 | COMMIT /* xid=25 */ | | mysql-bin.000001 | 2059 | Gtid | 1 | 2101 | BEGIN GTID 0-1-652 | | mysql-bin.000001 | 2101 | Annotate_rows | 1 | 2158 | insert into t3 values(4,'11aa111') | | mysql-bin.000001 | 2158 | Table_map | 1 | 2206 | table_id: 21 (test.t3) | | mysql-bin.000001 | 2206 | Write_rows_v1 | 1 | 2252 | table_id: 21 flags: STMT_END_F | | mysql-bin.000001 | 2252 | Xid | 1 | 2283 | COMMIT /* xid=26 */ | | mysql-bin.000001 | 2283 | Gtid | 1 | 2325 | BEGIN GTID 0-1-653 | | mysql-bin.000001 | 2325 | Annotate_rows | 1 | 2382 | insert into t3 values(4,'11aa111') | | mysql-bin.000001 | 2382 | Table_map | 1 | 2430 | table_id: 21 (test.t3) | | mysql-bin.000001 | 2430 | Write_rows_v1 | 1 | 2476 | table_id: 21 flags: STMT_END_F | | mysql-bin.000001 | 2476 | Xid | 1 | 2507 | COMMIT /* xid=27 */ | | mysql-bin.000001 | 2507 | Gtid | 1 | 2549 | BEGIN GTID 0-1-654 | | mysql-bin.000001 | 2549 | Annotate_rows | 1 | 2597 | delete from t3 where id=4 | | mysql-bin.000001 | 2597 | Table_map | 1 | 2645 | table_id: 21 (test.t3) | | mysql-bin.000001 | 2645 | Delete_rows_v1 | 1 | 2704 | table_id: 21 flags: STMT_END_F | | mysql-bin.000001 | 2704 | Xid | 1 | 2735 | COMMIT /* xid=29 */ | | mysql-bin.000001 | 2735 | Rotate | 1 | 2782 | mysql-bin.000002;pos=4 | +------------------+------+-------------------+-----------+-------------+-------------------------------------------------------+ 56 rows in set (0.001 sec)
测试删表
MariaDB [test]> drop table t3; Query OK, 0 rows affected (0.017 sec) MariaDB [test]> select * from t3; ERROR 1146 (42S02): Table 'test.t3' doesn't exist
通过binlog文件恢复相关的SQL语句：
[root@localhost mysql]# mysqlbinlog --start-position=1263 --stop-position=2476 mysql-bin.000001 > t3.sql
停止记录相关日志：
MariaDB [test]> set sql_log_bin=0; Query OK, 0 rows affected (0.001 sec)
执行恢复文件中的相关语句：
MariaDB [test]> source /var/lib/mysql/t3.sql Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Database changed
测试恢复情况：
MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | t | | t2 | | t3 | +----------------+ 3 rows in set (0.001 sec) MariaDB [test]> select * from t3; +------+-------------+ | id | name | +------+-------------+ | 1 | 11111111111 | | 2 | 11111111 | | 3 | 11aa111111 | | 4 | 11aa111 | +------+-------------+ 4 rows in set (0.001 sec)
恢复日志记录模式：
MariaDB [test]> set sql_log_bin=1; Query OK, 0 rows affected (0.001 sec)
