Pacific-Design.com

    
Home Index

1. MariaDB

2. Multiple Tables

MariaDB / Multiple Tables /

#!/bin/bash
#-----------------------------------------------------------------------------#
# Execute SQL against multiple databases and multiple tables
#-----------------------------------------------------------------------------#
USERNAME="user"
PASSWORD="password"
mydb="database1 database2 database3"

for db in $mydb
do  
    echo "Database: is [$db]"
    execute_database $db
done

#-----------------------------------------------------------------------------#
#  Execute on SQL on entire Database 
#-----------------------------------------------------------------------------#
execute_database()
{
  DB=$1
  TABLE_LIST=`mysql -u $USERNAME -p$PASSWORD -NB -e "show tables from $DB"`

  for TBL in $TABLE_LIST
  do
      echo "$(date +"%Y-%m-%d %H:%M") EXECUTING on "$DB"."$TBL

      SQL="LOCK TABLES $DB.$TBL WRITE;
           DELETE FROM $DB.$TBL WHERE url LIKE '%iquebec.com%';
           REPAIR TABLE $DB.$TBL;
           UNLOCK TABLES; "
      echo $SQL
      RES=`mysql -u $USERNAME -p$PASSWORD -NB -e  "$SQL"`
      sleep 1
  done
}
#-----------------------------------------------------------------------------#