Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a LAMP website with mysql backend with InnoDb engine for tables

I would like to be able to use mysqldump to take periodic dumps of the database - WITHOUT having to stop the mysql server (i.e. shutting down the website) for the duration of the backup.

I am surprised that I cant find this information anywhere - not even in the mySQL documentation. A lot of mention is made about mysqlhotcopy, but that only works for IMSAM tables - and is therefore of no interest/use to me.

Does anyone know if (how?) I can use mysqldump to take a copy/dump of a database that is still being used?.

A link to the official documentation would be very useful, since I want to make sure that I get this absolutely right.

I am running on Ubuntu 10.0.4 LTS

share|improve this question

5 Answers 5

up vote 2 down vote accepted

No, ytou don't have to take down the web site to do a MySQL backup. You not only can use mysqldump on a running MySQL database but in fact the server must be running for mysqldump to be able to connect to it. The fact that you're using InnoDB tables is a plus, as any locks will be brief and it's highly unlikely that your users will ever be aware of them.

If you are at all nervous or hesitant about performing the backup it is no big deal to run another instance of MySQL and set up master/slave replication, performing the backups on the slave. That way you can be completely certain the web site will be unaffected by the backups.

share|improve this answer
    
john: +1 for anticpating and understanding my nervousness. I think I would much prefer to use replication and take a backup on the slave server. I would like to run both the master and the slave on the same machine - could you please provide some hints (or links) on how to do this? thanks –  user35402 Aug 2 '10 at 10:50
    
@morpheous, it's just a matter of running 2 instances of MySQL, with the slave running on a different port. Setting up the master/slave replication is beyond the scope of a simple comment but Google will reveal there are already some excellent articles on that subject. Some good results are to be found by searching for "mysql master slave replication". –  John Gardeniers Aug 2 '10 at 12:49
    
unless I am mistaken, both (MASTER and SLAVE) servers have to started with binary logging switched on and mysqldump has to be run with --single-transaction?. Can you confirm this? –  user35402 Aug 3 '10 at 17:24

It depends on how high load you have, but Janne's suggestion is the one most admins settle on because of the load when doing backups.

For InnoDB you don't have many choices when it comes to backup:

I don't have any experience with any of them - but I've researched this a few months back when we were considering a MyISAM to InnoDB move (we didn't, have too many full-text index searches).

share|improve this answer

Yes, you can use mysqldump against a running server - you have to - mysqldump just connects to the server and runs SQL commands against the server to generate the dump file.

If you're concerned about consistency of the dump file, you can use --single-transaction if you're running on mostly InnoDB tables. It will wrap the dump in a transaction.

mysqldump may impact the performance of your server (just like any backup could), so test before putting it into production.

Another option, if you're using LVM underneath MySQL would be to use mylvmbackup - this takes a snapshot of the LVM volume so you can backup the data files however you like.

share|improve this answer

(Ah, I see you posted this to both superuser and here. Here's some copypastah.)

The usual way to do this is to setup a slave instance of MySQL and take the backup from there. You don't necessarily need a separate physical server for the slave MySQL, it's perfectly OK to run it on the same server (if you have the disk space and some memory for it; from the CPU side of things a mostly idle MySQL slave would not do much harm).

share|improve this answer

Using mysqldump to backup InnoDB tables is contemplated in the official documentation here

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

You will have no problem at all. I use it everyday to make periodical backups of a bunch of databases with a frequency up to 15 minutes.

To ensure you have your data in a consistent state you should use the --single-transaction optione while invoking mysqldump. That's all.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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