MySQL in bin-log use

Action command: Show Binlog eventsinch 'mysql-bin.000016'LimitTenReset Master Deletes all the binary logs flush logs produces a new Binlog log file show master logs; or showbinarylogs; View binary file list and file size./Mysqlbinlog--start-datetime= "2012-05-21 15:30:00"--stop-datetime= "2012-05-21 16:40:00"/binlog/mysql-bin.000005 > A.log 2. To recover the database through the log, you should have the following definition in your my.cnf file.Log-Bin=Mysql-bin, this is a must binlog-Do-Db=Db_test, this is to specify which databases require logs, one per line, and default is all databases if not specified.[mysqld] Log-Bin=Mysql-bin Binlog-Do-Db=db_test Binlog-Do-Db=Db_test23. Delete binary log: MySQL>Reset Master (empties all binary log files) purge Master logs to 'mysql-bin.000006'; (Delete MySQL-bin.000006 binary log files before purge master logs before'2007-08-10 04:07:00'(Delete the log before the date) in the MY.CNF configuration file[mysqld]added in: Expire_logs_day=3sets the number of days that the journal expires, and is automatically deleted after a specified number of days4The following is a special tip for recovery, which will regenerate a mysql-like one every time it starts-Bin.000003files, if your MySQL has to be restarted every day, this time you should pay special attention not to choose the wrong log file. Tip 1: You can pass –one-Databaseparameter selective recovery of a single database, example in the bottom, cool bar. Mysqlbinlog–stop-Date="2005-Geneva- - 9: -: -"/var/Log/Mysql/Mysql-Bin.000001  |Mysql-U root-Pmypwd–one-Databasedb_test Tip 2: If you've already used/Usr/Local/MySQL5/Bin/Mysqlbinlog–start-Date="005-Geneva- - 9: -:xx"/var/Data/MySQL5/Mysql-Bin.0* > /Home/Db/Tt.sql Similar statements will be translated into ASCII text file, then you can directly execute the file in the phpMyAdmin, because it is a standard SQL file, such as want to make some statements inside the file does not execute, find them to delete, and then put in the execution. Skill 3:mysqlbinlog–stop-Date="2005-Geneva- - 9: -: -"/var/Log/Mysql/Mysql-Bin.000001  |Mysql-U root-Pmypwd–one-DatabaseDb_test This is putting MySQL-bin.000001 the contents of this binary file into an ASCII file (i.e. 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. To determine the file name of the current binary log file, enter the following MySQL statement: SHOW BINLOG EVENTS1. Specify the recovery time for MySQL4.1.4, you can pass –start in the Mysqlbinlog statement-Date and –stop-The date option specifies the starting and ending time of the DateTime format. For example, suppose in this morning 10:xx(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-Geneva- - 9: -: -"/var/Log/Mysql/Mysql-Bin.000001  |Mysql-U root-pmypwd The command will revert to the –stop-all data for the date and time given in the DateTime format in the 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 the Mysqlbinlog:mysqlbinlog–start again-Date="2005-Geneva- - Ten: on:xx"/var/Log/Mysql/Mysql-Bin.000001  |Mysql-U root-pmypwd2You can also use the Mysqlbinlog option to specify a recovery location –start-Position and –stop-position to specify the log location. 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-Geneva- -"–stop-Date="2005-Geneva- -"/var/Log/Mysql/Mysql-Bin.000001 > /Tmp/mysql_restore.sql The command will be/The tmp directory creates a small text file that displays the SQL statement when the wrong SQL statement is 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-Pmypwdmysqlbinlog–start-Position="368315"/var/Log/Mysql/Mysql-Bin.000001 |Mysql-U root-Pmypwd The 1th row above will revert 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. 

MySQL in bin-log use