Database Reference
In-Depth Information
WARNING
Enabling the binary log does add a security vulnerability. All of the SQL statements executed on the
server that modify the data will be recorded in the binary log. This may include sensitive information
(e.g., credit card numbers, if your server records them) and passwords. So be sure that you protect the
log files and the directory where they are stored, and preferably don't log changes to the
mysql
table.
That's where passwords for user accounts are stored, so it's good not to log it. Use the
--binlog-
ignore-db
option to omit databases from the log.
To enable binary logs, edit the configuration file for MySQL (
my.cnf
or
my.ini
, depending
on your system). In the
[mysqld]
section, add the following lines:
log
-
bin
binlog
-
ignore
-
db
=
mysql
The
log-bin
option requires no equals sign or value. The second line here tells MySQL
to ignore any changes to the
mysql
database. When you've added these entries to the
configuration file, restart MySQL for it to take effect. Once that's done, log into MySQL
and check again whether binary logs are enabled. This time, we'll usethe
SHOW MASTER
STATUS
statement:
SHOW MASTER STATUS;
+---------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB |
+---------------------------+----------+--------------+------------------+
| mysqlresources-bin.000001 | 245 | |
mysql |
+---------------------------+----------+--------------+------------------+
Here you can see the name of the current binary log file and verify that it's ignoring
changes to the
mysql
table.
Now that MySQL is recording all of the SQL statements in the binary log, point-in-time
recovery is possible. To be able to experiment with this, log into MySQL and insert many
rows of data into a table. To make this easier, you may download two dump files from the
MySQL Resources site
called
birds-simple.sql
and
birds-simple-transactions.sql
. The
birds-simple.sql
dump file will add the
birds_simple
table with data to
rookery
.
The
birds-simple-transactions.sql
file will insert many rows of data in
birds_simple
,
change several rows with a single SQL statement — simulating an accident — and then
insert more rows. For the example that follows, we will restore everything up until the of-
fending SQL statement and all transactions after it — skipping the bad statements. To par-
