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
su - root

#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 {} \;

liked this article?

  • only together we can create a truly free world
  • plz support dwaves to keep it up & running!
  • (yes the info on the internet is (mostly) free but beer is still not free (still have to work on that))
  • really really hate advertisement
  • contribute: whenever a solution was found, blog about it for others to find!
  • talk about, recommend & link to this blog and articles
  • thanks to all who contribute!
admin