WebFaction
Community site: login faq

Hi,

I've been reading over the instructions for backing up a database here, which involves mysqldump with default options. However, I've been doing a lot of reading, and I'm not sure if the default options will give a consistent HOT backup. I use Drupal, which uses mixed InnoDB and myISAM tables. But I can't find a set of mysqldump options that works to create a consistent HOT backup of both types of tables. Any comments?

Thanks a lot!

asked 02 Jan '13, 08:38

sb56637
13813
accept rate: 0%


Hot backups are a feature of the commercial MySQL Enterprise Backup product (or the Percona xtrabackup utility), neither of which are installed on our servers.

permanent link

answered 02 Jan '13, 11:01

seanf
12.2k42136
accept rate: 37%

Well, maybe "HOT" backups aren't the correct term. In other words I need to do periodic backups of mySQL without shutting it down.

(02 Jan '13, 11:26) sb56637

What exactly do you mean by "shutting it down"?

(02 Jan '13, 12:06) seanf

I need to do daily backups of mySQL without taking it offline or locking the tables or putting in in a read-only state.

(02 Jan '13, 12:26) sb56637

There are several options for dumping your database without locking tables described here: Run MySQLDump without Locking Tables

I don't believe that will achieve the level of consistency of a true hot backup, so if that's your goal, then you might be able to build Percona XtraBackup from source in your home directory and use that to back up your databases - however, I've never used it, I don't know the build procedure, and I don't know if it requires root access to the shared MySQL data directory.

If you manage to build it and find that it does need root access, then you may be able to work around that by installing a private MySQL instance, move your databases to that, and then back them up from there (since you would have full access to the private instance, you would not need root to back it up).

Now that I think of it, a private MySQL instance would also give you the option of backing it up by simply making a copy of your MySQL data directory, so you may want to consider that option as well.

(02 Jan '13, 14:34) seanf

Thanks a lot Sean for the useful information. I was also considering XtraBackup. I did a preliminary quick test, and it looks like it's useable with Webfaction. The instructions for making a backup just say to give it a username and password, similar to mysqldump. And they offer a precompiled binary tar.gz download that I extracted into my bin directory, and "xtrabackup --help" at least works without errors. So it looks promising.

(02 Jan '13, 21:15) sb56637
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6

question asked: 02 Jan '13, 08:38

question was seen: 2,345 times

last updated: 02 Jan '13, 21:15

                              
WEBFACTION
REACH US
SUPPORT
LEGAL
© COPYRIGHT 2003-2021 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM