Back up MySQL databases daily on Debian, Ubuntu and Linux

Are you running Debian, Ubuntu or another Linux distribution on your VPS, root server or dedicated server, and do you want all your MySQL and MariaDB databases backed up automatically every day? Then you are in the right place. In this guide you set up a backup script that exports every database to a compressed SQL file each night and clears out old backups on its own.

The procedure is the same on every distribution and works on Debian and Ubuntu just as it does on AlmaLinux, Rocky Linux and RHEL. The differences are not in the script, but in which database server you are running and how it lets you log in. There is a separate section further down on Debian versus Ubuntu.

Requirements

You need SSH access with root privileges and an installed MySQL or MariaDB server. We recommend the current distribution releases: Debian 13 "Trixie" and Debian 12 "Bookworm", Ubuntu 24.04 LTS "Noble Numbat" and Ubuntu 22.04 LTS "Jammy Jellyfish", plus AlmaLinux and Rocky Linux in versions 9 and 10. Older systems should no longer be used in production: Debian 10 reached its end of support in June 2024, Ubuntu 20.04 LTS in May 2025, and CentOS 7 no longer receives regular security updates either.

First bring the system up to date and install the Nano text editor if it is not there yet.

For Debian and Ubuntu:

apt update && apt upgrade -y

apt install nano -y

For AlmaLinux, Rocky Linux and RHEL:

dnf update -y

dnf install nano -y

On current systems of the RHEL family, dnf is the successor to yum. The old command usually still works there as a link, but you should use dnf.

Also plan for enough storage. Depending on the size of your databases, a week of compressed backups quickly adds up to several gigabytes. Check the free space with df -h.

Store the credentials securely

The password does not belong directly in the mysqldump call, because command lines are visible to every user on the system through ps. Create a credentials file instead that only root may read:

nano /root/.my.cnf

Contents of the file:

[client]
user=root
password=YOUR_DATABASE_PASSWORD

Then set the permissions so that only root has access:

chmod 600 /root/.my.cnf

When there is no root password at all

On Debian and Ubuntu the database account root is usually not protected by a password but by the identity of the system user. MariaDB calls this method unix_socket, MySQL calls it auth_socket. In that case no password exists at all, and an entry in the credentials file simply goes nowhere. This query shows whether that applies to your system:

mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user='root';"

If it says unix_socket or auth_socket, either run the script as the system user root and skip the credentials file entirely, or create a dedicated backup user. The second option is the better one, because it works without the full privileges of the root account:

CREATE USER 'kh_backup'@'localhost' IDENTIFIED BY 'YOUR_BACKUP_PASSWORD';
GRANT SELECT, SHOW VIEW, EVENT, TRIGGER, LOCK TABLES, RELOAD, PROCESS ON *.* TO 'kh_backup'@'localhost';
FLUSH PRIVILEGES;

In the credentials file you then enter user=kh_backup. The privileges are deliberately kept tight: reading, viewing, events, triggers and, as a fallback, locking tables that do not use InnoDB. RELOAD and PROCESS can only be granted globally, hence ON *.*. MySQL 8 needs PROCESS in order to read the tablespace information along the way. If you would rather not grant it, add --no-tablespaces to the script instead. If you cannot get in at all any more, the article Reset the MySQL and MariaDB root password will help.

Create the backup script

Now we create a Bash script that handles the export and deletes old backups. In our example it is called mysql_export_all.sh and lives in the directory /opt/mysqlbackups:

mkdir -p /opt/mysqlbackups

nano /opt/mysqlbackups/mysql_export_all.sh

Write the following content into this script:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/opt/mysqlbackups"
KEEP_DAYS=7
DATE=$(date +%Y-%m-%d-%H-%M)

mkdir -p "$BACKUP_DIR"

mysqldump --defaults-extra-file=/root/.my.cnf --all-databases --single-transaction --routines --events | gzip > "$BACKUP_DIR/alldbs_$DATE.sql.gz"

find "$BACKUP_DIR" -type f -name "alldbs_*.sql.gz" -mtime +$KEEP_DAYS -delete

The most important options at a glance:

• --defaults-extra-file reads the user and password from the file you just created. This option has to come first.
• --single-transaction produces a consistent state for InnoDB tables without locking the database.
• --routines and --events include stored procedures and scheduled events in the backup. mysqldump saves triggers by itself anyway.
• gzip compresses the export and therefore saves a considerable amount of storage.
• The find command deletes only backup files that are older than seven days. Use KEEP_DAYS to adjust the retention period.

The line set -euo pipefail matters more than it looks. Without pipefail the shell only evaluates the return value of gzip, and that is zero even when mysqldump aborted beforehand. You would end up with a technically flawless archive file containing incomplete data, and nobody would notice.

