MySQL script: drop all tables in a db and import a .sql file

Used to refresh SimpleInvoices.org/demo each hour

#!/bin/bash

MUSER="-- Insert Db username here --"
MPASS="-- Insert Db password here --"
MDB="-- Insert Db name here --"
 FILE="/home/user/path/to/your/import_file.sql"
 
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
 
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v 'Tables' )
  
for t in $TABLES
do
    echo "Deleting $t table from $MDB database..."
    $MYSQL -u $MUSER -p $MPASS $MDB -e "drop table $t"
done

cat $FILE | mysql -u $MUSER -p$MPASS $MDB
echo "Sql imported"

Justin Kelly

Justin Kelly

Web Developer, Business Analytics, Data Engineer specialising in PHP, ReactJS, Tableau, AWS RedShift and Matillion ETL

Based in Melbourne, Australia

Feel free to contact me justin@kelly.org.au or _justin_kelly

Leave a comment

Name Notify me of replies by email.