1.先学基础命令
- 查看当前数据库，都用了哪些binlog
mysql> show binary logs;
- 当前正在用哪个binlog
mysql> show master status;
- 刷新binlog日志
mysql> flush logs;
- 展示binlog日志文件
mysql> show binlog events
2.模拟binlog恢复
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000003 | 936 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs;
mysql> show binlog events in 'mysql-log-bin.000003';
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+
| mysql-log-bin.000003 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-log-bin.000003 | 123 | Previous_gtids | 100 | 154 | |
| mysql-log-bin.000003 | 154 | Anonymous_Gtid | 100 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-log-bin.000003 | 219 | Query | 100 | 316 | create database lol01 |
| mysql-log-bin.000003 | 316 | Anonymous_Gtid | 100 | 381 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-log-bin.000003 | 381 | Query | 100 | 483 | use `lol01`; create table tanke(id int) |
| mysql-log-bin.000003 | 483 | Anonymous_Gtid | 100 | 548 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-log-bin.000003 | 548 | Query | 100 | 621 | BEGIN |
| mysql-log-bin.000003 | 621 | Table_map | 100 | 670 | table_id: 110 (lol01.tanke) |
| mysql-log-bin.000003 | 670 | Write_rows | 100 | 720 | table_id: 110 flags: STMT_END_F |
| mysql-log-bin.000003 | 720 | Xid | 100 | 751 | COMMIT /* xid=93 */ |
| mysql-log-bin.000003 | 751 | Anonymous_Gtid | 100 | 816 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-log-bin.000003 | 816 | Query | 100 | 936 | use `lol01`; DROP TABLE `tanke` /* generated by server */ |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+
- 使用这个命令从开始位置，到结束为止 导出Sql日志。退出数据库，进入到 上一篇保存binlog文件下
mysql> exit
[root@VM-4-8-centos ~]# cd /mysql_log/log_bin_3306/
[root@VM-4-8-centos log_bin_3306]# ls
mysql-log-bin.000001 mysql-log-bin.000002 mysql-log-bin.000003 mysql-log-bin.index
[root@VM-4-8-centos log_bin_3306]# mysqlbinlog --start-position=381 --stop-position=752 mysql-log-bin.000003 > /opt/huifu.txt
[root@VM-4-8-centos log_bin_3306]# 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 10
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.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------+
| log_bin | ON |
| log_bin_basename | /mysql_log/log_bin_3306/mysql-log-bin |
| log_bin_index | /mysql_log/log_bin_3306/mysql-log-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | OFF |
+---------------------------------+---------------------------------------------+
6 rows in set (0.00 sec)
# 加载数据源
mysql> source /opt/huifu.txt
# 开启日志事务同步
mysql> set sql_log_bin=1;
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------+
| log_bin | ON |
| log_bin_basename | /mysql_log/log_bin_3306/mysql-log-bin |
| log_bin_index | /mysql_log/log_bin_3306/mysql-log-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------------+
6 rows in set (0.03 sec)
