Summary of operations on Mysqlbinlog logs including enable, expired auto-delete

Operation Command:

Show Binlog events in ' binlog.000016 ' limit 10;

Reset master Deletes all binary logs

Flush logs generates a new Binlog log file

Show master logs; /show binary logs; View binary file lists and file sizes

./mysqlbinlog--start-datetime= "2012-05-21 15:30:00"--stop-datetime= "2012-05-21 16:40:00"/var/mysql/binlog/ binlog.000005 > A.log

The recent period of time has been in the study of MySQL log system, in the online read n more than MySQL log operation of the article, but are too messy, in order to let themselves no longer forget, special made the following summary:

1. I mistakenly think that the MySQL log can be restored to any time state, in fact, this is not the case, this recovery is a prerequisite, that is, you must have at least one database backup from the beginning of the log, the database is actually just a replay of the previous operation of the process, do not think too complex, Since it is playback you have to pay attention to, if you perform two restores then is equivalent to replay two times, the consequences of how you should be aware of it. [1: Be sure to back up your data before restoring. 2: Because of the large number of binaries and the data span that needs to be recovered, consider merging the log files in recovery .

2. In order to recover the database through the log, in your my.cnf file should have the following definition, Log-bin=mysql-bin, this is necessary. Binlog-do-db=db_test, this is to specify which databases require logs, and if there are multiple databases on each line, If not specified, the default is all databases.
[Mysqld]
Log-bin=mysql-bin
Binlog-do-db=db_test
Binlog-do-db=db_test2

3. Delete the binary log:
a.mysql> system ls-ltr/var/lib/mysql/bintest*;
Mysql>reset Master (empty all binary log files)
B.purge Master logs to ' bintest.000006′;(delete binary log files before bintest.000006)
C.purge master logs before ' 2007-08-10 04:07:00′ (delete logs prior to that date)
D. In the MY.CNF configuration file [mysqld], add:
Expire_logs_day=3 sets the number of days that the log expires, after a specified number of days, is automatically deleted

4. The following is the recovery operation
In particular, MySQL will regenerate a file like mysql-bin.000003 every time you start , and if your MySQL restarts every day, you should be careful not to choose the wrong log file.

(Note: Here are some tips, these things are the most valuable yo, the Common East Handbook has, this is my groping out of yo, others I do not tell him.)

Tip 1:
Below you will see mysqlbinlog–stop-date= "2005-04-20 9:59:59″/var/log/mysql/mysql-bin.000001 | Mysql-u root-pmypwd A similar statement, but it can only manipulate one log file at a time, if you work on it mysqlbinlog–stop-date= "2005-04-20 9:59:59″/var/log/mysql /mysql-bin.0* | Mysql-u Root-pmypwd then it will basically represent all of the log files, so that you can solve the problem of which log file you forget, of course, you may also use this writing more perfect, mysqlbinlog–stop-date= "2005-04-20 9:59:59″/var/log/mysql/mysql-bin.[ 0-9]* | Mysql-u root-pmypwd, see [0-9]* This east, it represents any character beginning with a number, convenient!

Tip 2:
You can recover individual databases selectively by –one-database parameters, example below, cool bar.
Mysqlbinlog–stop-date= "2005-04-20 9:59:59″/var/log/mysql/mysql-bin.000001 | Mysql-u Root-pmypwd–one-database Db_test

Tip 3:
If your ladyship has used/usr/local/mysql5/bin/mysqlbinlog–start-date= "2005-04-20 9:55:00″/var/data/mysql5/mysql-bin.0* >/home/db/tt.sql Similar statements will be translated into ASCII text files, then you can directly in the phpMyAdmin or any other seven bad eight of the client to execute this file file on the line [source *.sql], Because it is a standard SQL file in itself, such as want to let the file inside some statements do not execute, ok,it ' s easy, find them to delete, and then put in the execution on OK drop! This is a very gray often cool yo ...

Tip 4:
Let me tell you what the following statement has done.
Mysqlbinlog–stop-date= "2005-04-20 9:59:59″/var/log/mysql/mysql-bin.000001 | Mysql-u Root-pmypwd–one-database Db_test
This translates the contents of the mysql-bin.000001 binary into an ASCII file (that is, the SQL statement), directly through the pipe operator "|". Transfer to MySQL this program, and then filter out the other database statements, only in the db_test execution.

Tip 5:
, more dozen a skill, now no content, and so on later add it!!!
)

