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.000005
101 /*!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 4
106 #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
107 # Warning: this binlog is either in use or was not closed properly.
108 # at 120
109 #180804 15:24:48 server id 6  end_log_pos 211 CRC32 0x7fc33332     Query    thread_id=3    exec_time=8182    error_code=0
110 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 nod
120 /*!*/;
121 # at 211
122 #180804 15:24:48 server id 6  end_log_pos 314 CRC32 0xae4fffd2     Query    thread_id=3    exec_time=8274    error_code=0
123 use `binlog`/*!*/;
124 SET TIMESTAMP=1533367488/*!*/;
125 create table luna(id int)
126 /*!*/;
127 # at 314
128 #180804 15:24:48 server id 6  end_log_pos 388 CRC32 0x829f43a8     Query    thread_id=3    exec_time=8363    error_code=0
129 SET TIMESTAMP=1533367488/*!*/;
130 BEGIN
131 /*!*/;
132 # at 388
133 #180804 15:24:48 server id 6  end_log_pos 437 CRC32 0x1801dec8     Table_map: `binlog`.`luna` mapped to number 73
134 # at 437
135 #180804 15:24:48 server id 6  end_log_pos 477 CRC32 0xfb860ce0     Write_rows: table id 73 flags: STMT_END_F
136 ### INSERT INTO `binlog`.`luna`
137 ### SET
138 ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
139 # at 477
140 #180804 15:24:48 server id 6  end_log_pos 508 CRC32 0x88bca0f6     Xid = 135
141 COMMIT/*!*/;
142 # at 508
143 #180804 15:24:48 server id 6  end_log_pos 582 CRC32 0xfdea7f27     Query    thread_id=3    exec_time=8366    error_code=0
144 SET TIMESTAMP=1533367488/*!*/;
145 BEGIN
146 /*!*/;
147 # at 582
148 #180804 15:24:48 server id 6  end_log_pos 631 CRC32 0xac422642     Table_map: `binlog`.`luna` mapped to number 73
149 # at 631
150 #180804 15:24:48 server id 6  end_log_pos 671 CRC32 0x3817c497     Write_rows: table id 73 flags: STMT_END_F
151 ### INSERT INTO `binlog`.`luna`
152 ### SET
153 ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
154 # at 671
155 #180804 15:24:48 server id 6  end_log_pos 702 CRC32 0x4c81edc8     Xid = 136
156 COMMIT/*!*/;
157 # at 702
158 #180804 15:24:48 server id 6  end_log_pos 776 CRC32 0xf3c75c08     Query    thread_id=3    exec_time=8369    error_code=0
159 SET TIMESTAMP=1533367488/*!*/;
160 BEGIN
161 /*!*/;
162 # at 776
163 #180804 15:24:48 server id 6  end_log_pos 825 CRC32 0x7d9b565c     Table_map: `binlog`.`luna` mapped to number 73
164 # at 825
165 #180804 15:24:48 server id 6  end_log_pos 865 CRC32 0x773957ea     Write_rows: table id 73 flags: STMT_END_F
166 ### INSERT INTO `binlog`.`luna`
167 ### SET
168 ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
169 # at 865
170 #180804 15:24:48 server id 6  end_log_pos 896 CRC32 0x3df025e7     Xid = 137
171 COMMIT/*!*/;
172 # at 896
173 #180804 15:24:48 server id 6  end_log_pos 970 CRC32 0x500edb7f     Query    thread_id=3    exec_time=8432    error_code=0
174 SET TIMESTAMP=1533367488/*!*/;
175 BEGIN
176 /*!*/;
177 # at 970
178 #180804 15:24:48 server id 6  end_log_pos 1019 CRC32 0xdaf097a5     Table_map: `binlog`.`luna` mapped to number 73
179 # at 1019
180 #180804 15:24:48 server id 6  end_log_pos 1065 CRC32 0x3fccc29c     Update_rows: table id 73 flags: STMT_END_F
181 ### UPDATE `binlog`.`luna`
182 ### WHERE
183 ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
184 ### SET
185 ###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
186 # at 1065
187 #180804 15:24:48 server id 6  end_log_pos 1096 CRC32 0x355a2b60     Xid = 141
188 COMMIT/*!*/;
189 # at 1096
190 #180804 15:24:48 server id 6  end_log_pos 1170 CRC32 0x4f936f7d     Query    thread_id=3    exec_time=8703    error_code=0
191 SET TIMESTAMP=1533367488/*!*/;
192 BEGIN
193 /*!*/;
194 # at 1170
195 #180804 15:24:48 server id 6  end_log_pos 1219 CRC32 0x36b88afd     Table_map: `binlog`.`luna` mapped to number 73
196 # at 1219
197 #180804 15:24:48 server id 6  end_log_pos 1259 CRC32 0xd3e293de     Delete_rows: table id 73 flags: STMT_END_F
198 ### DELETE FROM `binlog`.`luna`
199 ### WHERE
200 ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
201 # at 1259
202 #180804 15:24:48 server id 6  end_log_pos 1290 CRC32 0x0a3ec6c4     Xid = 143
203 COMMIT/*!*/;
204 # at 1290
205 #180804 15:24:48 server id 6  end_log_pos 1411 CRC32 0x653cd09b     Query    thread_id=3    exec_time=8723    error_code=0
206 SET TIMESTAMP=1533367488/*!*/;
207 DROP TABLE `luna` /* generated by server */
208 /*!*/;
209 # at 1411
210 #180804 15:24:48 server id 6  end_log_pos 1494 CRC32 0x2ea0c2db     Query    thread_id=3    exec_time=8745    error_code=0
211 SET TIMESTAMP=1533367488/*!*/;
212 drop database nod
213 /*!*/;
214 DELIMITER ;
215 # End of log file
216 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