Which database server you are running depends on the distribution. Debian ships no mysql-server package and relies on MariaDB throughout, while Ubuntu offers both. The script works unchanged in every case:

Make the script executable and test it

Make the script executable:

chmod +x /opt/mysqlbackups/mysql_export_all.sh

Run it once by hand and check the result before you automate it:

/opt/mysqlbackups/mysql_export_all.sh

ls -lh /opt/mysqlbackups/

The file that appears should be considerably larger than zero bytes. The end of it says more than the beginning, though. So check whether the archive is undamaged and the export really ran through:

gzip -t /opt/mysqlbackups/alldbs_*.sql.gz

zcat /opt/mysqlbackups/alldbs_*.sql.gz | tail -n 1

The last line of a complete export starts with -- Dump completed on. If it is missing, the export was aborted and the file is worthless as a backup, even if it is several hundred megabytes in size. That one line is the fastest reliable test you have.

Set up a cron job for the daily backup

Open the cron job editor:

export VISUAL=nano; crontab -e

For a daily backup at 5 in the morning, add the following line:

0 5 * * * /opt/mysqlbackups/mysql_export_all.sh >> /var/log/mysql-backup.log 2>&1

The output then ends up in a log file, so you can trace errors later. Check with crontab -l whether the cron job was entered correctly. The job has to sit in the crontab of root. On many Ubuntu images the login as root is disabled, and there the command is sudo crontab -e. As a normal user you would otherwise create your own crontab, and the script would later fail on the credentials file under /root.

On minimal installations of the RHEL family the cron service is sometimes missing. This is how you install and enable it:

dnf install cronie -y

systemctl enable --now crond

From now on all databases are exported every night at 5 o'clock, and backups older than seven days disappear automatically. There is more on schedules and typical pitfalls in Setting up a cron job on Linux.

Restore a backup

A backup is only worth something once you can actually restore it. So test the restore deliberately at least once, ideally on a test system:

zcat /opt/mysqlbackups/alldbs_2026-07-26-05-00.sql.gz | mysql --defaults-extra-file=/root/.my.cnf

If you only want to bring back a single database, unpack the backup first and extract the matching section, or additionally back up individual databases separately with mysqldump --databases mydb. It is more convenient to write one file per database right away. To do that, replace the mysqldump line in the script with this loop:

for DB in $(mysql --defaults-extra-file=/root/.my.cnf -N -B -e "SHOW DATABASES;" | grep -Ev '^(information_schema|performance_schema|sys)$'); do
  mysqldump --defaults-extra-file=/root/.my.cnf --single-transaction --routines --events "$DB" | gzip > "$BACKUP_DIR/${DB}_$DATE.sql.gz"
done

The three excluded databases are views onto internal server state and cannot be restored in any meaningful way. Adjust the search pattern in the find command as well, otherwise it will no longer clean up the new file names. For a complete move to another server, Move WordPress to a new server walks through the procedure with a practical example.

Keep backups off the server

Backups that sit only on the same server are no help when the entire system loses its data. So copy the files to a second destination as well, for example with rsync or scp to another server or to backup storage:

rsync -avz /opt/mysqlbackups/ user@backuptarget:/path/to/backup/

Simply add this command at the end of your script and the transfer runs along automatically. For this to work in the cron job without a prompt, root needs an SSH key without a passphrase whose public part is stored on the target system. How to create one is described in Connecting to your server via SSH.

Differences between Debian and Ubuntu

Both systems use apt, both keep the configuration under /etc/mysql/, and the script above runs unchanged on both. Even so, there are six points where they differ, and every single one of them can make a backup fail silently:

Package names and tools

If you back up the database from another machine, all you need there is the client package. On Debian it is called mariadb-client, on Ubuntu with MySQL 8 it is called mysql-client-8.0. For a script that should run on both systems there is the meta package default-mysql-client: on Debian it points to the MariaDB client and on Ubuntu to the MySQL client.

As of MariaDB 10.5 the tools also carry a name with the prefix mariadb-, and as of MariaDB 11 that name is the real one while mysqldump is only a link to it. Both calls work there. On an Ubuntu system with MySQL 8, by contrast, only mysqldump exists, and a script using mariadb-dump ends there with command not found. For scripts that need to run in both worlds, mysqldump is therefore the right call.

Maintenance account and authentication

On Ubuntu the mysql-server package still creates the maintenance account debian-sys-maint and writes its credentials to /etc/mysql/debian.cnf. That file is readable only by root and can be used directly for a backup without any further preparation:

mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases --single-transaction --routines --events | gzip > /opt/mysqlbackups/alldbs.sql.gz

