MySQL binary log use operation steps

Brief introduction:

MySQL's binary log can be said or MySQL's most important log, it records all the DDL and DML (in addition to the data query statement) statements, as an event record, but also contains the statement of the consumption of time, MySQL binary log is a fault-safe type.

MySQL binary log is obvious, you can easily back up these logs to do data recovery, but also as a master-slave copy of the synchronization file, but the size of the binary log may be based on different requirements of the problem, so let log rollback is necessary, Of course MySQL has provided us with a binary rollback function, that is the max_binlog_size parameter.

The default MySQL binary log is automatically rolled back when it reaches 1G and can be set using the Max_binlog_size parameter if we want a smaller binary log. Test using max_binlog_size=200m, the specific application is to add this parameter in the configuration file max_binlog_size=200m, the official document explains this:

Mysqld adds a numeric extension after each binary log name. This number increases every time you start the server or refresh the log. If the current log size reaches Max_binlog_size, a new binary log is also created automatically. If you are using a large transaction, the binary log will also exceed the Max_binlog_size: The transaction is written to a binary log and should never be written into a different binary log. That is, if a large transaction is being processed when the max_binlog_size is reached, the binary log is rolled back after the transaction is processed, so the binary log may be larger than the set max_binlog_size.

In the master-slave replication application, perhaps we do not want the binary log too large, because the log is too large may affect the efficiency of the log, the appropriate adjustment of the value of max_binlog_size is very meaningful, of course, also remember to regularly clean up a long time not to log oh, Otherwise, your hard drive space will be run out soon.

Open MySQL binary log:

Edit My.cnf, add

Log-bin=/var/log/mysql/mysql-bin.log

When the log is turned on it requires myssqladmin flush logs to take effect.

Note that log-bin specifies that the extension is invalid, and when MySQL creates a binary log file, it first creates a file with "Mysql_log_bin" as the suffix of ". Index", and creates a "Mysql_log_bin" name. 000001 "for the suffix of the file. When the MySQL service restarts a file that has a suffix of ". 000001", it adds one, and the suffix is incremented by 1, and a new log file is created if the log length exceeds the upper limit of the Max_binlog_size (default is 1G); Use flush Logs (MySQL command) or executing mysqladmin–u–p flush-logs (Windows command Prompt) also creates a new log file.

View:

Because the logs are stored in binary form and cannot be read directly, you need to use the Mysqlbinlog tool from MySQL to view

Mysqlbinlog mysql-bin.000002-d Test
Mysqlbinlog There are some options available, a simple description of common options:
-D,--database=name: Specifies the database name, listing only the operations of the specified database.
-D,--disable-log-bin: Disables binary logging when performing a restore. Can prevent the same MySQL plus-t into the dead loop
-O,--offset=n: n-line command ignored before log off
-R,--result-file=name: output log to specified file
-R,--read-from-remote-server: Read binary from a MySQL server
-S,--short-form: Show simple format, omit some information
-S,--socket=name:socket file connection path.
-T,--to-last-log: Used with-R, will not stop at the end of the binary log, but at the end of the MySQL server-generated binlog, if output and input are in a MySQL can cause a dead loop.
--set-charset=char-name: In the output text format, add set names Char-name in the first line.
--start-datetime=#--stop-datetime=#: Log that specifies the starting date of the output.
--start-position=#--stop-position=#: Specifies the location of the start log.
Clean:

Delete all binary logs:

Reset Master

To delete a portion of a log:

PURGE Master LOGS to & PURGE master LOGS before
PURGE MASTER LOGS to ' mysql-bin.****** ' is to delete all logs prior to ' the ' Hu ' number
PURGE MASTER LOGS before ' yyyy-mm-dd hh:mm:ss ' command to delete all logs before the ' Yyyy-mm-dd Hh:mm:ss ' time

To set the log expiration time:

Modify MY.CNF
Expire_log_day=5

Here set the Save 5 days log, more than 5 days of log will be automatically deleted

Recovery:

Full recovery:

Mysqlbinlog mysql-bin.00001|mysql-uroot-p

Point-in-time-based recovery:

If you mistakenly delete a table, the use of full recovery is useless, because the log also retains the deleted SQL statements, so we need to revert to the state before the misoperation, and then skip the wrong operation of the statement.
If I accidentally deleted a table at 20:00, you can use the following statement to restore:

Mysqlbinlog--stop-date= ' 2012-06-05 19:59:59 '/var/log/mysql-bin.000001 | Mysql-uroot-p

Skip the Point-in-time of the accidental deletion and then execute:

Mysqlbinlog--start-date= ' 2012-06-05 20:01:00 '/var/log/mysql-bin.000001 | Mysql-uroot-p

Based on location-point recovery:

More accurate data can be obtained based on the restoration of location points.
Binlog
As shown above, the drop table test starts at 889107 and terminates at 889189, so we can use the following statement to recover:

Mysqlbinlog--stop-position= ' 889107 '/var/lib/mysql/mysql-bin.000001|mysql-uroot-p
Mysqlbinlog--start-position= ' 889189 '/var/lib/mysql/mysql-bin.000001|mysql-uroot-p

Sometimes it may be because of the system version of the problem, the above method does not work, you can export the binary to a SQL file, and then directly based on the SQL statement recovery

Mysqlbinlog mysqlbinlog.000001 >log.sql