Playing with MySQL’s Online Backup

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…

5 Comments

  1. aussieaubs says:

    CC – whats the timeline for 6.0.5 to be released??

  2. byte says:

    Aubs,

    6.0.4/6.0.5 is all alpha previews. Watch:
    http://dev.mysql.com/downloads/mysql/6.0.html

    I expect 6.0.5 binaries to be out in a few weeks at most…

    When will 6 be GA? That is much further down the line, as we should immediately focus on getting 5.1 out of the door.

  3. Sheeri says:

    What kind of CPU resources does it use?

  4. john oneill says:

    Sheeri
    Fairly substatial cpu usage as a time factor more so than resource. Your system should be able to do other work at the same time but the application may run for consideravle time. This however is preferable to the other way around. A fast turnaround with max cpu usage would cause more interference.

    John

  5. […] Playing with MySQL?s Online BackupThis really is going to be a great feature. Is this going to be available to everyone, or just in the “not free” version? […]


i