mysql binlog定点恢复锁表_MySQL -- binlog 操作与恢复

binlog 开启、查看：

> show variables like 'log_bin';  #查看是否开启

> set sql_log_bin=1 || set sql_log_bin=0； #启用 || 停用

> show binary logs;  //获取binlog文件列表，对应MySQL-bin.index；

> show master logs;  //查看主上的binlog

> show master status；  //查看当前正在写入的binlog

> show binlog events;  //查看第一个binlog内容

> show binlog events in 'mysql-bin.000002'指定查看。

binlog 删除：

1.关闭mysql主从，关闭binlog，然后重启数据库

2、设置自动清理：

> mysql -e "show variables like 'expire_log%';"

> mysql -e  'set global expire_logs_days=3;'  //设置自动清理时间为3天

> mysql -e  'flush logs'            //让二进制日志重新生成新文件，并自动清理过期日志

(如果执行不生效，确认mysql-bin.index与外面文件一致，否则删除多余的，再 > flush logs；)

3、

> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);  //删除10天前的MySQL binlog日志

> purge master logs before '2012-03-30 17:20:00';  //删除指定日期以前的日志索引中binlog日志文件

> purge master logs to 'mysql-bin.000002';    //删除指定日志文件的日志索引中binlog日志文件

> reset master;  or reset slave;      //置空master的binlog；置空slave的中继日志

若误删了最新的，确保mysql-bin.index内记录的和外面现有文件一致，否则删除多余的，再flush logs

4、直接rm

进入数据库数据目录 && 查看一下当前使用的binlog日志是哪个，除了这个以外的，其它都可以使用rm ，删除后把mysql-bin.index内容也对应一下

如果误删了当前使用的binlog，你会发现mysql不记录binlog日志了，你要先更改mysql-bin.index文件内容，对应一下，然后去flush logs。

----------------------------------------------------------------------------------------------------

binlog  简介：

mysql复制的三种方式：

基于SQL语句的复制(statement-based replication, SBR)

基于行的复制(row-based replication, RBR)

混合模式复制(mixed-based replication, MBR)

binlog的三种格式：

Statement

每一条会修改数据的sql都会记录在binlog中，不需记录每一行的变化，减少日志量，节约了io；

Row

5.1.5版本的MySQL才开始支持row level的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改；

Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存

文件配置：

binlog_format  = MIXED    //binlog日志格式

log_bin = 目录/mysql-bin.log  //binlog日志名

expire_logs_days = 7      //binlog过期清理时间

max_binlog_size  100m    //binlog每个日志文件大小，默认1G

binlog 恢复：

1、开启binlog，最好是改配置文件，或者数据库内直接打开

2、模拟数据写入，依照如下附录.......查询当前的数据，然后记录下当前内容，如下：

mysql> select * from number;

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

|  1 | 2016-06-29 23:27:15 |

|  2 | 2016-06-29 23:27:15 |

|  3 | 2016-06-29 23:27:15 |

|  4 | 2016-06-29 23:27:15 |

|  5 | 2016-06-29 23:27:15 |

|  6 | 2016-06-29 23:27:15 |

|  7 | 2016-06-29 23:27:15 |

|  8 | 2016-06-29 23:27:15 |

|  9 | 2016-06-29 23:27:15 |

|  10 | 2016-06-29 23:27:15 |

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

3、进行全量备份，mysqldump  -F --master-data=2 backup |gzip > backup_all.sql.gz  //-F 刷新binlog为0002  (或者我们最好每天早上4/5点进行一次全量备份)

4、继续模拟数据写入，执行附录下的内容，查看：

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

|  1  | 2016-06-29 23:27:15 |

|  2  | 2016-06-29 23:27:15 |

|  3  | 2016-06-29 23:27:15 |

|  4  | 2016-06-29 23:27:15 |

|  5  | 2016-06-29 23:27:15 |

|  6  | 2016-06-29 23:27:15 |

|  7  | 2016-06-29 23:27:15 |

|  8  | 2016-06-29 23:27:15 |

|  9  | 2016-06-29 23:27:15 |

|  10  | 2016-06-29 23:27:15 |

|  11  | 2016-06-29 23:31:03 |

|  12  | 2016-06-29 23:31:03 |

|  13  | 2016-06-29 23:31:03 |

|  14  | 2016-06-29 23:31:03 |

|  15  | 2016-06-29 23:31:03 |

|  16  | 2016-06-29 23:31:03 |

|  17  | 2016-06-29 23:31:03 |

|  18  | 2016-06-29 23:31:03 |

|  19  | 2016-06-29 23:31:03 |

|  20  | 2016-06-29 23:31:03 |

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

5、删除数据表：delete from number;

6、再次模拟数据写入，select * from bumber;

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

| id | updatetime          |

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

| 21 | 2016-06-29 23:41:06 |

| 22 | 2016-06-29 23:41:06 |

| 23 | 2016-06-29 23:41:06 |

| 24 | 2016-06-29 23:41:06 |

| 25 | 2016-06-29 23:41:06 |

| 26 | 2016-06-29 23:41:06 |

| 27 | 2016-06-29 23:41:06 |

| 28 | 2016-06-29 23:41:06 |

| 29 | 2016-06-29 23:41:06 |

| 30 | 2016-06-29 23:41:06 |

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

7、恢复数据：

先锁住表，不进行数据操作：lock table number read;

之后最好刷新一下日志，产生新的binlog，单独操作出问题的binlog(先备份问题binlog)

进数据库看问题的pos点：show binlog events in 'mysql-bin.000002';  //然后删除出问题的pos点记录

导入早上或某时的全量备份：mysql backup

binlog日志恢复法一：

mysqlbinlog mysql-bin.000002 | mysql -u用户名 -p密码 数据库名

[mysqlbinlog --start-position=1038 --stop-position=1164 --database=zyyshop  mysql-bin.00002 | mysql  -v zyyshop ]

常用选项：

--start-position=953  起始pos点

--stop-position=1437  结束pos点

---start-datetime="2013-11-29 13:18:54" 起始时间点

---stop-datetime="2013-11-29 13:21:53"  结束时间点

---database=zyyshop  指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

binlog日志恢复法二：

mysqlbinlog  mysql-bin.000002 > tmp.sql

vim  tmp.sql        //找到delete那步操作，将之删除

mysql backup < tmp.sql    //然后把操作打入数据库

注：因为锁表后续没有数据插入，如果有数据写入，也要把最新的binlog导入到数据库。

8、查看会发现数据都已经回来了。

附录：

1、建表

Create databases  backup；

CREATE TABLE `number` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',

`updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、写入数据的试验程序：

#coding:utf8

import MySQLdb

import time

def connect_mysql(db_host="192.168.11.169",user="martin",passwd="martin",db="backup",charset="utf8"):

conn = MySQLdb.connect(host=db_host,user=user,passwd=passwd,db=db,charset=charset)

conn.autocommit(True)

return conn.cursor()

3、数据插入：

for i in range(0,10):

#time=time.strftime("%Y-%m-%d %H:%M:%S")

sql = 'insert into number(updatetime) values(%s)'

values = [(time.strftime("%Y-%m-%d %H:%M:%S"))]

db1 = connect_mysql()

print db1.executemany(sql,values)