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

mysql backup database-backups
Matteo Riva picture Matteo Riva · Mar 20, 2010 · Viewed 23.3k times · Source

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

Etienne Dechamps picture Etienne Dechamps · Mar 20, 2010
  • 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).

Related questions

Big Database backup best practice

I maintain big MySQL database. I need to backup it every night, but the DB is active all the time. There are queries from users. Now I just disable the website and then do a backup, but this is very …

Read More
PDO MySQL backups function

This function here http://davidwalsh.name/backup-mysql-database-php Has been floating around the internets for a while and is pretty famous, but it's using old MySQL API. Does anyone have the same but in PDO? If not does anyone want to …

Read More
How to take backup of a single table in a MySQL database?

By default, mysqldump takes the backup of an entire database. I need to backup a single table in MySQL. Is it possible? How do I restore it?

Read More