1

I'm hosting a 60GB InnoDB file-per-table MySQL database with a VPS provider. I want to generate a dump of the file. Running mysqldump makes the website connected to this database extremely slow, resulting in recurrent timeouts during the period of the dump. The VPS provider offers a service of spontaneous disk cloning.

Is there a way to dump the database from the offline clone instead of the online live database? During that time I'll make the latter read-only to avoid any incoherences.

Update:

  • The clone is simply a duplicate of the database disk.
  • I have root access.
  • Hello mossaab, what kind of access do you have to & what format is the clone? – Eamonn Travers Mar 31 '15 at 5:39
  • Question updated. – mossaab Mar 31 '15 at 12:28
  • Spin up another VPS with MySQL running on it, and point datadir and other settings to the cloned disk in my.cnf – Bert Mar 31 '15 at 17:44
  • @Bert Can you please post your comment as an answer? This turned out to be the easiest way for my case. – mossaab Apr 7 '15 at 20:48
1

Spin up another VPS with MySQL running on it, and point datadir and other settings to the cloned disk in my.cnf

| improve this answer | |
  • Indeed, this turned out to be the easiest approach for my case. – mossaab Apr 8 '15 at 18:02
  • Okay, make sure that you are cloning innodb log as well. Also double check that you can use this datadir, i am not sure that you have consistent backup. – Navern Apr 13 '15 at 8:06
2

I believe you should use percona xtrabackup for this purpose. It's not so fast in terms of backup time, but it doesn't prevent you database from running correctly.

We are using this solution in production and it's pretty good.

http://www.percona.com/software/percona-xtrabackup

I'm not sure that this is good idea to make dump from cloned disk when mysql is running. If you want to do it this way, then you should run FLUSH TABLES WITH READ LOCK; or just shutdown mysql server.

| improve this answer | |
  • While percona seems an adequate solution, it will replace the existing mysql server. I can't take this risk currently, but am willing to try it in a future server upgrade. – mossaab Apr 7 '15 at 20:50
  • Acutally it won't replace: Look for innobackupex command and it's syntax, you can have backup in another dir and later use it for logical dump orr restore raw dump(but you have to do drop tablespace; import tablespace functionality for innodb tables). – Navern Apr 13 '15 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.