Mysql5.6 bin-log data recovery

After the project is launched in the first phase, the upgrade script is required in the later stages. You cannot directly import the entire SQL statement. So enable mysql bin-log and record all executed SQL statements to bin-log.
My system is windows and mysql is community version 5.6.

1. bin-log Directory

After mysql5.6 is installed, its Data files and logs are stored in the C: \ Documentsand Settings \ All Users \ Application data \ MySQL Server 5.6 \ Data directory.

2. Check whether bin-log is enabled.

Show variables like 'log _ bin'

3. Enable bin-log

Mysql configuration file my. ini, directory C: \ Documentsand Settings \ All Users \ Application Data \ MySQL Server 5.6. Add log-bin = "log_bin". The file name of bin-log is log_bin.00000x.
As shown in the following figure, the bin-log of myql is enabled. mysql generates the log_bin.00000x file in the data directory.

 

4. Convert bin-log to txt text

Bin-log is a binary file. You can use the mysqlbinlog command to convert it to text format.
Mysqlbinlog log_bin.000001> log_bin.txt

5. Generate a new log file

Flush logs

6. bin-log recovery

Restore the entire log_bin.000001 data

Mysqlbinlog log_bin.000001

Bin-log restore to a certain point

Now we want to restore a point to a certain point from log_bin.000001 (bin-log file:
Mysqlbinlog -- start-position 325 -- stop-position 567 log_bin.000001

Parameters:

-- Start-position start point
-- Stop-position end point
-- Start-date start time
-- Stop-date end time

Log_bin.000001 content:

/*! */;
# At 325
#130929 16:30:46 server id 1 end_log_pos 490 CRC32 0x9501d4ba Query thread_id = 16 exec_time = 0 error_code = 0
Set timestamp = 1380443446 /*! */;
Alter table 'sdghdb'. 'Test' add column 'name' VARCHAR (45) NULL comment' name 'after 'Info'
/*! */;
# At 490
#130929 16:31:22 server id 1 end_log_pos 567 CRC32 0xefc463e6 Query thread_id = 17 exec_time = 0 error_code = 0
Set timestamp = 1380443482 /*! */;
BEGIN
/*! */;
# At 567
#130929 16:31:22 server id 1 end_log_pos 706 CRC32 0xfa48b5d3 Query thread_id = 17 exec_time = 0 error_code = 0
Set timestamp = 1380443482 /*! */;
Insert into 'sdghdb'. 'Test' ('info', 'name') VALUES ('AAA', 'bbbbb ')
/*! */;