Having regular backups of MySQL and MariaDB databases is vitally important to ensure that data can be recovered in the event of corruption (which could be caused by a system failure or malicious software such as ransomware) or human error (such as when a user accidentally deletes a row, table, or entire database). MySQL and MariaDB provide a utility program to back up the databases – namely mysqldump (which is mariadb-dump for MariaDB 1.5 and higher) – but neither provides a program or script that allows backups to be easily automated. Aptum Technologies (my employer) provides a script allowing customers to back up the databases from a cron job. The original was a very basic script which didn’t have much flexibility. Using this as foundation, I rewrote the backup script and enhanced it over the years to improve its functionality and add features.

The script – backup-mysql – uses mysqldump to create a logical backup of each database. Each database is backed up to a separate file and then compressed using gzip (or pigz on systems with two or more processors). The database backup files are then archived to a tar file. This archive file is not compressed since the individual database backup files are already compressed. And a log file is created with information about the backup operation. Any errors that occur are recorded in the log file.

Some of features the backup script provides are:

  • Obtains a list of databases from INFORMATION_SCHEMA which eliminates the need to specify the databases to be backed up. Dynamically created system databases binlog, information_schema, performance_schema, sys, tmp and lost+found are excluded.
  • Allows databases to be excluded from the backup. A database name can be added to a list of databases to be excluded. A regular expression pattern can also be used to match databases to be excluded. Each database will be tested against both the excluded databases list and the exclusion pattern. If either matches the database, it will be excluded from the backup.
  • Can be used on failover clusters. Because the shared disk for the data directory will only be mounted on the active node, the script checks to see if the disk is mounted, in which case it is running on the active node and will proceed with backing up the databases.
  • Reads value of max_allowed_packet from the server to ensure the largest row can be backed up.
  • Records the sizes of the database backup file and compressed backup file, and records the duration for each database backup, backup file compression, and overall backup operation in the log file.
  • The tar file that archives the database backup files can use different file name formats such as time-stamp, date-stamp, and static names.
  • Sends a backup report to the specified recipient email addresses. Reports will normally be sent only if a failure occurs (or if the backup operation exceeds the specified maximum duration) but can be set to always send a backup report.
  • Handles the ongoing schism between MariaDB and MySQL. Starting with MariaDB 10.5, the backup client was renamed to mariadb-dump, but mysqldump was retained as a symlink. Starting MariaDB 11.0.1, the mysqldump symlink has been deprecated and will be removed in a future version. The script first looks for mysqldump, and if not found, looks for mariadb-dump. The first one found is used.

Included in the package is a SQL script to create a backup user with only the privileges needed to back up databases (importantly, the backup user cannot modify any data). There is also a script to generate a password for the backup user.

More information about the package is available in the readme file. The script is released under a BSD license and is free to use.

Package Download: backup-mysql