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")
BACKUP_DIR="/backup/$TIMESTAMP"
MYSQL_USER="backup"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="password"
MYSQLDUMP=/usr/bin/mysqldump

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"
done

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:

  • SHOW DATABASES
  • SELECT
  • LOCK TABLES
  • RELOAD
28
Apr 2013
POSTED BY
POSTED IN Hosting Linux Software
DISCUSSION 28 Comments
  • Pol

    Hi boy! Thanks! Very useful :-)

  • 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.

    #!/bin/bash

    TIMESTAMP=$(date +"%F")
    BACKUP_DIR="/home/backup/$TIMESTAMP"
    MYSQL_USER="root"
    MYSQL_PASSWORD="*******"
    MYSQL=/usr/bin/mysql
    MYSQLDUMP=/usr/bin/mysqldump

    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"
    done

    Cheers!

  • Updates. Thanks for response!

  • 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
    [mysql]
    user=root
    password=something

    [mysqldump]
    user=root
    password=something

    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:
    https://gist.github.com/timkuijsten/6067107

  • Pingback: MySql: Backup Databases | Ricardo Malla's Blog()

  • Pingback: MySQL – backup all database to separate files | Spyros' Ioakim Blog()

  • 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.

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

  • Arun

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

  • Pingback: First foray into Python()

  • Ehsan

    Thanks a lot

  • Amr Elkhedewy

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

    ! /bin/bash

    TIMESTAMP=$(date +”%F”)
    BACKUP_DIR=”/backup/mysql_dump/$TIMESTAMP”
    #MYSQL_USER=”root”
    #MYSQL=/usr/bin/mysql
    #MYSQL_PASSWORD=”pi8Eqd$tt9ksuvB”
    #MYSQLDUMP=/usr/bin/mysqldump
    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”
    done

  • Ruben

    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!

  • Jon

    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")
    BACKUP_DIR="/home/someuser/Documents/mysql_backups/$TIMESTAMP"
    MYSQL_USER="db-user"
    LINUX_USER="someuser"
    MYSQL=/usr/bin/mysql
    MYSQL_PASSWORD="my-password"
    MYSQLDUMP=/usr/bin/mysqldump

    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"
    done

    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"
    done

  • mike

    What is the best way to log the output?

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

  • Maciej Mensfeld

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

  • Luke

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

  • Paddy

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

  • 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")
    BACKUP_DIR="/"
    MYSQL_USER="username"
    MYSQL=/usr/bin/mysql
    MYSQL_PASSWORD="password"
    MYSQLDUMP=/usr/bin/mysqldump
    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"
    done

  • Orange

    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.

  • andrew

    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`;
    FLUSH PRIVILEGES;

    Change the password to “newpass”:

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

  • Pingback: Pipe multiple files into one tar gz - TecHub()

  • Miguel Nunes

    Thanks for that

  • Maciej Mensfeld

    glad I could help! :)

  • Hartverdrahtet

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

  • Simon Williams

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

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