43

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?
Jesse Nickles
  • 1,435
  • 1
  • 17
  • 25
Matteo Riva
  • 24,728
  • 12
  • 72
  • 104
  • 2
    Doing this for years, no inconsistency! – sepehr Mar 20 '10 at 10:00
  • 2
    Percona has a tool that basically takes this approach for doing "hot backups" of running servers: http://www.percona.com/doc/percona-xtrabackup/2.1/ – Eli Mar 27 '14 at 19:19

5 Answers5

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

Etienne Dechamps
  • 24,037
  • 4
  • 32
  • 31
  • 1
    Thanks everybody for this solution. It's cut out at least an hour of re-importing time ! And if you want to do it into one instruction: Type "FLUSH TABLES WITH READ LOCK;". – Jan Goyvaerts Jul 29 '10 at 09:15
  • Can you go into details on the answer for "can single tables be imported into another mysql"? "Yes so long as it's MyISAM" is a rather sparse answer. – bobpaul Nov 15 '13 at 00:17
6

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.

AlBlue
  • 23,254
  • 14
  • 71
  • 91
3

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.

Joonas Pulakka
  • 36,252
  • 29
  • 106
  • 169
1

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

TomDogg
  • 3,803
  • 5
  • 33
  • 60
0

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.

seven
  • 2,388
  • 26
  • 28