Is copying /var/lib/mysql a good alterntive to mysqldump?

Question

Since I'm making a full backup of my entire debian system, I was thinking if having a copy of /var/lib/mysql directory is a viable alternative to dumping tables with mysqldump.

• are all informations needed contained in that directory?
• can single tables be imported in another mysql?
• can there be problems while restoring those files on a (probably slightly) different mysql server version?

Answer 1

You'll probably get the databases in an inconsistent state, so won't be able to use them.

Better to use the proper backup systems.

Answer 2

It's perfectly OK as long as you shut down the MySQL sever first and use exactly the same version to retrieve the "backup". Otherwise it isn't.

Answer 3

• Yes
• Yes if the table is using the MyISAM (default) engine. Not if it's using InnoDB.
• Probably not, and if there is, you just need to execute mysql_upgrade to fix them

To avoid getting databases in a inconsistent state, you can either shutdown MySQL or use LOCK TABLES and then FLUSH TABLES before the backup. The second solution is a little better because the MySQL server will remain available during the backup (albeit read only).

Answer 4

This approach is only going to work safely if you shut the database down first. Otherwise you could well end up in an inconsistent state afterwards. Use the /etc/init.d/mysql stop command first. You can then restart it after the backup is taken.