Shell and MySQL

From Shrubbery

Shell/MySQL tips

Contents 1 Using a 'Here document' with MySQL 2 Dropping all indexes on a table 3 Automatically loading compressed database dumps 4 Kill all running queries

Using a 'Here document' with MySQL

If you want to execute a bunch of MySQL commands but you don't want to write a temporary SQL script file, you can use a Here Document:

mysql -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME <<END_SQL
... sql commands ...
END_SQL

Dropping all indexes on a table

Dropping all indexes on a table can be very useful when bulk-inserting lots of data or re-building the indexes. This is a variation on this idea I found in an email list. The steps are:

1. Use SHOW INDEX FROM ... to get the list of indexes.
2. Pipe the results through awk to get the non-primray key indexes and print ALTER TABLE ... DROP INDEX ...; statements.
3. Pipe the statements back into MySQL.

Here is an example:

DB_NAME=... some db ...
DB_USER=... db username ...
DB_PASS=... db pass ...
TABLE_NAME=... some table name ...
echo "show index from $TABLE_NAME" | \
   mysql -u$DB_USER -p$DB_PASS $DB_NAME | \
   awk '{if ($3 !~ /Key_name/ && $3 !~ /PRIMARY/) print "ALTER TABLE "$1" DROP INDEX "$3";"}' |
   mysql -u$DB_USER -P$DB_PASS $DBNAME

Automatically loading compressed database dumps

First, figure out the file extension, then use a case to set the uncompressing command:

SNAPSHOT_FILE=myfile.sql.gz
DB_HOST=localhost
DB_USER=someuser
DB_PASS=somepassword
DB_NAME=somedb

SNAPSHOT_TYPE=${SNAPSHOT_FILE/*./}
UNCOMP=''
case $SNAPSHOT_TYPE in
  gz)
	UNCOMP='gunzip -c'
	;;
  bz2)
	UNCOMP='bunzip2 -c'
        ;;
  *)
	;;
esac

echo "Loading $SNAPSHOT_FILE into $DB_NAME (on $DB_HOST)..."
$UNCOMP $SNAPSHOT_FILE | mysql --host=$DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME
if [ $? -ne 0 ]; then
        echo "[$SCRIPT_NAME] mysql failed!"
    exit -1
fi

Kill all running queries

$ mysql -uusername -ppassword -e "SHOW PROCESSLIST " | grep "$db.$table" | awk '{print "KILL QUERY ",$1,";";}' | mysql -uusername -ppassword