replacement for mysqldump --all-databases

[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

[falko]

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

[EvanCarroll]

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.

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

[thelorax]

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.

[sjau]

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

#!/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;

[drks]

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):

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

[Ovidiu]

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

[sjau]

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.

[Ovidiu]

would that look something like this?

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:

tar czvf /root/mysql_backup/$i.sql.tar.gz -C / root/mysql_backup/$i.sql;

[Ovidiu]

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:

#!/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:

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