mysql二进制日志管理_mysql学习------二进制日志管理

最新推荐文章于 2021-03-24 15:25:27 发布

高难饱

最新推荐文章于 2021-03-24 15:25:27 发布

阅读量110

点赞数

文章标签： mysql二进制日志管理

本文链接：https://blog.csdn.net/weixin_36228865/article/details/113225959

版权

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-updates该参数在搭建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、二进制日志存放位置

[root@mysrv ~]# 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)

查看日志信息：

[root@mysrv ~]# cd /usr/local/mysql/data/mysql/

[root@mysrv 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文件

[root@mysrv 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;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000011 | 114 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.08 sec)

[root@mysrv 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切换：

[root@mysrv ~]# mysqladmin flush-logs -u root -p

Enter password:

[root@mysrv 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

[root@mysrv ~]# mysqladmin refresh -u root -p

Enter password:

[root@mysrv 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)

查看生成的二进制日志内容：

[root@mysrv mysql]# mysqlbinlog mysql-bin.000014

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

/*!50003 SET @OLD_COMPLETION_TYPE=@@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 COMPLETION_TYPE=@OLD_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)

[root@mysrv mysql]# strings mysql-bin.000014

5.6.4-m7-log

prod

BEGIN

prod

insert into t1 values (4,'rose')

prod

BEGINw

prod

insert into t1 values (5,'john')w

[root@mysrv mysql]# mysqlbinlog mysql-bin.000014

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

/*!50003 SET @OLD_COMPLETION_TYPE=@@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 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

[root@mysrv 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;

+------------------+-----+-------------+-----------+-------------+-----------------------------------------+

+------------------+-----+-------------+-----------+-------------+-----------------------------------------+

| 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';

+------------------+-----+-------------+-----------+-------------+----------------------------------------------+

+------------------+-----+-------------+-----------+-------------+----------------------------------------------+

| 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)

利用日志恢复，将日志生成文本文件进行分析：

[root@mysrv mysql]# mysqlbinlog mysql-bin.000014 >/home/mysql/log14.txt

[root@mysrv mysql]# cat log14.txt

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

/*!50003 SET @OLD_COMPLETION_TYPE=@@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 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

[root@mysrv mysql]#

利用二进制日志恢复：

[root@mysrv mysql]# mysqlbinlog mysql-bin.000014 --stop-pos=597 |mysql -u root -p

Enter password:

或者

mysqlbinlog --start-position=362 --stop-position=891 master-bin.000002 | mysql -uroot -p

Enter password:

---在delete操作前停止日志的应用

查看已恢复的数据：

mysql> select * from t1;

+------+------+

| id | name |

+------+------+

| 4 | rose |

| 5 | john |

+------+------+

2 rows in set (0.00 sec)

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

---数据恢复成功！

高难饱

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql二进制日志管理_mysql学习------二进制日志管理

2、Binlog_cache_size的默认大小为32KBmysql>showvariableslike'binlog_cache%';+-------------------+-------+|Variable_name|Value|+-------------------+-------+|binlog_cache_size|32768|+--------...
复制链接

扫一扫