Issue with running multiple mysql commands in a bash for loop

Question

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

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

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.

Issue with running multiple mysql commands in a bash for loop

1 Answers1