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
Best Answer
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.