MySQL backup utility: MysqlDump

Basics

To do a manual backup a mysql database, use mysqldump with the following syntax:

    mysqldump -h hostname -u user -pPassword dbname >/path/to/file

Note that hostname is optional and will default to localhost. Depending on some settings user and password may be optional as well. Now, asssuming you are using a linux shell, if we want to have rotating logs, add the date name and stick the whole line in a script running in cron:

mysqldump -h hostname -u root mydb > /path/to/backup/$(date +%a)

Flexible Backup script

By default, mysqldump will backup all databases in a single file. While this is really helpful in the case of a disaster. In my humble opinion, it is better to backup databases individually

This is why I use the following script to backup databases individually.

As a bonus, this script appends the day of the week to maintain the last 7 days

#!/usr/bin/env bash
#backup all databases individually, appending day of the week to have 7 rotating days

USER='root'
PASS=''
DEST=""
TODAY=$(date +"%Y-%m-%d")

mkdir -p $DEST

#similar to:
#mysqldump -u root -p$pass --lock-tables=false --triggers --all-databases>all-$TODAY.sql

for dbname in $(mysql -N -u $USER -p$PASS -e "select distinct table_schema from information_schema.tables where table_schema not in ('INFORMATION_SCHEMA
','mysql','PERFORMANCE_SCHEMA')  ")
do
    mysqldump -u $USER -p$PASS $dbname >$DEST/$dbname_$(date "+%a").sql
done