Pacific-Design.com

    
Home Index

1. Bash

2. Tables

Bash / Tables /

Alter all tables within a database.

#!/bin/bash

repair_tables()
{
  USERNAME=user
  PASSWORD=password
  DATABASE=$1

  TABLE_LIST=`mysql -u $USERNAME -p$PASSWORD -NB -e "show tables from $DATABASE"`

  for E in $TABLE_LIST
  do
      echo "$(date +"%Y-%m-%d %H:%M") REPAIR and OPTIMIZE TABLE "$DATABASE"."$E

      SQL="
           ALTER TABLE $DATABASE.$E CHANGE rank  rank TINYINT(  1 ) UNSIGNED NOT NULL DEFAULT  '0';
           ALTER TABLE $DATABASE.$E CHANGE nlp   nlp  TINYINT(  1 ) UNSIGNED NOT NULL DEFAULT  '0';
           ALTER TABLE $DATABASE.$E CHANGE value hits INT    ( 10 ) UNSIGNED NOT NULL DEFAULT  '0'; 
           "
      echo "$SQL"
      RES=`mysql -u $USERNAME -p$PASSWORD -NB -e  "$SQL"`
      echo $RES
      sleep 1 
  done
}

#-----------------------------------------------------------------------------#
#--- Repair and optimize all tables in the following databases
#-----------------------------------------------------------------------------#

mydb="engine8"

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