linux mysql 二进制日志格式_MySQL二进制日志操作

最新推荐文章于 2024-08-11 03:09:22 发布

三月小公举

最新推荐文章于 2024-08-11 03:09:22 发布

阅读量171

点赞数

文章标签： linux mysql 二进制日志格式

本文链接：https://blog.csdn.net/weixin_30762363/article/details/113222019

版权

二进制日志

概念记录对数据发生或潜在发生更改的SQL语句，并且是以二进制格式保存的日志

使用用途查看数据库变更历史

数据库增量备份

数据库灾难恢复

MySQL复制(主从、主主复制)

二进制日志性能影响日志即影响MySQL性能又占用大量磁盘空间。因此，往往需要做采样分析时才会打开

即使做采样分析，也最好仅在一台测试机上开启

二进制日志由于用途广泛，大多数情况下会开启。需要制定合理的备份计划和管理策略

开启二进制日志

方法一：不重启修改二进制日志配置SET @@global.log_bin=1;

SET @@global.binlog_szie=37268;(单位：bytes)

其他参数可以通过以下命令查阅：

SHOW VARIABLES LIKE '%bin%';mysql> show variables like '%bin%';

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

| Variable_name | Value |

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

| bind_address | * |

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_error_action | IGNORE_ERROR |

| binlog_format | STATEMENT |

| binlog_gtid_simple_recovery | OFF |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_row_p_w_picpath | FULL |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| binlogging_impossible_mode | IGNORE_ERROR |

| innodb_api_enable_binlog | OFF |

| innodb_locks_unsafe_for_binlog | OFF |

| log_bin | ON |

| log_bin_basename | /data/3306/mysql-bin/mysql-bin |

| log_bin_index | /data/3306/mysql-bin/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| simplified_binlog_gtid_recovery | OFF |

| sql_log_bin | ON |

| sync_binlog | 0 |

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

26 rows in set (0.00 sec)

常用二进制日志相关配置参数log-bin=

#开启并指定二进制日志保存路劲及文件名，不设置则使用默认值。默认存放位置为数据库文件所目录下，名称为hostname-bin.xxxxxmax-binlog-size=500m

#设置单个二进制日志文件的最大值，默认1G，最大1G

binlog-do-db与binlog-ignore-db

#指定二进制日志文件记录哪些数据库操作

binlog-cache-size=100m

#设置二进制日志缓存大小

sync-binlog=N

#每隔N秒将缓存中的二进制日志记录写回硬盘。默认为0。不过，你经常会陷入group commit函数与I/O之间二选一的矛盾。如果在replication环境中，由于考虑到耐久性和一致性，则需要设置为1。同时，还需要设置innodb_flush_log_at_trx_commit=1以及innodb-support-ax=1(默认已开启)

暂停二进制日志SET sql_log_bin={0|1};

查看二进制日志mysqlbinlog host2-bin.000001

创建一个数据库并做相应修改，并查看二进制日志的变化

mysql> create database t1;

mysql> create table tt1(id int,name varchar(20),birthday date);

mysql> insert into tt1(id,name,birthday) values('1','demi','1991-10-12');

mysql> insert into tt1(id,name,birthday) values('2','hoai','1992-02-20');mysql> select * from tt1;

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

| id | name | birthday |

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

| 1 | demi | 1991-10-12 |

| 2 | hoai | 1992-02-20 |

| 3 | wiss | 1991-07-14 |

| 4 | kime | 1993-06-18 |

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

4 rows in set (0.00 sec)

mysql> update tt1 set birthday='1991-06-28' where id=4;mysql> select * from tt1;

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

| id | name | birthday |

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

| 1 | demi | 1991-10-12 |

| 2 | hoai | 1992-02-20 |

| 3 | wiss | 1991-07-14 |

| 4 | kime | 1991-06-28 |

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

4 rows in set (0.00 sec)

