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

 

You can build one yourself with this information: http://www.howtoforge.com/faq/6_3_en.html

 

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/en/schemata-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.

 

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.

 

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;

 

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

 

what would we need to change to make any of these scripts compress the dumps into .gz or similar?

 

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.

 

would that look something like this?

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:

 

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

 

the first is normal... not sure about the second one. Running out of diskspace? is tht db damaged?

 

the second might be a hardware problem. just did a hardware test through my provider and they are going to replace some hardware.

why would the first one be normal? I read that the information schema shouldn't be dumped by default anyway !?

Does this mean the first error doesn't affect my backups?

i.e.

 

the command looks up all databases including the INFORMATION_SCHEMA and tries to dump it.

You see, first is an command issued that build a list of the dbs in mysql and then it loops through that list and dumps each one to an own .sql file.

 

the command looks up all databases including the INFORMATION_SCHEMA and tries to dump it.

You see, first is an command issued that build a list of the dbs in mysql and then it loops through that list and dumps each one to an own .sql file.

 

this is a little shifty but...you may be able to do an ls of /var/lib/mysql and scoop up the dbs in there, and exclude the ones you don't want, but include everything else.