MySQL使用二进制日志来还原数据二种方法,数据复苏

  mysql> insert into test(val,data) values(20,’jia’);  

 

MySQlStudy学习之–MySQl二进制日志管理

MySQl Study学习之–MySQl二进制日志管理

MySQL二进制日志(Binary Log)

a、它包含的内容及作用如下:
包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE)
包含关于每个更新数据库(DML)的语句的执行时间信息
不包含没有修改任何数据的语句,如果需要启用该选项,需要开启通用日志功能
主要目的是尽可能的将数据库恢复到数据库故障点,因为二进制日志包含备份后进行的所有更新
用于在主复制服务器上记录所有将发送给从服务器的语句
启用该选项数据库性能降低1%,但保障数据库完整性,对于重要数据库值得以性能换完整。有些类似于Oracle开启归档模式。

b、开启二进制日志的方法及属性
使用–log-bin[=file_name]选项或在配置文件中指定log-bin启动时,mysqld写入包含所有更新数据的SQL命令的日志文件。
对于未给出file_name值,默认名为-bin后面所跟的主机名。
在未指定绝对路径的情形下,缺省位置保存在数据目录下。
每个二进制日志名会添加一个数字扩展名用于日志老化,因此不支持自定义的扩展名,会被mysql数字扩展名动态替换。
若当前的日志大小达到max_binlog_size,则自动创建新的二进制日志。
对于大的事务,二进制日志会超过max_binlog_size设定的值。也即是事务仅仅写入一个二进制日志。
由是可知,二进制日志文件大小接近,其size不是完全相等,这点不同于oracle。
二进制日志文件会有一个对应二进制日志索引文件,该文件包含所有的二进制日志,其文件名与二进制日志相同,扩展名为.index
二进制索引文件通过–log-bin-index[=file_name]选项来指定
RESETMASTER语句将删除所有二进制日志文件,这将影响到从库。也可以用PURGE
MASTER LOGS只删除部分二进制文件。

二进制日志记录了所有对数据库执行更改的操作,二进制主要有以下两种作用

1、恢复(recovery)2、复制(replication)

二进制日志的启动:配置参数log-bin[=name],如果不指定name,则默认二进制日志文件名为主机名,后缀名为二级制日志的序列号,所在路径为数据库所在目录。

以index为后缀的文件为二进制日志的索引文件,用来存储过程生产的二进制日志。


和二进制日志相关的参数:

max_binlog_size、binlog_cache_size、sync_binlog、binlog-do-db、binlog-ignore-db、log-slave-update、binlog_format

1、max_binlog_size,该参数指定了单个二进制日志文件的最大值,如果超过该值,则产生新的二进制日志文件,后缀名+1,并记录到.index文件,从Mysql5.0开始的默认值为1073741824,代表1G。

Mysql5.5的默认值

 

mysql> show variables like '%max_binlog%';
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| max_binlog_cache_size      | 18446744073709547520 |
| max_binlog_size            | 1073741824           |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+
3 rows in set (0.00 sec)

 

当使用事务的存储引擎InnoDB时,所有未提交的事务会记录到一个缓存中,等待事务提交时,直接将缓冲中的二进制日志写入二进制日志文件,而该缓冲的大小由binlog_cache_size决定,默认大小为32KB,此外,binlog_cache_size是基于session的,也就是,当一个线程开始一个事务时,mysql会自动分配一个大小为binlog_cache_size的缓存,因此该值得设置需要相当小心,可以通过show
global status
查看binlog_cache_use、binlog_cache_disk_use的状态,可以判断当前binlog_cache_size的设置是否合适。

2、Binlog_cache_size的默认大小为32KB

mysql> show variables like 'binlog_cache%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)

3、参数sync_binlog=[N]表示每写缓存多少次就同步到磁盘,如果将N设置为1,则表示采用同步写磁盘的方式来写二进制日志,该参数很重要,这个以后还会提到。值得注意的是,在将该参数设置为1时,也应该将innodb_support_xa设为1来解决,这可以确保二进制日志和InnoDB存储引擎数据文件的同步。

4、参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的日志,默认值为空,则表示将所有库的日志同步到二进制日志。

5、Log-slave-update该参数在搭建master=>slave=>slave的架构时,需要配置。

6、Binlog_format参数也特别重要。从mysql5.1版本开始引入这个参数,该参数可以设置的值有STATEMENT\、ROW、MIXED;

(1)STATEMENT格式和之前的mysql版本一样,二进制日志文件记录的是日志的逻辑SQL语句。

(2)在ROW格式下,二进制日志记录的不再是简单的SQL语句了,而是记录表的行更改情况,此时可以将InnoDB的事务隔离基本设为READ
COMMITTED,以获得更好的并发性。

(3)MIXED格式下,mysql默认采用的STATEMENT格式进行二进制日志文件的记录,但是在一些情况下会使用ROW格式,可能的情况包括:

1)表的存储引擎为NDB,这时对于表的DML操作都会以ROW格式记录

2)使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数

3)使用了INSERT DELAY语句

4)使用了用户定义函数

5)使用了临时表

 

**案例分析:

1、 二进制日志存放位置**

[[email protected]
~]# more /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data/mysql

mysql> show variables like ‘%datadir%’;
+—————+——————————+
| Variable_name | Value |
+—————+——————————+
| datadir | /usr/local/mysql/data/mysql/ |
+—————+——————————+
1 row in set (0.00 sec)

查看日志信息:

