LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 03-10-2011, 08:47 AM   #1
sir-lancealot
Member
 
Registered: Aug 2007
Posts: 336

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
corp769
Guru
 
Registered: Apr 2005
Posts: 5,807

Rep: Reputation: 996Reputation: 996Reputation: 996Reputation: 996Reputation: 996Reputation: 996Reputation: 996Reputation: 996
I found this on the internet:
Code:
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,

Josh
 
Old 03-10-2011, 11:04 PM   #3
ComputerErik
Member
 
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:

Code:
today=$(date +%m%d%Y)
log=/home/backup/logs/database/$today.log
dest=/home/backup/backups/database/$today
db_user=backups
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;
do
   filename=$dest/$db.sql
   mysqldump -u $db_user $db > $filename
done
 
1 members found this post helpful.
  


Reply


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


All times are GMT -5. The time now is 03:09 AM.

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