1.1 开启二进制日志

二进制日志实操作

1. 开启二进制日志

[root@db01 data]# vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format=row

   

1. 查看binlog信息

show master status;

第一个事务提交从120开始

 

binlog实战操作

create database binlog;

create table binlog_tb(id int);

   

1. 插入数据

insert into bt values(1);

insert into bt values(2);

insert into bt values(3);

 

1. 查看binlog

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000007 | 1002 | | | |

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

 

1. 删除id是1的

mysql> delete from bt where id=1;

mysql> update bt set id=22 where id=2;            修改

   

1. 删库,删表

drop database binlog;

drop database bt;

   

1. 查看事件

show binlog events in 'mysql-bin.000007';

   

1. 进入/data里查看binlog日志

[root@db01 data]#cd /application/mysql/data/

mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000007

   

1. 备份二进制日志数据

[root@db01 data]#mysqlbinlog --start-position=218 --stop-position=1076 mysql-bin.000007 >/tmp/huifu.sql

--start-position=218        创建库的起始号

--stop-position=1076        没删除之前的号

   

1. 恢复数据是要加这个,防止恢复的时候数据还写入二进制日志

set sql_log_bin=0;

   

1. 导入数据

mysql> source /tmp/huifu.sql

1. 查看恢复完成

思考，存在问题：

数据库或表被误删除的是很久之前创建的（一年前）

a)    如果基于binlog全量恢复，成本很高

i.    可以用备份恢复+短时间内二进制日志，恢复到故障之前

ii.    非官方方法，binlog2sql，binlog取反，类似于Oracle的flushback

iii.    延时从库

如果同一时间内和故障库无关的数据库都有操作，在截取binlog时都会被截取到

b)    其他过滤方案？

i.    -d 参数接库名

 

-d 指定库恢复

1. 创建数据
2. 建库建表

create database db1;

use db1

create table t1(id int);

insert into t1 values(1),(2),(3),(4),(5);

commit;

 

create database db2;

use db2

create table t2(id int);

insert into t2 values(1),(2),(3),(4),(5);

commit;

 

• 查看binlog日志名

show master status;

show binlog events in 'mysql-bin.000010';

 

• 查看二进制日志

-d 指定库 显示binlog里的db1库

[root@db01 data]#mysqlbinlog -d db1 --base64-output=decode-rows -vvv /application/my

sql/data/mysql-bin.000010

 

• 删库

mysql> drop database db1;

 

• 查看binlog日志

mysqlbinlog -d db1 --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000010

 

• 导入到文件

mysqlbinlog -d db1 --start-position=120 --stop-position=873 mysql.bin.000010 > /tmp/huifu.sql

 

• 恢复db1库

 

刷新binlog日志

• flush logs;
• 重启数据库时会刷新
• 二进制日志上限（max_binlog_size）1G 日志超过1G 生成第二个日志

查看二进制日志上限

Mys=SQL [db2]>show variables like 'max_binlog_size';

 

主从时候使用

mysqldump -A -l > /mtp/full.sql

-A 全部备份

-l 生成新的日志

 

删除二进制日志

• 原则
  • 在存储能力范围内，能多保留则多保留
  • 基于上一次全备前的可以选择删除
• 删除方式
  • 根据存在时间删除日志

#临时生效

SET GLOBAL expire_logs_days = 7;

#永久生效

[root@db01 data]# vim /etc/my.cnf

[mysqld]

expire_logs_days = 7

 

• 查看二进制日志

show binary logs;

 

• 使用purge命令删除    删除3天前的

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

 

• 根据文件名删除     删除000010文件名之前的日志

PURGE BINARY LOGS TO 'mysql-bin.000010';

 

• 使用reset master    全部删除

mysql> reset master;