On Debian with MariaDB from version 10.4 onwards, this account is no longer created. The file usually still exists there, but as a rule it only points to root through the socket. So do not assume that a script which runs through this file on Ubuntu does the same on Debian. You can check it in one step:

mysql --defaults-file=/etc/mysql/debian.cnf -e "SELECT current_user();"

MySQL 8 creates new accounts with the caching_sha2_password method, MariaDB with mysql_native_password. For an export over the local socket this makes no difference, but it does matter when you back up over the network from an older client. If that client reports "The server requested authentication method unknown to the client", it is too old for MySQL 8 and should be updated.

AppArmor on Ubuntu

On Ubuntu, AppArmor is active out of the box and restricts the database server, meaning the mysqld or mariadbd process. It does not restrict the mysqldump tool, and that difference decides whether you run into the trap at all.

The export in this guide writes the file through the shell (| gzip > ...), so under the user who runs the script. This path is not affected by AppArmor and works in any directory the user is allowed to write to. As soon as the server itself writes, however, different rules apply. That is the case with mysqldump --tab=/path and with every SELECT ... INTO OUTFILE, because there the server process creates the file, not your shell. Two independent locks then take effect: the server variable secure_file_priv and the AppArmor profile of the server. On Ubuntu the variable is set to /var/lib/mysql-files/ out of the box, and exactly that directory is also allowed in the AppArmor profile. A target path such as /opt/mysqlbackups therefore fails twice over.

The tricky part is the troubleshooting. The first lock reports itself clearly with ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. The second one, by contrast, shows up as a plain Errcode: 13 "Permission denied", even though the owner and permissions of the target directory look perfectly fine at first glance. Anyone who then starts handing out file permissions is looking in the wrong place. In that case, check both:

mysql -e "SHOW VARIABLES LIKE 'secure_file_priv';"

aa-status | grep -Ei 'mysqld|mariadbd'

journalctl -k | grep -i 'apparmor.*DENIED' | tail -n 20

If the last command turns up a line with apparmor="DENIED" and your target path, you have found the cause. The simplest approach is not to let it come to that and to stay with the export through the pipe. If you really do need server-side writes, write to /var/lib/mysql-files/ and move the file afterwards. Only when neither option works should you extend the profile with the additional path. The file /etc/apparmor.d/local/usr.sbin.mysqld is meant for that, with MariaDB accordingly usr.sbin.mariadbd, followed by systemctl reload apparmor. Switching AppArmor off is not a solution, it removes a protective layer that secures exactly this server.

Common errors and solutions

The backup is 0 bytes: Usually the credentials in /root/.my.cnf are wrong. Test the login with mysql --defaults-extra-file=/root/.my.cnf -e "SHOW DATABASES;". Often the reason is the case described above, where the account works through the socket and a stored password therefore does not fit at all.

"Access denied" in cron, but not on the console: The cron job runs under a different user than expected. Enter the job in the crontab of root and use only absolute paths in the script. Fix MySQL Access denied for user collects further causes.

Unknown table 'COLUMN_STATISTICS' in information_schema: You are backing up a MariaDB database with the mysqldump from MySQL 8, for example from an Ubuntu machine. During the export MySQL 8 queries a table that does not exist in MariaDB. Append --column-statistics=0 to the call. Only the MySQL client knows that option, so on a pure MariaDB system you must not set it.

Access denied; you need (at least one of) the PROCESS privilege(s) for this operation: Under MySQL 8 the backup user is not allowed to read the tablespace information. Grant PROCESS as described above, or add --no-tablespaces.

mariadb-dump: command not found: The script comes from a Debian system with MariaDB 11 and now runs on Ubuntu with MySQL 8. That name does not exist there. Write mysqldump, which works on both systems.

Can't connect to local MySQL server through socket: The server is not running, or the socket is at a different path than expected. Fix MySQL socket errors describes the possible causes.

SELinux blocks the script (AlmaLinux, Rocky Linux, RHEL): Use ausearch -m avc -ts recent to check whether an access denial was logged, and adjust the context of the backup directory if necessary.

The disk fills up: Reduce KEEP_DAYS, or move older backups to external storage. Clean up a full disk on Linux shows what else eats up space.

Error message about --single-transaction with MyISAM tables: This option only takes effect with InnoDB. For MyISAM tables you can use --lock-tables instead, which locks the tables for the duration of the export. Also keep in mind that on MariaDB the system tables live in the Aria engine and are not covered by --single-transaction.

While you are working on the database anyway, take care of the rest at the same time: remove anonymous users, drop the test database and disable remote access for root. Secure MariaDB and MySQL explains how.