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…