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
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.
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).
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.
(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).
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.