Latest LQ Deal: Complete CCNA, CCNP & Red Hat Certification Training Bundle
Go Back > Forums > Linux Forums > Linux - Server
User Name
Linux - Server This forum is for the discussion of Linux Software used in a server related context.


  Search this Thread
Old 03-10-2011, 08:47 AM   #1
Registered: Aug 2007
Posts: 346

Rep: Reputation: 31
mysqldump help on multiple databases

ok, I have a mysql database for a client who has 837 databases (99% are all tiny). The backup script simply looks like this;

/usr/local/mysql/bin/mysqldump -u root --password=x --all-databases -add-locks | gzip > /backup/server.mysql.$(date +%Y-%m-%d_%H%P).sql.gz

Now that takes a while due to the size, and when completed, I went to the development box, did;

mysql -u root -p < file

It took a while, then completed (no errors, etc.). I logged into mysql, did a show databases; and there was the primary one (the 12G one) and nothing else. I did a grep -i 'create database' on the backup file and only see the 1st. So I went to that server, made a test script like this;

/usr/local/mysql/bin/mysqldump -u root --password=x --databases db2 db3 --add-locks | gzip > /data/archives/server.mysql.$(date +%Y-%m-%d_%H%P).sql.gz

That took seconds (as those DB's are small), unzipped, then did a grep -i 'create database' file and saw;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db3` /*!40100 DEFAULT CHARACTER SET latin1 */;

Am I missing something in the first script that say's --all-databases? If there were just a few I could do them that way, but there 837 DB's so it's just not an option! Can I say backup all but NOT the main one?

Any help, ideas or suggestions are appreciated. I am not sure if it's the 12G size (doubtful), but I can't say --databases and name each one due to how many. Thanks
Old 03-10-2011, 01:05 PM   #2
LQ Guru
Registered: Apr 2005
Posts: 5,817

Rep: Reputation: 1002Reputation: 1002Reputation: 1002Reputation: 1002Reputation: 1002Reputation: 1002Reputation: 1002Reputation: 1002
I found this on the internet:
mysql --password=mypass --batch --skip-column-names --execute="SHOW DATABASES" | grep -v "test" | xargs mysqldump --password=mypass --databases > nottest.sql
This will exclude the database "test." Hope that helps,

Old 03-10-2011, 11:04 PM   #3
Registered: Apr 2005
Location: NYC
Distribution: Debian, RHEL
Posts: 268

Rep: Reputation: 42
This is what I use, but it dumps each database to its own file as I have a bunch of unrelated databases:

today=$(date +%m%d%Y)
db_list=`mysql -u $db_user -e'show databases;'`    #get list of all databases on server
db_list=${db_list##Database}                       #remove headers from list of databases

mkdir $dest

#loop through and use mysqldump to dump each database to a file
for db in $db_list;
   mysqldump -u $db_user $db > $filename
1 members found this post helpful.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple mysqldump lines immortaltechnique Linux - Software 1 02-10-2011 09:14 AM
Having multiple databases in ...As a DBA, need to start and stop each DB individually neeraj1m AIX 1 07-19-2010 07:02 PM
GRANT privileges on multiple databases to one user dipuasks Linux - General 1 01-28-2009 07:57 AM
Slony and multiple databases oudoubah Linux - Server 0 10-29-2007 10:31 AM
start|stop multiple oracle databases in the same oracle server prozac Linux - Software 8 01-23-2007 03:01 AM > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 04:44 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration