Mysqlbinlog Restore Backup Example

Binary Log incremental backup Database The tools used are: mysqlbinlog;

This is available after version 5.1.

Simple configuration, go to MySQL directory first: C:\Program files\mysql\mysql Server 5.5 is the directory where My.ini resides;

Modify My.ini:

[Mysqld]
log-bin= ' d:/log/mylog '//log output directory, must exist, or MySQL restart error;
Binlog_format = ' MIXED '//binary log format has 3 kinds:

Mysql Binlog logs are available in three formats, statement,mixed, respectively, and row!

1.Statement: Every SQL that modifies data is recorded in Binlog.

Advantages: No need to record the change of each line, reduce the Binlog log volume, save IO, improve performance. (compared to how much performance and log volume The row can save, depending on the SQL case of the application, the log volume generated by the normal record modification or the insertion of the row format is less than the amount of log generated by statement, but given the conditional update operation and the whole table deletion, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application. )

Cons: because the records only execute statements, in order for these statements to run correctly on the slave, it is also necessary to record some information about each statement at the time of execution, to ensure that all statements can be slave and executed at the master side of the same result. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues (such as the Sleep () function, last_insert_id (), and user-defined functions (UDF) can cause problems ).

Statements that use the following functions cannot be copied:

* Load_file ()

* UUID ()

* USER ()

* Found_rows ()

* Sysdate () (unless the--sysdate-is-now option is enabled at startup)

At the same time in the insert ... SELECT produces more row-level locks than RBR

2.Row: does not log the SQL statement context-sensitive information, only save which record is modified.

Advantage: Binlog can not record the context-sensitive information of executed SQL statements, only need to record what the record was modified to. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations

disadvantage: All executed statements when logged in the log, will be recorded in each row of changes to record, which may produce a large number of log content, such as an UPDATE statement, modify multiple records, then binlog each change will have a record, This causes the Binlog log volume to be large, especially when executing a statement such as ALTER TABLE, where each record is changed due to the table structure modification, and each record in the table is recorded in the log.

3.Mixedlevel: is the above two levels of mixed use, the 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 to save Binlog, MySQL differentiates the log form of the treated record according to each specific SQL statement executed, that is, choosing between statement and row. The new version of the MySQL Squadron row level mode is also optimized, and not all changes are recorded at the row level. The statement pattern is recorded when a table structure change is encountered. For statements that modify data such as update or delete, the changes are recorded for all rows.

There are 2 new files found in D:\log after a successful reboot: mylog.index,mylog.000001

Use Mysqlbinlog to view logs:

C:\Program files\mysql\mysql Server 5.5\bin>mysqlbinlog d:\log\mylog.000016 can be seen in the console.

can also be exported to the text to see;

C:\Program files\mysql\mysql Server 5.5\bin>mysqlbinlog d:\log\mylog.000016>e:\log.txt

Content:

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 4
#140917 16:51:04 Server ID 1 end_log_pos 107 start:binlog v 4, Server v 5.5.29-log created 140917 16:51:04
# Warning:this Binlog is either on use or was not closed properly.
BINLOG '
Eeszva8baaaazwaaagsaaaabaaqans41lji5lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaavaaegggaaaaicagcaa==
‘/*!*/;
# at 107
#140917 16:51:50 Server ID 1 end_log_pos 175 Query thread_id=1 exec_time=0 error_code=0
SET timestamp=1410943910/*!*/;
SET @ @session. pseudo_thread_id=1/*!*/;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;
SET @ @session. sql_mode=1344274432/*!*/;
SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;
/*!\c UTF8 *//*!*/;
SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/*!*/;
SET @ @session. lc_time_names=0/*!*/;
SET @ @session. collation_database=default/*!*/;
BEGIN
/*!*/;
# at 175
#140917 16:51:50 Server ID 1 end_log_pos 262 Query thread_id=1 exec_time=0 error_code=0
Use ' Test '/*!*/;
SET timestamp=1410943910/*!*/;
INSERT into T values (3)
/*!*/;
# at 262
#140917 16:51:50 Server ID 1 end_log_pos 289 Xid = 11
commit/*!*/;
# at 289
#140917 16:52:52 Server ID 1 end_log_pos 357 Query thread_id=1 exec_time=0 error_code=0
SET timestamp=1410943972/*!*/;
BEGIN
/*!*/;
# at 357
#140917 16:52:52 Server ID 1 end_log_pos 444 Query thread_id=1 exec_time=0 error_code=0
SET timestamp=1410943972/*!*/;
INSERT into T values (4)
/*!*/;
# at 444
#140917 16:52:52 Server ID 1 end_log_pos 471 Xid = 12
commit/*!*/;
# at 471
#140917 16:53:31 Server ID 1 end_log_pos 574 Query thread_id=1 exec_time=0 error_code=0
SET timestamp=1410944011/*!*/;
DROP TABLE ' t '/* generated by Server */
/*!*/;
# at 574
#140917 16:54:40 Server ID 1 end_log_pos 675 Query thread_id=3 exec_time=0 error_code=0
SET timestamp=1410944080/*!*/;
CREATE TABLE ' t ' (

' ID ' int NULL

)
/*!*/;
# at 675
#140917 16:51:50 Server ID 1 end_log_pos 743 Query thread_id=1 exec_time=404 error_code=0
SET timestamp=1410943910/*!*/;
BEGIN
/*!*/;
# at 743
#140917 16:51:50 Server ID 1 end_log_pos 830 Query thread_id=1 exec_time=404 error_code=0
SET timestamp=1410943910/*!*/;
INSERT into T values (3)
/*!*/;
# at 830
#140917 16:51:50 Server ID 1 end_log_pos 857 Xid = 58
commit/*!*/;
# at 857
#140917 16:52:52 Server ID 1 end_log_pos 925 Query thread_id=1 exec_time=342 error_code=0
SET timestamp=1410943972/*!*/;
BEGIN
/*!*/;
# at 925
#140917 16:52:52 Server ID 1 end_log_pos 1012 Query thread_id=1 exec_time=342 error_code=0
SET timestamp=1410943972/*!*/;
INSERT into T values (4)
/*!*/;
# at 1012
#140917 16:52:52 Server ID 1 end_log_pos 1039 Xid = 63
commit/*!*/;
# at 1039
#140917 16:51:50 Server ID 1 end_log_pos 1107 Query thread_id=1 exec_time=440 error_code=0
SET timestamp=1410943910/*!*/;
BEGIN
/*!*/;
# at 1107
#140917 16:51:50 Server ID 1 end_log_pos 1194 Query thread_id=1 exec_time=440 error_code=0
SET timestamp=1410943910/*!*/;
INSERT into T values (3)
/*!*/;
# at 1194
#140917 16:51:50 Server ID 1 end_log_pos 1221 Xid = 89
commit/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET[email protected]_completion_type*/;

You can then try to start the recovery:

In 675 I created a table T;

Now I type:

C:\Program files\mysql\mysql Server 5.5\bin>mysqlbinlog--stop-position=574 d:\l
Og\mylog.000016|mysql-uroot-proot

and then in view;

650) this.width=650; "title=" Untitled. png "src=" http://s3.51cto.com/wyfs02/M02/49/B4/ Wkiom1qzut-tbj57aaipvigtfm4896.jpg "width=" 680 "height=" 698 "alt=" wkiom1qzut-tbj57aaipvigtfm4896.jpg "/>

Found T has been removed. Recovery success;

Attention:

The insert,update,delte,drop,create in each operation is saved in the binary log,

After you insert, you can check the log to see, do not flush LOGS;

Mysqlbinlog Restore Backup Example