Is copying the /var/lib/mysql directory a good alternative to mysqldump?

Since I'm making a full backup of my en开发者_Go百科tire 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?

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

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.

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.

For a complete discussion of the 2 strategies, you need to read this: https://dev.mysql.com/doc/refman/5.5/en/backup-types.html

The currently best free and open-source solution seems to be Percona's: http://www.percona.com/software/percona-xtrabackup

I'll go with a strong NO.

From my experience, backing up/restoring raw mysql data files can be used only on the same os/server version. It does not work cross platform (eg. ubuntu/macos) with same server versions nor if mysql server versions are different on same platform.

Percona XtraBackup (innobackupex) from Percona MySQL distro will let you do live & differential mysql backup and serve you the backup files that can be restored by copying to /var/lib/mysql/. You need to be running Percona Server for MySQL to use all of this.