MariaDB backups in one line

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 TARGET folder 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 date --rfc-3339=date.
• Create the TARGET folder
• Change permissions on TARGET folder so same group and other users won’t be able to read and access it
• Now the backup:
  • List all databases
  • Ignore databases named information_schema, performance_schema and mysql
  • For each databse found, use mariadb-dump to export it to its SQL text
  • Compress and save as file name {TARGET}/{DB_NAME}.sql.gz

Put it in root user’s cron as a one line script using command crontab -e:

@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.