backing up a mysql or mariadb database can be done with mysqldump.

imho i would backup the whole database, but i would also advise to backup every database seperately, so you can restore them separately, if you maybe do not need the whole database but only parts of it.

this will export files.sql that are basically text-files / commands-instruction plus data for mysql – if the data is text based (no binary blobs) it compresses.gzip well.

# become root
sudo bash;

#create directories
mkdir -p /backup/mysql

# backup every database manually separately
mysqldump -u root DATABASE_NAME > /backup/DATABASE_NAME.sql;

# backup every database separately automatically (easier to restore)
for db in $(mysql -u root -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backup/mysql/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d).sql.gz"; done

# if you need hour minute and seconds when your backup was done go:
for db in $(mysql -u root -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backup/mysql/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d-%H.%M.%S).sql.gz"; done

so your whole backup script could look like this:

vim /scripts/backup_databases.sh

#!/bin/bash
echo "===== backup all databases (just in case)"
mysqldump -u root --all-databases | gzip > "/backup/mysql/mysqldump-$(hostname)-all-$(date +%Y-%m-%d).sql.gz";

echo "===== backup user database seperately"
mysqldump -u root mysql user | gzip > "/backup/mysql/mysqldump-$(hostname)-users-$(date +%Y-%m-%d).sql.gz";

echo "===== backup every database separately (easier to restore) "
for db in $(mysql -u root -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backup/mysql/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d).sql.gz"; done

echo "===== delete all backups older than 14 days to avoid overfilling harddisk"
find /backup/mysql/mysqldump-* -mtime +14 -exec rm {} \;
admin