Wednesday, May 27, 2009

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.


benjamin said...

Hi Barry,

Though this isn't related to this post – I have had a hard time finding support for PrimeBase so I figure I might as well just try here.

Basically what I am trying to do is create a shell script (UNIX on Mac OS X/Terminal) which can communicate with our PrimeBase database, and draw upon certain values, and then output them into a format outside of the pbcon/pbac console.

I hope that makes some sense to you - any help would be much appreciated.

Thanks in advance.
Ben Sehl

benjamin said...

-Update- I think I've found what I was looking for.

- What I need now is a way to export results into something like the terminal… thoughts?

NB I am using PrimeBase classic (not PBXT) – sorry if I'm a little too archaic for you.

Barry Leslie said...

Hi Benjamin,

Yes the best place for a question like this is on the PrimeBase talk list. It can be that somebody on the list may have already done something like this.

Your scrip will probably need to either pipe the data into 'pbac' or place the program to be execute into a file, call 'pbac' to execute the file and then return the output log.

Hope this helps,