2

I try to restore some .sql backups using a shell script with a for loop.

I run by bash command.sh 'password' using the command.sh below. However, when I typed it out, it didn't run and instead, mysql --help message showed up multiple times.. I think somehow the mysql command wasn't invoked. How to do it here?

#!/bin/bash

for i in *sql;
do
    fname=$(basename $i .sql)
    echo "working on $fname"
    mysql -uroot -p $1 $fname < $i
    wait
done
1

Is something like this anything like what you want?

This is how to run through all the databases in a server and backs them up.

#!/bin/bash

USER="your_user"
PASSWORD="your_password"
OUTPUT="/Users/rabino/DBs"

rm "$OUTPUT/*gz" > /dev/null 2>&1

databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/`date +%Y%m%d`.$db.sql
        gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

And in order to cycle through files of a particular type, you could look here.

As an example from this site, you could make use of this script

Filename Expansion You can do filename expansion in a loop such as work on all pdf files in current directory:

#!/bin/bash
for f in *.pdf
do
  echo "Removing password for pdf file - $f"
done

So, you want something like

#!/bin/bash
for f in *.sql
do
  echo "\nworking on filename"
  mysql -u root -ppassword < $f
  echo 
done

note: gap between -u and root, no gap between -p and password

Now, the script above isn't very sophisticated (and untested), but that combined with the database script above should give you stuff to work with. In any case, you can Google the terms here and similar and you'll get loads of stuff. A few more bash backup scripts (Rolando is a big hitter on this forum).

Here's a post about a lot of MySQL backup solutions that don't require bash.

| improve this answer | |
  • Thanks so much! Could you explain why" gap between -u and root, no gap between -p and password"? – olala Jun 28 '16 at 0:05
  • I ended up using Buttle Butkus's answer from here: stackoverflow.com/questions/20751352/… – olala Jun 28 '16 at 0:06
  • Yeah, well there are a million ways to do this stuff. Best of luck. – Vérace Jun 28 '16 at 0:28
  • -p requires no space -- to distinguish between password being provided versus prompted for. Other options (eg, -u) work with or without a space. – Rick James Jul 8 '16 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.