Pop quiz: when will a filesystem copy be an incomplete backup?
Let’s suppose that your backup process looks like this: you stop a replication slave, shut down MySQL, and copy away the data directory. Assume that the slave is perfect and has the same data as the master. Nothing is broken, nothing is wrong, everything is working fine. In most cases, this should work, right?
Under what kinds of circumstances will you not get all your data back if you restore the file copy and start MySQL?
Did you wait for replication to have no temporary tables open?
Is all InnoDB data actually located in the data directory?
Morgan Tocker
25 Sep 10 at 7:14 pm
Perhaps when you fail to consider symlinks within your datadir? Well, same as Morgan saying if all InnoDB data is actually located within datadir :).
Jervin
25 Sep 10 at 7:44 pm
Memory table’s data will lost.
huarong
25 Sep 10 at 9:22 pm
I was thinking of memory tables. The other answers are good too! Any more?
Xaprb
26 Sep 10 at 1:11 pm
Wouldn’t “FLUSH TABLES WITH READ LOCK” sort this?
Rhys
26 Sep 10 at 3:21 pm
A shutdown of mysql will flush the memory. The only situation I can think of is if you didn’t wait for the replication to catch up. You will then have tables of data which have not been updated with the pending data in the relay logs.
The other situation is if you did a LVM snapshot and DID NOT do a ‘sync’ or unmount the filesystem, then you will have buffered data that was never flushed.
Dale Lancaster
26 Sep 10 at 3:42 pm
I can think of a few reasons.
1. Replication using temporary tables and the backup was snapped when replication was using a table.
2. The backup is fine but the master rotated away the binary logs the backup needed when it was restored so there is no way to catch up replication.
3. Heap tables were used.
Eric Bergen
26 Sep 10 at 5:51 pm
I think one would also have to backup the content of the directory pointed to by slave_load_tmp_dir. It is needed to replicate LOAD DATA INFILE.
Pierre Dumont
27 Sep 10 at 4:10 am
The slave could be half way through some sort of maintenance/batch procedure when shut down which could leave the data in an inconsistent state. For example a shutdown while creating daily statistics could require manual intervention (a re-run of the script at a minimum) if the slave backup were recovered to a master.
Or how about a backup half way through rotating a table partitioned by date?
Are there any global variables which could affect the correctness of a backup when applied at runtime prior to the backup, but not put in my.cnf?
Could someone give an example of how temporary tables could be open if MySQL is shut down properly?
Matt
27 Sep 10 at 4:12 am
If you only safe the datadir then the InnoDB log files might be elsewhere. The my.cnf is probably not in the datadir so the settings for the log files (size/location) could be different which could lose some transactions if InnoDB was not shutdown properly.
Daniël van Eeden
27 Sep 10 at 4:18 am
you restore the datadir on some different filesystem/OS that doesn’t handle case sensitivity the same. this will instantly break all partitioned innodb tables for example.
sbester
27 Sep 10 at 6:03 am
That seems to be a tricky question. If the question just about data then you should get all data. Unless it is not under the data directory, i.e. memory or federated tables, symlinks, tables not marked for replication etc.
However there is another question whether it will be possible to restore that data easily due to different MySQL versions\engines, users\permissions, OS settings etc.
Alex
27 Sep 10 at 9:19 am
In your case you just have to make sure you tar / untar it with preserve permissions of the actual files in used. when I want to restore backup I completely remove data directory and then open the tar which replace the old data directory.
If you use “FLUSH TABLES WITH READ LOCK” then LVM snapshot without slave stop or mysql shutdown then when you restore the data innodb will have to recover because the state of the snapshot was “hot” and it will take your time when you restore large data sets.
Golan Zakai
30 Sep 10 at 7:22 am
Federated tables, if anyone uses those, will obviously not be backed up.
Also, if you’ve changed important global settings, without updating my.cnf on disk. You may run into some performance issues ( in addition to cold cache’s).
William
4 Oct 10 at 7:06 pm