Database Reference
In-Depth Information
a very active database. Therefore, if you intend to use
mysqldump
in conjunction with
mysqlbinlog
, you should have
mysqldump
flush the logs when it performs the backup. I
did this when I created the
birds-simple.sql
dump file by including the
--flush-logs
option. So now we need to restore data from the beginning of the current log file to the
point at which the
DELETE
statements were run. We can determine that point in time
from the binary logs.
We'll use the
mysqlbinlog
utility to extract all of the transactions from the current binary
log and save them to a text file. We'll then examine that text file to find the exact point in
which the erroneous SQL statements were run.
Finding information in the binary log
To get the information, we need to know the name of the binary log file that contains these
SQL statements, as well as where to find that log file. We'll runthe
SHOW MASTER
STATUS
to get the filename. Its location will be the data directory, which we can determ-
ine by executing the
SHOW VARIABLES
statement. Enter both of those as you see here:
SHOW MASTER STATUS;
+---------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB |
+---------------------------+----------+--------------+------------------+
| mysqlresources-bin.000002 | 7388360 | |
mysql |
+---------------------------+----------+--------------+------------------+
SHOW VARIABLES WHERE Variable_Name LIKE 'datadir';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| datadir | /data/mysql/ |
+---------------+--------------+
The results from the first SQL statement show the name of the current binary log file (i.e.,
mysqlresources-bin.000002
). The name changed since we last checked our server because
mysqldump
flushed the logs when the dump file was made. The results of the second SQL
statement in the previous listing shows that the data directory is
/data/mysql/
. Check the
contents of that directory to make sure that
mysqlresources-bin.000002
is there. Assuming
it is there, we're now ready to extract the transactions we need from the binary log.Enter
the following from the command line:
