MYSQL binlog简述及简单操作

来源：互联网发布：网上哪里买淘宝钻号编辑：程序博客网时间：2024/05/23 00:05

原文地址：http://blog.csdn.net/johnstrive/article/details/47946277

binlog主要两大作用

高可用
数据恢复。

如何开启binlog

在my.cnf中配置:log_bin =/home/data/mysql/binlog/mysql-bin

检查是否开启

mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------------+| Variable_name                   | Value                                   |+---------------------------------+-----------------------------------------+| log_bin                         | ON                                      || log_bin_basename                | /home/data/mysql/binlog/mysql-bin       || log_bin_index                   | /home/data/mysql/binlog/mysql-bin.index || sql_log_bin                     | ON                                      |+---------------------------------+-----------------------------------------+1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

关闭binlog

my.cnf注销#log_bin =/home/data/mysql/binlog/mysql-bin
需要restart数据库

线上开启binlog

SET sql_log_bin=1|0 （1为开启，0为关闭）
ps：重启数据库失效

切换binlog命令

在mysql中执行flush logs;

修改二进制日志文件（binlog）大小

my.cnf中配置max_binlog_size

binlog 清理

主从模式下需要关闭主从

设置有效天数清理binlog

my.cnf设置expire_logs_days=10;意思是保留10天的binlog滚动清理。

手动清除binlog文件

根据日志文件名：PURGE MASTER LOGS TO ′MySQL-bin.010′;
根据日志时间： PURGE MASTER LOGS BEFORE '2015-03-02 13:00:00';
根据日志天数： PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 1 DAY);
清除binlog时，注意slave保持一致，不能清除正在复制的日志。

使用mysqlbinlog进行二进制解析

执行mysqlbinlog可能会遇到的错误
ERROR: Error in Log_event::read_log_event(): ‘Found invalid event in binary log’, data_len: 77, event_type: 19 Could not read entry at offset 306709:Error in log format or read error，将最新安装的mysql bin目录放到环境变量中就可以了。或使用全路径： /usr/local/mysql/bin/mysqlbinlog

解析出来的格式(隔离级别rc模式)：read-commit

statement -> sql语句mixed -> dml(记录所有行的变更)  ddl(sql语句)row  ->记录所有行的变更1
2
3
1
2
3

rr模式不受此限制

使用binlog恢复数据

恢复条件：

旧表：全备份sql文件 + binlog日志
新表：binlog日志（在此binlog录入期间create）

binlog恢复命令

/usr/local/mysql/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000030 | /usr/local/mysql/bin/mysql -uroot -p1
1

管道符的作用是先执行前面的语句，然后通过后面的语句导入数据库

定点恢复

--start-position 开始点 --stop-position 结束点--start-date 开始时间 --stop-date  结束时间eg:/usr/local/mysql/bin/mysqlbinlog --start-datetime="2015-02-26 20:05:00" --stop-datetime="2015-02-26 20:07:00" mysql-bin.000067 | /usr/local/mysql/bin/mysql -uroot -p1
2
3
4
1
2
3
4

实战手记

在测试之前，需要保证my.cnf中几个关键配置是关闭的，因为如果我打开指定忽略和指定写入binlog的配置，测试表将无法写入binlog，也有一种情况是写入了。但是无法将数据恢复到表中。

#不写入binlog二进制日志中的数据库#binlog-ignore-db#写入binlog二进制日志中数据库#binlog-do-db#指定只复制哪个库的数据#replicate-do-db1
2
3
4
5
6
1
2
3
4
5
6

旧表：全备份sql文件 + binlog日志

1.以前录入的表数据：

create table test(id int,name varchar(20)) engine=innodb charset=utf8;insert into test(id,name)values(1,'a');insert into test(id,name)values(233,'bbbb');1
2
3
1
2
3

2.全备份

/usr/local/mysql/bin/mysqldump -uroot -p --opt --default-character-set=utf8 --extended-insert=false --triggers -R --hex-blob --all-databases --flush-logs   > /tmp/alldb.sql1
1

3.建表
– 录入数据
insert into test(id,name)values(3,'c');
//新开一个binlog日志，现在会生成一个新的binlog日志,下面的操作会记录在新的binlog日志中

mysql> select * from test;+------+------+| id   | name |+------+------+|    1 | a    ||    2 | b    ||    3 | c    ||    1 | a    ||  233 | bbbb ||    3 | c    |+------+------+6 rows in set (0.00 sec)1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12

4.切换日志
以便将完整的操作保存到单独的binlog中
flush logs;

5.删除数据

select * from test; #有数据delete from test;drop table test;1
2
3
1
2
3

6.恢复数据
先查看上一个binlog里面的内容：

/usr/local/mysql/bin/mysqlbinlog --no-defaults  --base64-output=decode-rows -v  mysql-bin.000042 | more1
1

7.查看新的binlog里面的内容

/usr/local/mysql/bin/mysqlbinlog --no-defaults  --base64-output=decode-rows -v  mysql-bin.000043 | more1
1

8.先恢复全备份集合：
/usr/local/mysql/bin/mysql -uroot -p </tmp/alldb.sql
9.恢复数据

/usr/local/mysql/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000042 | /usr/local/mysql/bin/mysql -uroot -p1
1

10.查看表中变化

新表新表：binlog日志（在此binlog录入期间create）

1.切换日志
flush logs;
2.建立新表

create table test2(id int,name varchar(20)) engine=innodb charset=utf8;insert into test2(id,name)values(1,'a');insert into test2(id,name)values(2,'b');1
2
3
1
2
3

3.切换日志
flush logs;
4.之后drop表；
drop table test2;

5.然后利用二进制日志进行恢复

/usr/local/mysql/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000044 | /usr/local/mysql/bin/mysql -uroot -p1
1

定点恢复

定位：

--start-position #开始点 --stop-position #结束点--start-date #开始时间 --stop-date  #结束时间1
2
1
2

按照起始时间来恢复

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2015-02-26 20:05:00" --stop-datetime="2015-02-26 20:07:00" mysql-bin.000067 | /usr/local/mysql/bin/mysql -uroot -p1
1

按照起始位置来进行恢复

/usr/local/mysql/bin/mysqlbinlog --start-position="20" --stop-position="2000" mysql-bin.000067 | /usr/local/mysql/bin/mysql -uroot -p1
1

0 0