Backup MySQL Server

Question

What's the best way to back up a MySQL server? I would like a method that doesn't require bringing down the server. Is something like InnoDB hot backup necessary, or can I just use the backup tools provided in the MySQL Administrator tools. What advantages does one offer over the other?

score 12 · Accepted Answer · answered May 14 '09 at 01:25

12

We use mysqldump, which works while MySQL is running. We started using mysqldump because it was the easiest to set up when our needs were small, and it seemed to meet those needs. Our databases have grown since then, but we haven't yet outgrown mysqldump. Whether it suits you will depend on the size of your databases, their usual operating load, and probably a bunch of other things.

answered May 14 '09 at 01:25

TimB

1,430
2
15
19

mysqldump is great. Unlike the binary dumps of some other RDMS's, by default the files mysqldump generates are text files with full-fledged sql commands -- mostly CREATE TABLE statements and INSERTs for the rows. As simple text files, they can be manipulated, compressed or encrypted easily. – nedm May 14 '09 at 02:37
3

mysqldump breaks down when you have big databases, and/or when you rely on MyISAM. We have some 400G databases with a lot of indexes that needs to be rebuilt when importing the dump, so that a recovery from backup takes tree days. LVM snapshot gives a much better recovery time when you've outgrown mysqldump. – Nils-Anders Nøttseter May 23 '09 at 07:22

score 4 · Answer 2 · answered May 14 '09 at 08:52

4

take a look at xtrabackup from percona - it's interesting open source alternative to mysqlhotcopy for innodb databases.

you can read more about it here

answered May 14 '09 at 08:52

pQd

29,561
5
64
106

score 3 · Answer 3 · answered May 14 '09 at 02:02

3

mysqldump can be used, but it will not guarantee a consistent dataset, unless you lock all of the tables for the duration of the dump. This will put the database in a read-only mode, but will ensure the dump is consistent.

You can lock the tables with the command:

FLUSH TABLES WITH READ LOCK;

And when you are finished you can release the lock with:

UNLOCK TABLES;

answered May 14 '09 at 02:02

Brent

22,219
19
68
102

7

mysqldump --single-transaction will ensure a consistent snapshot – Dave Cheney May 14 '09 at 05:52
5

--single-transaction only works when all your tables are InnoDB. The MyISAM tables don't support transactions. – Nils-Anders Nøttseter May 23 '09 at 07:13

score 2 · Answer 4 · edited May 23 '17 at 12:41

2

have a look at this question on stackoverflow

Best practice for backing up a production MySQL database?

it covers this topic well

edited May 23 '17 at 12:41

Community

1

answered May 14 '09 at 05:33

Charles Faiga

612
2
11
18

score 2 · Answer 5 · answered May 14 '09 at 05:52

2

Setup MySQL Replication. As a bonus you get a server to run heavy reporting jobs and mysqldump without affecting your main production db.

answered May 14 '09 at 05:52

Dave Cheney

18,307
7
48
56

Remember that replication may be delayed and so not necessarily a point-in-time backup of your master. – Jun 01 '09 at 18:14
2

Replication isn't backup. If you accidentaly delete something in the master, the slave deletes it also. The slave is good to use mysqldump on though, because it won't affect your production server (as Dave mentioned). – Martijn Heemels Dec 29 '09 at 15:07

score 0 · Answer 6 · answered May 14 '09 at 01:26

0

The easiest way is to simply use the mysqldump command.

answered May 14 '09 at 01:26

WerkkreW

5,879
3
23
32

score 0 · Answer 7 · answered May 14 '09 at 02:05

0

There is also a tool called mysqlhotcopy which I looked at once, and thought it held alot of promise - never used it though.

answered May 14 '09 at 02:05

Brent

22,219
19
68
102

Backup MySQL Server

7 Answers7

Linked