First allow the Unix user that will make backups (
root, in my case) to access MariaDB without a password (works only if accessing from same host that the server is running):
GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA unix_socket WITH GRANT OPTION;
Now the backup script, shown here in multiple lines for education purposes:
TARGET=/some/folder/Backup/databases/`hostname`/`date --rfc-3339=date`; mkdir -p "$TARGET"; chmod -R go-rwx "$TARGET"; mariadb --batch --skip-column-names -e 'show databases;' | grep -E -v 'information_schema|performance_schema|mysql' | while read db; do mariadb-dump --comments --dump-date --extended-insert --routines --system=user "$db" | gzip -c --best > "$TARGET/$db.sql.gz"; done
Explained line by line:
- Define a
TARGETfolder name which includes hostname and current date as
YYYY-MM-DD. It is tempting to use other date formats via the command’s advanced options as
date +%Y-%m-%d, but be aware that “
%” has a special meaning to cron and will be interpreted as a new line (
\n), so you can’t use it. That is why we use a more semantic
- Create the
- Change permissions on
TARGETfolder so same group and other users won’t be able to read and access it
- Now the backup:
- List all databases
- Ignore databases named
- For each databse found, use
mariadb-dumpto export it to its SQL text
- Compress and save as file name
Put it in root user’s cron as a one line script using command
@daily TARGET=/some/fo...; mkdir -p...; chmod -R go...; ...
The above one-liner is identical to the shown in the beginning, but with no line breaks.
After a few days of execution, this is how database dump files will look like in my file system:
/some/folder/Backup/databases/ db1.alkalay.net/ 2023-05-11/ drupal.sql.gz nextcloud.sql.gz powerdns.sql.gz wordpress_multisite.sql.gz 2023-05-12/ drupal.sql.gz nextcloud.sql.gz powerdns.sql.gz wordpress_multisite.sql.gz
Of course, before everything, I need MariaDB commands installed in the system I run the MariaDB server. You probably already have them installed, but just in case, in my Fedora system these are the packages:
dnf install cronie mariadb-backup mariadb
I used to use Holland Backup Manager to automate MariaDB backups, but I had to come up with this script because Holland package was orphaned and removed from Fedora.
3 thoughts on “MariaDB backups in one line”
Debian has automysqlbackup package. Does the stuff with rotating dumps etc.
Good to know! Where is the source code for this software?