[Favorite] mysql binary log file to restore the database

2011-10-23  来源:本站原创  分类:Database  人气:105 

Like directly on the server or database operations brothers under your worth collecting! Otherwise you "one hundred tear shall not ride jie" (second line you know!)

-----( Course, I also search for information online! Their next test!)

The role of the binary log file

mysql binary log file to record all users of database operations, database operations that records the user's sql statement. If you have this file, when the accident database, you can see through this file to the user documentation of the period of time in this operation made by the user, and database backup and then used in conjunction, can reproduce the user, the database recovery.

The drawbacks of the binary log file

Binary log file is opened, all the records of database operations will be recorded into this file,

So, when a long time after the opening, the log files become very large, taking up disk space.

To restore the database using the binary log file

Open the log file

mysql default is not open log file function, we need to manually open. As follows:

1, open the mysql installation directory \ my.ini (windows system is the my.ini file, linux is the my.cnf file).

2, find the [mysqld] tag, the following line in this tab, add the statement: log-bin = binary-log. The above statement, log-bin description to open the binary log file, binary-log is a binary log file name.

(Test when I set: log-bin = d: / mysql_log / logbin_ouyang.log)

3, restart the mysql service. Installation directory in the mysql \ data folder to see "binary-log. Figures" file,

Such as binary-log.000001. After each restart a mysql service will re-generate the binary log file,

Filename numbered in ascending order.

(Your file name is given on the step, added a file number and an overall index file as shown in Figure 1:)

[Favorite] mysql binary log file to restore the database

Restore the database

Use the log file to restore the database must have a database backup (as long as enabled in the binary log file after the backup on the line). Restore the database, you can recover through time can also be restored by operating point.

1, by the time recovery are as follows:

At 2011-04-02 00:00:00 If I open a binary log file time function, and back up the database files. 2011-04-02 12:00:00 in the database and an exception occurs, the need to restore to 2011-04-02 10:00:00. Is as follows:

l back to the database with the backup time 2011-04-02 00:00:00 status.

l at the command line type the following command:

mysqlbinlog - stop-date = "The time you want to restore" the log file directory \ binary-log.000001 | mysql-u root-p

(D: \ mysql_log> mysqlbinlog logbin_ouyang.000007 - stop-date = "2011-10-23 15:05:00" | mysql-uroot-proot)

In this way, the system will automatically start from the binary log record as of 2011-04-02 10:00:00, users have done all operations. Correspond with the stop-date, as well as start-date attribute can be set to perform recording start time. Both properties can be set.

2, through the operating point recovery method is as follows:

In the command line, type mysqlbinlog D: \ binary-log.000003> D: \ log.txt, after the implementation of open log.txt,

See which statements can be found: Before each operation, there will be a unique number, as shown in red:


# At 450 / * Number 450 * /

# 110402 15:31:50 server id 1 end_log_pos 529 Query thread_id = 2 exec_time = 0 error_code = 0

SET TIMESTAMP = 1301729510 /*!*/;


This number increased with the larger operand. And time as a mark, to restore the operating point by the following statement:

mysqlbinlog - stop-position = "450" ​​mysql installation directory \ data \ binary-log.000001 | mysql-u root-p

In this way, the system will automatically execute the binary log statements to the lowest numbered statement number 450.

Correspond with the stop-position, as well as start-position attribute, you can set the start of the implementation record number.

Attachment: (you should see)

1.0 mysqlbinlog is a logging operation that comes with mysql tool!

2.0 If you directly open the log file may be garbled, so we generally will:

D: \ mysql_log> mysqlbinlog logbin_ouyang.000003> log_3.txt copy down and then use Notepad to open!

You can clearly see the number of operations or (line number)

3.0 If an error occurs the operation! If you can! You should immediately stop the database! Then restore the data!

4.0 If you just remove a single statement, you can choose to recover twice, as shown:

[Favorite] mysql binary log file to restore the database

D: \ mysql_log> mysqlbinlog logbin_ouyang.000009 - stop-position = 10625 | mysql-uroot


D: \ mysql_log> mysqlbinlog logbin_ouyang.000009 - start-position = 10843 | mysql-uroo


5.0 If Could not read entry at offset 1478: Error in log format or read error. Similar mistakes!

Please check your numbers are correct! Note that this number is not certain that the editor displays the line number Oh, # at 450 /

