Backup MySQL databases except system databases in a single file - Rocketeers

  [ Rocketeers ](/)   

[Login](https://rocketeersapp.com) 

 On this page

 Knowledge
---------

Backup MySQL databases except system databases in a single file
===============================================================

### [\#Databases](https://rocketeersapp.com/databases)

How to dump all MySQL databases on a server in a single file with the exception of certain (system) databases.

 Published by [Mark van Eijk](https://rocketeersapp.com/author/mark-van-eijk) on January 12, 2023 
Updated on September 18, 2024 · 1 minute read

1. [MySQL backup bash script](#content-mysql-backup-bash-script)
2. [How to use](#content-how-to-use)
3. [Related](#content-related)
4. [Let Rocketeers handle it](#content-let-rocketeers-handle-it)

[\#](#content-mysql-backup-bash-script "Permalink")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

```

[\#](#content-how-to-use "Permalink")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`.

[\#](#content-related "Permalink")Related
-----------------------------------------

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

[\#](#content-let-rocketeers-handle-it "Permalink")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](/features/safely-backup-files-and-databases) on a schedule, streamed straight to cloud storage.

### 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!

  Fill in your email address to receive updates  Subscribe 

#### More in [\#Databases](https://rocketeersapp.com/databases)

- [Stream MySQL backup directly to S3 bucket](https://rocketeersapp.com/stream-mysql-backup-s3-bucket)
- [Importing database in MySQL using command line](https://rocketeersapp.com/import-database-mysql-command-line)
- [Export MySQL database using command line](https://rocketeersapp.com/export-database-mysql-command-line)
- [How to upgrade MySQL 5.7 to 8.0 on Ubuntu](https://rocketeersapp.com/upgrade-mysql-5-7-to-8-0-ubuntu)
- [Backup MySQL databases in separate files](https://rocketeersapp.com/backup-mysql-databases-separate-files)
- [Backup MySQL databases in single file](https://rocketeersapp.com/backup-mysql-databases-single-file)

 [View all 20 articles →](https://rocketeersapp.com/databases)