Backup MySQL databases except system databases in a single file

1. MySQL backup bash script
2. How to use
3. Related
4. Let Rocketeers handle it

#MySQL backup bash script

A clear and simple bash script to export all MySQL databases on your server into a single backup file, excluding databases you don't want to backup.

With inline comments, this is the bash script to export all databases into a single file with exception of certain databases:

# MySQL username & password
USER=""
PASSWORD=""

# MySQL dump options
OPTIONS="--add-drop-table --extended-insert --single-transaction --skip-comments"

# Skip exporting these databases, separated by "|"
SKIP="Database|mysql|sys|information_schema|performance_schema"

# Get all databases on server, except the skipped ones
DATABASES=`
    echo "SHOW DATABASES;" |
    mysql --user="$USER" --password="$PASSWORD" |
    grep -v -E "^(${SKIP})$" |
    tr '\n' ' '
`

# Export database into a single file
mysqldump \
    --user="$USER" \
    --password="$PASSWORD" \
    $OPTIONS \
    --databases $DATABASES \
    > ./databases.sql

#How to use

You can use this by copying the script, save it in an backup.sh file and then execute chmod +x ./backup.sh to give the file execution permissions.

Execute the script with ./backup.sh.

#Related

Prefer a different layout? You can also backup all databases into a single file or backup each database into separate files. To keep backups off the server itself, stream the backup directly to an S3 bucket.

#Let Rocketeers handle it

Rolling your own backup scripts works, but they're easy to forget and easy to break. Rocketeers can safely back up your files and databases on a schedule, streamed straight to cloud storage.