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.
- Passing Parameters To A MYSQL Query Inside A Shell Script
- Using mysql in batch mode
- Passing Parameters To A MYSQL Query Inside A Shell Script
- How do I execute an SQL script in MySQL?
[c]
#!/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};" #>${data_file};
/usr/bin/mysql -uUsername -p${sqlpassword} -h localhost -D my_dBname -A -e "set @origTable=’${sqltable}’; SOURCE ${sql_script};"
exit
[/c]
Now here is the sql portion
[sql]
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;
[/sql]