使用mysqlbinlog命令查看二进制日志的变化[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

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

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#160314 18:34:51 server id 3 end_log_pos 120 CRC32 0xfd6a32fe Start: binlog v 4, server v 5.6.27-log created 160314 18:34:51 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

y5PmVg8DAAAAdAAAAHgAAAABAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADLk+ZWEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf4y

av0=

'/*!*/;

# at 120

#160314 21:03:56 server id 3 end_log_pos 208 CRC32 0x12f2847a Querythread_id=4exec_time=0error_code=0

SET TIMESTAMP=1457960636/*!*/;

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

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

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

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

/*!\C utf8 *//*!*/;

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

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

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

create database t1

/*!*/;

# at 208

#160314 21:08:36 server id 3 end_log_pos 333 CRC32 0x806b0266 Querythread_id=4exec_time=0error_code=0

use `t1`/*!*/;

SET TIMESTAMP=1457960916/*!*/;

create table tt1(id int,name varchar(20),birthday date)

/*!*/;

# at 333

#160314 21:10:05 server id 3 end_log_pos 408 CRC32 0xc4950130 Querythread_id=4exec_time=0error_code=0

SET TIMESTAMP=1457961005/*!*/;

BEGIN

/*!*/;

# at 408

#160314 21:10:05 server id 3 end_log_pos 543 CRC32 0xab0bc88f Querythread_id=4exec_time=0error_code=0

SET TIMESTAMP=1457961005/*!*/;

insert into tt1(id,name,birthday) values('1','demi','1991-10-12')

/*!*/;

# at 543

#160314 21:10:05 server id 3 end_log_pos 574 CRC32 0xacd9a973 Xid = 27

COMMIT/*!*/;

# at 574

#160314 21:10:40 server id 3 end_log_pos 649 CRC32 0xb6e8ef83 Querythread_id=4exec_time=0error_code=0

SET TIMESTAMP=1457961040/*!*/;

BEGIN

/*!*/;

# at 649

#160314 21:10:40 server id 3 end_log_pos 784 CRC32 0xef33bae2 Querythread_id=4exec_time=0error_code=0

SET TIMESTAMP=1457961040/*!*/;

insert into tt1(id,name,birthday) values('2','hoai','1992-02-20')

/*!*/;

# at 784

#160314 21:10:40 server id 3 end_log_pos 815 CRC32 0xc1ec540f Xid = 29

COMMIT/*!*/;

# at 815

#160314 21:11:00 server id 3 end_log_pos 890 CRC32 0xb4e3c45e Querythread_id=4exec_time=0error_code=0

SET TIMESTAMP=1457961060/*!*/;

BEGIN

/*!*/;

# at 890

#160314 21:11:00 server id 3 end_log_pos 1025 CRC32 0xf8584b1d Querythread_id=4exec_time=0 error_code=0

SET TIMESTAMP=1457961060/*!*/;

insert into tt1(id,name,birthday) values('3','wiss','1991-07-14')

/*!*/;

# at 1025

#160314 21:11:00 server id 3 end_log_pos 1056 CRC32 0xe75877f8 Xid = 30

COMMIT/*!*/;

# at 1056

#160314 21:11:33 server id 3 end_log_pos 1131 CRC32 0x12b7b345 Querythread_id=4exec_time=0 error_code=0

SET TIMESTAMP=1457961093/*!*/;

BEGIN

/*!*/;

# at 1131

#160314 21:11:33 server id 3 end_log_pos 1266 CRC32 0xab6f89cc Querythread_id=4exec_time=0 error_code=0

SET TIMESTAMP=1457961093/*!*/;

insert into tt1(id,name,birthday) values('4','kime','1993-06-18')

/*!*/;

# at 1266

#160314 21:11:33 server id 3 end_log_pos 1297 CRC32 0x2e4dbdb4 Xid = 31

COMMIT/*!*/;

# at 1297

#160314 21:14:04 server id 3 end_log_pos 1372 CRC32 0x571a3dda Querythread_id=4exec_time=0 error_code=0

SET TIMESTAMP=1457961244/*!*/;

BEGIN

/*!*/;

# at 1372

#160314 21:14:04 server id 3 end_log_pos 1489 CRC32 0xaee0efb9 Querythread_id=4exec_time=0 error_code=0

SET TIMESTAMP=1457961244/*!*/;

update tt1 set birthday='1991-06-28' where id=4

/*!*/;

# at 1489

#160314 21:14:04 server id 3 end_log_pos 1520 CRC32 0xa7faed24 Xid = 33

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

修改二进制日志记录配置为ROW方式(ROW记录的二进制信息最为完整)

mysql> set @@global.binlog_format=ROW;

mysql> set binlog_format=ROW;

mysql> show variables like 'binlog_format';

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

| Variable_name | Value |

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

| binlog_format | ROW |

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

1 row in set (0.00 sec)

修改mysql数据mysql> update tt1 set birthday='1992-12-20' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from tt1;

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

| id | name | birthday |

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

| 1 | demi | 1992-12-20 |

| 2 | hoai | 1992-02-20 |

| 3 | wiss | 1991-07-14 |

| 4 | kime | 1991-06-28 |

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

4 rows in set (0.00 sec)

查看二进制日志的变化

[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011BINLOG '

07zmVhMDAAAAMAAAAGYGAAAAAEcAAAAAAAEAAnQxAAN0dDEAAwMPCgI8AAe2N8/Z

07zmVh8DAAAAPgAAAKQGAAAAAEcAAAAAAAEAAgAD///4AQAAAARkZW1pTI8P+AEAAAAEZGVtaZSR

D3vrjpU=

'/*!*/;

# at 1700

#160314 21:29:55 server id 3 end_log_pos 1731 CRC32 0xff29a00b Xid = 41

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011 -v

#加-v解析二进制日志

BINLOG '

07zmVhMDAAAAMAAAAGYGAAAAAEcAAAAAAAEAAnQxAAN0dDEAAwMPCgI8AAe2N8/Z

07zmVh8DAAAAPgAAAKQGAAAAAEcAAAAAAAEAAgAD///4AQAAAARkZW1pTI8P+AEAAAAEZGVtaZSR

D3vrjpU=

'/*!*/;

### UPDATE `t1`.`tt1`

### WHERE

### @1=1

### @2='demi'

### @3='1991:10:12'

### SET

### @1=1

### @2='demi'

### @3='1992:12:20'

# at 1700

#160314 21:29:55 server id 3 end_log_pos 1731 CRC32 0xff29a00b Xid = 41

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqlbinlog命令参数--start-datetime=name

--stop-datetime=name

-j, --start-position=#

--stop-position=#(指定范围查看二进制日志和--start-position一起使用)

使用二进制日志恢复数据

#mysqlbinlog host-bin.000001 | mysql

#mysqlbinlog host-bin.000001 > backup.sql

#mysqlbinlog host-bin.000002 >> backup.sql

#mysql dbname < backup.sql

删除二进制日志二进制日志会不断的增长，并产生多个文件。因此，需要制定备份计划和管理策略。无用的二进制日志要记得及时删除。

删除二进制日志的方法：

1、操作系统命令直接删除

2、RESET MASTER

3、PURGE

格式：

PURGE {MASTER|BINARY} LOGS TO 'log_name'

PURGE {MASTER|BINARY} LOGS BEFORE 'date'

1、使用RESET MASTER命令删除二进制文件查看二进制文件

[root@slave1 ~]# ls /data/3306/mysql-bin/

mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.000009 mysql-bin.000011

mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008 mysql-bin.000010 mysql-bin.index执行reset master命令删除二进制日志文件mysql> reset master;

Query OK, 0 rows affected (0.01 sec)查看结果

[root@slave1 ~]# ls /data/3306/mysql-bin/

mysql-bin.000001 mysql-bin.index

2、使用PURGE命令指定删除某个二进制文件之前的二进制文件

[root@slave2 ~]# ls /data/3306/mysql-bin/

mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.000009

mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008 mysql-bin.index

mysql> purge master logs to 'mysql-bin.000005';

Query OK, 0 rows affected (0.00 sec)

[root@slave2 ~]# ls /data/3306/mysql-bin/

mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.index

3、使用PURGE命令删除指定日期之前的二进制文件

[root@slave2 ~]# date "+%F %T"

2016-03-14 22:12:41

删除5天前的全部二进制日志mysql> purge master logs before '2016-03-10 00:00:00';

Query OK, 0 rows affected (0.02 sec)

查看结果

[root@slave2 ~]# ls /data/3306/mysql-bin/

mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.index

删除2016-03-14之前的全部二进制日志mysql> purge master logs before '2016-03-14 00:00:00';

Query OK, 0 rows affected (0.00 sec)

查看结果

[root@slave2 ~]# ls /data/3306/mysql-bin/

mysql-bin.000008 mysql-bin.000009 mysql-bin.index

查看binlog日志

mysql> show binary logs;

删除10天前的二进制日志文件

mysql> purge master logs before date_sub(current_date,interval 10 day);

设置自动清理二进制日志方法设置binlog的过期时间为5天

mysql> set global expire_logs_days=5;

刷新设置

mysql> flush logs;

修改my.cnf配置，增加二进制日志过期参数(使重启后仍然生效)

expire_logs_days = 5

三月小公举

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