{"id":809,"date":"2008-05-03T16:02:44","date_gmt":"2008-05-03T21:02:44","guid":{"rendered":"http:\/\/www.bytebot.net\/blog\/?p=809"},"modified":"2008-05-03T16:03:33","modified_gmt":"2008-05-03T21:03:33","slug":"playing-with-mysqls-online-backup","status":"publish","type":"post","link":"http:\/\/www.bytebot.net\/blog\/archives\/2008\/05\/03\/playing-with-mysqls-online-backup","title":{"rendered":"Playing with MySQL’s Online Backup"},"content":{"rendered":"

Something that has excited me for a long time with upcoming features in the MySQL Server, is online backup<\/strong>. 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.<\/p>\n

If you’ve not read the Forge Wiki page about it yet, please head over to Online Backup<\/a> on the Wiki. You can grab the latest source from mysql-6.0-backup<\/a> from mysql.bkbits.net<\/a>. If you’ve never built MySQL from source before, go ahead and read Building MySQL from source<\/a>. And you naturally need to test it once built, so I suggest making use of MySQL Sandbox<\/a>.<\/p>\n

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.<\/strong><\/p>\n

So, you’ve got BitKeeper (bkf<\/tt>) built, you’ve checked out the code, you’ve built it, and you have a binary distribution.<\/p>\n

Place the built version in a location that sanbox<\/tt> likes (\/opt\/mysql<\/tt> in my case). Now, run .\/express-install.pl \/opt\/mysql\/mysql-6.0.6-alpha-darwin9.2.1-i386.tar.gz<\/tt>. Once the install is completed, head over to ~\/msb_6_0_6<\/tt> and run .\/use<\/tt>.<\/p>\n

Backing up…<\/strong><\/p>\n

I now loaded the sakila sample database. Then, I proceeded to backup the database.<\/p>\n

\r\nBACKUP DATABASE sakila TO 'sakila-backup.sql';\r\n+-----------+\r\n| backup_id |\r\n+-----------+\r\n| 1         | \r\n+-----------+\r\n1 row in set (0.37 sec)\r\n<\/pre>\n
sakila-backup.sql<\/tt> is saved in your MySQL “data” directory, and in the case of the sandbox, its kept in your home directory.<\/p>\n
du -sh ~\/msb_6_0_6\/data\/sakila-backup.sql\r\n1.9M\t\/Users\/ccharles\/msb_6_0_6\/data\/sakila-backup.sql<\/pre>\n
Out of curiosity, I ran file on the backup, and it was reported to be data (not ASCII English text, with very long lines):<\/p>\n
file ~\/msb_6_0_6\/data\/sakila-backup.sql\r\n\/Users\/ccharles\/msb_6_0_6\/data\/sakila-backup.sql: data<\/pre>\n
Once you’ve done the backup, you might want to check the state:<\/p>\n
SELECT * FROM mysql.online_backup WHERE backup_id = 1 \\G\r\n*************************** 1. row ***************************\r\n          backup_id: 1\r\n         process_id: 0\r\n         binlog_pos: 0\r\n        binlog_file: NULL\r\n       backup_state: complete\r\n          operation: backup\r\n          error_num: 0\r\n        num_objects: 16\r\n        total_bytes: 1654492\r\nvalidity_point_time: 2008-05-03 18:55:19\r\n         start_time: 2008-05-03 18:55:18\r\n          stop_time: 2008-05-03 18:55:19\r\nhost_or_server_name: localhost\r\n           username: msandbox\r\n        backup_file: sakila-backup.sql\r\n       user_comment:\r\n            command: BACKUP DATABASE sakila TO 'sakila-backup.sql'\r\n            engines: Default\r\n1 row in set (0.00 sec)<\/pre>\n
online_backup<\/tt> provides statistics and metadata about a backup or restore. There is another table in the mysql<\/tt> database, that allows you to find progress information, and its called online_backup_progress<\/tt>.<\/p>\n
If you run SELECT * FROM mysql.online_backup_progress WHERE backup_id = 1 \\G<\/tt>, you’ll see notes changing from starting, running, validity point, running to complete.<\/p>\n
Restoring…<\/strong><\/p>\n
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).<\/p>\n
RESTORE FROM 'sakila-backup.sql';\r\n+-----------+\r\n| backup_id |\r\n+-----------+\r\n| 2         |\r\n+-----------+\r\n1 row in set (3.04 sec)<\/pre>\n
That’s it! You’ve restored your database. For posterity, here’s some statistics on the restore:<\/p>\n
SELECT * FROM mysql.online_backup WHERE backup_id = 2 \\G\r\n*************************** 1. row ***************************\r\n          backup_id: 2\r\n         process_id: 0\r\n         binlog_pos: 0\r\n        binlog_file: NULL\r\n       backup_state: complete\r\n          operation: restore\r\n          error_num: 0\r\n        num_objects: 16\r\n        total_bytes: 1654492\r\nvalidity_point_time: NULL\r\n         start_time: 2008-05-03 19:01:25\r\n          stop_time: 2008-05-03 19:01:28\r\nhost_or_server_name: localhost\r\n           username: msandbox\r\n        backup_file: sakila-backup.sql\r\n       user_comment:\r\n            command: RESTORE FROM 'sakila-backup.sql'\r\n            engines: Default\r\n1 row in set (0.00 sec)<\/pre>\n
There you have it, MySQL 6.0’s Backup and Restore<\/a> 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…<\/p>\n
Share this:<\/h3>