MySQL 二进制文件恢复数据基础版本

 

 

 

先来一段 自行体会 


 1 #---------------------------------------------------------------------------------- 2 #模拟通过binlog进行数据恢复 3 #---------------------------------------------------------------------------------- 4  5 #开启新的二进制日志 便于记录 6 mysql> flush logs; 7 Query OK, 0 rows affected (0.02 sec) 8  9 mysql> show master status; 10 +------------------+----------+--------------+------------------+-------------------+ 11 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 12 +------------------+----------+--------------+------------------+-------------------+ 13 | mysql-bin.000005 | 120 | | | | 14 +------------------+----------+--------------+------------------+-------------------+ 15 1 row in set (0.00 sec) 16  17 #开启自动commit 便于测试 18 mysql> set autocommit=1; 19 mysql> create database nod; 20 Query OK, 1 row affected (0.00 sec) 21  22 mysql> show master status; 23 +------------------+----------+--------------+------------------+-------------------+ 24 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 25 +------------------+----------+--------------+------------------+-------------------+ 26 | mysql-bin.000005 | 211 | | | | 27 +------------------+----------+--------------+------------------+-------------------+ 28 1 row in set (0.00 sec) 29  30 mysql>use nod; 31 mysql> create table luna(id int); 32 Query OK, 0 rows affected (0.08 sec) 33  34 mysql> show master status; 35 +------------------+----------+--------------+------------------+-------------------+ 36 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 37 +------------------+----------+--------------+------------------+-------------------+ 38 | mysql-bin.000005 | 314 | | | | 39 +------------------+----------+--------------+------------------+-------------------+ 40 1 row in set (0.00 sec) 41  42 mysql> insert into luna values(1); 43 Query OK, 1 row affected (0.00 sec) 44  45 mysql> insert into luna values(2); 46 Query OK, 1 row affected (0.00 sec) 47  48 mysql> insert into luna values(3); 49 Query OK, 1 row affected (0.02 sec) 50  51 mysql> show master status; 52 +------------------+----------+--------------+------------------+-------------------+ 53 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 54 +------------------+----------+--------------+------------------+-------------------+ 55 | mysql-bin.000005 | 896 | | | | 56 +------------------+----------+--------------+------------------+-------------------+ 57 1 row in set (0.00 sec) 58  59 mysql> select * from luna; 60 +------+ 61 | id | 62 +------+ 63 | 1 | 64 | 22 | 65 | 3 | 66 +------+ 67 3 rows in set (0.00 sec) 68  69 mysql> drop table luna; 70 Query OK, 0 rows affected (0.02 sec) 71  72 mysql> show databases; 73 +--------------------+ 74 | Database | 75 +--------------------+ 76 | information_schema | 77 | binlog | 78 | db | 79 | mysql | 80 | nod | 81 | performance_schema | 82 | test | 83 | world | 84 +--------------------+ 85 8 rows in set (0.00 sec) 86  87 mysql> drop database nod; 88 Query OK, 0 rows affected (0.00 sec) 89  90  91 mysql> show master status; 92 +------------------+----------+--------------+------------------+-------------------+ 93 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 94 +------------------+----------+--------------+------------------+-------------------+ 95 | mysql-bin.000005 | 1494 | | | | 96 +------------------+----------+--------------+------------------+-------------------+ 97 1 row in set (0.00 sec) 98  99 100 [root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/mysql-bin.000005101 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;102 /*!40019 SET @@session.max_insert_delayed_threads=0*/;103 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;104 DELIMITER /*!*/;105 # at 4106 #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:48107 # Warning: this binlog is either in use or was not closed properly.108 # at 120109 #180804 15:24:48 server id 6 end_log_pos 211 CRC32 0x7fc33332 Query thread_id=3 exec_time=8182 error_code=0110 SET TIMESTAMP=1533367488/*!*/;111 SET @@session.pseudo_thread_id=3/*!*/;112 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;113 SET @@session.sql_mode=1075838976/*!*/;114 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;115 /*!\C utf8 *//*!*/;116 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;117 SET @@session.lc_time_names=0/*!*/;118 SET @@session.collation_database=DEFAULT/*!*/;119 create database nod120 /*!*/;121 # at 211122 #180804 15:24:48 server id 6 end_log_pos 314 CRC32 0xae4fffd2 Query thread_id=3 exec_time=8274 error_code=0123 use `binlog`/*!*/;124 SET TIMESTAMP=1533367488/*!*/;125 create table luna(id int)126 /*!*/;127 # at 314128 #180804 15:24:48 server id 6 end_log_pos 388 CRC32 0x829f43a8 Query thread_id=3 exec_time=8363 error_code=0129 SET TIMESTAMP=1533367488/*!*/;130 BEGIN131 /*!*/;132 # at 388133 #180804 15:24:48 server id 6 end_log_pos 437 CRC32 0x1801dec8 Table_map: `binlog`.`luna` mapped to number 73134 # at 437135 #180804 15:24:48 server id 6 end_log_pos 477 CRC32 0xfb860ce0 Write_rows: table id 73 flags: STMT_END_F136 ### INSERT INTO `binlog`.`luna`137 ### SET138 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */139 # at 477140 #180804 15:24:48 server id 6 end_log_pos 508 CRC32 0x88bca0f6 Xid = 135141 COMMIT/*!*/;142 # at 508143 #180804 15:24:48 server id 6 end_log_pos 582 CRC32 0xfdea7f27 Query thread_id=3 exec_time=8366 error_code=0144 SET TIMESTAMP=1533367488/*!*/;145 BEGIN146 /*!*/;147 # at 582148 #180804 15:24:48 server id 6 end_log_pos 631 CRC32 0xac422642 Table_map: `binlog`.`luna` mapped to number 73149 # at 631150 #180804 15:24:48 server id 6 end_log_pos 671 CRC32 0x3817c497 Write_rows: table id 73 flags: STMT_END_F151 ### INSERT INTO `binlog`.`luna`152 ### SET153 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */154 # at 671155 #180804 15:24:48 server id 6 end_log_pos 702 CRC32 0x4c81edc8 Xid = 136156 COMMIT/*!*/;157 # at 702158 #180804 15:24:48 server id 6 end_log_pos 776 CRC32 0xf3c75c08 Query thread_id=3 exec_time=8369 error_code=0159 SET TIMESTAMP=1533367488/*!*/;160 BEGIN161 /*!*/;162 # at 776163 #180804 15:24:48 server id 6 end_log_pos 825 CRC32 0x7d9b565c Table_map: `binlog`.`luna` mapped to number 73164 # at 825165 #180804 15:24:48 server id 6 end_log_pos 865 CRC32 0x773957ea Write_rows: table id 73 flags: STMT_END_F166 ### INSERT INTO `binlog`.`luna`167 ### SET168 ### @1=3 /* INT meta=0 nullable=1 is_null=0 */169 # at 865170 #180804 15:24:48 server id 6 end_log_pos 896 CRC32 0x3df025e7 Xid = 137171 COMMIT/*!*/;172 # at 896173 #180804 15:24:48 server id 6 end_log_pos 970 CRC32 0x500edb7f Query thread_id=3 exec_time=8432 error_code=0174 SET TIMESTAMP=1533367488/*!*/;175 BEGIN176 /*!*/;177 # at 970178 #180804 15:24:48 server id 6 end_log_pos 1019 CRC32 0xdaf097a5 Table_map: `binlog`.`luna` mapped to number 73179 # at 1019180 #180804 15:24:48 server id 6 end_log_pos 1065 CRC32 0x3fccc29c Update_rows: table id 73 flags: STMT_END_F181 ### UPDATE `binlog`.`luna`182 ### WHERE183 ### @1=2 /* INT meta=0 nullable=1 is_null=0 */184 ### SET185 ### @1=22 /* INT meta=0 nullable=1 is_null=0 */186 # at 1065187 #180804 15:24:48 server id 6 end_log_pos 1096 CRC32 0x355a2b60 Xid = 141188 COMMIT/*!*/;189 # at 1096190 #180804 15:24:48 server id 6 end_log_pos 1170 CRC32 0x4f936f7d Query thread_id=3 exec_time=8703 error_code=0191 SET TIMESTAMP=1533367488/*!*/;192 BEGIN193 /*!*/;194 # at 1170195 #180804 15:24:48 server id 6 end_log_pos 1219 CRC32 0x36b88afd Table_map: `binlog`.`luna` mapped to number 73196 # at 1219197 #180804 15:24:48 server id 6 end_log_pos 1259 CRC32 0xd3e293de Delete_rows: table id 73 flags: STMT_END_F198 ### DELETE FROM `binlog`.`luna`199 ### WHERE200 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */201 # at 1259202 #180804 15:24:48 server id 6 end_log_pos 1290 CRC32 0x0a3ec6c4 Xid = 143203 COMMIT/*!*/;204 # at 1290205 #180804 15:24:48 server id 6 end_log_pos 1411 CRC32 0x653cd09b Query thread_id=3 exec_time=8723 error_code=0206 SET TIMESTAMP=1533367488/*!*/;207 DROP TABLE `luna` /* generated by server */208 /*!*/;209 # at 1411210 #180804 15:24:48 server id 6 end_log_pos 1494 CRC32 0x2ea0c2db Query thread_id=3 exec_time=8745 error_code=0211 SET TIMESTAMP=1533367488/*!*/;212 drop database nod213 /*!*/;214 DELIMITER ;215 # End of log file216 ROLLBACK /* added by mysqlbinlog */;217 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;218 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;219 220 221 #-------------------------------------------------------222 223 224 225 [root@db01 ~]# mysqlbinlog --start-position=120 --stop-position=1170 /data/mysql/mysql-bin.000005 >/tmp/nod.sql;226 227 mysql> source /tmp/nod.sql;228 229 #-------------------------------------------------------230 查看231 #-------------------------------------------------------232 mysql> show databases;233 +--------------------+234 | Database |235 +--------------------+236 | information_schema |237 | binlog |238 | db |239 | mysql |240 | nod |241 | performance_schema |242 | test |243 | world |244 +--------------------+245 246 mysql> select * from luna;247 +------+248 | id |249 +------+250 | 1 |251 | 22 |252 | 3 |253 +------+254 3 rows in set (0.00 sec)

View Code

 

 

 

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表示的含义 最后一个事件的结束位置

相关文章