scripting mysql on unix to clean tables

One could clean the table, but on hostgator’s phpmyadmin in cpanel which is REALLY REALLY REALLY SLOW, it times out and loses connection before it is done cleaning. So – on another server which is lickity split, I have root access. To overcome the slowness, I first, copy the table which cleans it, then rename the original and rename the copy. This allows me to also have a backup table. Below is my unix bash script:

Here is a list of my heros who helped get me to my script below.


#!/bin/bash

## script name; cleanDirtyTable.sh
## wrapper script to execute mysql script with variables

ARGS=2

if [ $# -ne "$ARGS" ]
then

echo "you passed $# parameters"
 echo "Usage: `basename $0` sql_table sql_password"

exit
fi

sqltable=$1
sqlpassword=$2
sql_script=cleanDirtyTable.sql
data_file=cleanDirtyTable_results.txt

#run mysql query with paramenters

echo "set @origTable=${sqltable}"

#/usr/bin/mysql –uUserName -pXXXXXXXXXX –h localhost -D my_dBname -A -e "set @origTable='${sqltable}'; source ${sql_script};" #&amp;amp;gt;${data_file};

/usr/bin/mysql -uUsername -p${sqlpassword} -h localhost -D my_dBname -A -e "set @origTable='${sqltable}'; SOURCE ${sql_script};"
exit

Now here is the sql portion

USE fcs_jo151;

-- SET @origTable = 'jos_fcls_masterquotewjobs';

SET @ds1 = CONCAT('CREATE table `',@origTable, '_copy` LIKE `',@origTable,'`');
SET @b =1;
SET @ds2 = CONCAT('INSERT `', @origTable, '_copy` SELECT * FROM `',@origTable,'`;');
SET @rename1 = CONCAT('RENAME TABLE `',@origTable,'` TO `', @origTable ,'_bak_',CURDATE(),'`;');

SET @rename2 = CONCAT('RENAME TABLE `',@origTable,'_copy` TO `', @origTable ,'`;');



PREPARE stmt FROM @ds1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt FROM @ds2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


PREPARE stmt FROM @rename1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


PREPARE stmt FROM @rename2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;