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 <  backup_all.sql
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
#python2.7
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)