[[email protected] ~]# cd /usr/local/mysql/data/mysql/
[[email protected] mysql]# ls -l
total 29928
-rw-rw---- 1 mysql mysql       56 Jun 30 17:12 auto.cnf
-rw-rw---- 1 mysql mysql 18874368 Jul  8 15:17 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul  8 15:17 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun 30 17:12 ib_logfile1
-rw-rw---- 1 mysql mysql      124 Jul  8 15:17 master.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 mysql
-rw-rw---- 1 mysql mysql      114 Jun 30 17:15 mysql-bin.000001
-rw-rw---- 1 mysql mysql    63438 Jun 30 17:15 mysql-bin.000002
-rw-rw---- 1 mysql mysql  1096670 Jun 30 17:15 mysql-bin.000003
-rw-rw---- 1 mysql mysql      252 Jun 30 17:26 mysql-bin.000004
-rw-rw---- 1 mysql mysql      114 Jun 30 17:40 mysql-bin.000005
-rw-rw---- 1 mysql mysql      133 Jun 30 17:51 mysql-bin.000006
-rw-rw---- 1 mysql mysql      114 Jun 30 17:56 mysql-bin.000007
-rw-rw---- 1 mysql mysql      114 Jul  8 15:17 mysql-bin.000008
-rw-rw---- 1 mysql mysql      157 Jul  8 15:18 mysql-bin.000009
-rw-rw---- 1 mysql mysql      157 Jul  8 15:20 mysql-bin.000010
-rw-rw---- 1 mysql mysql      114 Jul  8 15:20 mysql-bin.000011
-rw-rw---- 1 mysql mysql      209 Jul  8 15:20 mysql-bin.index
-rw-rw---- 1 mysql mysql    24920 Jul  8 15:21 mysrv.err
-rw-rw---- 1 mysql mysql        5 Jul  8 15:17 mysrv.pid
-rw-rw---- 1 mysql mysql      163 Jul  8 15:18 mysrv-relay-bin.000003
-rw-rw---- 1 mysql mysql      163 Jul  8 15:20 mysrv-relay-bin.000004
-rw-rw---- 1 mysql mysql      114 Jul  8 15:20 mysrv-relay-bin.000005
-rw-rw---- 1 mysql mysql       75 Jul  8 15:20 mysrv-relay-bin.index
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
-rw-rw---- 1 mysql mysql       58 Jul  8 15:20 relay-log.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

mysrv-relay-bin.index,为记录日志文件的index文件

[[email protected] mysql]# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014

2、二进制日志文件的切换
执行flush logs产生新的二进制日志:
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000011 | 114 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

[[email protected] mysql]# ls -l
total 29936
-rw-rw---- 1 mysql mysql       56 Jun 30 17:12 auto.cnf
-rw-rw---- 1 mysql mysql 18874368 Jul  8 15:17 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul  8 15:17 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun 30 17:12 ib_logfile1
-rw-rw---- 1 mysql mysql      124 Jul  8 15:17 master.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 mysql
-rw-rw---- 1 mysql mysql      114 Jun 30 17:15 mysql-bin.000001
-rw-rw---- 1 mysql mysql    63438 Jun 30 17:15 mysql-bin.000002
-rw-rw---- 1 mysql mysql  1096670 Jun 30 17:15 mysql-bin.000003
-rw-rw---- 1 mysql mysql      252 Jun 30 17:26 mysql-bin.000004
-rw-rw---- 1 mysql mysql      114 Jun 30 17:40 mysql-bin.000005
-rw-rw---- 1 mysql mysql      133 Jun 30 17:51 mysql-bin.000006
-rw-rw---- 1 mysql mysql      114 Jun 30 17:56 mysql-bin.000007
-rw-rw---- 1 mysql mysql      114 Jul  8 15:17 mysql-bin.000008
-rw-rw---- 1 mysql mysql      157 Jul  8 15:18 mysql-bin.000009
-rw-rw---- 1 mysql mysql      157 Jul  8 15:20 mysql-bin.000010
-rw-rw---- 1 mysql mysql      157 Jul  8 15:24 mysql-bin.000011
-rw-rw---- 1 mysql mysql      114 Jul  8 15:24 mysql-bin.000012
-rw-rw---- 1 mysql mysql      228 Jul  8 15:24 mysql-bin.index
-rw-rw---- 1 mysql mysql    25322 Jul  8 15:24 mysrv.err
-rw-rw---- 1 mysql mysql        5 Jul  8 15:17 mysrv.pid
-rw-rw---- 1 mysql mysql      163 Jul  8 15:18 mysrv-relay-bin.000003
-rw-rw---- 1 mysql mysql      163 Jul  8 15:20 mysrv-relay-bin.000004
-rw-rw---- 1 mysql mysql      163 Jul  8 15:24 mysrv-relay-bin.000005
-rw-rw---- 1 mysql mysql      114 Jul  8 15:24 mysrv-relay-bin.000006
-rw-rw---- 1 mysql mysql      100 Jul  8 15:24 mysrv-relay-bin.index
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
-rw-rw---- 1 mysql mysql       58 Jul  8 15:24 relay-log.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

采用mysqladmin切换:
[[email protected]
~]# mysqladmin flush-logs -u root -p
Enter password:

[[email protected] mysql]# ls -l
total 29944
-rw-rw---- 1 mysql mysql       56 Jun 30 17:12 auto.cnf
-rw-rw---- 1 mysql mysql 18874368 Jul  8 15:17 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul  8 15:17 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun 30 17:12 ib_logfile1
-rw-rw---- 1 mysql mysql      124 Jul  8 15:17 master.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 mysql
-rw-rw---- 1 mysql mysql      114 Jun 30 17:15 mysql-bin.000001
-rw-rw---- 1 mysql mysql    63438 Jun 30 17:15 mysql-bin.000002
-rw-rw---- 1 mysql mysql  1096670 Jun 30 17:15 mysql-bin.000003
-rw-rw---- 1 mysql mysql      252 Jun 30 17:26 mysql-bin.000004
-rw-rw---- 1 mysql mysql      114 Jun 30 17:40 mysql-bin.000005
-rw-rw---- 1 mysql mysql      133 Jun 30 17:51 mysql-bin.000006
-rw-rw---- 1 mysql mysql      114 Jun 30 17:56 mysql-bin.000007
-rw-rw---- 1 mysql mysql      114 Jul  8 15:17 mysql-bin.000008
-rw-rw---- 1 mysql mysql      157 Jul  8 15:18 mysql-bin.000009
-rw-rw---- 1 mysql mysql      157 Jul  8 15:20 mysql-bin.000010
-rw-rw---- 1 mysql mysql      157 Jul  8 15:24 mysql-bin.000011
-rw-rw---- 1 mysql mysql      157 Jul  8 15:34 mysql-bin.000012
-rw-rw---- 1 mysql mysql      114 Jul  8 15:34 mysql-bin.000013
-rw-rw---- 1 mysql mysql      247 Jul  8 15:34 mysql-bin.index
-rw-rw---- 1 mysql mysql    26536 Jul  8 15:34 mysrv.err
-rw-rw---- 1 mysql mysql        5 Jul  8 15:17 mysrv.pid
-rw-rw---- 1 mysql mysql      163 Jul  8 15:18 mysrv-relay-bin.000003
-rw-rw---- 1 mysql mysql      163 Jul  8 15:20 mysrv-relay-bin.000004
-rw-rw---- 1 mysql mysql      163 Jul  8 15:24 mysrv-relay-bin.000005
-rw-rw---- 1 mysql mysql      163 Jul  8 15:34 mysrv-relay-bin.000006
-rw-rw---- 1 mysql mysql      114 Jul  8 15:34 mysrv-relay-bin.000007
-rw-rw---- 1 mysql mysql      125 Jul  8 15:34 mysrv-relay-bin.index
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
-rw-rw---- 1 mysql mysql       58 Jul  8 15:34 relay-log.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

