Knowledge

Backup MySQL databases in separate files

#Databases

How to dump existing MySQL databases on a server in separate backup files.

Published by Mark van Eijk on January 12, 2023 · 1 minute read

  1. MySQL backup bash script
  2. How to use

MySQL backup bash script

A clear and simple bash script to export all MySQL databases on your server into separate .SQL files.

With inline comments, this is the bash script to export all databases into separate files:

# 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
DATABASES=`
    echo "SHOW DATABASES;" |
    mysql --user="$USER" --password="$PASSWORD" |
    grep -v -E "^(${SKIP})$"
`

# Loop through databases
for DATABASE in $DATABASES
do
    # Export database into separate file
    mysqldump \
        --user="$USER" \
        --password="$PASSWORD" \
        $OPTIONS \
        "$DATABASE" \
        > ./$DATABASE$DATABASE.sql
done

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.

Subscribe to our newsletter

Do you want to receive regular updates with fresh and exclusive content to learn more about web development, hosting, security and performance? Subscribe now!

Related articles

Stream MySQL backup directly to S3 bucket

How to dump existing MySQL databases on a server in separate backup files.

Read more →

Exporting database in MySQL using command line

How to dump existing MySQL databases on a server in separate backup files.

Read more →