Backup (mysql dump) all your MySQL databases in separate files

Sometimes we would like to dump all the MySQL databases. MySQL provides an easy solution to this problem:

mysqldump -u root -p --all-databases > all_dbs.sql

However this will dump stuff into one file. How to dump all databses into separate files? Well, here is my solution. A small bash script:

#! /bin/bash

TIMESTAMP=$(date +"%F")

mkdir -p "$BACKUP_DIR/mysql"

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"

Be aware, that in order to execute this script from cron, you need to store password in it (so cron won't be prompted to provide a password). That's why, you should not use a root account. Instead just create a new user only for backups, with following privileges:


Categories: Hosting, Linux, Software


  1. Hi boy! Thanks! Very useful :-)

  2. Hi guys,

    Very nice idea, very creative and I just used it to backup a server.
    I do have to point out a few errors on the script.
    There are 2 constants you forgot to define, MYSQL and MYSQLP_DUMP

    Nice touch with the TIMESTAMP folder, but you need to then mkdir -p $BACKUP_DIR

    Here is my version of the script after the fixes.


    TIMESTAMP=$(date +"%F")

    mkdir -p $BACKUP_DIR

    databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

    for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.gz"


  3. Updates. Thanks for response!

  4. I highly recommend you to store the password in the home directory of the user and never specify the password on the command line. This way you can securely run mysqldump as root without the password being exposed in the process list.
    First create a .my.cnf in the home dir of root and make sure only root can read or change it by running the following three commands:
    `sudo touch /root/.my.cnf`
    `sudo chown 0:0 /root/.my.cnf`
    `sudo chmod 600 /root/.my.cnf`

    then make sure it contains something like this:
    # cat /root/.my.cnf


    You could also do this for your “normal” mysql account so you never have to type your password again and still be secure. Just put a .my.cnf in your own home di
    rectory and make sure only you are able to read and write to it (chmod 600).

    Aside from that, here’s another take on a per-database backup script that prevents warnings on mysql.event that popped up in recent versions of mysql:

  5. The current script tries to put the actual backup within “$BACKUP_DIR/mysql” but it only uses “mkdir -p $BACKUP_DIR” to create the backup directory, hence it fails.

  6. Good point! Sorry for that :/ probably when I was copy-pasting that from server and “cleaning” to put here, I removed the mysql dir. Thanks again!

  7. Scripts is Super.. How to modify this script for incremental backup…

  8. Thanks a lot

  9. Amazing idea, But you missed some points and i added it to my script:

    ! /bin/bash

    TIMESTAMP=$(date +”%F”)
    find /backup/mysql_dump/ -maxdepth 1 -type d -mtime +7 -exec rm -f {} \;

    mkdir -p “$BACKUP_DIR/”

    databases=`mysql –defaults-file=/root/.my.cnf -e “SHOW DATABASES;” | grep -Ev “(Database|information_schema|performance_schema)”`

    for db in $databases; do
    mysqldump –defaults-file=/root/.my.cnf –databases $db | gzip > “$BACKUP_DIR/$db.gz”

  10. Hi,

    Thanks a lot for all, it worked.

    But I needed to add to the grep databases “performance_schema” because I got trouble with the locks. Now it works fine.

    Thx again!

  11. I needed to dump the db’s to another users’ home folder and therefore change the ownership of the dir and files.

    My version:

    #! /bin/bash

    TIMESTAMP=$(date +"%F")

    mkdir -p "$BACKUP_DIR"

    databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

    for db in $databases; do
    $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.gz"

    databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

    for db in $databases; do
    $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"

    chown -R $LINUX_USER "$BACKUP_DIR"

  12. What is the best way to log the output?

  13. mysqldump -u root -p –all-databases – when i using this command, where i find db after that ?? helpme. plz

  14. Maciej Mensfeld

    January 26, 2015 — 22:41

    I don;t use –all-databases because it dumps all of them into one file

  15. You need to specify a file i.e
    mysqldump -uroot -p –all-databases > /path/to/file

  16. hi but I want hello friends.. does anybody tell how to take database backup between dates?

  17. Worked great! Thank you :)
    I removed a few things as I just needed a quick dump and not a chron. Worked like a charm!

    #! /bin/bash
    TIMESTAMP=$(date +"%F")
    databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
    for db in $databases; do
    $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$db.gz"

  18. Just take the above example or follow the link given. If you need them not date based but rather twice a day, etc. simply adjust the timestamp value to include hours and ofcourse set your cron job accordingly.

  19. About using a specific user instead of root: very interesting hint. It may be useful to include SQL statements for that.

    (NB: I have taken for granted that the privileges listed in the post are actually the minimum needed for the script to run correctly – I still haven’t checked this fact)

    Create user with password “changeme”:

    CREATE USER `backupusr`@`localhost` IDENTIFIED BY ‘changeme’;
    GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* TO `backupusr`@`localhost`;

    Change the password to “newpass”:

    SET PASSWORD FOR `backupusr`@`localhost` = PASSWORD(‘newpass’);

  20. Thanks for that

  21. Maciej Mensfeld

    December 11, 2015 — 11:40

    glad I could help! :)

  22. Hartverdrahtet

    January 8, 2016 — 19:42

    Thanks a LOT for this post, mate… You are seriously up on my “would-buy-you-a-beer” list now ;-))

  23. Simon Williams

    March 3, 2016 — 10:54

    Use -N (–skip-column-names) instead of grepping out the “Database” header

  24. Don’t forget to escape the variables! If you have a password with a space it will break for example.

  25. Adrian Miller

    May 31, 2016 — 15:36

    If you have views, also need to GRANT SHOW VIEW to get complete dataset

  26. Is there some parameter to split the database into individual files, that is, one SQL file per table and get all those files inside a ZIP file? I know phpmyadmin can do that, but it fails for large databases.

  27. Please help how to sent mail after the back up is completed

  28. Hi this script is super.. to backup DB as separate files.
    But not able to restore the DB, no tables get restored, please help

    mysql –host= -u root -p TestDB < DB.sql

Leave a Reply

Your email address will not be published. Required fields are marked *


Copyright © 2024 Closer to Code

Theme by Anders NorenUp ↑