Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 360

mysqlbinlog --database= rookery \
/data/mysql/mysqlresources-bin.000002 >
recovery-research.txt
Here you can see that I've included the --database option to instruct mysqlbinlog to
extract only transactions for the rookery database. If we didn't do this, we would get
transactions for other databases. On this particular server, there are over two dozen data-
bases, some of them large and very active. To make restoration simpler and avoid over-
writing data in other databases, it's best to limit the results to only what is needed.
Next, we specify the path and name of the binary file. This is followed by a redirect to
have the system write the results from mysqlbinlog to a text file ( recovery-research.txt ).
Extracting and executing information from the binary log
When mysqlbinlog has finished creating a text file for us, we'll open the file with a simple
text editor and search for the DELETE statements.Because we know that there were only
two DELETE statements that occurred together, this will be easy to fix. Here's an excerpt
from the output of the binary log showing these two transactions:
# at 1258707
#140916 13:10:24 server id 1 end_log_pos 1258778
Query thread_id=382 exec_time=0 error_code=0
SET TIMESTAMP=1410887424/*!*/;
SET @@session.sql_mode=0/*!*/;
BEGIN
/*!*/;
# at 1258778
#140916 13:10:24 server id 1 end_log_pos 1258900
Query thread_id=382 exec_time=0 error_code=0
use `rookery`/*!*/;
SET TIMESTAMP=1410887424/*!*/;
DELETE FROM birds_simple WHERE common_name LIKE '%Blue%'
/*!*/;
# at 1258900
#140916 13:10:24 server id 1 end_log_pos 1258927 Xid = 45248
COMMIT/*!*/;
...