升级版本：

多库单表误删除期中某个库的恢复操作

使用mysqlbinlog -d参数注意-d参数的使用

  1 #-------------------------------------------------------------------------------
  2 #
  3 # 多库单表的操作
  4 # Author：nod
  5 # Date:18-08-05
  6 #-------------------------------------------------------------------------------
  7 
  8 
  9 #-------------------------------------------------------------------------------
 10 # 模拟环境
 11 #-------------------------------------------------------------------------------
 12 mysql> flush logs;
 13 
 14 mysql> show master status;
 15 +------------------+----------+--------------+------------------+-------------------+
 16 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 17 +------------------+----------+--------------+------------------+-------------------+
 18 | mysql-bin.000010 |      120 |              |                  |                   |
 19 +------------------+----------+--------------+------------------+-------------------+
 20 1 row in set (0.00 sec)
 21 
 22 mysql> show variables like 'autocommit';
 23 +---------------+-------+
 24 | Variable_name | Value |
 25 +---------------+-------+
 26 | autocommit    | ON    |
 27 +---------------+-------+
 28 1 row in set (0.00 sec)
 29 
 30 mysql> create database nod;
 31 Query OK, 1 row affected (0.00 sec)
 32 
 33 mysql> create database luna;
 34 Query OK, 1 row affected (0.00 sec)
 35 
 36 mysql> use nod;
 37 Database changed
 38 mysql> create table t1(id int);
 39 Query OK, 0 rows affected (0.06 sec)
 40 
 41 mysql> insert into t1 values(1);
 42 Query OK, 1 row affected (0.01 sec)
 43 
 44 mysql> insert into t1 values(2);
 45 Query OK, 1 row affected (0.00 sec)
 46 
 47 mysql> insert into t1 values(3);
 48 Query OK, 1 row affected (0.00 sec)
 49 
 50 mysql> use luna
 51 Database changed
 52 mysql> create table t2(id int);
 53 Query OK, 0 rows affected (0.15 sec)
 54 
 55 mysql> insert into t2 values(44);
 56 Query OK, 1 row affected (0.00 sec)
 57 
 58 mysql> insert into t2 values(55);
 59 Query OK, 1 row affected (0.06 sec)
 60 
 61 
 62 mysql> use nod;
 63 Database changed
 64 mysql> insert into t1 values(88);
 65 Query OK, 1 row affected (0.02 sec)
 66 
 67 mysql> select * from nod.t1;
 68 +------+
 69 | id   |
 70 +------+
 71 |    1 |
 72 |    2 |
 73 |    3 |
 74 |   88 |
 75 +------+
 76 4 rows in set (0.00 sec)
 77 
 78 mysql> select * from luna.t2;
 79 +------+
 80 | id   |
 81 +------+
 82 |   44 |
 83 |   55 |
 84 +------+
 85 2 rows in set (0.00 sec)
 86 
 87 mysql> drop database nod;
 88 Query OK, 1 row affected (0.05 sec)
 89 
 90 
 91 mysql> show databases;
 92 +--------------------+
 93 | Database           |
 94 +--------------------+
 95 | information_schema |
 96 | binlog             |
 97 | db                 |
 98 | luna               |
 99 | mysql              |