Often delete, update wrong! All to yourself a document! Of course I also hope you never do not have access this document! Weekend! (Layout and some random Ha! Forgive me!)

  • [Favorite] mysql binary log file to restore the database 2011-10-23

    Like directly on the server or database operations brothers under your worth collecting! Otherwise you "one hundred tear shall not ride jie" (second line you know!) -----( Course, I also search for information online! Their next test!) The role

  • binlog mysql binary log file cleanup 2011-07-03

    mysql> PURGE MASTER LOGS BEFORE DATE_SUB (NOW (), INTERVAL 30 DAY); (Clean-up 30 days before the binary log file, if the main library for 30 days before the error log file) Modify the my.cnf file [Mysqld] expire_logs_day = 20; Restart the database my

  • . Bak file to restore the database 2010-09-19

    . Bak file to restore the database SQL statement: restore database data name from disk = 'd: \ your backup file name'

  • oracle log file is missing (ORA-O1109: database not open) approach (reproduced) 2011-01-20

    This is my website to find the solution in the other missing log files oracle approach, to help me solve the problem of lost logs. So specifically in turn want to help everyone. Original: http://blogold.chinaunix.net/u1/40226/showart_1968470.html ora

  • mysql database log file 2011-09-23

    Study reference site http://www.miiceic.org.cn/database/ Overview of the log file type: 1. Error logging start, stop mysqld when running or problems. My.ini configuration information: # Enter a name for the error log file. Otherwise a default name wi

  • MySQL uses log files to recover data 2010-09-27

    MySQL binary log file may not make data backup of the database of last resort bar. Met today, yesterday, just in the project have not been able to do regular backups of data on a problem. Only the use of log files (fortunately my logging function to

  • MySQL database under Linux binary log recovery methods 2010-07-08

    MySQL database under Linux binary log recovery methods September 21, 2009 04:04 pm Monday, if the MySQL server binary log is enabled, you can use mysqlbinlog tool to recover starting from the specified point in time (for example, from your last backu

  • MySQL log file (change) 2010-07-22

    5.11. MySQL log files 5.11.1. Error log 5.11.2. General Query Log 5.11.3. Binary log 5.11.4. Slow query log 5.11.5. Log file maintenance MySQL has several different log files that can help you find out what happened inside mysqld: Log files Credited

  • MySQL Log File Analysis 2010-09-16

    Original Source: http://www.mysqlsystems.com/2009/05/mysql-log.html Log file (log) is a track record of the list, it can help us grasp the system and application service time action status, time in troubleshooting to provide the most detailed and acc

  • mysql-bin.00000 * A similar document is to delete the log file mysql 2011-05-06

    First note, mysql-bin.00000 * files are similar to mysql's log file. Many people install the mysql log file is opened, and generally have more frequent mysql database operations will generate a lot of log files in / usr / local / mysql / var / below

  • mysql binary incremental backups 2010-05-05

    mysqlbinlog -u root -p*** v:/dbbackup/db-bin-log.000002 > v:/dbbackup/db-bin-log.000002.sql In the MySQL configuration file, My.ini or add my.cnf log-bin configuration. log-bin="v:/dbbackup/db-bin-log" #binlog-do-db=#log-slow-queries=#max_bin

  • MySQL binary logs on the understanding Binlog 2010-10-28

    MySQL, MySQL binary log can be said that the most important or log, it records all the DDL and DML (in addition to the data query) statement, to form an event record also contains statements by the execution time consumed, MySQL binary logs is error-

  • sqlserver log file is too large a solution <switch> 2010-05-07

    SQL Server's transaction log grows unexpectedly or becomes full approach Transaction log file Transaction Log File is used to record updates the database file extension ldf. In SQL Server 7.0 and SQL Server 2000, if the automatic growth feature set,

  • SQL Server 2005 database without log file attached 2011-05-12

    Work, sometimes encountered, the log file super in reloading the system, in order to save, there is no backup log file, and put the log files is not isolated to cut out (next time remember to be separated again delete the log files). Result, how are

  • oracle archive log file archieve log 2010-12-16

    Archive log files: the inactive (INACTIVE) state of the redo log file backups. It ORACLE database backup and recovery play a vital role. Online redo log file is a copy, complete with the members being copied, as that is the same redo log, the log seq

  • Mssql log file is full of quick fix caused the problem can not write 2010-04-22

    Resulting in the log file full of SQL database can not write to file, we can use the following two methods to resolve this problem: (suggest you use the first method) The first method: Clear the log. ◆ 1. Open Query Analyzer, enter the command DUMP T

  • The database log is full, backup the database transaction log to free up some log space solutions 2010-12-22

    Database 'yoursitedb' log is full. Back up the database transaction log to free up some log space. Description: During the execution of the current Web request, an unhandled exception. Please review the stack trace information, to learn more about th

  • MySQL binary logs to restore data 2010-02-23

    Reprinted from: http://blog.csdn.net/liangCK/archive/2009/04/27/4129920.aspx http://blog.chinaunix.net/u1/54130/showart_1912663.html Good stuff, learning the next, to be a record. mysqlbinlog the use of tools, we can look at MySQL's help manual. Whic

  • mysql log file shows 2010-05-01

    1. Error Log Error Log Mysql server error log recorded during the operation of all the more serious warning and error messages, and every time you start and shut down mysql details. Open the way when you start mysql with - log-error option. The defau

  • MySQL Log Files - Mysql log file 2010-07-22

    4.9 4.9 The MySQL Log Files mysql to provide several different functions of the log file, so you know what happened mysqld. 1. The error log: records all mysqld start, implementation and end when the error message. 2. The isam log: changes ISAM table