MySQL--Binlog operation and recovery

Binlog Open, view:
> Show variables like ' Log_bin '; #查看是否开启

> Set Sql_log_bin=1 | | Set sql_log_bin=0; #启用 | | Deactivate
> show binary logs; Get a list of binlog files, corresponding to Mysql-bin.index;

> Show master Logs; View our Lord's Binlog
> Show master Status;//View the Binlog currently being written
> Show Binlog events; View the first Binlog content

> Show Binlog events in ' mysql-bin.000002 ' specified view.
Binlog Delete:
1. Turn off MySQL master/slave, turn off binlog, and restart the database

2. Set up automatic cleanup:

> mysql-e "show variables like ' expire_log% ';"
> mysql-e ' Set global expire_logs_days=3; ' Set automatic cleanup time to 3 days
> mysql-e ' flush logs '//Let binary log regenerate new files and automatically clean up expired logs
(If the execution does not take effect, confirm that the Mysql-bin.index is consistent with the outside file, otherwise remove redundant, then > flush logs;)
3.

> PURGE MASTER LOGS before Date_sub (current_date, INTERVAL Day); Delete MySQL Binlog logs from 10 days ago
> Purge master logs before ' 2012-03-30 17:20:00 '; Deletes the Binlog log file from the previous log index for the specified date
> Purge master logs to ' mysql-bin.000002 '; Delete Binlog log files in the log index of the specified log file
> Reset Master;       or reset slave; Binlog of empty master; relay log for empty slave

If you mistakenly delete the latest, to ensure that the records within the Mysql-bin.index and the existing documents in the same, otherwise delete the excess, and then flush logs

4. Direct RM

Go to the database data Catalog && See what Binlog logs are currently in use, except for this, you can use RM for the other, delete the Mysql-bin.index content
If you mistakenly delete the currently used Binlog, you will find that MySQL does not log binlog logs, you have to change the Mysql-bin.index file content, corresponding, and then go to flush logs.
----------------------------------------------------------------------------------------------------

Binlog Introduction:

Three ways to copy MySQL:
SQL statement-based replication (statement-based replication, SBR)

Row-based replication (row-based replication, RBR)

Mixed mode replication (mixed-based replication, MBR)

Three formats for Binlog:

Statement

Each SQL that modifies the data is recorded in the Binlog, which does not need to record the change of each row, reduce the amount of log, and save IO;
Row

5.1.5 version of MySQL began to support the row level of replication, it does not log the SQL statement context-sensitive information, only save which record is modified;
Mixed

Starting with the 5.1.8 version, MySQL provides the mixed format, which is actually a combination of statement and row, general statement modification using statment format to save Binlog, such as some functions, statement can not complete the operation of the master-slave copy, the row format is saved
file configuration:
Binlog_format = MIXED//binlog log format
Log_bin = Directory/mysql-bin.log//binlog log name
Expire_logs_days = 7//binlog Expired cleanup time
Max_binlog_size 100m//binlog per log file size, default 1G
binlog Recovery:
1, open Binlog, it is best to change the configuration file, or directly opened in the database
2, the analog data write, according to the following appendix .... Query the current data, and then record the current content, as follows:
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, for full-scale backup, mysqldump-f--master-data=2 backup |gzip > backup_all.sql.gz//-f refresh Binlog to 0002 (or we'd better do a full backup every morning at 4/5 points)
4, continue to write the simulation data, the implementation of the contents of the Appendix, view:
+----+---------------------------+
| 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 Data sheet: delete from number;
6, again analog data write, 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. Recover Data:
Lock table First, no data operation: Lock table number read;
After that, it's better to refresh the log, create a new binlog, and operate the problematic Binlog separately (back up the problem Binlog first)
Into the database to see the problem POS point: Show Binlog events in ' mysql-bin.000002 '; Then delete the problematic POS record.
Import full-time backups in the morning or at some point: MySQL backup < Backup_all.sql
Binlog Log Recovery method one:
Mysqlbinlog mysql-bin.000002 | Mysql-u User name-p password database name
[Mysqlbinlog--start-position=1038--stop-position=1164--database=zyyshop mysql-bin.00002 | mysql-v zyyshop]
Common options:
--start-position=953 Starting POS Point
--stop-position=1437 End POS Point
---start-datetime= "2013-11-29 13:18:54" Start point
---stop-datetime= "2013-11-29 13:21:53" end point in time
---database=zyyshop specifies that only the Zyyshop database is restored (there are often multiple databases on a single host, local log logs only)
Binlog Log Recovery Method II:
Mysqlbinlog mysql-bin.000002 > Tmp.sql
Vim Tmp.sql//Find delete action, delete it
MySQL Backup < tmp.sql//Then put the operation into the database
Note: Since no data is inserted in the lock table, the latest binlog should be imported into the database if there is data written.
8, the view will find that the data have been back.

Appendix:
1. Build a table

Create databases backup;create TABLE ' number ' (' ID ' int (one) not NULL auto_increment COMMENT ' numbered ', ' UpdateTime ' Timesta MP not NULL default ' 0000-00-00 00:00:00 ', PRIMARY KEY (' id ')) engine=innodb default Charset=utf8;

2. Test procedure for writing data:

#coding: Utf8#python2.7import mysqldbimport timedef 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. Data insertion:

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)

This article is from the "North Ice--q" blog, please be sure to keep this source http://beibing.blog.51cto.com/10693373/1889397

MySQL--Binlog operation and recovery