Mysql backup/restore problem


#1

Hi, I’d like to create a full backup of one of my production databases. However, the database that I want to back up is very big (two or three tables have over 14 million rows) and when I’ve tried to back it up in the past, I’ve received ‘segmentation’ errors. I’d like to then restore this backup to my ‘beta’ environment so that I can develop new features using a copy of the live data.

I know that DreamHost do an automatic database backup, but I’m led to believe that this is incremental and I don’t think there’s a way of restoring it to a different database (at least not from the DreamHost panel). Just wondered if anyone could give me any pointers as to how to achieve the above.

Thanks in advance

Jon


#2

Were you trying the mysqldump command?
http://wiki.dreamhost.com/Backup_MySQL#Getting_a_Backup_From_the_Shell

-Scott


#3

Hi, thanks for the reply.

Yes, I tried backing up from the shell using mysqldump. Due to the size of the database, it takes a long time to backup and also seems to create table locks while it is backup up which is not great as my website is then not usable during the backup process. What tends to happen is, maybe an hour or two after kicking off mysqldump, I receive a “segmentation fault” error message.

The database tables are a mixture of InnoDB (for referential integrity) and MyISAM. The total database size is approx 5.5GB with one table 2.5GB (14 million rows).

I’d like to be able to create a full backup, if at all possible, without creating table locks as I’d like the system to still be usable while the backup is running (consistency is not vitally important). I’m not sure why the segmentation fault occurs (which is my main issue) but I’m led to believe it might be something to do with InnoDB.

Jon