CODEWITHSUNDEEP
mysqlbashautocomplete
WonderLand
WonderLand

Reputation: 5674

Restore all MySQL Databases from individual files

I can backup all my db in separate files using the following script:

#!/bin/bash
MYSQL_USER="USER"
MYSQL_PASS="PASSWORD"

if [ -z "$1" ]
  then
    echo "Dumping all DB ..."
    for I in $(mysql -u $MYSQL_USER --password=$MYSQL_PASS -e 'show databases' -s --skip-column-names); 
    do 
      echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;" > "$I.sql"
      mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $I >> "$I.sql"; 
      echo "SET autocommit=1;SET unique_checks=1;SET foreign_key_checks=1;commit;" >> "$I.sql"
      gzip "$I.sql"
    done
    echo "END."
else
      echo "Dumping $1 ..."
      echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;" > "$1.sql"
      mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $1 >> "$1.sql"; 
      echo "SET autocommit=1;SET unique_checks=1;SET foreign_key_checks=1;commit;" >> "$1.sql"
      gzip "$1.sql"
fi

I'm looking for the reverse command:

Upvotes: 1

Views: 2295

Answers (1)

anubhava
anubhava

Reputation: 785856

You can use this script:

for f in *.sql.gz; do
   db="${f%%.*}"
   echo "creating database $db"
   mysql -h localhost -u root -p mysql --password=passwd <<< "create database $db"
   echo "restoring database $db"
   gunzip "$f"
   mysql -h localhost -u root -ppasswd "$db" < "$db.sql"
done

Upvotes: 1

Related Questions