Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Programming/Scripts

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 21st April 2006, 01:42
sjau sjau is offline
Local Meanie
 
Join Date: Apr 2006
Location: Switzerland
Posts: 1,146
Thanks: 4
Thanked 55 Times in 51 Posts
Default replacement for mysqldump --all-databases

Hiya

anyone knows a script that loops through all existing mysql dbs and makes a dump of them. So that you end up with multiple sql files e.g.

dump_db1.sql
dump_db2.sql
dump_db3.sql
Reply With Quote
Sponsored Links
  #2  
Old 21st April 2006, 11:38
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,741 Times in 2,575 Posts
Default

You can build one yourself with this information: http://www.howtoforge.com/faq/6_3_en.html
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #3  
Old 11th June 2006, 09:44
EvanCarroll EvanCarroll is offline
Junior Member
 
Join Date: Jun 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The rightmost way to accomplish this -- how i would do it, is to querry on the information_schema http://dev.mysql.com/doc/refman/5.0/...ata-table.html CATALOG_NAME is database. that will get you a list of dbs, you can then easily dump in parellel to a like named file. using the command above.


Quote:
Originally Posted by sjau
Hiya

anyone knows a script that loops through all existing mysql dbs and makes a dump of them. So that you end up with multiple sql files e.g.

dump_db1.sql
dump_db2.sql
dump_db3.sql
Reply With Quote
  #4  
Old 2nd September 2006, 22:00
thelorax thelorax is offline
Junior Member
 
Join Date: Jul 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Try automysqlbackup

Give AutoMySQL backup a try - http://sourceforge.net/projects/automysqlbackup/

You can set it up to do specific databases or all of them and it will also do a daily/weekly/monthly rotation for you.
Reply With Quote
  #5  
Old 3rd September 2006, 10:06
sjau sjau is offline
Local Meanie
 
Join Date: Apr 2006
Location: Switzerland
Posts: 1,146
Thanks: 4
Thanked 55 Times in 51 Posts
Default

if you want to have a unique sql for each database:

Code:
#!/bin/bash
unset PATH
# USER VARIABLES
MYSQLUSER=root
MYSQLPWD=**********************
MYSQLHOST=localhost
MYSQLBACKUPDIR=/mysql_backup
# PATH VARIABLES
MK=/bin/mkdir;
RM=/bin/rm;
GREP=/bin/grep;
MYSQL=/usr/bin/mysql;
MYSQLDUMP=/usr/bin/mysqldump;
# CREATE MYSQL BACKUP
# Remove existing backup dir
$RM -Rf $MYSQLBACKUPDIR
# Create new backup dir
$MK $MYSQLBACKUPDIR
#Dump new files
for i in $(echo 'SHOW DATABASES;' | $MYSQL -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST|$GREP -v '^Database$'); do
  $MYSQLDUMP                                                    \
  -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST                         \
  -Q -c -C --add-drop-table --add-locks --quick --lock-tables   \
  $i > $MYSQLBACKUPDIR/$i.sql;
done;
Reply With Quote
  #6  
Old 3rd September 2006, 19:31
drks drks is offline
Junior Member
 
Join Date: Aug 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I do this with a one liner (for onetime backups).... you could break it up into a script if you wanted (or use the great suggestions above):

Code:
linuxbox /]# for database in $(mysql -e "show databases" | grep "^\|" | grep -v Database); \
do echo -n "backing up $database ... "; \
mysqldump $database > $database.sql && \
echo "ok" || \
echo "failed"; \
done
__________________
drks - http://www.5dollarwhitebox.org
Reply With Quote
  #7  
Old 3rd December 2008, 05:47
Ovidiu Ovidiu is offline
Senior Member
 
Join Date: Sep 2005
Posts: 1,262
Thanks: 78
Thanked 24 Times in 20 Posts
Default

what would we need to change to make any of these scripts compress the dumps into .gz or similar?
Reply With Quote
  #8  
Old 3rd December 2008, 08:44
sjau sjau is offline
Local Meanie
 
Join Date: Apr 2006
Location: Switzerland
Posts: 1,146
Thanks: 4
Thanked 55 Times in 51 Posts
Default

you just need to add at the end of my script the command to create tgz or whatever you want... with my script you have all the files in one folder so just zip that one.
__________________
"Common sense is not as common as commonly believed" by sjau

Auto-Install Script for ISPConfig and Horde on a Vanilla Debian Stable

Need more Repos for Ubuntu? Repository Generator
Need more Repos for Debian? Debian Repository Generator
Reply With Quote
  #9  
Old 5th December 2008, 00:22
Ovidiu Ovidiu is offline
Senior Member
 
Join Date: Sep 2005
Posts: 1,262
Thanks: 78
Thanked 24 Times in 20 Posts
Default

would that look something like this?

Quote:
for i in $(echo 'SHOW DATABASES;' | mysql -u$USER -p$PASSWORD -h$HOST|grep -v '^Database$'); do
mysqldump \
-u$USER -p$PASSWORD -h$HOST \
-Q -c -C --add-drop-table --add-locks --quick --lock-tables \
$i > /root/mysql_backup/$i.sql;
tar czvf $i.sql.tar.gz /root/mysql_backup/$i.sql;
done;
I'd like to tar.gz the single DB dumps, not the whole folder.
I am terribly bad with this stuff :-(

###edit###
above code gives an error about removing a / so I changed to this:
Quote:
tar czvf /root/mysql_backup/$i.sql.tar.gz -C / root/mysql_backup/$i.sql;

Last edited by Ovidiu; 5th December 2008 at 01:04.
Reply With Quote
  #10  
Old 3rd March 2011, 19:41
Ovidiu Ovidiu is offline
Senior Member
 
Join Date: Sep 2005
Posts: 1,262
Thanks: 78
Thanked 24 Times in 20 Posts
 
Default

sorry for reviving this old thread but now I ran into a problem with this solution after upgrading from debian lenny to squeeze...

the working script was this:

Code:
#!/bin/bash
#Dump new files
USER=root
PASSWORD=myrootpw
HOST=localhost

for i in $(echo 'SHOW DATABASES;' | mysql -u$USER -p$PASSWORD -h$HOST|grep -v '^Database$'); do
  mysqldump                                                     \
  -u$USER -p$PASSWORD -h$HOST                                   \
  -Q -c -C --add-drop-table --add-locks --quick --lock-tables   \
  $i > /root/mysql_backup/$i.sql;
#disabled for now  tar czvf /root/mysql_backup/$i.sql.tar.gz -C / root/mysql_backup/$i.sql;
done;
unfortunately after the upgrade I get this error:

Code:
Output: mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
mysqldump: Got error: 23: Out of resources when opening file './c1zice/wp_1067_terms.MYD' (Errcode: 24) when using LOCK TABLES
I have googled the first error so far and had different suggestions but I thought I'd ask here first.

using:

mysqldump --version
mysqldump Ver 10.13 Distrib 5.1.55, for debian-linux-gnu (x86_64)

mysql Ver 14.14 Distrib 5.1.55, for debian-linux-gnu (x86_64) using readline 6.1
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

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 On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Backup of Client Webspaces and Databases JohnSmith Feature Requests 5 2nd July 2007 08:53
Spam Databases plucchetti Installation/Configuration 4 6th March 2006 23:09
website's databases names Prompt General 2 19th October 2005 09:54
Rename Databases AngelDrago Installation/Configuration 2 18th October 2005 06:01


All times are GMT +2. The time now is 16:24.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.