Whether on Linux or Windows, it’s imperative that MySQL databases be regularly backed up to prevent data loss. And any data loss is much more likely to be from human error than a system failure. But the installers for MySQL and MariaDB do not touch on database backups beyond installing the mysqldump utility. Information to be found for backing up MySQL databases pertains mostly to Linux, and the plethora of backup scripts available for Linux are generally not suitable on Windows.

To fill this gap, I created the PS-BackupMySQL package. It provides the PowerShell script Backup-MySqlDatabases to back up all user databases and the mysql system database. (Other system databases are dynamically created and do not need to be backed up.) It’s designed to be run interactively or from a Windows scheduled task to back up databases on a regular schedule. And since databases that are backed up may eventually need to be restored, the script Restore-MySqlDatabases is provided to restore databases – one, many, or all – from the backup files. Also included are Create-MySqlBackupUser which simplifies creating a backup user with only those privileges needed to back up the databases and Create-MySqlBackupTask to create a Windows scheduled task to back up the databases.

The scripts avoid using MySQL PowerShell modules or .NET assemblies as these tend to be version specific. They use the native client programs – mysql and mysqldump – ensuring the scripts will work with any version of MySQL or MariaDB.

Being able to compress the backup files is desirable to reduce disk space usage, but it’s a bit of challenge. The Compress-Archive cmdlet cannot handle files larger than 2 GB. This is a limitation built into the .NET class it uses. Another compression utility must be used. Although there are many compression utilities that could be used, I limited the choices to free, open source utilities. The scripts will look for one of the following in this order of preference and use the first one found.

  • 7ZipforPowerShell – PowerShell module based on the 7-Zip compression
  • 7-Zip – Open source Windows GUI and command-line compression utility
  • Tar – Windows command-line compression utility (added in Windows Server 2019)

Microsoft quietly slipped Tar into Windows 10 and Windows Server 2019, finally giving Windows a native command-line compression utility without a 2 GB file size limitation. It uses the libarchive compression library originally developed by FreeBSD and thus is more program ported from Unix.. It will be available on Windows Server 2019 and higher ensuring backup files can be compressed.

The databases to back up is obtained by querying the information_schema system database. As this dynamically creates the list databases, it’s not necessary to maintain a database list that must be updated every time a database is added or removed.

MySQL’s embrace of binary logging (it’s enabled by default for MySQL but disabled by default for MariaDB) has created problems for database backups. Recent versions of MySQL enable use of Global Transaction Identifiers (GTID) to support replication and information about executed transactions is included in the database backup by default. Such a backup is intended for restoring the database on a replica server, but it often fails when restored on a non-replica server. If GTID is used by MySQL, the backup script disables including information about executed transactions in the backup to ensure it can be restored. This does not apply to MariaDB which does replication in a completely different manner.

The scripts have other features which are described in the included user guide. The package is distributed as a zip file and can be downloaded from the link below.

PS-BackupMySQL