Backup multiple MySQL databases into separate files
| 16-Nov-2005 | Posted by Sonia Hamilton under MySQL |
Update: want to know how to do this on Windows? See my post Backup multiple MySQL databases into separate files – Windows.
#!/bin/bash
# sonia 16-nov-05
# backup each mysql db into a different file, rather than one big file
# as with --all-databases - will make restores easier
USER="root"
PASSWORD="secret"
OUTPUTDIR="/var/lib/bacula"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
# clean up any old backups - save space
rm "$OUTPUTDIR/*bak" > /dev/null 2>&1
# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
-e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
# dump each database in turn
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
--databases $db > "$OUTPUTDIR/$db.bak"
done
Share This
Very nice script, thanks for posting it. :)
Very nice and works first time.
You’re welcome!
Hi,
Excellent.Nice article thanks for sharing.
how can i import these backup files back to mysql server all at once?
or i have to import then one by one? or i can issue this command
mysql -u user -ppassword -h mysqlhost < *.bak
?
How about something like:
A good idea is also to modify the line to show the databases to avoid the “information_schema” DB which we don’t want to back up…you get a MySQL error if you try to.
databases=`$MYSQL –user=$USER –password=$PASSWORD -e ‘SHOW DATABASES;’ | tr -d ‘| ‘ | grep -Ev ‘(Database|information_schema)’`
More good news! Your whole “tr” part is useless in fact :p I’m quoting the MySQL manual:
The default output format is different (more concise) when you run mysql in batch mode than when you use it interactively. For example, the output of SELECT DISTINCT species FROM pet looks like this when mysql is run interactively:
+———+
| species |
+———+
| bird |
| cat |
| dog |
| hamster |
| snake |
+———+
In batch mode, the output looks like this instead:
species
bird
cat
dog
hamster
snake
So you just don’t need the whole tr part. Finally, I’m using your updated script this way, by compressing the output too:
#Clean up any old backups
rm -rf $OUTPUTDIR/*
#Get a list of databases
databases=`$MYSQL –user=$USER –password=$PASSWORD -e ‘SHOW DATABASES;’ | grep -Ev ‘(Database|information_schema)’`
#Dump each database in turn
for db in $databases; do
$MYSQLDUMP –opt –hex-blob –force –user=$USER –password=$PASSWORD $db | gzip > $OUTPUTDIR/$db.gz
done
Hey, thanks for all your comments! I’ve left them there in full so ppl can see your suggestions. Sonia.
[...] modified a MySQL backup shell script from Sonia Hamilton, and these scripts could both be modified to work with nix, but they are mainly for Windows. Read [...]
Thanks for this guys!!
I’ve just modified a version of this to work on our system and back up our DBs.
K
Thank you very much
This one worked for me.
Have a nice day!
[...] Backup multiple MySQL databases into separate files « sonia hamilton – life on the digital bikepa… (tags: mysql backup bash) [...]
Isn’t this line missing a $ before OUTPUTDIR?
rm “OUTPUTDIR/*bak” > /dev/null 2>&1
Thanks!
Awesome worked with a few tweaks ! ! ! :)
Thanks for the script, very helpful.
Is there anyway to use the same for windows server?
I’m having following in mysql-backup.bat file to take backup
@echo off
echo Running dump…
D:wampbinmysqlmysql5.0.51bbinmysqldump -u root -p –result-file=D:backup_newbackup_%DATE:~10,4%%DATE:~7,2%%DATE:~4,2%_%time:~0,2%%time:~3,2%%time:~6,2%.sql –all-databases
Pls advise the way to seperate this backup in to all seperate database.
Hi!
Thank you Sonia for the great script. I made some enhancements to the script, you can find my article here: http://carrotplant.com/en/blog/how-to-dump-all-mysql-databases-into-separate-files
Best regards,
Daniel
You’re welcome Daniel, and thanks for writing the better script! Sonia.
Had to make some slight mods for OSX, it didn’t like the new lines mid command. Take out the new lines,, gravy. Thanks! Very helpful script. Going into the toolbox.
-Sean
You’re welcome Sean! I use git to manage my ‘toolbox’ – means I can easily sync it, even on corporate machine with strong firewalls – can sync to a USB key :-)
Just wanted to chime in my thanks on this, I now have mysql backups happening every day.. Although there were a few tweaks us BSD users have to make and a few BASH changes for it to run properly.. It ended up looking like this:
databases=$(/usr/local/bin/mysql -u$USER -p$PASSWORD -e “SHOW DATABASES;” | grep
-Ev “(Database|information_schema)”)
#Dump each database in turn
for db in $databases; do
echo “Now Dumping $db”
$MYSQLDUMP -u$USER -p$PASSWORD $db | gzip > $OUTPUTDIR/$db.gz
done
Thanks very much for those ‘tweaks’!
Sorry for the late reply – I was on holidays and didn’t notice I had problems with my mail server :-/
[...] go out to Sonia Hamilton, as her blog is where I found the original version of this script, however it did need a fair bit [...]
Thanks for linking!
Sorry for the late reply – I was on holidays and didn’t notice I had problems with my mail server :-/
6 years have passed, and this script still rocks!
Thank you Sonia!
Dotan
:-D
Thank you very much Sonia, you saved my life :)
You’re welcome :-)
replace
| tr -d “| ” | grep -v Database`
by
mysql -BN -e etc …
Yes, that’s a nice way of doing it.
Sorry for the late reply – I was on holidays and didn’t notice I had problems with my mail server :-/
Very nice, just what I was looking for!
Obviously you need to have the backslashes in place for the commands to work.
EG:
databases=`$MYSQL --user=$USER --password=$PASSWORD \
-e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
Thanks, fixed!
Sorry for the late reply – I was on holidays and didn’t notice I had problems with my mail server :-/
Sonia, thank you for this. I just found it and was able to cut a backup script of a system I inherited from 274 lines to only 165. (a mysqldump line for each database) :)
Gracias! y saludos desde Puerto Rico :)
Gracias Rubén, es un placer. Algún día iré a Costa Rica, dicen que es un país muy lindo!
Saludos desde Sydney, Australia :-)
hello Sonia
your script worked perfectly. Just did a small change so that output files will be in gz format.
thanks for script
http://www.cogitsolutions.com/blog/2012/03/mysql-database-backup-multiple-database-in-seperate-files/
Thanks Prasanna, you’re welcome!
Hi,
Does any one have a version of this for windows?
Would be greatly appreciated.
Thanks
Hi Dan, I might get around to writing one for Windows in a few weeks, since lot’s of people ask for it :-)
:: MySQl DB user
set dbuser=xxxx
:: MySQl DB users password
set dbpass=xxxx
:: Switch to the MySQL data directory and collect the folder names
pushd “C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data”
:: Loop through the folders and use the fnames for the sql filenames, collects all databases automatically this way
echo “hello”
echo “Pass each name to mysqldump.exe and output an individual .sql file for each”
FOR /D %%F IN (*) DO (
“C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe” –user=%dbuser% –password=%dbpass% –databases %%F > “C:\SQLBackup\%%F.%backupdate%.sql”
)
Great, thanks Dan!
[...] ago I wrote a post on how to Backup multiple MySQL databases into separate files (Linux). I get a lot of questions about how to do the same on Windows; here’s a script [...]
[...] Referência: snowfrog.net [...]
Hi Sonia.. Clean concise script..
I use your script to backup my home webserver dbs to Dropbox!
I just archive all the .bak files and use https://github.com/andreafabrizi/Dropbox-Uploader to upload them to dropbox.
Thanks.
No worries, you’re welcome!
[...] script was taken from http://www.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/ and works wonderfully! This entry was posted in Backup, FreeBSD by michael. Bookmark the [...]
Thanks Sonia for my time saving.
I add this script into the logrotate:
# cat buckupdb
/var/backup/sql/*sql {
daily
missingok
notifempty
compress
postrotate
/root/scripts/db_bak.sh > /dev/null 2>/dev/null || true
endscript
}
Thanks Andre, that’s a nice way of doing it, with logrotate. Sonia.
Thank you!! This really is truly helpfull, i’ll try it out making use of my own, individual 1st wordpress weblog.
You’re welcome Abdul!
BTW, if you’re installing WordPress on your own box, I found the best way to manage it long-term is to clone WordPress from github. That way you can easily upgrade without overwriting any of your own customisations.
WoW ! Thank you, I saved hours with this.
[...] гнев победил лень, и я нашёл на http://www.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/, а потом и немного модифицировал скрипт для бекапа [...]