Backup MySQL Server

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?

Solution 1:

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.

Solution 2:

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

Solution 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;

Solution 4:

have a look at this question on stackoverflow

Best practice for backing up a production MySQL database?

it covers this topic well