Mysql Database recovery (using the Mysqlbinlog command) _mysql

1: Open Binlog log record
Modify the MySQL configuration file Mysql.ini, add under [mysqld] Node

Copy Code code as follows:

# Log-bin
Log-bin = E:/log/logbin.log

Do not include Chinese and white spaces in the path. Restart the MySQL service. Stop and start the MySQL service from the command line

Copy Code code as follows:

C:\>net stop MySQL;
c:\>net start MySQL;

Go to the command line to go to MySQL and see if the binary log has started
SQL code

Copy Code code as follows:

Mysql>show variables like ' log_% ';

After the log is successfully opened, the Logbin.index and logbin.000001 two files are created in the e:/log/directory. LOGBIN.000001 is the backup file of the database, which can then be restored to the database through this file.

2: View the binary files of the backup
SQL code

Copy Code code as follows:

C:\mysql\bin\>mysqlbinlog e:/log/logbin.000001

There are many more operations to be recorded in the future, and the command line is basically out of the way. You can view log content by using the way you export the log
2.1 Export
XML code

Copy Code code as follows:

C:\mysql\bin\>mysqlbinlog e:/log/logbin.000001 > E:/log/log.txt

">": Import to file; ' >> ': Append to File
If you have more than one log file
SQL code

Copy Code code as follows:

c:\mysql\bin\> Mysqlbinlog e:/log/logbin.000001 > E:/log/log.sql
c:\mysql\bin\> mysqlbinlog e:/log/logbin.000002 >> e:/log/log.sq

2.2 Export by specified location:
SQL code

Copy Code code as follows:

C:\mysql\bin\>mysqlbinlog--start-position=185--stop-position=338 e:/log/logbin.000001 > E:/log/log3.txt

2.3 Export at specified time:
XML code

Copy Code code as follows:

C:\mysql\bin\>mysqlbinlog--start-datetime= "2010-01-07 11:25:56"--stop-datetime= "2010-01-07 13:23:50" e:/log/ logbin.000001 > E:/log/log_by_date22.txt

3: Restore the database from backup
An update is done, and the contents of the log are as follows:
SQL code

Copy Code code as follows:

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 4
#110107 13:23:50 Server ID 1 end_log_pos start:binlog v 4, Server v 5.1.53-community-log created 110107 13:23:50 at s Tartup
# Warning:this Binlog is either in-use or being not closed properly.
rollback/*!*/;
Binlog '
Zqmmtq8baaaazgaaagoaaaabaaqans4xljuzlwnvbw11bml0es1sb2caaaaaaaaaaaaaaaaaaaaa
Aaaaaaaaaaaaaaaaaabmoyznezgnaagaegaebaqeegaauwaegggaaaaicagc
'/*!*/;
# at 106
#110107 13:26:58 Server ID 1 end_log_pos 185 Query thread_id=44 exec_time=1 error_code=0
SET timestamp=1294378018/*!*/;
SET @ @session. pseudo_thread_id=44/*!*/;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @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 185
#110107 13:26:58 Server ID 1 end_log_pos 338 Query thread_id=44 exec_time=1 error_code=0
Use ncl-interactive/*!*/;
SET timestamp=1294378018/*!*/;
UPDATE ' t_system_id ' SET ' id_value ' = ' 3000 ' WHERE (' table_name ' = ' t_working_day ')
/*!*/;
# at 338
#110107 13:26:58 Server ID 1 end_log_pos 365 Xid = 8016
commit/*!*/;
DELIMITER;
DELIMITER/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog * *;
/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 4
#110107 13:23:50 Server ID 1 end_log_pos start:binlog v 4, Server v 5.1.53-community-log created 110107 13:23:50 at s Tartup
# Warning:this Binlog is either in-use or being not closed properly.
rollback/*!*/;
Binlog '
Zqmmtq8baaaazgaaagoaaaabaaqans4xljuzlwnvbw11bml0es1sb2caaaaaaaaaaaaaaaaaaaaa
Aaaaaaaaaaaaaaaaaabmoyznezgnaagaegaebaqeegaauwaegggaaaaicagc
'/*!*/;
# at 106
#110107 13:26:58 Server ID 1 end_log_pos 185 Query thread_id=44 exec_time=1 error_code=0
SET timestamp=1294378018/*!*/;
SET @ @session. pseudo_thread_id=44/*!*/;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @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 185
#110107 13:26:58 Server ID 1 end_log_pos 338 Query thread_id=44 exec_time=1 error_code=0
Use ncl-interactive/*!*/;
SET timestamp=1294378018/*!*/;
UPDATE ' t_system_id ' SET ' id_value ' = ' 3000 ' WHERE (' table_name ' = ' t_working_day ')
/*!*/;
# at 338
#110107 13:26:58 Server ID 1 end_log_pos 365 Xid = 8016
commit/*!*/;
DELIMITER;
DELIMITER/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog * *;
/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;

3.1 Recovery:
SQL code

Copy Code code as follows:

C:\mysql\bin\>mysqlbinlog e:/log/logbin.000001 | Mysql-u root-p

3.2 Restore at specified location:
SQL code

Copy Code code as follows:

C:\mysql\bin\>mysqlbinlog--start-position=185--stop-position=338 e:/log/logbin.000001 | Mysql-u root-p

3.3 Restore at specified time:
XML code

Copy Code code as follows:

C:\mysql\bin\>mysqlbinlog--start-datetime= "2010-01-07 11:25:56"--stop-datetime= "2010-01-07 13:23:50" e:/log/ logbin.000001 | Mysql-u root-p

3.4 Recovering from exported script files
SQL code

Copy Code code as follows:

C:\mysql\bin\>mysql-e "Source E:/log/log.sql"

4. Other common operations
4.1 View all log files

SQL code

Copy Code code as follows:

Mysql>show master logs;

4.2 Binlog files currently in use
SQL code

Copy Code code as follows:

Mysql>show Binlog Events \g;

4.3 Generate a new binlog log file
SQL code

Copy Code code as follows:

Mysql>flush logs;

4.4 Delete all binary logs and start recording from scratch (note: The Reset Master command deletes all binary logs)
SQL code

Copy Code code as follows:

MySQL > Flush logs;
mysql > Reset master;

4.5 Fast backup data to SQL files

SQL code

Copy Code code as follows:

C:\mysql\bin>mysqldump-u root-p--opt--quick Interactive > E:/log/mysqldump.sql

For easy viewing, write the command to restore from the script once
SQL code

Copy Code code as follows:

C:\mysql\bin\>mysql-e "Source E:/log/mysqldump.sql"