Something that has excited me for a long time with upcoming features in the MySQL Server, is online backup. Since seeing it first being demonstrated by Chuck Bell at the Heidelberg Developers Conference in 2007, I’ve been enthralled. Now you too, can try online backup.
If you’ve not read the Forge Wiki page about it yet, please head over to Online Backup on the Wiki. You can grab the latest source from mysql-6.0-backup from mysql.bkbits.net. If you’ve never built MySQL from source before, go ahead and read Building MySQL from source. And you naturally need to test it once built, so I suggest making use of MySQL Sandbox.
NOTE: mysql-6.0-backup is the MySQL Backup Team Tree, and frequently changes and can break sometimes. This is not for production use. It can eat babies.
So, you’ve got BitKeeper (bkf) built, you’ve checked out the code, you’ve built it, and you have a binary distribution.
Place the built version in a location that sanbox likes (/opt/mysql in my case). Now, run ./express-install.pl /opt/mysql/mysql-6.0.6-alpha-darwin9.2.1-i386.tar.gz. Once the install is completed, head over to ~/msb_6_0_6 and run ./use.
Backing up…
I now loaded the sakila sample database. Then, I proceeded to backup the database.
BACKUP DATABASE sakila TO 'sakila-backup.sql';
+-----------+
| backup_id |
+-----------+
| 1 |
+-----------+
1 row in set (0.37 sec)
sakila-backup.sql is saved in your MySQL “data” directory, and in the case of the sandbox, its kept in your home directory.
du -sh ~/msb_6_0_6/data/sakila-backup.sql
1.9M /Users/ccharles/msb_6_0_6/data/sakila-backup.sql
Out of curiosity, I ran file on the backup, and it was reported to be data (not ASCII English text, with very long lines):
file ~/msb_6_0_6/data/sakila-backup.sql
/Users/ccharles/msb_6_0_6/data/sakila-backup.sql: data
Once you’ve done the backup, you might want to check the state:
SELECT * FROM mysql.online_backup WHERE backup_id = 1 \G
*************************** 1. row ***************************
backup_id: 1
process_id: 0
binlog_pos: 0
binlog_file: NULL
backup_state: complete
operation: backup
error_num: 0
num_objects: 16
total_bytes: 1654492
validity_point_time: 2008-05-03 18:55:19
start_time: 2008-05-03 18:55:18
stop_time: 2008-05-03 18:55:19
host_or_server_name: localhost
username: msandbox
backup_file: sakila-backup.sql
user_comment:
command: BACKUP DATABASE sakila TO 'sakila-backup.sql'
engines: Default
1 row in set (0.00 sec)
online_backup provides statistics and metadata about a backup or restore. There is another table in the mysql database, that allows you to find progress information, and its called online_backup_progress.
If you run SELECT * FROM mysql.online_backup_progress WHERE backup_id = 1 \G, you’ll see notes changing from starting, running, validity point, running to complete.
Restoring…
Now, its time to restore. Note that the restore is what is known as a destructive restore (i.e. it will replace the current version of the database).
RESTORE FROM 'sakila-backup.sql';
+-----------+
| backup_id |
+-----------+
| 2 |
+-----------+
1 row in set (3.04 sec)
That’s it! You’ve restored your database. For posterity, here’s some statistics on the restore:
SELECT * FROM mysql.online_backup WHERE backup_id = 2 \G
*************************** 1. row ***************************
backup_id: 2
process_id: 0
binlog_pos: 0
binlog_file: NULL
backup_state: complete
operation: restore
error_num: 0
num_objects: 16
total_bytes: 1654492
validity_point_time: NULL
start_time: 2008-05-03 19:01:25
stop_time: 2008-05-03 19:01:28
host_or_server_name: localhost
username: msandbox
backup_file: sakila-backup.sql
user_comment:
command: RESTORE FROM 'sakila-backup.sql'
engines: Default
1 row in set (0.00 sec)
There you have it, MySQL 6.0’s Backup and Restore functionality. Still in its early stages of development, but very, very cool! All these features will also be available in MySQL 6.0.5, when this gets released…
