Using the Binary Update Log to Restore a Database to the Most Recent Position
|
|
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.
| Option | Description |
|---|---|
| -?, --help | Displays help and exits |
| -d, --database=dbname | Only lists entries for the specified database |
| -s, --short-form | Shows only the queries, not any extra info |
| -o, --offset=N | Skips a number of entries starting from the beginning, specified by N |
| -h, --host=server | Gets the binary log from the specified server |
| -P, --port=port | Uses the specified port to connect to the remote server |
| -u, --user=username | Username to connect to the server |
| -p, --password=password | Password to connect to the server |
| -r, --result-file=file | Places the output in the specified file |
| -j, --position=N | Starts reading the binary log at position N |
| -t, --table=name | Gets the raw table dump using COM_TABLE_DUMB |
| -V, --version | Displays the version and exits |
|
|
