How to backup with mysqldump --all-databases so that it does a database_name.sql for each database?

Question

MySQL

How to backup with mysqldump --all-databases so that it does a database_name.sql for each database?

Answer 1

for dbname in `echo show databases| mysql -u user -ppass`; do
echo "Dump $dbname..."
mysqldump -u user -ppass $dbname > "$dbname.sql"
done;

Answer 2

There is another such script, you can even put it on cron.

#!/bin/bash

MyUSER="dbuser"
MyPASS="dbpass"
MyHOST="localhost"
 
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

#MYSQL="/usr/local/bin/mysql"
#MYSQLDUMP="/usr/local/bin/mysqldump"

CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
 
DEST="./db_backup"
 
MBD="$DEST"
HOST="$(hostname)"
NOW="$(date +"%Y%m%d")"
 
FILE=""
DBS=""
 
# DO NOT BACKUP these databases, delemiter SPACE
IGN="information_schema"

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do

    skipdb=-1
    if [ "$IGN" != "" ]; then
    for i in $IGN
    do
      [ "$db" == "$i" ] && skipdb=1 || :
    done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
    MBD="$DEST/$db"    	
    [ ! -d $MBD ] && mkdir -p $MBD || :
    FILE="$MBD/$NOW.sql.gz"
          $MYSQLDUMP --opt -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    FNUM="$(find $MBD/* | wc -l)"
    if [ $FNUM -ge 0 ] ; then
      find $MBD/* -type f -mtime 20 -exec rm -rf {} \;
    fi
    fi

done

Answer 3

And further. It doesn't have to be so perverted. You can extract the desired database from a full backup with just one command:
sed -n -e '/CREATE TABLE.*mytable/,/CREATE TABLE/p' full.dump > mytable.dump
Either without even pulling it out, but directly directly like this:
mysql --one-database db_to_restore < full.dump

Answer 4

#!/usr/local/bin/bash
DIR="/path/to/backup"
mkdir -p $DIR
LOG="/path/to/log.log"
touch $LOG
TIMENAME=`date +%d.%m.%Y-%H.%M`
db=`mysql -u USERNAME -h localhost -pPASSWORD -Bse 'show databases'`
for n in $db; do
TIMEDUMP=`date '+%T %x'`
echo "backup has been done at $TIMEDUMP : $TIMENAME on db: $n" >> $LOG
mysqldump -u USERNAME -h localhost -pPASSWORD $n | gzip -c > "$DIR/mysql-$n-$TIMENAME-db.dump.gz"

I am using something like this.

Answer 5

no way. either back up one database at a time, or separate the databases by files dumped into a heap

Answer 6

cd /home/backup/ && mysql -uappsroot -uUSER -e 'show databases' | while read dbname; do mysqldump -uUSER -pPASS --complete-insert "$dbname"|gzip > "$dbname".sql.gz; done