mysql设置停止二进制文件的操作_MySQL二进制日志操作

二进制日志

概念

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

使用用途

查看数据库变更历史

数据库增量备份

数据库灾难恢复

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

二进制日志性能影响

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

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

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

开启二进制日志

方法一：不重启修改二进制日志配置

SET @@global.log_bin=1;

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

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

SHOW VARIABLES LIKE '%bin%';

常用二进制日志相关配置参数

log-bin=

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

max-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> update tt1 set birthday='1991-06-28' where id=4;

使用mysqlbinlog命令查看二进制日志的变化

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

mysql> set @@global.binlog_format=ROW;

mysql> set binlog_format=ROW;

修改mysql数据

查看二进制日志的变化

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

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

#加-v解析二进制日志

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命令删除二进制日志文件

查看结果

[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

[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天前的全部二进制日志

查看结果

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

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

删除2016-03-14之前的全部二进制日志

查看结果

[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