MariaDB Database Disk snapshot backups on Windows

Using disk snapshots to perform MariaDB backups has become more and more common, but this seems to have been limited to Linux and Cloud environments. There seems that there has been a notion that Snapshots cannot be done on native Windows, the way we can do snapshots using LVM on Linux for example. This is actually False, if you use the NTFS filesystem snapshoting is built in and has been so for a long time, the base for it is the Volume Snapshot Service (VSS) which was introduced as far back as Windows XP.

Using Snapshots to backup on Linux or Windows works well, assuming that the storage engine you use can recover on startup, the backups created this was are physically consistent but not logically, but InnoDB will recover this. Also note that any warnings about using snapshots with MyISAM also applies to the mysql database, i.e. the database metadata. Usually though, this is not an issue, but if you want to protect yourself even more then use a FLUSH TABLES WITH READ LOCK and backup the MyISAM tables separately somehow. But this is no different between Windows and Linux in this case, this is an attribute of any kind of disk snapshot backups.

VSS is a services that run on Windows and to utilize it some programming is needed, but luckily that has already been done for us, there is a simple utility called VSHADOW.EXE available from Microsoft that can be used to access the VSS service. Some version of Windows, such as Windows Server 2008, Windows Server 2012 and also with a version of Windows that is very little used, probably there are more users of Windows 3.10, than of Windows 8, come with DISKSHADOW.EXE, a utility that does the same thing, basically, but has a different interface. In Windows 7, that I tested on, there is VSSADMIN.EXE instead, but as VSHADOW.EXE can be added, I downloaded that one as it is more functional (It is part of the Windows SDK). All this mess (c) Microsoft.

So, how does this work then? As I said, this is different than on Linux (what? Windows being different than Linux? No kidding?). Using the API you can create two types of snapshots, persistent and non-persistent. The difference is that a persistent snapshot is one that remains until specifically removed, whereas a non-persistent one goes away when the program that created it exists. As we are about to take a backup here, I guess you think we want a permanent one, but that is not so, as VSHADOW can run a command file for us, before it exists.

I will use an example here, for that I have placed my MariaDB data on the E-drive in a directory called \data, which means my my.ini has this line in it:
[mysqld]
datadir=E:/Data

Next question then is where the snapshot is placed? Well, the snapshot ends up as a Windows device with a name along the lines of \\?\Volume{5b3610ce-d2cb-11e2-a649-2cd05a8ad0c9}\ which is just as easy to remember and type as the novel War and Peace (Yes, Tolstoy was a Windows guy, I guess you didn't know that!). Yes, again, thank you Microsoft, Windows is SOO user friendly! Anyway, yet again VSHADOW helps us out, as it can create a command file for us that will contain the names of the devices and some other things. It can look something like this and I have called it vss-setvar.cmd:
@echo.
@echo [This script is generated by VSHADOW.EXE for the shadow set {c8fcb322-ce02-409a-b609-7fc04311fad1}]
@echo.

SET SHADOW_SET_ID={c8fcb322-ce02-409a-b609-7fc04311fad1}
SET SHADOW_ID_1={11243eaf-0234-4784-92e1-9bc24668b5b4}
SET SHADOW_DEVICE_1=\\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy8

OK then, so this is generated for us, how do we use that? Well, what we do is create a command script that is called by VSHADOW as I said before, and this calls the generated scripts to get all the details. An example script, that I call e-backup.cmd, can look like this:
call vss-setvar.cmd
@echo off
dosdev T: %SHADOW_DEVICE_1%
robocopy T:\Data C:\Backup /MIR
dosdev /r /d T:

There are still a few missing bits here though. The call command is calling the generated file as above (you can call this anything you want, see the example command line below) and what about the command DOSDEV? What is that, that is a simple little utility you can find on the net (Google is your friend) that allows you to create a DOS device from a device descriptor, and it can also remove them. There are other ways of dealing with this, but I choose this path my my example here. Robocopy you probably know already and the /MIR option is used to tell robocopy to mirror a full directory tree.

So what the command script above will do is create a DOS device called T: for my snapshot, copy that to a directory called C:\Backup and then remove the T: device.

Now we have a command script (not two, the first one I showed was an example of a generated one) and an idea what it does, to do a shadow copy now, we just need to call vshadow, and it will do the work for us. We pass three parameters:

• The name of a script with the device names and things to generate.
• The name of a script to run
• The name of the drive to create a shadow device of.

Using the example above to create a backup of the E-drive, where I have my MariaDB datafiles in the \data directory, this is the command I use:
vshadow -script=vss-setvar.cmd -exec=e-backup.cmd E:
So that's about it, now you know how to create an online snapshot backup of a MariaDB database on Windows.

/Karlsson

Workaround for CURRENT_TIMESTAMP as default for a DATETIME value

Currently you cannot specify the current time as a default value for a DATETIME column. It works for TIMESTAMP but not for DATETIME,and even with TIMESTAMP this only work for the one timestamp column in the table, if you have more than 1 TIMESTAMP column you have to specify a default for all but the first.

This is fixed in MySQL 5.6 and MariaDB 10 which allows you to use a construct like this:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());

So what can you do if you are on MariaDB? Well, you can use a trigger to achieve a similar result:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());
delimiter //
mysql> CREATE TRIGGER td1_ins BEFORE INSERT ON td1 FOR EACH ROW
BEGIN
  IF new.c2 IS NULL THEN
    SET new.c2 = now();
  END IF;
END// 
delimiter ;

And then this works:
mysql> INSERT INTO td1 VALUES(NULL, '1960-04-10 16:45');
mysql> INSERT INTO td1 VALUES(NULL, NULL);
mysql> INSERT INTO td1(c1) VALUES(NULL);
mysql> SELECT * FROM td1;
+----+---------------------+
| c1 | c2                  |
+----+---------------------+
|  1 | 1960-01-04 16:45:00 |
|  2 | 2013-11-23 15:57:51 |
|  3 | 2013-11-23 16:00:55 |
+----+---------------------+
4 rows in set (0.00 sec)


Is this perfect? Nope, but it works while we wait for MariaDB 10.

/Karlsson