MySQL LVM backups


#1

Hi, I am a MySQL PS customer and I’m struggling trying to find a quick and reliable method of backing up my quite large database (approx 10 GB with some tables containing over 25 million rows). The database contains a mix of MyISAM and InnoDB tables. I’ve tried using mysqldump, but it just locks the whole database for several hours (making my busy website unusable for the duration) and mysqldump also has problems backing up InnoDB tables. In the past, I’ve received segmentation faults during the backup process and I’ve not yet managed to complete a full backup and I don’t want to bring my site down for several hours every time I try and do a backup (theoretically, at a minimum, I’d like to do a daily backup).

As you can imagine, I’m becoming paranoid about not being able to create a proper backup of my database and while searching for a solution, I’ve come across some articles regarding LVM snapshots and this method seems to ‘tick all of the boxes’ in that it is a more or less instantaneous backup solution (minimal downtime) & also agnostic to table format. See e.g. http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/

There’s also a script here which makes setting it all up look quite easy: http://www.lenzg.net/mylvmbackup/

However, it seems I’m unable to run LVM on the MySQL private server as I don’t have enough permissions to do this. I was wondering if anyone else was using this approach, or what other alternatives there might be.

Any tips appreciated…

Jon


#2

Won’t work for PS MySQL. LVM operates at the level of the “raw” hard disk, so it’s not available at the layer that DreamHost PS has access to.

mysqldump shouldn’t have that sort of trouble dumping InnoDB tables, though… we use it ourselves, and we certainly don’t have to take the Panel down for days at a time to run our backups! You should probably check the options on mysqldump (especially --lock-tables: you don’t want that) and see what you can do to speed things up.


#3

thanks very much for your reply, I guess I’m not up to speed on the finer details (the default mysqldump locks everything).

The --single-transaction parameter looks like it might help me a bit (my biggest tables use InnoDB).
http://www.ducea.com/2006/10/26/dumping-large-mysql-innodb-tables/

So, I’ll give it another stab using the following:
[font=Courier]mysqldump --opt --single-transaction --quick --routines --user=username --password=password --host=mysql.host.name mydbname > backup.sql[/font]

(the previous backup didn’t use the --single-transaction or --quick parameters)

I’m going to test on a (smaller) copy of the database and see if it works.


#4

I haven’t worked with a DB that big, but I think your case could be a good example of the replication scheme that mysql offers (was going to suggest mysql-hotcopy but it’s only FS level and archive and mysIsam specific) http://dev.mysql.com/doc/refman/5.1/en/ha-overview.html and http://dev.mysql.com/doc/refman/5.1/en/replication.html it’s the only I can think you can make with tables that big… maybe DRDB (but not sure it works with the DH-PS setup.)

I hope it works for you.


#5

I’m not a huge fan of DRBD for databases. As the DBA Hulk put it:

It also requires two physical servers. (Virtual servers, like DreamHost PS, simply won’t do — like LVM, it operates on raw disks.)

Like I said earlier, though, we use mysqldump internally for databases considerably larger than yours. So you should be able to get it to do what you need.


#6

Hi, thanks for the responses guys. I’ve tried a further mysqldump with the --single-transaction parameter and it has improved the locking behaviour, but I still receive a segmentation fault part-way through the backup (I get this error with or without this parameter, so it hasn’t just been introduced by this argument). The backup process terminates without finishing and I’m therefore unable to backup the whole database. (This was one of my original reasons for looking at alternative backup methods, such as LVM.)

I’ve tried adding a --debug parameter to mysqldump to try and see the detail of what is going wrong, but I receive an error “Option --debug used, but is disabled” when I try to use this and I think to enable this option, MySQL needs to be re-compiled with debug support, which I don’t think is possible on MySQL PS? ( http://www.mail-archive.com/mysql@lists.mysql.com/msg106398.html ). I’ve read elsewhere that the seg fault might be caused by InnoDB.


#7

After a lot of digging around, I’ve finally managed to solve the issue :slight_smile: It appears that the segmentation fault is caused by the [font=Courier]–routines[/font] option in mysqldump.

I’m now running the same command as above but minus the [font=Courier]–routines[/font] option and everything works ok (I’ll have to script the stored routines separately). I’ve found a reference to the bug here: http://bugs.mysql.com/bug.php?id=27293

Next step is to try mysqldump on my production database…


#8

If mysqldump doesn’t do the job, mydumper may also be worth a shot: https://launchpad.net/mydumper