Simple question about using mysqldump

Question

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

Answer 1

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

EDIT:: Added the official MySQL replication document links.

Long explanation about MySQL replication: http://dev.mysql.com/doc/refman/5.1/en/replication.html

Replication setup how-to: http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

Answer 2

I agree with Janne, using replication is the usual way to do things. But you could just use MySqlDumb. You don't have to switch off the DB for that. It will just be a bit unresponsive for a while. Depends what type of app and how much data you have.

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

Answer 3

I am using a modified version of the script found here:

http://bash.cyberciti.biz/backup/backup-mysql-database-server-2/

(I simply removed some of the lines that create and set permissions on the backup directories)

It works pretty well for me on a live ubuntu web server

Answer 4

You dont have to shutdown the database for using the MySQL Dump This is a script i use in Windows

@echo Dumping data...

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --add-drop-table=true -h localhost -u root -ppassword mydatabase table1 table2 table3 1> %1

If you want to run this directly, just use

mysqldump.exe" --add-drop-table=true -h localhost -u root -p mydatabase table1 table2 table3 > datadump.sql

And is going to ask for the password. In this example I'm just backing up some talbes (1 to 3), but you can backup everything. There are a lot of different options. For restoring, the batch file is:

@echo Loading Data...
@"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" -u root -ppasword  -h localhost mydatabase 0< %1

Or just

mysql.exe" -u root -p -h localhost mydatabase < datadump.sql