Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. 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

4 Answers 4

up vote 1 down vote accepted

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

share|improve this answer
    
Janne: Do you have a URL that shows how to do this?. Since its my first time, I would much rather use a set of instructions that have been "tried and tested", rather than trying to put something together myseld. Since successful backup/restore is so important. –  morpheous Aug 2 '10 at 10:46
    
morpheous: Sure. I hope MySQL's own documentation is "tried and tested" enough. I edited my previous reply. –  Janne Pikkarainen Aug 2 '10 at 10:57

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

share|improve this answer

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

share|improve this answer

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