mysql differential/incremental backup in windows

Question

I have done different type of backup a lot many times before, but I did everything in LINUX, because thats what people use normally :P.

This time its a new app and it runs on client system independently and its windows. So xtrabackup/percona of no help now :(.

And I am not i a favor of using binLog for differential/incremental backup as, to me its both risky and time consuming.

Can any of you please help me out with a reliable option by which I can perform incremental backup in a windows system.( I can not purchase a tool for backup for every system our app will be used).

Answer 1

There is a way of running Percona XtraBackup given that you are familiar with that tool. Although Percona don't plan to create a version that is native to windows, you can run Percona Xtra Backup in a Docker container.

In summary, once you have set up Docker and given it the necessary access, you can run Percona XtraBackup from within the container and it will write the backups to a folder in your C drive.

The full information can be found in this blog post: https://www.percona.com/blog/2017/03/20/running-percona-xtrabackup-windows-docker/

I give you that reference rather than repeating how-to in full because if there are any updates to the procedure then that post is likely to be updated before this answer. I hope this helps.

Disclosure: I work for Percona

Answer 2

There are three ways how you can perform incremental backups on Windows:

Binglog backups

You need to copy new binlog files from time to time to your destination place. This is not very difficult, and in it can be achieved with a simple script.

The main drawback: is a longer recovery time.

mysqlbackup

MySQL Enterprise Edition includes a backup utility: mysqlbackup. It allows you to make physical backups of MySQL, as well as incremental backups. This utility is similar to Percona backup. More details are in the official documentation.

The main drawback: is the price.

XtraBackup in a Docker container

XtraBackup can be run under Windows using Docker. Just map /var/opt/mysql to directory with DB files in Windows C:\Program Files\MySQL\MySQL Server 5.6\data. It is a good alternative to mysqlbackup

The main drawback: is difficulties when using Docker on Windows.

More details about the way how MySQL Incremental backups can be performed please find in this blog post.

--

Choose what you like. For a small database, I would choose a binlog backup - unlike the physical backup that XtraBackup and mysqlbackup do. Binlog backup is a logical backup, binlogs are less "capricious" when restoring, and their redundancy can be fixed by compression.