Blog

Danger Zone: MySQL and Windows/NTFS ‘Volume Shadow Copy’ Technology

Let us start when the first version of Windows XP was released. It included a feature that was not available in Windows 2000: ‘System Restore’. It was basically a set of hidden copies of system files that could be restored to the system on demand. It was a highly useful feature at the time as a lot of bad device drivers were around and installing one could destroy the system to an extent so that reinstalling the system would often be faster than repairing the damage (not to mention that repair would sometimes require skills that many users did not have). It saved my day at least 10 times back in the mid-00′es – in particular before the release of Servicepack 1 for WinXP that ‘hardened’ WinXP a lot against such ‘bad driver attacks’.

‘System Restore’ is still available in recent Windows. I will concentrate on Windows 7 here. In Windows 7 ‘System Restore’ uses a technology what is a part of the NTFS file system: ‘Volume Shadow Copies’. If you have ‘System Restore’ enabled (for the system as a whole or for specific partitions) Windows will automatically and periodically create ‘shadow copies’ of all folders and files. It can also be done on demand. You probably have noticed that a lot of installers do.   A set of ‘shadow copies’ created simultaneously is basically a file system snapshot, but what is particular about the implementation is that ‘shadow copies’ are NOT files/folders themselves. The current working copy of a file/folder and all existing ‘shadow copies’ of same are contained in ONE file/folder.

‘System Restore’ will simply ‘promote’ ‘shadow copies’ of system files to current working copies and restart the OS. But ‘shadow copies’ can be accessed from programmers’ interfaces and even from the Windows GUI. And then it does not only apply to system files.

Some recent backup tools for Windows will actually create ‘shadow copies’ and next export/copy selected partitions/folders/files to a file. This includes my preferred set of tools for backup and hard disk management. Also the system backup tool that is included with some editions of Windows 7 does the same.

Even a user can ‘promote’ a ‘shadow copy’ to a current working instance. Just right-click on any file system object in Windows Explorer, select ‘properties’ and activate ‘Previous Versions’ tab and available ‘shadow copies’ are listed. You can ‘promote’ (restore), view and copy.

Here you will see a MySQL 5.5 /datadir with a number of ‘shadow copies’. So if something here was recently messed up, I could just ‘promote’ a ‘shadow copy’ of the /datadir to current instance and next use the binlog for recovery from the time of the snapshot (if I had been smart enough not to have the binlog in the /datadir, of course)? There was a question mark – and the answer is NO. It is not safe. MySQL may have executed changes to data that are not written to disk yet and if that was the case when the snapshot was created it will result in an incomplete and possibly inconsistent snapshot. All sorts of weird and inconsistent errors may result from this. If system tables are affected, the server may even refuse to start or access could be denied for users. MySQL has no interface to Windows that will ensure that a ‘shadow copy’ is consistent. InnoDB will (or should, at least) be able to recover to a consistent state if all files needed for recovery are there and restored from simultaneous ‘shadow copies’, but this is not the case for most other storage engines.

So the lessons are:
1) Do not rely on a backup tool for Windows making use of ‘Volume Shadow Copy’ technology for backing up MySQL data – unless you are perfectly sure that the snapshot is consistent (what is most simply achieved by stopping the server for the few seconds it takes to create the snapshot).
2) Don’t ‘promote’/restore a MySQL /datadir (fully or – even worse – partially) from a ‘shadow copy’ – also not unless your are perfectly sure about the consistence of the snapshot.

Actually I’d prefer – for production systems at least – to have the MySQL /datadir on a separate partition where ‘System Restore’ is turned of. Then ‘shadow copies’ will not be created, and mistakes in this respect are avoided.

But it would be nice IMO if Windows could manage MySQL for safe creation or ‘shadow copies’. I think it would require the steps: 1) make the server read-only 2) FLUSH everything 3) create a ‘Restore Point’ 4) make MySQL read/write again. And now add the 5th step: export/copy the ‘shadow copy’ created for the MySQL /datadir – and you will have a backup tool for MySQL on Windows not so much different from LVM-based backup tools for Linux, I believe.  However it can be done manually if you can afford a few seconds downtime:  Stop MySQL and create a ‘Restore Point’ manually from Control Panel .. System. Once completed start MySQL again and copy the ‘shadow copy’ created for the /datadir from Windows Explorer.

(finally I’d like to thank Vladislav Vaintrub from Monty Program and my colleague Vishal for clarifications to the content of this blog)

8 thoughts on “Danger Zone: MySQL and Windows/NTFS ‘Volume Shadow Copy’ Technology

  1. I have very infrequently used MySQL on Windows, but I’ve always been curious about VSS. Is this not a proper point-in-time snapshot, like an LVM snapshot? I have maintained many environments over the years where we skip the FTWRL entirely, since only InnoDB is in play and there’s no DDL during the backup window – we rely on innodb recovery to make things consistent and, aside from a MySQL bug or two, have not seen any problems. Does VSS not provide the same point-in-time guarantees?

  2. In my understanding it is not a problem with the snapshot technology (I don’t think you find it better).

    The problems are in my understanding
    1) buffers in memory not flushed to disk will not be in the snapshot. In particular a MyISAM problem (and system tables are MyISAM).
    2) that MySQL is ‘agnostic’ about Windows/NTFS ‘colume shadow copy’ technology (and vice versa).

  3. You should be able to use the VSS to do a backup/restore and PITR as long as you do a FLUSH TABLES WITH READ LOCK before the snapshot, just like you would do for a LVM or EBS snapshot. This forces unflushed buffers to disk before the snapshot can begin. You can release the lock as soon as the snapshot is completed being created. This will work for MyISAM and InnoDB databases. LVM snapshots are the most popular form of hot backup for MyISAM tables in the Linux world.

  4. @Andrew, the basic guarantee that LVM or VSS or other filesystem snapshot technology provides is that it is point-in-time . There would not make sense to name it “snapshot” otherwise .What VSS additionally has is pluggability, software vendors could implement VSS providers. E.g , given sufficient interest, one could write a VSS provider for MySQL that would just do some “flush tables with read lock” for MyISAM internally. This would make every VSS snapshot consistent, even if MyISAM is used.

  5. The ‘trick’ with flush tables with read lock is not safe for InnoDB. It only works with atomic snapshots. I’ve tried this with InnoDB data on 1 NetApp volume and MySQL data on another and it won’t work. We solved it by using 1 NetApp volume with 2 qtrees (subvolumes) and then snapshotting the volume.

    As XtraBackup doesn’t seem to be fully compatible with Windows I’d recommend MySQL Enterprise Backup to create backups. That will give a non blocking backup. On Linux using snaphots can give issues if the snapshots are not instantanious.

    • @Daniel “The ‘trick’ with flush tables with read lock is not safe for InnoDB. It only works with atomic snapshots”

      It is safe for innodb. Because snapshots are atomic. If they were not atomic they would not be called snapshots

    • Also, it is not like there would be restrictions for a single volume only. I have no idea what you tried to accomplish with NetApp tools , but if your intention was to backup multiple volumes in a single “transaction”, this is a supported functionality and the first google hit for “diskshadow” can serve as example on how to do it.

  6. “You should be able to use the VSS to do a backup/restore and PITR as long as you do a FLUSH TABLES WITH READ LOCK before the snapshot”. What about FLUSH PRIVILEGES? Should this also not be done (for completeness)? More?

    Anyway: my primary point was that those snapshots created automatically by Windows ‘System Restore’ (as well as various backup tools when running in scheduled mode) are not safe to recover MySQL data from. This would require a ‘snapshot provider’ interfacing MySQL to Windows (or vice versa) as described by wlad.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>