1.显示当前的binlog
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000004 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.创建库
mysql> create database lol;
Query OK, 1 row affected (0.01 sec)
mysql> show binlog events in 'mysql-log-bin.000004';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-log-bin.000004 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-log-bin.000004 | 123 | Previous_gtids | 100 | 154 | |
| mysql-log-bin.000004 | 154 | Anonymous_Gtid | 100 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-log-bin.000004 | 219 | Query | 100 | 310 | create database lol |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)
3.重新生成一个binlog
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000005 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.创建表
mysql> use lol;
Database changed
mysql> create table fashi(name char(50));
Query OK, 0 rows affected (0.05 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000005 | 324 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-log-bin.000005';
+----------------------+-----+----------------+-----------+-------------+----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+----------------------------------------------+
| mysql-log-bin.000005 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-log-bin.000005 | 123 | Previous_gtids | 100 | 154 | |
| mysql-log-bin.000005 | 154 | Anonymous_Gtid | 100 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-log-bin.000005 | 219 | Query | 100 | 324 | use `lol`; create table fashi(name char(50)) |
+----------------------+-----+----------------+-----------+-------------+----------------------------------------------+
4 rows in set (0.00 sec)
5.重新生成一个binlog
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000006 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
6.插入法师表
mysql> insert into fashi values('ceshi'),('ceshi2'),('ceshi3');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show binlog events in 'mysql-log-bin.000006';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-log-bin.000006 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-log-bin.000006 | 123 | Previous_gtids | 100 | 154 | |
| mysql-log-bin.000006 | 154 | Anonymous_Gtid | 100 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-log-bin.000006 | 219 | Query | 100 | 290 | BEGIN |
| mysql-log-bin.000006 | 290 | Table_map | 100 | 339 | table_id: 114 (lol.fashi) |
| mysql-log-bin.000006 | 339 | Write_rows | 100 | 397 | table_id: 114 flags: STMT_END_F |
| mysql-log-bin.000006 | 397 | Xid | 100 | 428 | COMMIT /* xid=180 */ |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set (0.00 sec)
7.删除lol库
mysql> drop database lol;
Query OK, 1 row affected (0.13 sec)
8.开始恢复
cd /mysql_log/log_bin_3306/
mysqlbinlog --start-position=154 --stop-position=428 mysql-log-bin.000004 mysql-log-bin.000005 mysql-log-bin.000006 > /opt/huifu2.txt
* 进入mysql 数据库
[root@VM-4-8-centos opt]# mysql -uroot -psummer66
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
* 关闭日志事务同步
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.03 sec)
* 恢复日志
mysql> source /opt/huifu2.txt
* 再查询表
mysql> select * from lol.fashi;
+--------+
| name |
+--------+
| ceshi |
| ceshi2 |
| ceshi3 |
+--------+
3 rows in set (0.00 sec)
mysql>
