0

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

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

  • 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
1

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

  • It requires 6 characters change for an edit...I think though that MySqlDumb should really be correctly spelled to MySqlDump because it's a pretty bad typo ;) – Uncle Iroh Apr 2 at 23:16
0

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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