Home > Database, Linux, mysql > mysqldump all databases on mysql

mysqldump all databases on mysql

February 20th, 2011

I was wondering for a few minutes if I can easily backup all databases created by users on a mysql database server. I was trying to automate the process regardless of users creating new databases without my knowledge. I was aware of the convention under which mysql stores the separate databases on the filesystem so I gave it a try.
Fist I tried with few ‘ls’ commands to retrieve the folder names which respectively referred to the database names in the mysql server. On FreeBSD here is the command I used:

[root@bsdbox08 ~]# find /var/db/mysql/ -type d
/var/db/mysql/
/var/db/mysql/mysql
/var/db/mysql/db1
/var/db/mysql/performance_schema
/var/db/mysql/db2

Here I decided not to mysqldump the mysql database and the performance_schema so I changed the command slightly by adding sed and grep to it.

[root@bsdbox08 ~]# find /var/db/mysql/ -type d| sed -e s,/var/db/mysql/,,g|grep -v performance_schema|grep -v mysql

db1
db2

I decided to use a comma separator for the sed command due to the difficulties the ‘/’ delimiter presents in bash scripts and I was totally willing to use a bash script for the task.

Here is how my initial script looked like:

#!/usr/local/bin/bash
ESCAPE="/var/db/mysql/"
DATE=`/bin/date +%F_%T`
for var1 in `find /var/db/mysql/ -type d| sed -e s,$ESCAPE,,g|grep -v performance_schema|grep -v mysql`
do
echo "Dumping database $var1"
mysqldump -uroot -p`cat /root/.mysqladminpass $var1 > /mnt/backup/rsync_backup/dumps/$var1-$DATE.sql
echo "Database $var1 dumped successfully!"
done
exit 0

You should note in the code above that I have a .mysqladminpass file in the root folder that contains the mysql server password for the root user. I do this out of laziness but you can supply this argument however you want. Again my plan was automation but you could easily have input for a variable from the keyboard here.

The result is a flawless export of all user databases to the desired backup location.
In my case I worked out some more details so I bzip2 the dumped sql files and added md5 checksums in a separate file for security I am implementing later on but in general this is all you need to backup your mysql user databases.

Hope this saves some time to a curious mind out there.

Categories: Database, Linux, mysql Tags:
  1. Jeremy
    April 1st, 2011 at 07:46 | #1

    Why didn’t you just use the –all-database option?

  2. April 1st, 2011 at 09:23 | #2

    Hello Jeremy,

    Quite true.. I didn’t read the man to refresh on the options.
    The databases belong to different users and I had to make some sort of separation. The -all-databases option dumps the whole content into one and it’s more difficult to separate it later on.
    However this had me thinking of the –database option which would be more useful. I’ll probably rewrite my backup scripts as soon as I get a chance.
    Thanks for the comment and have a great weekend!

    KR,
    – S

Comments are closed.