Mastering MySQL 4 [Electronic resources] نسخه متنی

Using the Binary Update Log to Restore a Database

to the Most Recent Position

The binary update log is an ideal way to restore your database to a point as close as possible to when a crash happened (see Chapter 10, "Basic Administration"). The binary update log logs all changes made to your database. The binary update log is enabled when MySQL is started with the

--log-bin option. You can specify a name with --log-bin = filename; otherwise the default name will be the name of the server machine, with -bin appended. A new log file is created every time the server is restarted, the logs flushed, the server refreshed, or the maximum size (set in max_bin_log_size) is reached.

After you've made a backup with mysqldump, restart MySQL with the --log-bin option. When the time comes to restore, restore the mysqldump file, and then use the binary log files to return the database to its most recent status.

For example, let's assume that the last backup was from customer.dat, which restores it to the

10 records shown here:

mysql> SELECT * FROM customer;
+----+------------+-------------+---------+
| id | first_name | surname     | initial |
+----+------------+-------------+---------+
|  1 | Yvonne     | Clegg       | X       |
|  2 | Johnny     | Chaka-Chaka | B       |
|  3 | Winston    | Powers      | M       |
|  4 | Patricia   | Mankunku    | C       |
|  5 | Francois   | Papo        | P       |
|  7 | Winnie     | Dlamini     | NULL    |
|  6 | Neil       | Beneke      | NULL    |
| 10 | Breyton    | Tshabalala  | B       |
+----+------------+-------------+---------+
8 rows in set (0.00 sec)

Once you're at this stage (having just made the backup), start the server with binary logging enabled if you haven't already:

C:\MySQL\bin> mysqladmin shutdown
020601 23:59:01  mysqld ended

If it's not there already, place the following option inside your my.cnf or my.ini file to enable binary logging:

log-bin

Now restart the server:

C:\MySQL\bin> mysqld-max
020602 18:58:21  InnoDB: Started
C:\MySQL\bin> mysql firstdb;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to
 server version: 4.0.1-alpha-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> INSERT INTO customer VALUES(11,'Robin','McKenzie',NULL);
Query OK, 1 row affected (0.00 sec)

Now let's simulate a crash by stopping the server and deleting the customer data and index files:

mysql> exit
Bye
C:\MySQL\bin> del c:\MySQL\data\firstdb\customer.*

Depending on your setup, you may not have permission to remove the files until you shut the server down or change to root.

If you delete the files, and still have a connection active, and then try to perform a query on the customer table, you may still get results, as the results may be cached. But when you shut the server down and restart, you will not be able to find any customer data:

C:\MySQL\bin> mysqladmin shutdown
020601 23:59:01  mysqld ended
C:\MySQL\bin> mysqld-max
020602 18:58:21  InnoDB: Started
C:\MySQL\bin>  mysql firstdb;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.1-alpha-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM customer;
ERROR 1146: Table 'firstdb.customer' doesn't exist
mysql> exit
Bye

Now restore the backup made earlier:

C:\MySQL\bin> copy c:\db_backups\customer.* c:\MySQL\data\firstdb

Doing a query, you see you have lost the most recent record, which was added after the backup:

C:\MySQL\bin> mysql firstdb;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.1-alpha-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM customer;
+----+------------+-------------+---------+
| id | first_name | surname     | initial |
+----+------------+-------------+---------+
|  1 | Yvonne     | Clegg       | X       |
|  2 | Johnny     | Chaka-Chaka | B       |
|  3 | Winston    | Powers      | M       |
|  4 | Patricia   | Mankunku    | C       |
|  5 | Francois   | Papo        | P       |
|  7 | Winnie     | Dlamini     | NULL    |
|  6 | Neil       | Beneke      | NULL    |
| 10 | Breyton    | Tshabalala  | B       |
+----+------------+-------------+---------+
8 rows in set (0.00 sec)

In order to restore it, you need to use the binary update log. First, let's look at what's in the binary update log. It's not a text file, so you can't use an ordinary text editor, but MySQL comes with a utility, mysqbinlog. Running this utility on one of the binary update log files will output the contents of the log. The syntax is as follows:

mysqlbinlog path_to_binary_update_log

Let's see what's in the log:

C:\MySQL\bin>mysqlbinlog ..\data\speed_demon-bin.001
# at 4
#020602 18:58:21 server id  1   
Start: binlog v 2, server v 4.0.1-alpha-max-log
created 020602 18:58:21
# at 79
#020602 19:01:11 server id  1  
 Query   thread_id=2 exec_time=0  error_code=0
use firstdb;
SET TIMESTAMP=1023037271;
INSERT INTO customer VALUES(11,'Robin','McKenzie');
# at 167
#020602 19:01:48 server id  1   Stop

If you'd already been running binary update logging, you may have many log files. Choose the second most recent one that would have captured the latest INSERT statement.

Of course, this output is not much good on the screen. You can pipe it to the actual database as follows:

C:\MySQL\bin>mysqlbinlog ..\data\speed_demon-bin.001 | mysql firstdb

Now, you can view your table and see that the record has been restored:

C:\MySQL\bin> mysql firstdb;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.1-alpha-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM customer;
+----+------------+-------------+---------+
| id | first_name | surname     | initial |
+----+------------+-------------+---------+
|  1 | Yvonne     | Clegg       | X       |
|  2 | Johnny     | Chaka-Chaka | B       |
|  3 | Winston    | Powers      | M       |
|  4 | Patricia   | Mankunku    | C       |
|  5 | Francois   | Papo        | P       |
|  7 | Winnie     | Dlamini     | NULL    |
|  6 | Neil       | Beneke      | NULL    |
| 10 | Breyton    | Tshabalala  | B       |
| 11 | Robin      | McKenzie    | NULL    |
+----+------------+-------------+---------+
9 rows in set (0.00 sec)

The record has been successfully restored.

Table 11.4 describes the options available to mysqlbinlog.