The following excerpt is from the Internet.

If the MySQL server has binary logging enabled, you can use the Mysqlbinlog tool to recover data from a specified point in time (for example, from your last backup) until now or another specified point in time. For information on enabling binary logging, see section 5.11, 3, "binary logs". For more information on Mysqlbinlog, see MySQL Manual section 8.6, "Mysqlbinlog: Utility for working with binary log files".

To recover data from a binary log, you need to know the path and file name of the current binary log file. You can typically find the path from the options file (that is, my.cnf or My.ini, depending on your system). If it is not included in the options file, it can be given as an option on the command line when the server is started. The option to enable binary logging is –log-bin. To determine the file name of the current binary log file, enter the following MySQL statement:

SHOW BINLOG EVENTS G

You can also enter the following from the command line:

Mysql–user=root-pmy_pwd- e ' SHOW BINLOG EVENTS G '

Replace the password my_pwd with the root password of the server.

1. Specify the recovery time

For MySQL 4.1.4, you can specify the start and end time of the DateTime format in the Mysqlbinlog statement through the –start-date and –stop-date options. For example, suppose that today 10:00 (today is April 20, 2005), execute the SQL statement to delete a large table. To restore the table and data, you can restore the backup of the previous night and enter:

Mysqlbinlog–stop-date= "2005-04-20 9:59:59″/var/log/mysql/mysql-bin.000001 | Mysql-u root-pmypwd

This command restores all data as of the date and time given in datetime format in the –stop-date option. If you do not detect a few hours after entering the wrong SQL statement, you may want to restore the activity that occurs later. Based on these, you can use the date and time to run Mysqlbinlog again:

Mysqlbinlog–start-date= "2005-04-20 10:01:00″/var/log/mysql/mysql-bin.000001 | Mysql-u root-pmypwd

In that row, the SQL statement that is logged in from 10:01 runs. Combined execution of the previous night's dump file and two lines of Mysqlbinlog can restore all data to one second before 10:00. You should check the logs to make sure the time is correct. The next section describes how to implement it.

2. Specify the recovery location

You can also specify a log location without specifying a date and time, and using the Mysqlbinlog options –start-position and –stop-position. They function the same as the start and End Date option, and the difference is that the location number from the log is given. Using the log location is a more accurate method of recovery, especially when many transactions occur simultaneously due to destructive SQL statements. To determine the location number, you can run Mysqlbinlog to find the time range for the transaction that you did not expect, but you should re-point the result to a text file for review. How to do this:

Mysqlbinlog–start-date= "2005-04-20 9:55:00″–stop-date=" 2005-04-20 10:05:00″
/var/log/mysql/mysql-bin.000001 >/tmp/mysql_restore.sql

The command will create a small text file in the/tmp directory, which will display the SQL statement when the wrong SQL statement was executed. You can open the file with a text editor and look for statements that you don't want to repeat. If the location number in the binary log is used to stop and resume the recovery operation, comments should be made. Use Log_pos plus a number to mark the position. After recovering the previous backup file using the location number, you should enter the following from the command line:

mysqlbinlog–stop-position= "368312″/var/log/mysql/mysql-bin.000001 | Mysql-u root-pmypwd
mysqlbinlog–start-position= "368315″/var/log/mysql/mysql-bin.000001 | Mysql-u root-pmypwd

The 1th row above reverts to all transactions until the stop location. The next line restores all transactions from the given starting position until the end of the binary log. Because the output of Mysqlbinlog includes a set TIMESTAMP statement before each SQL statement is recorded, the recovered data and the associated MySQL log will react to the original time of the transaction execution.

Transferred from: http://blog.csdn.net/bopzhou/article/details/8330610

Summary of operations on Mysqlbinlog logs including enable, expired auto-delete