Percona XtraBackup for Windows

Don’t Try This at Home!

Disclaimer: The procedure described in this blog post is not officially supported by Percona XtraBackup. Use it under your responsibility. I tested and used it successfully, but your mileage may vary.

Note from the author: Wikipedia defines clickbait as text (or a link) that is designed to attract attention and to entice users to read that online content, with a defining characteristic of being deceptive. Maybe the headline of this post is a bit deceptive, as there is no Percona XtraBackup for Windows, but I hope you will not feel deceived after reading this blog post, I just felt that it was funny to use a clickbait-style while writing it.

He Couldn’t Believe it When He Read That Post!

Pep had to migrate a client database from Windows to Linux and was considering all the available options to move the database with the minimal downtime possible. He found this blog post, Running Percona XtraBackup on Windows … in Docker from Vadim Tkachenko that describes a procedure to backup a Windows database using Percona XtraBackup and Docker.

There is also the post Running XtraBackup on Windows using WSL, which describes a similar process using Windows Services for Linux.

Out of an old version, compiled using Cygwin, and an inactive effort to build a Windows version Percona XtraBackup 1.6 for Windows “try me” edition, the only way to use XtraBackup to copy a database running on Windows involved some sort of virtualization. Was it possible to use Percona XtraBackup without involving virtualization?

Datafiles Then And Now: How They’ve Changed!

To perform a backup, XtraBackup does a lot of different things.

It connects to the database and retrieves information about it, and executes commands that are required during the process.

Then it copies all the datafiles and InnoDB redo logs. As the contents of the datafiles change during the backup, the copy is inconsistent. This is why the contents of redo logs are copied also. They are used to turn the inconsistent backup copy into a consistent one during the “prepare” stage.

A detailed description of this process is beyond the scope of this post, you just need to know that XtraBackup needs logical access to the database and physical access to the database files. Feel free to look at the documentation or watch my recent Percona Live talks about Percona XtraBackup if you need more information:

Dr. XtraBackup or: How I Learned to Stop Worrying and Love Backups I
Dr. XtraBackup or: How I Learned to Stop Worrying and Love Backups II

This DBA Wanted to Make a Backup and Did The Most Incredible Thing!

Could we execute XtraBackup from a remote Linux box to perform a backup of a Windows database? Regarding the database connection, you don’t need to connect locally to the database to perform a backup, it can be done remotely.

Could we access the database files remotely? The answer is yes, of course. You just need to create network shares for the folders that contain the database files. And then mount them on Linux to execute the backup there.

This cute network share will melt your heart!

Usually, you need to share only the “datadir” but, if the database you want to backup has the redo log files located in a different directory, then you’ll need to share that directory also.

If binary logging is enabled, XtraBackup also needs access to the directory where binary logs are located. So, in the best case, you need to create only one network share, otherwise, you will need to share every directory that contains database files.

My recommendation is that you create the Windows shares with read-only permissions, to avoid writing to them by mistake.

Once you create the network shares, you need to mount them on the Linux box that will effectively run the backup, for example:

The filesystem type is CIFS. Use the highest version supported by your server. You should see something like this if you run the mount command without parameters:

Check the mount options for the credential file and other parameters. Do not include the credentials in the mount command!

This is Why Configuration Parameters Exist!

Now we have access to the database, using database credentials. And we have access to the files, using the shared folders. But we need to tell XtraBackup how to access them.

To perform a backup you need a database user with the following privileges:

• RELOAD and LOCK TABLES (unless the –no-lock option is specified) to be able to execute “FLUSH TABLES WITH READ LOCK” and “FLUSH ENGINE LOGS” before starting to copy the files.
• LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP are privileges required to use backup locks.
• REPLICATION CLIENT to obtain the binary log position.
• CREATE TABLESPACE to import tables.
• PROCESS to run SHOW ENGINE INNODB STATUS and to see all the threads running on the server.
• SUPER to start/stop the replica threads in a replication environment (optional), and create, delete and select privileges on the PERCONA_SCHEMA (if it exists) to store incremental and history data.

Usually, we tend to grant all privileges to the user that will connect to the database to run the backup.

You can tell XtraBackup how to connect to the database using these parameters:

For example:

Remember to always use the –strict option, this will make xtrabackup fail if you make a typo and a parameter is not specified properly. And if there is any relevant InnoDB parameter that has been modified, then you can add it also as a parameter to xtrabackup (–innodb_log_file_size in the example)

He Thought It Was Over, but It Was Not!

Once we have the backup stored in the Linux server, we can prepare it following the standard procedure:

But now that we have a backup prepared and stored in Linux, is it possible to start that database on Linux? Yes! It is possible!

You only need to make sure that the database starts with lower_case_table_names enabled as the database comes from a case-insensitive filesystem.

And, if you enable binary logging in the new server, you may need to manually edit the binlog index file to replace the \ by a / as this is the directory delimiter on Linux.

I hope you will be able to use this procedure successfully and, as always, test restoring your backups frequently!