Re: MySQL on readonly LVM snapshot fs

[Rob Ostrander]

Thank you for the info.

We are using ext3 and LVM1, and the snapshots are in fact read only.

Here is what I came up with for a working solution to run mysql on a read only filesystem:
/usr/bin/mysqld_safe --no-defaults --port=XXXX --socket=/tmp/mysql_backup.sock --datadir=/dbbackup/mysql --pid-file=/tmp/backup.pid --skip-innodb --log-error=/tmp/backup.err --verbose &

It seems I was having trouble with the socket and logging options.

I came up with a similar scheme only I need to FLUSH as the tables are myisam.

Here is the abridged version we use to grab regular dumps as archives:

#!/usr/bin/perl

use DBIx::DWIW;

my $db_user = '';
my $db_pass = '';
my $db_host = 'localhost';
my $db_name = 'mysql';
my $conn = DBIx::DWIW->Connect(DB=>$db_name, User=>$db_user, Pass=>$db_pass, Host=>$db_host);

$conn->Execute("FLUSH TABLES WITH READ LOCK");

`/sbin/lvcreate -L36G -s -n dbbackup /dev/d1/db`;

$conn->Execute("UNLOCK TABLES");

$conn->Disconnect;

`mount -o ro -t ext2 /dev/d1/dbbackup /dbbackup`;

system ("/usr/bin/mysqld_safe --no-defaults --port=XXXX --socket=/tmp/mysql_backup.sock --datadir=/dbbackup/mysql --pid-file=/tmp/backup.pid --skip-innodb --log-error=/tmp/backup.err --verbose &");

system ("mysqldump --master-data --add-drop-table -A -h 127.0.0.1 -PXXXX | gzip -c > mysqldump.gz ");

system ("kill `cat /tmp/backup.pid`");

`umount /dbbackup`;

`/sbin/lvremove -f /dev/d1/dbbackup`;