how to run mysql drop and create synonym in shell script
- by bgrif
I have added this command to a script I am writing and I am running into a issue with it not logging onto mysql and running the commands. How can i fix this and make it run.
#! /bin/bash
Subject: Please stage the following TFL09143 Locator Bulletin to all TF90 staging environments:
#
This next section is to go to mysql server and make changes.
you can drop and create synonyms
truncate a table and insert into a different one.
you will be able to verify the counts to the different locations
#
$ mysql --host=app03-bsi --u "" --p "" "TF90BPS" -bse "drop synonym TF90.BTXADDR && drop synonym TF90.BTXSUPB && CREATE SYNONYM TF90.BTXADDR FOR TF90BP.TFBPS2.BTXADDR && CREATE SYNONYM TF90.BTXSUPB FOR TF90BP.TFBPS3.BTXSUPB && TRUNCATE TABLE TF90BP.TFBPS3.BTXSUPB SELECT * FROM TF90BP.TFBPS2.BTXSUPB; select count () from TF90BP.TF90.BTXADDR select count() from TF90BPS.TF90.BTXADDR; select count() from TF90BP.TF90.BTXSUPB; select count() from TF90BPS.TF90.BTXSUPB;"
$ mysql --host=app03-bsi --u "" --p "" "TF90LMS" -bse "drop synonym TF90.BTXADDR && drop synonym TF90.BTXSUPB && CREATE SYNONYM TF90.BTXADDR FOR TF90LM.TFBPS2.BTXADDR && CREATE SYNONYM TF90.BTXSUPB FOR TF90LM.TFBPS3.BTXSUPB; TRUNCATE TABLE TF90LM.TFLMS2.BTXADDR;TRUNCATE TABLE TF90LM.TFLMS3.BTXSUPB;INSERT INTO TF90LM.TFLMS3.BTXSUPB SELECT * FROM TF90LM.TFLMS2.BTXSUPB;Verify select count() from TF90LM.TF90.BTXADDR;select count() from TF90LMS.TF90.BTXADDR;select count() from TF90LM.TF90.BTXSUPB;select count() from TF90LMS.TF90.BTXSUPB"
$ mysql --host=app03-bsi --u "" --p "" "TF90NCS" -bse "drop synonym TF90.BTXADDR && drop synonym TF90.BTXSUPB && CREATE SYNONYM TF90.BTXADDR FOR TF90NC.TFBPS2.BTXADDR && CREATE SYNONYM TF90.BTXSUPB FOR TF90NC.TFBPS3.BTXSUPB; TRUNCATE TABLE TF90NC.TFNCS2.BTXADDR; TRUNCATE TABLE TF90NC.TFNCS3.BTXSUPB; INSERT INTO TF90NC.TFNCS3.BTXSUPB SELECT * FROM TF90NC.TFNCS2.BTXSUPB; Verify select count() from TF90NC.TF90.BTXADDR; select count() from TF90NCS.TF90.BTXADDR;select count() from TF90NC.TF90.BTXSUPB;select count() from TF90NCS.TF90.BTXSUPB"
$ mysql --host=app03-bsi --u "" --p "" "TF90PVS" -bse "drop synonym TF90.BTXADDR && drop synonym TF90.BTXSUPB && CREATE SYNONYM TF90.BTXADDR FOR TF90PV.TFBPS2.BTXADDR && CREATE SYNONYM TF90.BTXSUPB FOR TF90PV.TFBPS3.BTXSUPB; TRUNCATE TABLE TF90PV.TFPVS2.BTXADDR;TRUNCATE TABLE TF90PV.TFPVS3.BTXSUPB;INSERT INTO TF90PV.TFPVS3.BTXSUPB SELECT * FROM TF90PV.TFPVS2.BTXSUPB;Verify select count() from TF90PV.TF90.BTXADDR;select count() from TF90PVS.TF90.BTXADDR;select count() from TF90PV.TF90.BTXSUPB;select count() from TF90PVS.TF90.BTXSUPB"
TFL09143 Staging
cd \ntsrv\common\To\IT-CERT-TEST\TFL09143 #change to mapped network drive
cp -p TFL09143.pkg /d:/tf90/code_stg && /tf90bp/code_stg && /tf90lm/code_stg && /tf90pv/code_stg # Copies the package from the networked folder and then copies to the location(s) needed.#
InvalidInput="true"
if [ $# -eq 0 ] ; then
echo "This script sets up TF90 Staging"
echo -n "Which production do you want to run? (RB/TaxLocator/Cyclic)"
read ProductionDistro
else
ProductionDistro="$1"
fi
while [ "$InvalidInput" = "true" ]
do
if [ "$ProductionDistro" = "RB" -o "$ProductionDistro" = "TaxLocator" -o "$ProductionDistro" = "Cyclic" ] ; then
InvalidInput="false"
break
else
echo "You have entered an error"
echo "You must type RB or TaxLocator or Cyclic"
echo "you typed $ProductionDistro"
echo "This script sets up TF90 Staging"
read ProductionDistro
fi
done
InvalidInput="true"
if [ $# -eq 0 ] ; then
echo "This script sets up RB TF90 Staging"
echo -n "Which Element do you want to run? (TF90/TF90BP/TF90LM/TF90PV/ALL)"
read ElementDistro
else
ElementDistro="$1"
fi
while [ "$InvalidInput" = "true" ]
do
if [ "$ElementDistro" = "TF90" -o "$ElementDistro" = "TF90BP" -o "$ElementDistro" = "TF90LM" -o "$ElementDistro" = "TF90PV" -o "$ElementDistro" = "ALL" ] ; then
InvalidInput="false"
break
else
echo "You have entered an error"
echo "You must type TF90 or TF90BP or TF90LM or TF90PV"
echo "you typed $ElementDistro"
echo "This script sets up TF90 Staging"
read ElementDistro
fi
done
if [ "$ElementDistro" = "TF90" ] ; then
cd /d/tf90/code_stg
vim TFL09143.pkg
export var=TF90_CONNECT_STRING=DSN=TF90NCS;export Description=TF90NCS;export Trusted_Connection=Yes;export WSID=APP03-
BSI;export DATABASE=TF90NCS;
export DATASET=DEFAULT
pkgintall -l -v ../TFL09143.pkg
fi
if [ "$ElementDistro" = "$TF90BP" ] ; then
cd /d/tf90bp/code_stg
vim TFL09143.pkg
export TF90_CONNECT_STRING=DSN=TF90BPS;export Description=TF90BPS;export Trusted_Connection=Yes;export WSID=APP03-
BSI;export DATABASE=TF90BPS;
start tfloader -l –v ../TFL09143.pkg
fi
if [ "$ElementDistro" = "$TF90LM" ] ; then
cd /d/tf90lm/code_stg
vim TFL09143.pkg
export TF90_CONNECT_STRING=DSN=TF90LMS;export Description=TF90LMS;export Trusted_Connection=Yes;export WSID=APP03-
BSI;export DATABASE=TF90LMS;
start tfloader -l -v ../TFL09143.pkg
fi
if [ "$ElementDistro" = "TF90PV" ] ; then
cd /d/tf90pv/code_stg
vim TFL09143.pkg
export TF90_CONNECT_STRING=DSN=TF90PVS;Description=TF90PVS;Trusted_Connection=Yes;WSID=APP03-
BSI;DATABASE=TF90PVS;
start tfloader -l –v ../TFL09143.pkg
fi
exit 0
