先来一段 自行体会
#----------------------------------------------------------------------------------
#模拟通过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-bin.000005 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) #开启自动commit 便于测试
mysql> set autocommit=1;
mysql> create database nod;
Query OK, 1 row affected (0.00 sec) mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 211 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) mysql>use nod;
mysql> create table luna(id int);
Query OK, 0 rows affected (0.08 sec) mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 314 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) mysql> insert into luna values(1);
Query OK, 1 row affected (0.00 sec) mysql> insert into luna values(2);
Query OK, 1 row affected (0.00 sec) mysql> insert into luna values(3);
Query OK, 1 row affected (0.02 sec) mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 896 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) mysql> select * from luna;
+------+
| id |
+------+
| 1 |
| 22 |
| 3 |
+------+
3 rows in set (0.00 sec) mysql> drop table luna;
Query OK, 0 rows affected (0.02 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| db |
| mysql |
| nod |
| performance_schema |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec) mysql> drop database nod;
Query OK, 0 rows affected (0.00 sec) mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 1494 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) [root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/mysql-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180804 15:24:48 server id 6 end_log_pos 120 CRC32 0x2f031b05 Start: binlog v 4, server v 5.6.38-log created 180804 15:24:48
# Warning: this binlog is either in use or was not closed properly.
# at 120
#180804 15:24:48 server id 6 end_log_pos 211 CRC32 0x7fc33332 Query thread_id=3 exec_time=8182 error_code=0
SET TIMESTAMP=1533367488/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database nod
/*!*/;
# at 211
#180804 15:24:48 server id 6 end_log_pos 314 CRC32 0xae4fffd2 Query thread_id=3 exec_time=8274 error_code=0
use `binlog`/*!*/;
SET TIMESTAMP=1533367488/*!*/;
create table luna(id int)
/*!*/;
# at 314
#180804 15:24:48 server id 6 end_log_pos 388 CRC32 0x829f43a8 Query thread_id=3 exec_time=8363 error_code=0
SET TIMESTAMP=1533367488/*!*/;
BEGIN
/*!*/;
# at 388
#180804 15:24:48 server id 6 end_log_pos 437 CRC32 0x1801dec8 Table_map: `binlog`.`luna` mapped to number 73
# at 437
#180804 15:24:48 server id 6 end_log_pos 477 CRC32 0xfb860ce0 Write_rows: table id 73 flags: STMT_END_F
### INSERT INTO `binlog`.`luna`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 477
#180804 15:24:48 server id 6 end_log_pos 508 CRC32 0x88bca0f6 Xid = 135
COMMIT/*!*/;
# at 508
#180804 15:24:48 server id 6 end_log_pos 582 CRC32 0xfdea7f27 Query thread_id=3 exec_time=8366 error_code=0
SET TIMESTAMP=1533367488/*!*/;
BEGIN
/*!*/;
# at 582
#180804 15:24:48 server id 6 end_log_pos 631 CRC32 0xac422642 Table_map: `binlog`.`luna` mapped to number 73
# at 631
#180804 15:24:48 server id 6 end_log_pos 671 CRC32 0x3817c497 Write_rows: table id 73 flags: STMT_END_F
### INSERT INTO `binlog`.`luna`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
# at 671
#180804 15:24:48 server id 6 end_log_pos 702 CRC32 0x4c81edc8 Xid = 136
COMMIT/*!*/;
# at 702
#180804 15:24:48 server id 6 end_log_pos 776 CRC32 0xf3c75c08 Query thread_id=3 exec_time=8369 error_code=0
SET TIMESTAMP=1533367488/*!*/;
BEGIN
/*!*/;
# at 776
#180804 15:24:48 server id 6 end_log_pos 825 CRC32 0x7d9b565c Table_map: `binlog`.`luna` mapped to number 73
# at 825
#180804 15:24:48 server id 6 end_log_pos 865 CRC32 0x773957ea Write_rows: table id 73 flags: STMT_END_F
### INSERT INTO `binlog`.`luna`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 865
#180804 15:24:48 server id 6 end_log_pos 896 CRC32 0x3df025e7 Xid = 137
COMMIT/*!*/;
# at 896
#180804 15:24:48 server id 6 end_log_pos 970 CRC32 0x500edb7f Query thread_id=3 exec_time=8432 error_code=0
SET TIMESTAMP=1533367488/*!*/;
BEGIN
/*!*/;
# at 970
#180804 15:24:48 server id 6 end_log_pos 1019 CRC32 0xdaf097a5 Table_map: `binlog`.`luna` mapped to number 73
# at 1019
#180804 15:24:48 server id 6 end_log_pos 1065 CRC32 0x3fccc29c Update_rows: table id 73 flags: STMT_END_F
### UPDATE `binlog`.`luna`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=22 /* INT meta=0 nullable=1 is_null=0 */
# at 1065
#180804 15:24:48 server id 6 end_log_pos 1096 CRC32 0x355a2b60 Xid = 141
COMMIT/*!*/;
# at 1096
#180804 15:24:48 server id 6 end_log_pos 1170 CRC32 0x4f936f7d Query thread_id=3 exec_time=8703 error_code=0
SET TIMESTAMP=1533367488/*!*/;
BEGIN
/*!*/;
# at 1170
#180804 15:24:48 server id 6 end_log_pos 1219 CRC32 0x36b88afd Table_map: `binlog`.`luna` mapped to number 73
# at 1219
#180804 15:24:48 server id 6 end_log_pos 1259 CRC32 0xd3e293de Delete_rows: table id 73 flags: STMT_END_F
### DELETE FROM `binlog`.`luna`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 1259
#180804 15:24:48 server id 6 end_log_pos 1290 CRC32 0x0a3ec6c4 Xid = 143
COMMIT/*!*/;
# at 1290
#180804 15:24:48 server id 6 end_log_pos 1411 CRC32 0x653cd09b Query thread_id=3 exec_time=8723 error_code=0
SET TIMESTAMP=1533367488/*!*/;
DROP TABLE `luna` /* generated by server */
/*!*/;
# at 1411
#180804 15:24:48 server id 6 end_log_pos 1494 CRC32 0x2ea0c2db Query thread_id=3 exec_time=8745 error_code=0
SET TIMESTAMP=1533367488/*!*/;
drop database nod
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; #------------------------------------------------------- [root@db01 ~]# mysqlbinlog --start-position=120 --stop-position=1170 /data/mysql/mysql-bin.000005 >/tmp/nod.sql; mysql> source /tmp/nod.sql; #-------------------------------------------------------
查看
#-------------------------------------------------------
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| db |
| mysql |
| nod |
| performance_schema |
| test |
| world |
+--------------------+ mysql> select * from luna;
+------+
| id |
+------+
| 1 |
| 22 |
| 3 |
+------+
3 rows in set (0.00 sec)
升级版本：
多库单表误删除期中某个库的恢复操作
使用mysqlbinlog -d参数 注意-d参数的使用
#-------------------------------------------------------------------------------
#
# 多库单表的操作
# Author：nod
# Date:18-08-05
#------------------------------------------------------------------------------- #-------------------------------------------------------------------------------
# 模拟环境
#-------------------------------------------------------------------------------
mysql> flush logs; mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec) mysql> create database nod;
Query OK, 1 row affected (0.00 sec) mysql> create database luna;
Query OK, 1 row affected (0.00 sec) mysql> use nod;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.06 sec) mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(3);
Query OK, 1 row affected (0.00 sec) mysql> use luna
Database changed
mysql> create table t2(id int);
Query OK, 0 rows affected (0.15 sec) mysql> insert into t2 values(44);
Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values(55);
Query OK, 1 row affected (0.06 sec) mysql> use nod;
Database changed
mysql> insert into t1 values(88);
Query OK, 1 row affected (0.02 sec) mysql> select * from nod.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 88 |
+------+
4 rows in set (0.00 sec) mysql> select * from luna.t2;
+------+
| id |
+------+
| 44 |
| 55 |
+------+
2 rows in set (0.00 sec) mysql> drop database nod;
Query OK, 1 row affected (0.05 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| db |
| luna |
| mysql |
| performance_schema |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec) #-------------------------------------------------------------------------------
# 分析binlog
# 注意参数使用-d 指定数据库 nod 此处不希望出现 luna
#-------------------------------------------------------------------------------
mysqlbinlog -d nod --base64-output=decode-rows -vvv /data/mysql/mysql-bin.000010
mysqlbinlog -d nod --start-position=120 --stop-position=1617 /data/mysql/mysql-bin.000010 >/tmp/bak_nod.sql #-------------------------------------------------------------------------------
# 数据库开始恢复
#-------------------------------------------------------------------------------
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec) mysql> source /tmp/bak_nod.sql;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Charset changed
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Database changed
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.10 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) #-------------------------------------------------------------------------------
# 检查恢复数据
#-------------------------------------------------------------------------------
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| db |
| luna |
| mysql |
| nod |
| performance_schema |
| test |
| world |
+--------------------+
9 rows in set (0.00 sec) mysql> select * from nod.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 88 |
+------+
4 rows in set (0.00 sec) mysql> select * from luna.t2;
+------+
| id |
+------+
| 44 |
| 55 |
+------+
2 rows in set (0.00 sec) #-------------------------------------------------------------------------------
# 数据恢复完毕，如之前drop前所示
#-------------------------------------------------------------------------------
sql层的查询日志
3.1、二进制日志都记录了什么？ 除了标准的select语句
(1) 已提交的DML事务语句,并拆分为多个事件（event）来进行记录
已提交的事务语句 注意是从begin-commit
例如此处begin—commit 拆分成5个event
begin --- 1
1 --- 2
2 --- 3
3 --- 4
commit --- 5
(2) 记录所有DDL、DCL等语句
总之二进制日志，会记录所有对数据库发生修改的操作
2、二进制记录格式有哪些？
statement：语句模式 就是sql语句
row：行模式，即数据行的变化过程
mixed：以上两者的混合模式。
我们企业推荐使用row模式，5.6中默认模式statement，5.7中默认row
3、两种模式有什么优缺点？
statement:
优点：简单明了，容易被看懂，就是sql语句，记录时需要更小的磁盘空间
缺点：记录不够严谨。特别害怕函数类的操作
row 模式：
优点：记录更加严谨
缺点：有可能需要更多的磁盘空间，不太容易读懂
为什么说row模式严谨：比如插入操作中含有now() 仔细揣摩 建议使用row模式
4、binlog的作用
binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看
记住一句话：
如果我拥有从数据库搭建开始所有的二进制日志，那么我可以把数据库恢复到任意一个时刻
备份恢复
复制
二进制日志管理操作实战：
- 1、查看二进制日志开关状态
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
- 2、设置binlog
vim /etc/my.cnf
二进制日志的开关，并且设置二进制日志的位置（/data/mysql/,路径必须事先存在并且有权限），
并且设定二进制日志格式前缀（mysql-bin），例如：mysql-bin.000001 mysql-bin.000002
# 在配置文件当中进行修改 /etc/my.cnf
log_bin=/data/mysql/mysql-bin # 二进制日志的开关并且设置二进制日志的位置
binlog_format=row
- 3、二进制日志的操作：
3.1 查询二进制日志的基本信息
(1)操作系统层面查看
cd /data/mysql/
ls –l #会发现有很多 最大数值编号的是最新的
(2)数据库内部查看 现在是statement格式
show binary logs; #查看有多少mysql-bin文件 查看目录作用类似
show master status;#查看当前的使用的mysql-bin文件
写满&重启数据库&执行命令会做文件的切割
show binlog events in 'mysql-bin.000032' limit 5;#查看mysql-bin文件内容
说明：
event：binlog最小的记录单元为event，一个事务会被拆分为多个event
evenet特性：每个event都有一个开始位置（start-position）和一个结束位置(stop-position)
所谓的位置：是evenet对整个二进制文件的相对位置
对于一个二进制日志中，前120个position是文件格式信息预留空间
也就是mysql第一个记录的事件，都是从120开始的。
截取日志一定要从Begin到commit 完整
show master status当中 Positon表示的含义 最后一个事件的结束位置