Backing up BLOBs

The PBMS BLOB repositories can now be backed up with mysqldump.

A new system table ‘pbms_dump’ contains 1 row for each repository record. Each row consists of 1 column which is a BLOB. The content of this table has no purpose other than for the use in repository backup. By inserting the data back into this table the repository is recovered. Before doing the backup the pbms_dump system table needs to be discovered, to do this execute the command “select * from pbms_dump where false”. After doing this then mysqldump will include it in backups. Be sure to use the “—hex-blob” option. For example:

Mysqldump –u root –hex-blob mydatabase > mydatabase.sql

Before recovering the database you need to notify PBMS that a recovery operation is in progress so that the recovery of the tables containing the BLOB references do not increment the BLOB reference count. This notification is done by setting a bool flag in a new system table (I like system tables ☺) pbms_variable. To restore a database perform the following operations:

----------------------------
create database recovtest;
use recovtest;

update pbms_variable set Value = "true" where Name = "Restoring-Dump";
source mydatabase.sql;
update pbms_variable set Value = "false" where Name = "Restoring-Dump";
-------------------------------

I am also planning on implementing the engine level backup and recovery supported in MySQL 6.o as well as an internal backup method that will produce a backup that you can recover just by dragging and dropping it into the ‘pbms’ folder where your database folders are located.

The internal backup will run asynchronously in the background and will not block normal operations of the database including adding and deleting BLOBs. The way that it would work is that the administrator would lock the tables containing the BLOB references and back them up. Sometime during the backup, before releasing the locks, the asynchronously PBMS backup of the database would be started. Progress of the backup could be monitored via a pbms_status table.

The version supporting mysqldump is in the PBMS branch "~pbms-core/pbms/cloud" on launchpad. Be warned though that this is a development branch with everything that implies.