[[email protected]
~]# mysqladmin refresh -u root -p
Enter password:

[[email protected] mysql]# ls -l
total 29952
-rw-rw---- 1 mysql mysql       56 Jun 30 17:12 auto.cnf
-rw-rw---- 1 mysql mysql 18874368 Jul  8 15:17 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul  8 15:17 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun 30 17:12 ib_logfile1
-rw-rw---- 1 mysql mysql      124 Jul  8 15:17 master.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 mysql
-rw-rw---- 1 mysql mysql      114 Jun 30 17:15 mysql-bin.000001
-rw-rw---- 1 mysql mysql    63438 Jun 30 17:15 mysql-bin.000002
-rw-rw---- 1 mysql mysql  1096670 Jun 30 17:15 mysql-bin.000003
-rw-rw---- 1 mysql mysql      252 Jun 30 17:26 mysql-bin.000004
-rw-rw---- 1 mysql mysql      114 Jun 30 17:40 mysql-bin.000005
-rw-rw---- 1 mysql mysql      133 Jun 30 17:51 mysql-bin.000006
-rw-rw---- 1 mysql mysql      114 Jun 30 17:56 mysql-bin.000007
-rw-rw---- 1 mysql mysql      114 Jul  8 15:17 mysql-bin.000008
-rw-rw---- 1 mysql mysql      157 Jul  8 15:18 mysql-bin.000009
-rw-rw---- 1 mysql mysql      157 Jul  8 15:20 mysql-bin.000010
-rw-rw---- 1 mysql mysql      157 Jul  8 15:24 mysql-bin.000011
-rw-rw---- 1 mysql mysql      157 Jul  8 15:34 mysql-bin.000012
-rw-rw---- 1 mysql mysql      157 Jul  8 15:36 mysql-bin.000013
-rw-rw---- 1 mysql mysql      114 Jul  8 15:36 mysql-bin.000014
-rw-rw---- 1 mysql mysql      266 Jul  8 15:36 mysql-bin.index
-rw-rw---- 1 mysql mysql    26671 Jul  8 15:35 mysrv.err
-rw-rw---- 1 mysql mysql        5 Jul  8 15:17 mysrv.pid
-rw-rw---- 1 mysql mysql      163 Jul  8 15:18 mysrv-relay-bin.000003
-rw-rw---- 1 mysql mysql      163 Jul  8 15:20 mysrv-relay-bin.000004
-rw-rw---- 1 mysql mysql      163 Jul  8 15:24 mysrv-relay-bin.000005
-rw-rw---- 1 mysql mysql      163 Jul  8 15:34 mysrv-relay-bin.000006
-rw-rw---- 1 mysql mysql      163 Jul  8 15:36 mysrv-relay-bin.000007
-rw-rw---- 1 mysql mysql      114 Jul  8 15:36 mysrv-relay-bin.000008
-rw-rw---- 1 mysql mysql      150 Jul  8 15:36 mysrv-relay-bin.index
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
-rw-rw---- 1 mysql mysql       58 Jul  8 15:36 relay-log.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

3、查看和日志相关的参数:

mysql> show variables like '%log%';
+-----------------------------------------+---------------------------------------------+
| Variable_name                           | Value                                       |
+-----------------------------------------+---------------------------------------------+
| back_log                                | 50                                          |
| binlog_cache_size                       | 32768                                       |
| binlog_checksum                         | NONE                                        |
| binlog_direct_non_transactional_updates | OFF                                         |
| binlog_format                           | MIXED                                       |
| binlog_row_image                        | FULL                                        |
| binlog_rows_query_log_events            | OFF                                         |
| binlog_stmt_cache_size                  | 32768                                       |
| expire_logs_days                        | 0                                           |
| general_log                             | OFF                                         |
| general_log_file                        | /usr/local/mysql/data/mysql/mysrv.log       |
| innodb_flush_log_at_trx_commit          | 1                                           |
| innodb_locks_unsafe_for_binlog          | OFF                                         |
| innodb_log_buffer_size                  | 8388608                                     |
| innodb_log_file_size                    | 5242880                                     |
| innodb_log_files_in_group               | 2                                           |
| innodb_log_group_home_dir               | ./                                          |
| innodb_mirrored_log_groups              | 1                                           |
| innodb_undo_logs                        | 128                                         |
| log_bin                                 | ON     ;是否开启二进制日志                                     |
| log_bin_basename                        | /usr/local/mysql/data/mysql/mysql-bin       |
| log_bin_index                           | /usr/local/mysql/data/mysql/mysql-bin.index |
| log_bin_trust_function_creators         | OFF                                         |
| log_error                               | /usr/local/mysql/data/mysql/mysrv.err       |
| log_output                              | FILE                                        |
| log_queries_not_using_indexes           | OFF                                         |
| log_slave_updates                       | OFF                                         |
| log_warnings                            | 1                                           |
| max_binlog_cache_size                   | 18446744073709547520                        |
| max_binlog_size                         | 1073741824                                  |
| max_binlog_stmt_cache_size              | 18446744073709547520                        |
| max_relay_log_size                      | 0                                           |
| relay_log                               |                                             |
| relay_log_basename                      |                                             |
| relay_log_index                         |                                             |
| relay_log_info_file                     | relay-log.info                              |
| relay_log_info_repository               | FILE                                        |
| relay_log_purge                         | ON                                          |
| relay_log_recovery                      | OFF                                         |
| relay_log_space_limit                   | 0                                           |
| slow_query_log                          | OFF                                         |
| slow_query_log_file                     | /usr/local/mysql/data/mysql/mysrv-slow.log  |
| sql_log_bin                             | ON                                          |
| sql_log_off                             | OFF                                         |
| sync_binlog                             | 0                                           |
| sync_relay_log                          | 0                                           |
| sync_relay_log_info                     | 0                                           |
+-----------------------------------------+---------------------------------------------+
47 rows in set (0.01 sec)

**mysql 二进制日志应用案例:

事务操作生成二进制日志:**
mysql> use prod;
Database changed
mysql> show tables;
+—————-+
| Tables_in_prod |
+—————-+
| t1 |
+—————-+
1 row in set (0.02 sec)

mysql> select * from t1;
+——+——-+
| id | name |
+——+——-+
| 1 | tom |
| 2 | scott |
| 3 | jerry |
+——+——-+
3 rows in set (0.00 sec)

