MySQL binary log (bin-log) configuration and related operations

1. Turn on Bin-log

(1) Inside the My.con configuration file

[Mysqld] #选项添加

Log-bin=mysql-bin #日志文件名称, location not specified, default data file location

Restart MySQL Service

Log_bin is the file name of the generated bin-log, the suffix is the 6-digit encoding, starting from 000001, according to the above configuration, the resulting file is:

mysql_bin.000001

mysql_bin.000002

......

2. Basic operation

(1) View all log files:

Mysql> show binary logs; or show master logs;

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

| Log_name | File_size |

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

|       mysql-bin.000001 | 120 |

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

(2) To view the log file being written:

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000001 |              120 |                  |                   | |

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

(3) View current Binlog file contents:

Mysql> Show Binlog events; #可以格式化输出 show Binlog events\g; Or specify a log file to view Show Binlog events in ' mysql-bin.000001 ';

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

| Log_name | Pos | Event_type | server_id | End_log_pos | Info |

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

|   mysql-bin.000001 | 4 |         Format_desc |         1 | 120 | Server ver:5.6.25-log, Binlog ver:4 |

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

Note:

Log_name: Which file does this log exist in

Pos:log start position in Bin-log

Type information for Event_type:log

SERVER_ID: You can view the server_id in the configuration to indicate which server the log is generating

End_log_pos:log the end position in the Bin-log

Info:log Some of the notes information, you can visually see what is doing

(4) Manually enable the new log file, usually after the database is backed up execution

Mysql> Show master status; #

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000001 |              120 |                  |                   | |

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

1 row in Set (0.00 sec)

mysql> flush logs; #结束正在写入日志文件

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000002 |              120 |                  |                   | |

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

(5) Delete all binary logs and record from new start

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000002 |              120 |                  |                   | |

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

1 row in Set (0.00 sec)

mysql> Reset Master; #重新开始

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000001 |              120 |                  |                   | |

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

Other than that:

Mysql> purge master logs to ' mysql-bin.000002 '; Delete all logs before #是将 ' mysql-bin.000002 ' number

Mysql> purge master logs before ' yyyy-mm-dd hh:mm:ss ' #是将在 ' yyyy-mm-dd hh:mm:ss ' time before all logs are deleted

3. Binary log file Export

# mysqlbinlog--start-datetime= "2015-07-02 11:25:56"--stop-datetime= "2015-07-02 14:20:10" mysql-bin.000001 >/data /test01.log #按时间点导出

# mysqlbinlog--start-position=203--stop-position=203 mysql-bin.000001 >/data/test02.log #按事件位置导出

4. Recovering data

It is strongly recommended that you make a full backup of the database before doing any recovery, and the new library will be restored.

Recovery

Bin-log is a record of all MySQL events, can be done through bin-log full recovery, point-in-time recovery, and location-based recovery

(1) Full recovery, perform the last full backup restore, and then perform the binary log file recovery resulting from the last backup

# mysql localhost mysql-bin.000001 | Mysql-uroot-p

This allows the database to be fully restored to its full state before the crash.

(2) based on the point-in-time recovery, if you confirm that the misoperation point of Time is 2015-06-04 10:00:00 do the following

# mysqlbinlog--stop-date= ' 2015-06-04 9:59:59 ' mysql-bin.000001 | Mysql-uroot-p

Then skip the time of the misoperation and proceed to the following Binlog

# mysqlbinlog--start-date= ' 2015-06-04 10:01:00 ' mysql-bin.000001 | Mysql-uroot-p

where--stop-date= ' 2015-06-04 9:59:59 ' and--start-date= ' 2015-06-04 10:01:00 '

Take two time points

# mysqlbinlog--start-datetime= "2015-07-02 11:25:56"--stop-datetime= "2015-07-02 14:20:10" mysql-bin.000001 | Mysql-u root-p

#注: The time is the time you mistakenly operate, and this point of time may also involve more than just the wrong operation, there may be the correct operation has been skipped. Then perform a location recovery

Based on location recovery, verify that 6259-6362 is the wrong operation point by viewing the log file information

# Mysqlbinlog--stop-position=6259 mysql-bin.000001 | Mysql-uroot-p #从1开始至6259的事件读, excluding 6259 events

# Mysqlbinlog--start-position=6363 mysql-bin.000001 | Mysql-uroot-p #从6259的事件开始读

# Take two event points

Mysqlbinlog--start-position=5786--stop-position=6254 mysql-bin.000001 | Mysql-uroot-p

This article is from the "Home_tang" blog, make sure to keep this source http://yagetang.blog.51cto.com/1780698/1670236

MySQL binary log (bin-log) configuration and related operations