100 | performance_schema |
101 | test               |
102 | world              |
103 +--------------------+
104 8 rows in set (0.00 sec)
105 
106 #-------------------------------------------------------------------------------
107 # 分析binlog
108 # 注意参数使用-d  指定数据库 nod  此处不希望出现 luna
109 #-------------------------------------------------------------------------------
110 mysqlbinlog -d nod --base64-output=decode-rows -vvv /data/mysql/mysql-bin.000010
111 mysqlbinlog -d nod --start-position=120 --stop-position=1617 /data/mysql/mysql-bin.000010 >/tmp/bak_nod.sql
112 
113 
114 #-------------------------------------------------------------------------------
115 # 数据库开始恢复
116 #-------------------------------------------------------------------------------
117 mysql> set sql_log_bin=0;
118 Query OK, 0 rows affected (0.00 sec)
119 
120 mysql> source /tmp/bak_nod.sql;
121 Query OK, 0 rows affected (0.00 sec)
122 
123 Query OK, 0 rows affected, 1 warning (0.00 sec)
124 
125 Query OK, 0 rows affected (0.00 sec)
126 
127 Query OK, 0 rows affected (0.00 sec)
128 
129 Query OK, 0 rows affected (0.00 sec)
130 
131 Query OK, 0 rows affected (0.00 sec)
132 
133 Query OK, 0 rows affected (0.00 sec)
134 
135 Query OK, 0 rows affected (0.00 sec)
136 
137 Query OK, 0 rows affected (0.00 sec)
138 
139 Charset changed
140 Query OK, 0 rows affected (0.00 sec)
141 
142 Query OK, 0 rows affected (0.00 sec)
143 
144 Query OK, 0 rows affected (0.00 sec)
145 
146 Query OK, 0 rows affected (0.00 sec)
147 
148 Query OK, 1 row affected (0.00 sec)
149 
150 Database changed
151 Query OK, 0 rows affected (0.00 sec)
152 
153 Query OK, 0 rows affected (0.10 sec)
154 
155 Query OK, 0 rows affected (0.00 sec)
156 
157 Query OK, 0 rows affected (0.00 sec)
158 
159 Query OK, 0 rows affected (0.00 sec)
160 
161 Query OK, 0 rows affected (0.02 sec)
162 
163 Query OK, 0 rows affected (0.00 sec)
164 
165 Query OK, 0 rows affected (0.00 sec)
166 
167 Query OK, 0 rows affected (0.00 sec)
168 
169 Query OK, 0 rows affected (0.01 sec)
170 
171 Query OK, 0 rows affected (0.00 sec)
172 
173 Query OK, 0 rows affected (0.00 sec)
174 
175 Query OK, 0 rows affected (0.00 sec)
176 
177 Query OK, 0 rows affected (0.00 sec)
178 
179 Query OK, 0 rows affected (0.00 sec)
180 
181 Query OK, 0 rows affected (0.00 sec)
182 
183 Query OK, 0 rows affected (0.00 sec)
184 
185 Query OK, 0 rows affected (0.00 sec)
186 
187 Query OK, 0 rows affected (0.00 sec)
188 
189 Query OK, 0 rows affected (0.00 sec)
190 
191 Query OK, 0 rows affected (0.00 sec)
192 
193 Query OK, 0 rows affected (0.00 sec)
194 
195 Query OK, 0 rows affected (0.00 sec)
196 
197 Query OK, 0 rows affected (0.01 sec)
198 
199 Query OK, 0 rows affected (0.00 sec)
200 
201 Query OK, 0 rows affected (0.00 sec)
202 
203 Query OK, 0 rows affected (0.00 sec)
204 
205 #-------------------------------------------------------------------------------
206 # 检查恢复数据
207 #-------------------------------------------------------------------------------
208 mysql> show databases;
209 +--------------------+
210 | Database           |
211 +--------------------+
212 | information_schema |
213 | binlog             |
214 | db                 |
215 | luna               |
216 | mysql              |
217 | nod                |
218 | performance_schema |
219 | test               |
220 | world              |
221 +--------------------+
222 9 rows in set (0.00 sec)
223 
224 mysql> select * from nod.t1;
225 +------+
226 | id   |
227 +------+
228 |    1 |
229 |    2 |
230 |    3 |
231 |   88 |
232 +------+
233 4 rows in set (0.00 sec)
234 
235 mysql> select * from luna.t2;
236 +------+
237 | id   |
238 +------+
239 |   44 |
240 |   55 |
241 +------+
242 2 rows in set (0.00 sec)
243 
244 #-------------------------------------------------------------------------------
245 # 数据恢复完毕，如之前drop前所示
246 #-------------------------------------------------------------------------------

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

posted on 2018-08-04 18:03 90500042陈阅读(658) 评论(0) 编辑收藏举报