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
- SHOW VIEW
June 11, 2013 — 12:59
Hi boy! Thanks! Very useful :-)
July 1, 2013 — 22:08
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!
July 1, 2013 — 22:12
Updates. Thanks for response!
July 24, 2013 — 02:09
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
October 17, 2013 — 11:06
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.
October 17, 2013 — 11:08
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!
October 25, 2013 — 16:39
Scripts is Super.. How to modify this script for incremental backup…
February 1, 2014 — 10:22
Thanks a lot
February 4, 2014 — 20:06
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
February 28, 2014 — 00:02
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!
July 1, 2014 — 12:38
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
November 9, 2014 — 22:47
What is the best way to log the output?
January 25, 2015 — 21:49
mysqldump -u root -p –all-databases – when i using this command, where i find db after that ?? helpme. plz
January 26, 2015 — 22:41
I don;t use –all-databases because it dumps all of them into one file
March 10, 2015 — 15:22
You need to specify a file i.e
mysqldump -uroot -p –all-databases > /path/to/file
June 25, 2015 — 06:46
hi but I want hello friends.. does anybody tell how to take database backup between dates?
August 3, 2015 — 07:02
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
August 12, 2015 — 11:27
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.
August 14, 2015 — 15:31
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;
December 10, 2015 — 21:40
Thanks for that
December 11, 2015 — 11:40
glad I could help! :)
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 ;-))
March 3, 2016 — 10:54
Use -N (–skip-column-names) instead of grepping out the “Database” header
May 3, 2016 — 13:17
Don’t forget to escape the variables! If you have a password with a space it will break for example.
May 31, 2016 — 15:36
If you have views, also need to GRANT SHOW VIEW to get complete dataset
March 28, 2017 — 00:15
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.
September 21, 2017 — 07:42
Please help how to sent mail after the back up is completed
November 24, 2017 — 13:59
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=192.140.12.212 -u root -p TestDB < DB.sql