Backup MySQL databases except system databases in a single file
How to dump all MySQL databases on a server in a single file with the exception of certain (system) databases.
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.