mysql> insert into t1 values (4,’rose’);
Query OK, 1 row affected (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看生成的二进制日志内容:
[[email protected]
mysql]# mysqlbinlog mysql-bin.000014

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET
@[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150708 15:36:17 server id 2 end_log_pos 114 Start: binlog v 4,
server v 5.6.4-m7-log created 150708 15:36:17
# Warning: this binlog is either in use or was not closed properly.
BINLOG ‘
8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w=
‘/*!*/;
# at 114
#150708 15:39:44 server id 2 end_log_pos 189 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436341184/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 189
#150708 15:39:44 server id 2 end_log_pos 291 Query thread_id=5
exec_time=0 error_code=0
use prod/*!*/;
SET TIMESTAMP=1436341184/*!*/;
insert into t1 values (4,’rose’)
/*!*/;
# at 291
#150708 15:39:44 server id 2 end_log_pos 318 Xid = 23
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET
[email protected]_COMPLETION_TYPE*/;

mysql> insert into t1 values (5,’john’);
Query OK, 1 row affected (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

[[email protected]
mysql]# strings mysql-bin.000014

5.6.4-m7-log
prod
prod
BEGIN
prod
prod
insert into t1 values (4,’rose’)
prod
prod
BEGINw
prod
prod
insert into t1 values (5,’john’)w
[[email protected]
mysql]# mysqlbinlog mysql-bin.000014

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET
@[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150708 15:36:17 server id 2 end_log_pos 114 Start: binlog v 4,
server v 5.6.4-m7-log created 150708 15:36:17
# Warning: this binlog is either in use or was not closed properly.
BINLOG ‘
8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w=
‘/*!*/;
# at 114
#150708 15:39:44 server id 2 end_log_pos 189 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436341184/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 189
#150708 15:39:44 server id 2 end_log_pos 291 Query thread_id=5
exec_time=0 error_code=0
use prod/*!*/;
SET TIMESTAMP=1436341184/*!*/;
insert into t1 values (4,’rose’)
/*!*/;
# at 291
#150708 15:39:44 server id 2 end_log_pos 318 Xid = 23
COMMIT/*!*/;
# at 318
#150708 15:42:47 server id 2 end_log_pos 393 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436341367/*!*/;
BEGIN
/*!*/;
# at 393
#150708 15:42:47 server id 2 end_log_pos 495 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436341367/*!*/;
insert into t1 values (5,’john’)
/*!*/;
# at 495
#150708 15:42:47 server id 2 end_log_pos 522 Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET
[email protected]_COMPLETION_TYPE*/;
[[email protected]
mysql]#

查看生成的二进制日志:
mysql> show binary logs;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       114 |
| mysql-bin.000002 |     63438 |
| mysql-bin.000003 |   1096670 |
| mysql-bin.000004 |       252 |
| mysql-bin.000005 |       114 |
| mysql-bin.000006 |       133 |
| mysql-bin.000007 |       114 |
| mysql-bin.000008 |       114 |
| mysql-bin.000009 |       157 |
| mysql-bin.000010 |       157 |
| mysql-bin.000011 |       157 |
| mysql-bin.000012 |       157 |
| mysql-bin.000013 |       157 |
| mysql-bin.000014 |       522 |
+------------------+-----------+
14 rows in set (0.00 sec)

查看日志记录的事件:

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                    |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         114 | Server ver: 5.6.4-m7-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
1 row in set (0.06 sec)


mysql> show binlog events in 'mysql-bin.000014';
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                         |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000014 |   4 | Format_desc |         2 |         114 | Server ver: 5.6.4-m7-log, Binlog ver: 4      |
| mysql-bin.000014 | 114 | Query       |         2 |         189 | BEGIN                                        |
| mysql-bin.000014 | 189 | Query       |         2 |         291 | use `prod`; insert into t1 values (4,'rose') |
| mysql-bin.000014 | 291 | Xid         |         2 |         318 | COMMIT /* xid=23 */                          |
| mysql-bin.000014 | 318 | Query       |         2 |         393 | BEGIN                                        |
| mysql-bin.000014 | 393 | Query       |         2 |         495 | use `prod`; insert into t1 values (5,'john') |
| mysql-bin.000014 | 495 | Xid         |         2 |         522 | COMMIT /* xid=25 */                          |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
7 rows in set (0.00 sec)

数据恢复案例: 1、模拟数据环境

mysql> select * from t1;
+——+——-+
| id | name |
+——+——-+
| 1 | tom |
| 2 | scott |
| 3 | jerry |
| 4 | rose |
| 5 | john |
+——+——-+
5 rows in set (0.00 sec)

删除数据:
mysql> delete from t1;
Query OK, 5 rows affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

利用日志恢复,将日志生成文本文件进行分析:
[[email protected]
mysql]# mysqlbinlog mysql-bin.000014 >/home/mysql/log14.txt

[[email protected]
mysql]# cat log14.txt
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET
@[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150708 15:36:17 server id 2 end_log_pos 114 Start: binlog v 4,
server v 5.6.4-m7-log created 150708 15:36:17
# Warning: this binlog is either in use or was not closed properly.
BINLOG ‘
8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w=
‘/*!*/;
# at 114
#150708 15:39:44 server id 2 end_log_pos 189 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436341184/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 189
#150708 15:39:44 server id 2 end_log_pos 291 Query thread_id=5
exec_time=0 error_code=0
use prod/*!*/;
SET TIMESTAMP=1436341184/*!*/;
insert into t1 values (4,’rose’)
/*!*/;
# at 291
#150708 15:39:44 server id 2 end_log_pos 318 Xid = 23
COMMIT/*!*/;
# at 318
#150708 15:42:47 server id 2 end_log_pos 393 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436341367/*!*/;
BEGIN
/*!*/;
# at 393
#150708 15:42:47 server id 2 end_log_pos 495 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436341367/*!*/;
insert into t1 values (5,’john’)
/*!*/;
# at 495
#150708 15:42:47 server id 2 end_log_pos 522 Xid = 25
COMMIT/*!*/;
# at 522
#150708 15:56:57 server id 2 end_log_pos 597 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436342217/*!*/;
BEGIN
/*!*/;
# at 597
;;日志记录的操作事件的编号,如果要恢复前面的数据,需要在delete前停止

#150708 15:56:57 server id 2 end_log_pos 681 Query thread_id=5
exec_time=0 error_code=0
SET TIMESTAMP=1436342217/*!*/;
delete from t1
/*!*/;
# at 681
#150708 15:56:57 server id 2 end_log_pos 708 Xid = 31
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET
[email protected]_COMPLETION_TYPE*/;
[[email protected]
mysql]#

利用二进制日志恢复:
[[email protected]
mysql]# mysqlbinlog mysql-bin.000014 –stop-pos=597 |mysql -u root
-p

Enter password:

—在delete操作前停止日志的应用
查看已恢复的数据:

mysql> select * from t1;
+——+——+
| id | name |
+——+——+
| 4 | rose |
| 5 | john |
+——+——+
2 rows in set (0.00 sec)

—前面三行数据由于从master同步而来,在slave日志中没有记载,此次不再做恢复操作

—数据恢复成功!

MySQl
Study学习之–MySQl二进制日志管理 MySQL二进制日志(Binary Log)
a、它包含的内容及作用如下: 包含了…

         7. relay
log:如果你是在用mysql的复制、备份功能,那么从服务器还提供了一种叫做relay
log的日志文件。

 

  # at 545  

mysql> flush logs;   –产生第二个日志文件

  SET TIMESTAMP=1240817421/*!*/;  

 

  #090427 15:28:46 server id 1 end_log_pos 409 Query thread_id=1
exec_time=0 error_code=0  

# at 339

  BEGIN  

 

        [mysqld]

+—-+——+——-+

  /*!*/;  

Query OK, 0 rows affected (0.11 sec)

  COMMIT/*!*/;  

# at 4

         2. ISAM操作日志(The isam
log):记录了所有对ISAM表的修改,该日志仅仅用于调试ISAM模式;

 

  #090427 15:30:34 server id 1 end_log_pos 1106 Intvar  

 

  SET @@session.sql_mode=1344274432/*!*/;  

# at 778

  SET TIMESTAMP=1240817326/*!*/;  

 

  insert into test(val,data) values(50,’bbb’)  

# at 981

Sql代码  

 

 

fF71SQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMyLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA

       
Ok,接着,我们需要分析的是第二个日志文件。为什么要分析它呢,因为它中途执行了一个操作是DELETE,因为我们要做的是恢复全部数据,也就是我们不希望去重做这个语句。所以在这里我们要想办法去绕开它。

  要想从二进制日志恢复数据,你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf
or my.ini,取决于你的系统)中找到路径。如果未包含在选项文件中。

  SET INSERT_ID=4/*!*/;  

 

  BEGIN  

BEGIN

  # at 670  

 

  mysql> delete from test where id between 4 and 5; –删除记录  

#090427 15:30:21 server id 1  end_log_pos 981 Query  thread_id=1  
exec_time=0   error_code=0

 

第三个日志文件也是同理,只要找到DROP TABLE的位置,就可以了。

        我们先打开.txt文件来分析一下。

 

  #090427 15:30:34 server id 1 end_log_pos 1214 Query thread_id=1
exec_time=0 error_code=0  

因为我们需要重做第一个日志文件的所有操作,所以这里只需要将第一个日志文件全恢复就行了。

  COMMIT/*!*/;  

 

         3. SQL执行日志(The query
log):记录了客户端的连接以及所执行的SQL语句;

 

  mysql>mysqlbinlog /var/log/mysql/mysql-bin.000001 >
/var/log/mysql/000001.txt;  

 

  SET INSERT_ID=7/*!*/;  

 

        1. 创建实验环境

OK,现在开始,要启动二进制日志记录,要先在my.cnf /
my.ini文件的mysqld里添加

     

 

  # at 981  

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000003
–stop-pos=574 | mysql -uroot –p

 

mysql> insert into test(val,data) values(30,’hui’);

        1. 首先确定是否开启了二进制文件记录功能

 

mysql日志文件的使用、数据恢复

/*!/C gbk *//*!*/;

  # at 1214  

 

  /*!*/;  

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000001 >
G:/001.txt

  SET TIMESTAMP=1240817375/*!*/;  

 

  #090427 15:30:21 server id 1 end_log_pos 1008 Xid = 15  

 

  Query OK, 1 row affected (0.03 sec)  

 

Sql代码  

 

       
因为我们需要重做第一个日志文件的所有操作,所以这里只需要将第一个日志文件全恢复就行了。

 

  | 7 | 70 | ddd |

 

  Query OK, 1 row affected (0.03 sec)  

#090427 15:29:35 server id 1  end_log_pos 778 Query  thread_id=1  
exec_time=0   error_code=0

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 >
/var/log/mysql/000002.txt;  

|  7 |   70 | ddd   |

  # at 312  

 

  +—-+——+——-+

 

  insert into test(val,data) values(40,’aaa’)  

 

       
这些日志的启动方式可以在mysqld_safe方式启动数据库的时候,后面跟选项参数,也可以在配置文件里配置,推荐采用第二种方式,配置方法很简单,我只配置了三种日志:

 

  #090427 15:30:21 server id 1 end_log_pos 875 Query thread_id=1
exec_time=0 error_code=0  

 

mysql>show master status;  

对于MySQL
4.1.4,可以在mysqlbinlog语句中通过–start-date和–stop-date选项指定DATETIME格式的起止时间。举例说明,假设在今天上午10:00(今天是2006年4月20日),执行SQL语句来删除一个大表。要想恢复表和数据,你可以恢复前晚上的备份,并输入:

  BEGIN  

在这个例子中,重点是–start-position参数和–stop-position参数的使用。

  BINLOG ‘  

#090427 15:30:41 server id 1  end_log_pos 1282    Rotate to
liangck.000003  pos: 4

  /*!*/;  

 

        

 

  #090427 15:28:37 server id 1 end_log_pos 312 Query thread_id=1
exec_time=0 error_code=0  

  mysql -u root -pmypwd

  #090427 15:28:46 server id 1 end_log_pos 545 Query thread_id=1
exec_time=0 error_code=0  

·–start-position=N

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000003 >
/var/log/mysql/000003.txt;  

Query OK, 2 rows affected (0.05 sec)

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000040
>/var/log/mysql/000040.txt;  

 

  BEGIN  

 

  # at 204  

 

  | 6 | 60 | ccc |

#090427 15:28:46 server id 1  end_log_pos 409 Query  thread_id=1  
exec_time=0   error_code=0

       现在我们再查一下数据看看:

SHOW BINLOG EVENTS G

  #090427 15:28:46 server id 1 end_log_pos 437 Intvar  

#090427 15:29:35 server id 1  end_log_pos 670 Intvar

  #090427 15:30:41 server id 1 end_log_pos 1282 Rotate to
mysql-bin.000003 pos: 4  

#090427 15:28:37 server id 1  end_log_pos 204 Intvar

  /*!*/;  

| id | val  | data  |

  Query OK, 2 rows affected (0.05 sec)  

 

mysql>show variables like ‘log_bin’;  

 

  | id | val | data |

SET INSERT_ID=4/*!*/;

  +—-+——+——-+

 

  # at 4  

 

Sql代码  

#090427 15:28:46 server id 1  end_log_pos 572 Xid = 13

  | 5 | 50 | bbb |

 

        2. 恢复数据

Ok,接着,我们需要分析的是第二个日志文件。为什么要分析它呢,因为它中途执行了一个操作是DELETE,因为我们要做的是恢复全部数据,也就是我们不希望去重做这个语句。所以在这里我们要想办法去绕开它。

  /*!*/;  

 

        

‘/*!*/;

Sql代码  

|  1 |   10 | liang |

  # at 176  

mysql> insert into test(val,data) values(90,’eeee’);

  COMMIT/*!*/;  

 

  /*!*/;  

 

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000003 –stop-pos=574 |
mysql -uroot –p  

 

  ’/*!*/;  

 

        –start-position=N :从二进制日志中位置等于N参量时的事件开始读。

 

        在一测试数据库里,创建一个表,并添加记录,然后产生日志文件。

 

  SET TIMESTAMP=1240817434/*!*/;  

 

  #090427 15:29:35 server id 1 end_log_pos 642 Query thread_id=1
exec_time=0 error_code=0  

/*!*/;

  # at 437  

|  4 |   40 | aaa   |

  SET TIMESTAMP=1240817317/*!*/;  

 

  mysql> insert into test(val,data) values(70,’ddd’);  

# at 409

  /*!*/;  

 

  SET TIMESTAMP=1240817326/*!*/;  

insert into test(val,data) values(60,’ccc’)

       
 在这个文件中,我们可以看到DELETE的操作的起始位置是875,终止位置是1008,那么我们只要重做第二个日志文件的开头到的操作,然后再从到末尾的操作,我们就可以把数据给恢复回来,而不会DELETE数据。所以执行两个命令:

 

  use mytest/*!*/;  

 

  SET @@session.lc_time_names=0/*!*/;  

 

  Query OK, 0 rows affected (0.11 sec)  

 

  Query OK, 0 rows affected (0.09 sec)  

 

Sql代码  

 

  SET TIMESTAMP=1240817375/*!*/;  

 

  mysql> insert into test(val,data) values(90,’eeee’);  

 

  SET TIMESTAMP=1240817421/*!*/;  

SET INSERT_ID=5/*!*/;

  insert into test(val,data) values(60,’ccc’)  

 

  Query OK, 1 row affected (0.03 sec)  

 

  Query OK, 1 row affected (0.05 sec)  

 

Sql代码  

 

  SET @@session.collation_database=DEFAULT/*!*/;  

|  8 |   80 | dddd  |

Sql代码  

# at 106

三、 mysql日志的查看

 

mysql>mysqlbinlog –start-datetime=’2008-01-01 00:00:00′
–stop-datetime=’2008-08-08 00:00:00′  /var/log/mysql/mysql-bin.000040
> ./tmp.log  

 

  # at 805  

  mysql -u root -pmypwd

        log-error=/var/log/mysqld_err.log

 

  | 3 | 30 | hui |

 

  /*!50003 SET
@[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 

Query OK, 1 row affected (0.03 sec)

  mysql> drop table test; –删除表  

DELIMITER /*!*/;

        log-bin=/var/log/mysqld_bin.bin

 

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000001  | mysql -uroot –p
 

 

二、 mysql日志的配置

也可以不指定日期和时间,而使用mysqlbinlog的选项–start-position和–stop-position来指定日志位置。它们的作用与起止日选项相同,不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法,特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号,可以运行mysqlbinlog寻找执行了不期望的事务的时间范围,但应将结果重新指向文本文件以便进行检查。操作方法为:

 

 

  /*!40019 SET @@session.max_insert_delayed_threads=0*/;  

 

        第三个日志文件也是同理,只要找到DROP TABLE的位置,就可以了。

 

       
通过这三个命令,可以生成分别记录了日志文件的内容,也就是用户操作的步骤。

  1. 指定恢复位置

         4. 更新日志(The update
log):记录了改变数据的语句,已经不建议使用,由二进制日志替代;

SET TIMESTAMP=1240817375/*!*/;

  9 rows in set (0.00 sec)

 

  mysql> select * from test;

 

        4. 将二进制日志文件导出txt文本文件查看

/*!50003 SET
[email protected]_COMPLETION_TYPE*/;

  +—-+——+——-+

/*!*/;

  mysql> insert into test(val,data) values(30,’hui’);  

通过这三个命令,可以在G盘下生成个文件,里面分别记录了日志文件的内容,也就是用户操作的步骤。

         5. 二进制日志(The binary
log):记录了所有对数据库数据的修改语句;

mysql> insert into test(val,data) values(20,’jia’);

  #090427 15:30:34 server id 1 end_log_pos 1078 Query thread_id=1
exec_time=0 error_code=0  

use mytest/*!*/;

  /*!*/;  

 

  #090427 15:28:37 server id 1 end_log_pos 339 Xid = 12  

 

        

 

       
 默认情况下所有日志文件会记录在MYSQL的数据目录下,你可以通过强制mysql去关闭并重新打开一个文件进行日志记录,当然系统会自动加后缀
(如.00001, .00002),方式有在mysql环境下执行语句 mysql>flush logs;
或者通过mysqladmin管理程序执行 #mysqladmin flush-logs 或 #mysqladmin
refresh

# at 312

  # at 1008  

 

         6. 超时日志(The slow
log):记录所有执行时间超过最大SQL执行时间(long_query_time)或未使用索引的语句;

 

  COMMIT/*!*/;  

 

  | 4 | 40 | aaa |

#090427 15:29:35 server id 1  end_log_pos 805 Xid = 14

  # at 778  

 

  # at 642  

mysql> insert into test(val,data) values(10,’liang’);

       
mysqlbinlog工具的使用,大家可以看MySQL的帮助手册,里面有详细的用。在这个例子中,重点是–start-position参数和–stop-position参数的使用。

/*!*/;

        2.
如果你想知道现在记录二进制数据的文件具体信息,你可以通过下列语句看到现在正在记录哪个文件,以及记录的当前位置:

/*!*/;

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 –start-pos=1008 |
mysql -uroot -p  

 

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 –stop-pos=875 |
mysql -uroot –p  

方法二

  Query OK, 1 row affected (0.08 sec)  

# at 1078

  mysql> flush logs; –产生第二个日志文件  

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000002 >
G:/002.txt

  Query OK, 1 row affected (0.03 sec)  

从二进制日志中第个位置等于和大于N参量时的事件起停止读。

  /*!*/;  

 

        5.
查询某个时间范围的可以执行下列语句,如果记录很多可以将结果定向到一个文件里自己慢慢看:-)

 

  Query OK, 1 row affected (0.03 sec)  

 

  delete from test where id between 4 and 5  

 

 

先用mysqlbinlog工具将日志文件生成txt文件出来分析。

  | 8 | 80 | dddd |

 

 

SET @@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/*!*/;

  #090427 15:28:37 server id 1 end_log_pos 176 Query thread_id=1
exec_time=0 error_code=0  

 

  Query OK, 1 row affected (0.03 sec)  

/*

  DELIMITER /*!*/;  

 

Sql代码  

 

       

# at 642

 

 

  #090427 15:30:21 server id 1 end_log_pos 981 Query thread_id=1
exec_time=0 error_code=0  

 

  SET @@session.pseudo_thread_id=1/*!*/;  

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

  ROLLBACK /* added by mysqlbinlog */;  

 

  # at 339  

 

  /*!/C gbk *//*!*/;  

SET @@session.lc_time_names=0/*!*/;

mysql>mysqlbinlog /var/log/mysql/mysql-bin.000040;  

mysql> delete from test where id between 4 and 5;  –删除记录

  #090427 15:29:35 server id 1 end_log_pos 778 Query thread_id=1
exec_time=0 error_code=0  

+—-+——+——-+

  SET TIMESTAMP=1240817434/*!*/;  

mysqlbinlog –stop-position=”368312″ /var/log/mysql/bin.123456

       

OK,现在测试数据已经建好了,要求是什么呢?

  SET
@@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=28/*!*/;
 

你还可以从命令行输入下面的内容:

  mysql> insert into test(val,data) values(50,’bbb’);  

SET INSERT_ID=7/*!*/;

  

 

  SET @@session.foreign_key_checks=1,
@@session.sql_auto_is_null=1, @@session.unique_checks=1,
@@session.autocommit=1/*!*/;  

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000002
–start-pos=1008 | mysql -uroot -p mytest

  # at 1241  

?????????????????????????????????????

  */  

mysql> flush logs;          –产生第三个文件文件

  #090427 15:28:46 server id 1 end_log_pos 572 Xid = 13  

|  5 |   50 | bbb   |

        log=/var/log/mysqld_common.log

COMMIT/*!*/;

  #090427 15:30:34 server id 1 end_log_pos 1241 Xid = 16  

mysql –user=root -pmy_pwd -e ‘SHOW BINLOG EVENTS G’

  Query OK, 0 row affected (0.05 sec)  

 

  #090427 15:28:37 server id 1 end_log_pos 204 Intvar  

 

  Query OK, 1 row affected (0.05 sec)  

如果MySQL服务器启用了二进制日志,你可以使用mysql教程binlog工具来恢复从指定的时间点开始
(例如…

       
–stop-position=N:从二进制日志中位置等于和大于N参量时的事件起停止读。

 

       
日志的查看很简单,大部分都是文本,直接用vim、less、more之类的工具看就可以了,值得说明的是二进制文件的查看:

该命令将恢复截止到在–stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句,可能你想要恢复后面发生的活动。根据这些,你可以用起使日期和时间再次运行mysqlbinlog:

  # at 106  

 

  DELIMITER ;  

|  9 |   90 | eeee  |

一、 mysql日志类型

mysql> insert into test(val,data) values(50,’bbb’);

mysql> create table test(id int auto_increment not null primary
key,val int,data varchar(20));  

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

  | 2 | 20 | jia |

上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET
TIMESTAMP语句,恢复的数据和相关MySQL日志将反应事务执行的原时间

  # at 409  

insert into test(val,data) values(70,’ddd’)

       
MYSQL有不同类型的日志文件(各自存储了不同类型的日志),从它们当中可以查询到MYSQL里都做了些什么,对于MYSQL的管理工作,这些日志文件是不可缺少的。

 

  SET INSERT_ID=5/*!*/;  

|  6 |   60 | ccc   |

        先用mysqlbinlog工具将日志文件生成txt文件出来分析。

 

  SET @@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/*!*/;  

 

  # at 1106  

 

  mysql> insert into test(val,data) values(10,’liang’);  

 

  insert into test(val,data) values(70,’ddd’)  

# at 572

  | 9 | 90 | eeee |

 

  # End of log file  

#090427 15:28:37 server id 1  end_log_pos 176 Query  thread_id=1  
exec_time=0   error_code=0

  /*!50003 SET
[email protected]_COMPLETION_TYPE*/;
 

 

         OK,现在第二个日志文件的数据了。

#090427 15:30:21 server id 1  end_log_pos 1008    Xid = 15

  #090427 15:29:35 server id 1 end_log_pos 670 Intvar  

我们先打开.txt文件来分析一下。

  ―――――――――――――――――――――――――――――――――――――  

·–stop-position=N

Sql代码  

mysql> insert into test(val,data) values(70,’ddd’);

  mysql> insert into test(val,data) values(80,’dddd’);  

ROLLBACK /* added by mysqlbinlog */;

Sql代码  

 

  #090427 15:27:56 server id 1 end_log_pos 106 Start: binlog v 4,
server v 5.1.32-community-log created 090427 15:27:56  

SET @@session.collation_database=DEFAULT/*!*/;

/*  

# at 670

一、 mysql日志类型
MYSQL有不同类型的日志文件(各自存储了不同类型的日志),从它们当中可以查询到MYSQL里都…

#090427 15:30:34 server id 1  end_log_pos 1078    Query 
thread_id=1   exec_time=0    error_code=0

# at 875  

 

Sql代码  

COMMIT/*!*/;

         1. 错误日志(The error
log):记录了数据库启动、运行以及停止过程中错误信息;

/*!*/;

  SET TIMESTAMP=1240817317/*!*/;  

 

  BEGIN  

 

  | 1 | 10 | liang |

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;

       可以看到,全部数据都回来了。

Query OK, 1 row affected (0.03 sec)

  SET INSERT_ID=6/*!*/;  

#090427 15:30:21 server id 1  end_log_pos 875 Query  thread_id=1  
exec_time=0   error_code=0

  mysql> insert into test(val,data) values(60,’ccc’);  

mysql> insert into test(val,data) values(40,’aaa’);

        3.
查看二进制数据需要借助程序mysqlbinlog,看看它支持哪些选项,根据自己需要来使用。

# at 875

  mysql> insert into test(val,data) values(40,’aaa’);  

Query OK, 0 rows affected (0.09 sec)

  #090427 15:29:35 server id 1 end_log_pos 805 Xid = 14  

 

  # at 572  

 

  /*!*/;  

 

  fF71SQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMyLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
 

#090427 15:28:46 server id 1  end_log_pos 545 Query  thread_id=1  
exec_time=0   error_code=0

四、 使用二进制日志恢复数据

 

  COMMIT/*!*/;  

insert into test(val,data) values(40,’aaa’)

  AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC  

Query OK, 1 row affected (0.03 sec)

  mysql> flush logs; –产生第三个文件文件  

mysqlbinlog –stop-date=”2005-04-20 9:59:59″ /var/log/mysql/bin.123456

  # at 1078  

 

#090427 15:30:34 server id 1  end_log_pos 1241    Xid = 16

 

mysql> insert into test(val,data) values(80,’dddd’);

 

SET TIMESTAMP=1240817375/*!*/;

 

 

 

 

 

 

 

BINLOG ‘

#090427 15:30:34 server id 1  end_log_pos 1106    Intvar

要想从二进制日志恢复数据,你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf
or
my.ini,取决于你的系统)中找到路径。如果未包含在选项文件中,当服务器启动时,可以在命令行中以选项的形式给出。启用二进制日志的选项为–
log-bin。要想确定当前的二进制日志文件的文件名,输入下面的MySQL语句:

 

Query OK, 1 row affected (0.03 sec)

mysql> insert into test(val,data) values(60,’ccc’);

SET TIMESTAMP=1240817317/*!*/;

#090427 15:30:34 server id 1  end_log_pos 1214    Query 
thread_id=1   exec_time=0    error_code=0

/*!*/;

 

 

 

 

mysqlbinlog –start-date=”2005-04-20 10:01:00″ /var/log/mysql/bin.123456

 

 

 

/*!*/;

 

 

 

SET TIMESTAMP=1240817434/*!*/;

mysql> select * from test;

 

# at 805

+—-+——+——-+

 

 

|  3 |   30 | hui   |

# at 437

# at 176

/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

 

 

#090427 15:28:37 server id 1  end_log_pos 312 Query  thread_id=1  
exec_time=0   error_code=0

 

SET TIMESTAMP=1240817421/*!*/;

BEGIN

COMMIT/*!*/;

 

就是将test表的数据全部恢复出来。

 

 

 

Query OK, 1 row affected (0.08 sec)

 

 

 

 

/*!*/;

 

 

 

将密码my_pwd替换为服务器的root密码。

SET INSERT_ID=6/*!*/;

 

# End of log file

  mysql -u root -pmypwd

Query OK, 1 row affected (0.03 sec)

 

在这里,偶是的设置是log-bin=liangck

 

  mysql -u root -pmypwd

#090427 15:28:46 server id 1  end_log_pos 437 Intvar

SET
@@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=28/*!*/;

 

|  2 |   20 | jia   |

在该行中,从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

 

*/

 

 

 

9 rows in set (0.00 sec)

 

/*!*/;

该命令将在/tmp目录创建小的文本文件,将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件,寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作,应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后,你应从命令行输入下面内容:

Query OK, 1 row affected (0.05 sec)

Query OK, 1 row affected (0.03 sec)

BEGIN

delete from test where id between 4 and 5

 

 

 

 

 

/*!*/;

 

从二进制日志中第个位置等于N参量时的事件开始读。

??????????????????????????????????

SET TIMESTAMP=1240817434/*!*/;

Query OK, 1 row affected (0.05 sec)

Query OK, 0 row affected (0.05 sec)

COMMIT/*!*/;

BEGIN

# at 1106

mysql> create table test(id int auto_increment not null primary
key,val int,data varchar(20));

MySQL使用二进制日志来恢复数据二种方法

DELIMITER ;

 

 

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000002
–stop-pos=875 | mysql -uroot -p

 

COMMIT/*!*/;

 

# at 1008

BEGIN

现在我们再查一下数据看看:

#090427 15:29:35 server id 1  end_log_pos 642 Query  thread_id=1  
exec_time=0   error_code=0

 

 

 

mysqlbinlog –start-date=”2005-04-20 9:55:00″ –stop-date=”2005-04-20
10:05:00″

mysql> drop table test;       –删除表

在这个文件中,我们可以看到DELETE的操作的起始位置是,终止位置是.那么我们只要重做第二个日志文件的开头到的操作,然后再从到末尾的操作,我们就可以把数据给恢复回来,而不会DELETE数据。所以执行两个命令:

log-bin=日志名

  1. 指定恢复时间

SET @@session.sql_mode=1344274432/*!*/;

mysqlbinlog工具的使用,大家可以看MySQL的帮助手册。里面有详细的用,

SET TIMESTAMP=1240817421/*!*/;

# at 204

 

 

 

 

#090427 15:27:56 server id 1  end_log_pos 106 Start: binlog v 4,
server v 5.1.32-community-log created 090427 15:27:56

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000003 >
G:/003.txt

 

 

SET @@session.ps教程eudo_thread_id=1/*!*/;

 

 

如果MySQL服务器启用了二进制日志,你可以使用mysql教程binlog工具来恢复从指定的时间点开始
(例如,从你最后一次备份)直到现在或另一个指定的时间点的数据。关于启用二进制日志的信息,参见5.11.3节,“二进制日志”。对于
mysqlbinlog的详细信息,“mysqlbinlog:用于处理二进制日志文件的实用工具”。

 

 

 

# at 1241

OK,现在第二个日志文件的数据了。

 

 

SET TIMESTAMP=1240817317/*!*/;

/*!50003 SET
@[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

SET TIMESTAMP=1240817326/*!*/;

SET TIMESTAMP=1240817326/*!*/;

# at 1214

 

 

# at 545

 

 

然后再启动mysql服务,因为偶是用windows系统,所以执行net start
mysql命令即可。

#090427 15:28:37 server id 1  end_log_pos 339 Xid = 12

insert into test(val,data) values(50,’bbb’)

mysqlbinlog –start-position=”368315″ /var/log/mysql/bin.123456

然后在一测试数据库教程里,创建一个表,并添加记录。

F:/Program Files/MySQL_Data/data/log>mysqlbinlog liangck.000001 |
mysql -uroot –p