Because not the entire library to restore, so first by mysqlbinlog binlog file for conversion, conversion of the SQL statement for the operation of the whole library, and then to the operation of the need to restore table statement is extracted, is at the time of extraction have problems, don't know how to extract, consequences teach it to me?
My steps are as follows:
1. The binary log into SQL file, modify the data of time determine roughly before now, so now the data of the previous operation is derived;
/usr/local/mysql/bin/test1 mysqlbinlog - d - base64 - output=decode - rows - v - stop - a datetime='2017-02-23 11:55:00 mysql_bin. 000001 & gt; SQL
test1.
Put up a key part of the SQL content:
# at 319
# 170217 10:28:23 server id 1 end_log_pos 392 CRC32 0 x48479b8f Query thread_id=4 exec_time=0 error_code=0
SET the TIMESTAMP=1487298503/*! */.
The BEGIN
/*! */.
# at 392
# 170217 10:28:23 server id 1 end_log_pos 445 CRC32 0 xdb09ce03 Table_map: ` test1 `. ` account ` mapped to number 70
# at 445
# 170217 10:28:23 server id 1 end_log_pos 547 CRC32 0 xf2e41ab9 Update_rows: table id 70 flags: STMT_END_F
# # # UPDATE ` test1 `. ` account `
# # # WHERE
# # # @ 1='zhangsan'
# # # SET
# # # @ 1='ceshi'
# # # UPDATE ` test1 `. ` account `
# # # WHERE
# # # @ 1='lisi'
# # # SET
# # # @ 1='ceshi'
# # # UPDATE ` test1 `. ` account `
# # # WHERE
# # # @ 1='wanger'
# # # SET
# # # @ 1='ceshi'
# # # UPDATE ` test1 `. ` account `
# # # WHERE
# # # @ 1='mazi'
# # # SET
# # # @ 1='ceshi'
# at 547
# 170217 10:28:23 server id 1 end_log_pos 578 CRC32 0 x5ee48b32 Xid=22
COMMIT/*! */.
DELIMITER.
# End of the log file
The ROLLBACK/* added by mysqlbinlog */.
2. Select the mistake operation table statement: (look at the others online is so operations can be filtered out, I don't know this screening is not why)
More test1. SQL | grep, ignore - case - E 'update' - A2 - B2 | grep account
I selected the result is:
[root @ redis - slave data] # more test1. SQL | grep, ignore - case - E 'update' - A2 - B2 | grep account
# 170217 10:28:23 server id 1 end_log_pos 445 CRC32 0 xdb09ce03 Table_map: ` test1 `. ` account ` mapped to number 70
# # # UPDATE ` test1 `. ` account `
# # # UPDATE ` test1 `. ` account `
# # # UPDATE ` test1 `. ` account `
# # # UPDATE ` test1 `. ` account `
This result is clearly not someone tell why?
If screening out wrong operation table statement, you can
More test1. SQL | grep, ignore - case - E 'update' - A2 - B2 | grep account & gt; SQL
the update.And then perform the update. SQL is wrong operation table can be restored
CodePudding user response:
[root @ c65muban data] # cat 1# # # UPDATE ` test1 `. ` account `
# # # WHERE
# # # @ 1='zhangsan'
# # # SET
# # # @ 1='ceshi'
[root @ c65muban data] # sed 's/# # #//g; S/SET/limit 1;/g; S/@ 1='"' "ceshi" '"'//g; S/WHERE/SET/g; S/@/name/g '1
The UPDATE ` test1 `. ` account `
SET
Name='zhangsan'
Limit 1;
[root @ c65muban data] #
CodePudding user response:
Online the grep a to b is wrong, you can't guarantee the complete SQL statements in lines, before and after the match the I once wrote a PHP script, you can try, there are also some case processing, such as dependence on database on the set insertid,& lt; ? PHP
$sql_file="out. SQL";//a complete SQL file
$tab_name="mod_son";//to extract the table
$SQL=new SplFileObject ($sql_file);
$log=false;
SQL as foreach ($$line) {//read line by line
If (preg_match ("/^ (SET TIMESTAMP=)/", $line)) {//current line matches the
$timestamp=$line;
}
If (preg_match ("/(create | delete | replace | insert | alter | update) ([A - Z_ \ - \ \ s \ `] +) {$tab_name}/I ", $line)) {//current line matches the
File_put_contents ($tab_name. "_out. SQL", $timestamp, FILE_APPEND to);//timestamp
$log=true;
}
If ($log) {
File_put_contents ($tab_name _out. SQL "", $line, FILE_APPEND to);
}
If ($log & amp; & Preg_match ("/\ \/\ *! \ \ */;/", $line)) {
$log=false;
}
}
Echo "export finished! \ n ";
CodePudding user response:
To write according to the circumstance, it is not that simple, such as more number, names of replacement and so onCodePudding user response:
Binlog2sql https://github.com/danfengcao/binlog2sqlCodePudding user response:
The inside of the https://github.com/danfengcao/binlog2sql screening specify log table as useless,CodePudding user response:
- base64 - output=decode - rows exported can't use this one instead, directly using the original binary format