{"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":"https:\/\/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","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[23],"tags":[],"class_list":["post-809","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p4vJD-d3","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"amp_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/809","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/comments?post=809"}],"version-history":[{"count":0,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/809\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/media?parent=809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/categories?post=809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/tags?post=809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}