Difference between revisions of "SQWareCentral:mysql"

From Wiki_dbSQWare
Jump to: navigation, search
m (Updated by BatchUser)
 
(9 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
{{SQWareCentralPresSgbd|oracle}}
+
== auto ==
 +
=== sqwmys_AdddbSQWareProfile.ksh ===
 +
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_AdddbSQWareProfile.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_AdddbSQWareProfile.ksh.<br>
 +
Add dbSQWare profile
 +
<br><br>
 +
Action:
 +
<pre>
 +
lfsqw_Action()
 +
{
 +
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
 +
do
 +
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
 +
lvsqw_Instance=$(echo "$lvsqw_Instance"|cut -d':' -f1)
 +
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
 +
if [ -r ~/.profile ]
 +
then
 +
if [ \$(grep -c 'gvsqw_MysBin' ~/.profile) -eq 0 ]
 +
then
 +
cat <<EOFCAT >>~/.profile
 +
 
 +
#dbSQWare
 +
export gvsqw_Env=''
 +
export gvsqw_MysBin=\\\$HOME/SQWareProduction/mysql/bin
 +
lvsqw_IsTerminal=\\\$(tty 2>&1 >/dev/null;echo \\\$?)
 +
if [ "\\\$lvsqw_IsTerminal" = "0" ] && [ -r \\\$gvsqw_MysBin/../etc/.profile_confort ]
 +
then
 +
. \\\$gvsqw_MysBin/../etc/.profile_confort
 +
fi
 +
 
 +
EOFCAT
 +
fi
 +
elif [ -r ~/.bash_profile ]
 +
then
 +
if [ \$(grep -c 'gvsqw_MysBin' ~/.bash_profile) -eq 0 ]
 +
then
 +
cat <<EOFCAT >>~/.bash_profile
  
−
== auto ==
+
#dbSQWare
 +
export gvsqw_Env=''
 +
export gvsqw_MysBin=\\\$HOME/SQWareProduction/mysql/bin
 +
lvsqw_IsTerminal=\\\$(tty 2>&1 >/dev/null;echo \\\$?)
 +
if [ "\\\$lvsqw_IsTerminal" = "0" ] && [ -r \\\$gvsqw_MysBin/../etc/.profile_confort ]
 +
then
 +
. \\\$gvsqw_MysBin/../etc/.profile_confort
 +
fi
 +
 
 +
EOFCAT
 +
fi
 +
fi
 +
EOFSSH
 +
done <$gvsqw_TmpFile.lst
 +
 
 +
}
 +
</pre>
 +
<br>
 
=== sqwmys_DeplScripts.ksh ===
 
=== sqwmys_DeplScripts.ksh ===
−
Script d'action $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_DeplScripts.ksh:<br>
+
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_DeplScripts.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_DeplScripts.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_DeplScripts.ksh.<br>
−
Deploiement de SQWareProduction pour Mysql
+
SQWareProduction deployment for Mysql
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Action:
 
<pre>
 
<pre>
 
lfsqw_Action()
 
lfsqw_Action()
Line 14: Line 67:
 
do
 
do
 
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
 
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
−
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
+
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
 
mkdir -p $gvsqw_RootSQWareProductionMysql/$gvsqw_VersDeplSQWareProductionMysql
 
mkdir -p $gvsqw_RootSQWareProductionMysql/$gvsqw_VersDeplSQWareProductionMysql
 
cd $gvsqw_RootSQWareProductionMysql
 
cd $gvsqw_RootSQWareProductionMysql
−
ln -sf $gvsqw_VersDeplSQWareProductionMysql $gvsqw_VersSQWareProductionMysql
+
rm -f $gvsqw_VersSQWareProductionMysql
 +
ln -s $gvsqw_VersDeplSQWareProductionMysql $gvsqw_VersSQWareProductionMysql
 
EOFSSH
 
EOFSSH
 
rsync -aHv $gvsqw_RsyncPathMysql --delete $gvsqw_RootSQWareProduction/mysql/$gvsqw_VersDeplSQWareProductionMysql $lvsqw_UserName@$lvsqw_HostName:$gvsqw_RootSQWareProductionMysql/.
 
rsync -aHv $gvsqw_RsyncPathMysql --delete $gvsqw_RootSQWareProduction/mysql/$gvsqw_VersDeplSQWareProductionMysql $lvsqw_UserName@$lvsqw_HostName:$gvsqw_RootSQWareProductionMysql/.
 
done <$gvsqw_TmpFile.lst
 
done <$gvsqw_TmpFile.lst
 +
}
 +
</pre>
 +
<br>
 +
=== sqwmys_GatherIndicatorsDist.ksh ===
 +
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GatherIndicatorsDist.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GatherIndicatorsDist.ksh.<br>
 +
Mysql gather indicators in distant mode
 +
<br><br>
 +
Action:
 +
<pre>
 +
lfsqw_Action()
 +
{
 +
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
 +
do
 +
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
 +
ssh $gvsqw_SshOptions $gvsqw_DistSQWareProdUser_mysql <<EOFSSH &
 +
\$gvsqw_MysBin/sqwmys_GatherIndicators.ksh -I "$lvsqw_Instance" -Dist
 +
EOFSSH
 +
sleep 1
 +
done <$gvsqw_TmpFile.lst
 +
jobs
 +
wait
 
}
 
}
 
</pre>
 
</pre>
 
<br>
 
<br>
 
=== sqwmys_GatherIndicators.ksh ===
 
=== sqwmys_GatherIndicators.ksh ===
−
Script d'action $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GatherIndicators.ksh:<br>
+
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GatherIndicators.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GatherIndicators.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GatherIndicators.ksh.<br>
−
Collecte des indicateurs Mysql
+
Mysql gather indicators
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Action:
 
<pre>
 
<pre>
 
lfsqw_Action()
 
lfsqw_Action()
Line 36: Line 112:
 
do
 
do
 
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
 
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
−
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
+
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
−
. ./.profile $lvsqw_Instance
+
. \$gvsqw_MysBin/../tools/sqwmys_SetEnv.ksh $lvsqw_Instance
−
$gvsqw_RootSQWareProductionMysql/$gvsqw_VersSQWareProductionMysql/bin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A IndicDba
+
\$gvsqw_MysBin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A IndicDba
 
EOFSSH
 
EOFSSH
 
sleep 1
 
sleep 1
Line 44: Line 120:
 
jobs
 
jobs
 
wait
 
wait
 +
}
 +
</pre>
 +
<br>
 +
=== sqwmys_Gen_dbSQWare.ksh ===
 +
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_Gen_dbSQWare.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_Gen_dbSQWare.ksh.<br>
 +
generate dbSQWare files
 +
<br><br>
 +
Action:
 +
<pre>
 +
lfsqw_Action()
 +
{
 +
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
 +
do
 +
lvsqw_Instance=$(echo "$lvsqw_Instance"|cut -d':' -f1)
 +
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
 +
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
 +
. \$gvsqw_MysBin/../tools/sqwmys_SetEnv.ksh $lvsqw_Instance
 +
if [ "\$MYSQL_SID" != "$lvsqw_Instance" ]
 +
then
 +
export MYSQL_SID="$lvsqw_Instance"
 +
fi
 +
\$gvsqw_MysBin/../tools/sqwmys_GenerateCreateInstance.ksh -dbsOnly <<EOF
 +
y
 +
EOF
 +
EOFSSH
 +
done <$gvsqw_TmpFile.lst
 +
}
 +
</pre>
 +
<br>
 +
=== sqwmys_GenSshKeysInstance.ksh ===
 +
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GenSshKeysInstance.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GenSshKeysInstance.ksh.<br>
 +
ssh keys deployment for MySql
 +
<br><br>
 +
Action:
 +
<pre>
 +
lfsqw_Action()
 +
{
 +
 +
echo "#########################################################################"
 +
echo "#Create/Retrieve key for SQWareCentral"
 +
mkdir -p $HOME/tmp
 +
if [ -r $HOME/.ssh/id_dsa.pub ]
 +
then
 +
echo "Id DSA found:"
 +
ls -l $HOME/.ssh/id_dsa.pub
 +
lvsqw_CentralKey=$(cat $HOME/.ssh/id_dsa.pub)
 +
elif [ -r $HOME/.ssh/id_rsa.pub ]
 +
then
 +
echo "Id RSA found:"
 +
ls -l $HOME/.ssh/id_rsa.pub
 +
lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
 +
else
 +
ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
 +
lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
 +
fi
 +
#Retrieve additionnal keys if found (your windows key for exemple)
 +
if [ -r $HOME/.ssh/id_dbSQWare.pub ]
 +
then
 +
echo "Id dbSQWare found:"
 +
ls -l $HOME/.ssh/id_dbSQWare.pub
 +
lvsqw_AdditionnalKey=$(cat $HOME/.ssh/id_dbSQWare.pub)
 +
else
 +
lvsqw_AdditionnalKey=""
 +
fi
 +
chmod go-w $HOME
 +
touch $HOME/.ssh/authorized_keys
 +
chmod 700 $HOME/.ssh
 +
chmod 600 $HOME/.ssh/authorized_keys
 +
echo "${c_menu}#########################################################################"
 +
echo "#create key if not found and put SQWareCentral key for distant users"
 +
echo "Enter password when necessary${c_normal}"
 +
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
 +
do
 +
echo ""
 +
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
 +
ssh -o StrictHostKeyChecking=no $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
 +
mkdir -p \$HOME/tmp
 +
if [ -r \$HOME/.ssh/id_dsa.pub ]
 +
then
 +
echo "Id DSA found:"
 +
ls -l \$HOME/.ssh/id_dsa.pub
 +
cat \$HOME/.ssh/id_dsa.pub
 +
elif [ -r \$HOME/.ssh/id_rsa.pub ]
 +
then
 +
echo "Id RSA found:"
 +
ls -l \$HOME/.ssh/id_rsa.pub
 +
cat \$HOME/.ssh/id_rsa.pub
 +
else
 +
ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
 +
cat \$HOME/.ssh/id_rsa.pub
 +
fi
 +
chmod go-w \$HOME
 +
echo "$lvsqw_CentralKey" >>\$HOME/.ssh/authorized_keys
 +
echo "$lvsqw_AdditionnalKey" >>\$HOME/.ssh/authorized_keys
 +
chmod 700 \$HOME/.ssh
 +
chmod 600 \$HOME/.ssh/authorized_keys
 +
EOFSSH
 +
 +
done <$gvsqw_TmpFile.lst
 +
}
 +
</pre>
 +
<br>
 +
=== sqwmys_GenSshKeys.ksh ===
 +
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GenSshKeys.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GenSshKeys.ksh.<br>
 +
ssh keys deployment for MySql
 +
<br><br>
 +
Action:
 +
<pre>
 +
lfsqw_Action()
 +
{
 +
 +
echo "#########################################################################"
 +
echo "#Create/Retrieve key for SQWareCentral"
 +
mkdir -p $HOME/tmp
 +
if [ -r $HOME/.ssh/id_dsa.pub ]
 +
then
 +
echo "Id DSA found:"
 +
ls -l $HOME/.ssh/id_dsa.pub
 +
lvsqw_CentralKey=$(cat $HOME/.ssh/id_dsa.pub)
 +
elif [ -r $HOME/.ssh/id_rsa.pub ]
 +
then
 +
echo "Id RSA found:"
 +
ls -l $HOME/.ssh/id_rsa.pub
 +
lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
 +
else
 +
ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
 +
lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
 +
fi
 +
#Retrieve additionnal keys if found (your windows key for exemple)
 +
if [ -r $HOME/.ssh/id_dbSQWare.pub ]
 +
then
 +
echo "Id dbSQWare found:"
 +
ls -l $HOME/.ssh/id_dbSQWare.pub
 +
lvsqw_AdditionnalKey=$(cat $HOME/.ssh/id_dbSQWare.pub)
 +
else
 +
lvsqw_AdditionnalKey=""
 +
fi
 +
chmod go-w $HOME
 +
touch $HOME/.ssh/authorized_keys
 +
chmod 700 $HOME/.ssh
 +
chmod 600 $HOME/.ssh/authorized_keys
 +
echo "${c_menu}#########################################################################"
 +
echo "#create key if not found and put SQWareCentral key for distant users"
 +
echo "Enter password when necessary${c_normal}"
 +
while read lvsqw_HostName lvsqw_UserName
 +
do
 +
echo ""
 +
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
 +
ssh -o StrictHostKeyChecking=no $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
 +
mkdir -p \$HOME/tmp
 +
if [ -r \$HOME/.ssh/id_dsa.pub ]
 +
then
 +
echo "Id DSA found:"
 +
ls -l \$HOME/.ssh/id_dsa.pub
 +
cat \$HOME/.ssh/id_dsa.pub
 +
elif [ -r \$HOME/.ssh/id_rsa.pub ]
 +
then
 +
echo "Id RSA found:"
 +
ls -l \$HOME/.ssh/id_rsa.pub
 +
cat \$HOME/.ssh/id_rsa.pub
 +
else
 +
ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
 +
cat \$HOME/.ssh/id_rsa.pub
 +
fi
 +
chmod go-w \$HOME
 +
echo "$lvsqw_CentralKey" >>\$HOME/.ssh/authorized_keys
 +
echo "$lvsqw_AdditionnalKey" >>\$HOME/.ssh/authorized_keys
 +
chmod 700 \$HOME/.ssh
 +
chmod 600 \$HOME/.ssh/authorized_keys
 +
EOFSSH
 +
 +
done <$gvsqw_TmpFile.lst
 
}
 
}
 
</pre>
 
</pre>
 
<br>
 
<br>
 
=== sqwmys_PurgeAlert.ksh ===
 
=== sqwmys_PurgeAlert.ksh ===
−
Script d'action $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_PurgeAlert.ksh:<br>
+
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_PurgeAlert.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_PurgeAlert.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_PurgeAlert.ksh.<br>
−
Rotation erreur log Mysql
+
Mysql rotate error log
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Action:
 
<pre>
 
<pre>
 
lfsqw_Action()
 
lfsqw_Action()
Line 59: Line 310:
 
do
 
do
 
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
 
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
−
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
+
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
−
. ./.profile $lvsqw_Instance
+
. \$gvsqw_MysBin/../tools/sqwmys_SetEnv.ksh $lvsqw_Instance
−
$gvsqw_RootSQWareProductionMysql/$gvsqw_VersSQWareProductionMysql/bin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A PurgeAlert
+
\$gvsqw_MysBin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A PurgeAlert
 
EOFSSH
 
EOFSSH
 
sleep 1
 
sleep 1
Line 71: Line 322:
 
<br>
 
<br>
 
=== sqwmys_SlowQuery.ksh ===
 
=== sqwmys_SlowQuery.ksh ===
−
Script d'action $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_SlowQuery.ksh:<br>
+
Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_SlowQuery.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_SlowQuery.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_SlowQuery.ksh.<br>
−
Rotation slow query log Mysql
+
Mysql rotate slow query log
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Action:
 
<pre>
 
<pre>
 
lfsqw_Action()
 
lfsqw_Action()
Line 82: Line 333:
 
do
 
do
 
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
 
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
−
ssh -T $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
+
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
−
. ./.profile $lvsqw_Instance
+
. \$gvsqw_MysBin/../tools/sqwmys_SetEnv.ksh $lvsqw_Instance
−
$gvsqw_RootSQWareProductionMysql/$gvsqw_VersSQWareProductionMysql/bin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A SlowQuery
+
\$gvsqw_MysBin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A SlowQuery
 
EOFSSH
 
EOFSSH
 
sleep 1
 
sleep 1
Line 96: Line 347:
  
 
== etc ==
 
== etc ==
 +
=== CrontabRef.cfg ===
 +
Configuration file $gvsqw_RootexpdbCentral/action/mysql/etc/CrontabRef.cfg:<br>
 +
It contains the operating variables of SQWareCentral for mysql.<br>
 +
If you want to overload default variables,<br>
 +
put in $gvsqw_RootexpdbCentral/action/mysql/etc_cust/CrontabRef.cfg<br>
 +
your modifications (only variables you want to modify).<br>
 +
<br>
 +
File content:
 +
<pre>
 +
</pre>
 +
<br>
 +
<br>
 +
Variable explanation:
 +
<pre>
 +
</pre>
 +
<br>
 
=== sqwc_DbRefVar.cfg ===
 
=== sqwc_DbRefVar.cfg ===
−
Fichier de configuration $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_DbRefVar.cfg:<br>
+
Configuration file $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_DbRefVar.cfg:<br>
−
Il contient les variables de fonctionnement de SQWareCentral pour le SGBD mysql.<br>
+
It contains the operating variables of SQWareCentral for mysql.<br>
−
Les variables que l'on veut modifier sont à placer dans $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_DbRefVar.cfg.<br>
+
If you want to overload default variables,<br>
−
{{Information | Vous dévez forcément adapter ce fichier à votre environnement}}
+
put in $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_DbRefVar.cfg<br>
−
gvsqw_DbRefDatabase_mysql => Database du referentiel [par défaut: 'DatabaseInstanceNameForMysql']
+
your modifications (only variables you want to modify).<br>
−
gvsqw_DbRefInstance_mysql => Instance du referentiel [par défaut: 'InstanceNameForMysql']
+
<br>
−
gvsqw_DbRefPass_mysql => Mot de passe de connexion au referentiel [par défaut: 'ConnexionPassForMysql']
+
File content:
−
gvsqw_DbRefPort_mysql => Port du referentiel [par défaut: 'InstancePortForMysql']
+
<pre>
−
gvsqw_DbRefType_mysql => Type de referentiel [par défaut: 'mysql']
+
export gvsqw_DbRefType_mysql='mysql'
−
gvsqw_DbRefUser_mysql => User de connexion au referentiel [par défaut: 'ConnexionUserForMysql']
+
export gvsqw_DbRefPort_mysql='InstancePortForMysql'
 +
export gvsqw_DbRefInstance_mysql='InstanceNameForMysql'
 +
export gvsqw_DbRefDatabase_mysql='DatabaseInstanceNameForMysql'
 +
export gvsqw_DbRefUser_mysql='ConnexionUserForMysql'
 +
export gvsqw_DbRefPass_mysql='ConnexionPassForMysql'
 +
</pre>
 +
<br>
 +
<br>
 +
Variable explanation:
 +
<pre>
 +
gvsqw_DbRefDatabase_mysql =>
 +
gvsqw_DbRefInstance_mysql =>
 +
gvsqw_DbRefPass_mysql =>
 +
gvsqw_DbRefPort_mysql =>  
 +
gvsqw_DbRefType_mysql =>  
 +
gvsqw_DbRefUser_mysql =>  
 +
</pre>
 
<br>
 
<br>
−
 
 
=== sqwc_ExpVar.cfg ===
 
=== sqwc_ExpVar.cfg ===
−
Fichier de configuration $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_ExpVar.cfg:<br>
+
Configuration file $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_ExpVar.cfg:<br>
−
Il contient les variables de fonctionnement de SQWareCentral pour le SGBD mysql.<br>
+
It contains the operating variables of SQWareCentral for mysql.<br>
−
Les variables que l'on veut modifier sont à placer dans $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_ExpVar.cfg.<br>
+
If you want to overload default variables,<br>
−
gvsqw_MailMsg_mysql => message de début de sujet de mail [par défaut: 'TrtMysqlAuto']
+
put in $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_ExpVar.cfg<br>
−
gvsqw_Mail_mysql => adresse pour envoi des mails [par défaut: 'dba']
+
your modifications (only variables you want to modify).<br>
−
gvsqw_RootSQWareProductionMysql => Répertoire de déploiement de SQWareProduction [par défaut: "~$gvsqw_UserUniqueMysql/SQWareProduction/mysql"]
+
<br>
−
gvsqw_VersDeplSQWareProductionMysql => Version de SQWareProduction à déployer  [par défaut: 'v1.1']
+
File content:
−
gvsqw_VersSQWareProductionMysql => Lien utilisé pour la version courante [par défaut: 'prod']
+
<pre>
 +
export gvsqw_RootexpdbExpMysql="~$gvsqw_UserUniqueMysql/SQWareProduction/mysql"
 +
export gvsqw_VersexpdbExpMysql='prod'
 +
export gvsqw_VersDeplexpdbExpMysql='v3.1'
 +
export gvsqw_Mail_mysql='dba'
 +
export gvsqw_MailMsg_mysql='TrtMysqlAuto'
 +
</pre>
 +
<br>
 +
<br>
 +
Variable explanation:
 +
<pre>
 +
gvsqw_MailMsg_mysql =>
 +
gvsqw_Mail_mysql =>
 +
gvsqw_RootexpdbExpMysql =>
 +
gvsqw_VersDeplexpdbExpMysql =>
 +
gvsqw_VersexpdbExpMysql =>
 +
</pre>
 
<br>
 
<br>
−
 
 
=== sqwc_GlobalVar.cfg ===
 
=== sqwc_GlobalVar.cfg ===
−
Fichier de configuration $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_GlobalVar.cfg:<br>
+
Configuration file $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_GlobalVar.cfg:<br>
−
Il contient les variables de fonctionnement de SQWareCentral pour le SGBD mysql.<br>
+
It contains the operating variables of SQWareCentral for mysql.<br>
−
Les variables que l'on veut modifier sont à placer dans $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_GlobalVar.cfg.<br>
+
If you want to overload default variables,<br>
−
gvsqw_UserUniqueMysql => User Unix generique (unique) de deploiement [par défaut: 'sysdba']
+
put in $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_GlobalVar.cfg<br>
 +
your modifications (only variables you want to modify).<br>
 +
<br>
 +
File content:
 +
<pre>
 +
export gvsqw_Mail_mysql='default@dbsqware.com'
 +
export gvsqw_MailMsg_mysql='TrtMysqlAuto'
 +
export gvsqw_UserUniqueMysql='dbsqware'
 +
export gvsqw_RootSQWareProductionMysql="~$gvsqw_UserUniqueMysql/SQWareProduction/mysql"
 +
export gvsqw_VersSQWareProductionMysql='prod'
 +
export gvsqw_VersDeplSQWareProductionMysql='v3.1'
 +
export gvsqw_DistSQWareProdUser_mysql="$gvsqw_UserUniqueMysql@$gvsqw_Hostname"
 +
</pre>
 +
<br>
 +
<br>
 +
Variable explanation:
 +
<pre>
 +
gvsqw_DistSQWareProdUser_mysql =>
 +
gvsqw_MailMsg_mysql =>
 +
gvsqw_Mail_mysql =>
 +
gvsqw_RootSQWareProductionMysql =>
 +
gvsqw_UserUniqueMysql =>
 +
gvsqw_VersDeplSQWareProductionMysql =>
 +
gvsqw_VersSQWareProductionMysql =>
 +
</pre>
 
<br>
 
<br>
−
 
 
=== .profile_confort ===
 
=== .profile_confort ===
−
Fichier de positionnement d'alias $gvsqw_RootexpdbCentral/action/mysql/etc/.profile_confort:<br>
+
File to put aliases $gvsqw_RootexpdbCentral/action/mysql/etc/.profile_confort:<br>
−
Il contient les alias de confort de SQWareCentral pour le SGBD mysql.<br>
+
Contains confort aliases of SQWareCentral for RDBMS mysql.<br>
−
Peut-être surchargé par $gvsqw_RootexpdbCentral/action/mysql/etc_cust/.profile_confort.<br>
+
<br>
−
act_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/prod;ls -al'
+
Alias explanation:
−
depl_mys => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh mysql sqwmys_DeplScripts.ksh sqwmys_GenLstUnique.ksh NOLOG CONFIRM'
+
<pre>
−
gen_mys => '$gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefMysql.ksh'
+
  act_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/prod;ls -al'
−
repind_mys => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh mysql sqwmys_GatherIndicators.ksh sqwmys_GenLstRepind.ksh NOLOG'
+
  etc_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/etc;ls -al'
 +
  lst_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/lst;ls -al'
 +
  auto_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/auto;ls -al'
 +
  cetc_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/etc_cust;ls -al'
 +
  clst_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/lst_cust;ls -al'
 +
  cauto_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/auto_cust;ls -al'
 +
  depl_mys => '$gvsqw_RootexpdbCentral/menu/sqwc_MenuAction.ksh mysql sqwmys_DeplScripts.ksh sqwmys_GenLstUnique.ksh'
 +
  repind_mys => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh mysql sqwmys_GatherIndicators.ksh sqwmys_GenLstRepind.ksh NOLOG'
 +
  repind_mys_dist => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh mysql sqwmys_GatherIndicatorsDist.ksh sqwmys_GenLstRepindDist.ksh NOLOG'
 +
  gen_mys => '$gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefMysql.ksh'
 +
  menu_mys => '$gvsqw_RootexpdbCentral/menu/sqwc_MenuAction.ksh mysql'
 +
</pre>
 +
<br>
 
<br><br>
 
<br><br>
  
 
== lst ==
 
== lst ==
 +
=== sqwc_Extract_CMDB_Mysql.ksh ===
 +
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_Extract_CMDB_Mysql.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwc_Extract_CMDB_Mysql.ksh.<br>
 +
<br><br>
 +
Query used:
 +
<pre>
 +
select concat('MYS;',ref.mysql_sid,';',ifnull(hst.host_name,'N.A'),';',
 +
from tsqw_Repository ref left outer join tsqw_GenHisto hist
 +
        on (ref.mysql_sid = hist.mysql_sid and hist.month = str_to_date(concat('01/',month(now()),'/',year(now())), '%d/%m/%Y'))
 +
order by 1
 +
</pre>
 +
<br>
 
=== sqwc_GenRefDbMysql.ksh ===
 
=== sqwc_GenRefDbMysql.ksh ===
−
Script de génération de liste $gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefDbMysql.ksh:<br>
+
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefDbMysql.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwc_GenRefDbMysql.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwc_GenRefDbMysql.ksh.<br>
−
Génére le fichier de référence ''database'' Mysql automatiquement à partir du référentiel contenu en bdd.<br>
 
−
[[SQWareRepository:general|En savoir plus sur le référentiel dbb.]]<br>
 
−
[[SQWareRepository:mysql|En savoir plus sur le référentiel dbb mysql.]]<br>
 
−
Le fichier de référence se trouvent dans $gvsqw_RootexpdbCentral/etc_cust/RefDb_mysql.txt.
 
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Query used:
 
<pre>
 
<pre>
 
select concat('MYS;',ref.mysql_sid,';',vol.database_name,';',ref.env,';',
 
select concat('MYS;',ref.mysql_sid,';',vol.database_name,';',ref.env,';',
−
  inst.version,';',hist.maj_version,';',round(vol.size_db/1024,0),';',
 
−
  ref.comments,';',ref.virt_host_name,';',ref.host_name,';',date_format(hist.gather_date, '%d/%m/%Y'))
 
 
from tsqw_Repository ref, tsqw_GenHisto hist, tsqw_VolInstance inst, tsqw_VolDb vol
 
from tsqw_Repository ref, tsqw_GenHisto hist, tsqw_VolInstance inst, tsqw_VolDb vol
 
where hist.month = str_to_date(concat('01/',month(now()),'/',year(now())), '%d/%m/%Y')
 
where hist.month = str_to_date(concat('01/',month(now()),'/',year(now())), '%d/%m/%Y')
Line 161: Line 499:
 
</pre>
 
</pre>
 
<br>
 
<br>
−
 
 
=== sqwc_GenRefMysql.ksh ===
 
=== sqwc_GenRefMysql.ksh ===
−
Script de génération de liste $gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefMysql.ksh:<br>
+
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefMysql.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwc_GenRefMysql.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwc_GenRefMysql.ksh.<br>
−
Génére le fichier de référence Mysql automatiquement à partir du référentiel contenu en bdd.<br>
 
−
[[SQWareRepository:general|En savoir plus sur le référentiel dbb.]]<br>
 
−
[[SQWareRepository:mysql|En savoir plus sur le référentiel dbb mysql.]]<br>
 
−
Le fichier de référence se trouvent dans $gvsqw_RootexpdbCentral/etc_cust/Ref_mysql.txt.
 
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Query used:
 
<pre>
 
<pre>
−
select cast(virt_host_name as char(22)) "Virthost",
+
select cast(virt_host_name as char(30)) "Virthost",
−
cast(host_name as char(12)) "Host",
 
−
cast(username as char(15)) "User",
 
−
cast(mysql_sid as char(20)) "Mysql_sid",
 
−
cast(port as char(4)) "Port",
 
−
cast(status as char(5)) "stat",
 
−
cast(env as char(3)) "env",
 
−
cast(comments as char(26)) "description",
 
−
cast(contact as char(20)) "contact",
 
−
cast(client as char(8)) "client"
 
 
from tsqw_Repository
 
from tsqw_Repository
−
union
+
select cast(virt_host_name as char(30)) "Virthost",
−
select cast(virt_host_name as char(22)) "Virthost",
 
−
cast(host_name as char(12)) "Host",
 
−
cast(username as char(15)) "User",
 
−
cast(mysql_sid as char(20)) "Mysql_sid",
 
−
cast(port as char(4)) "Port",
 
−
cast(status as char(5)) "stat",
 
−
cast(env as char(3)) "env",
 
−
cast(comments  as char(26)) "description",
 
−
cast(contact as char(20)) "contact",
 
−
cast(client as char(8)) "client"
 
 
from tsqw_RepositoryOther
 
from tsqw_RepositoryOther
−
order by 6,2,1,3,4'
+
order by 6,2,1,3,4
 +
</pre>
 +
<br>
 +
=== sqwmys_GenLstInstanceDist.ksh ===
 +
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstInstanceDist.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstInstanceDist.ksh.<br>
 +
Generation of the list of Mysql instances for distant connection (tsqw_Repository)
 +
<br><br>
 +
Query used:
 +
<pre>
 +
select virt_host_name, username, mysql_sid
 +
from tsqw_Repository
 +
where status  in ('DIST','MIX')
 +
order by virt_host_name, username
 
</pre>
 
</pre>
 
<br>
 
<br>
−
 
 
=== sqwmys_GenLstInstance.ksh ===
 
=== sqwmys_GenLstInstance.ksh ===
−
Script de génération de liste $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstInstance.ksh:<br>
+
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstInstance.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstInstance.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstInstance.ksh.<br>
−
Generation de la liste des instances Mysql (tsqw_Repository)
+
Generation of the list of Mysql instances (tsqw_Repository)
 +
<br><br>
 +
Query used:
 +
<pre>
 +
select virt_host_name, username, mysql_sid
 +
from tsqw_Repository
 +
where status  in ('ON','MIX')
 +
order by virt_host_name, username
 +
</pre>
 +
<br>
 +
=== sqwmys_GenLstInstanceNew.ksh ===
 +
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstInstanceNew.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstInstanceNew.ksh.<br>
 +
Generation of the list of Mysql instances (tsqw_Repository) status 'NEW'
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Query used:
 
<pre>
 
<pre>
 
select virt_host_name, username, mysql_sid
 
select virt_host_name, username, mysql_sid
 
from tsqw_Repository
 
from tsqw_Repository
−
where status = 'ON'
+
where status = 'NEW'
 
order by virt_host_name, username
 
order by virt_host_name, username
 +
</pre>
 +
<br>
 +
=== sqwmys_GenLstRepindDist.ksh ===
 +
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstRepindDist.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstRepindDist.ksh.<br>
 +
Generation of the list for reprise of indicators for distant connection (tsqw_Repository/tsqw_GenHisto)
 +
<br><br>
 +
Query used:
 +
<pre>
 +
select virt_host_name, username, mysql_sid
 +
from tsqw_Repository ref
 +
where ref.status in ('DIST','MIX')
 +
and not exists ( select 1 from tsqw_GenHisto inf
 +
where inf.gather_date = date(now())
 +
and ref.mysql_sid = inf.mysql_sid)
 
</pre>
 
</pre>
 
<br>
 
<br>
 
=== sqwmys_GenLstRepind.ksh ===
 
=== sqwmys_GenLstRepind.ksh ===
−
Script de génération de liste $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstRepind.ksh:<br>
+
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstRepind.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstRepind.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstRepind.ksh.<br>
−
Generation de la liste pour reprise des indicateurs (tsqw_Repository/tsqw_GenHisto)
+
Generation of the list for reprise of indicators (tsqw_Repository/tsqw_GenHisto)
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Query used:
 
<pre>
 
<pre>
 
select virt_host_name, username, mysql_sid
 
select virt_host_name, username, mysql_sid
 
from tsqw_Repository ref
 
from tsqw_Repository ref
−
where ref.status = 'ON'
+
where ref.status in ('ON','MIX')
 
and not exists ( select 1 from tsqw_GenHisto inf
 
and not exists ( select 1 from tsqw_GenHisto inf
 
where inf.gather_date = date(now())
 
where inf.gather_date = date(now())
 +
and ref.mysql_sid = inf.mysql_sid)
 
</pre>
 
</pre>
 
<br>
 
<br>
 
=== sqwmys_GenLstUnique.ksh ===
 
=== sqwmys_GenLstUnique.ksh ===
−
Script de génération de liste $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstUnique.ksh:<br>
+
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstUnique.ksh:<br>
−
Peut-être remplacé par $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstUnique.ksh.<br>
+
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstUnique.ksh.<br>
−
Generation liste user unique (tsqw_Repository)
+
Generation of the user unique list (tsqw_Repository)
 
<br><br>
 
<br><br>
−
Requête utilisée:
+
Query used:
 
<pre>
 
<pre>
 
select distinct host_name, '$gvsqw_UserUniqueMysql'
 
select distinct host_name, '$gvsqw_UserUniqueMysql'
 
from tsqw_Repository
 
from tsqw_Repository
−
where status != 'OFF'
+
where status in ('ON','MIX')
 
select distinct host_name, '$gvsqw_UserUniqueMysql'
 
select distinct host_name, '$gvsqw_UserUniqueMysql'
 
from tsqw_RepositoryOther
 
from tsqw_RepositoryOther
 +
order by host_name
 +
</pre>
 +
<br>
 +
=== sqwmys_GenLstUniqueNew.ksh ===
 +
List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstUniqueNew.ksh:<br>
 +
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstUniqueNew.ksh.<br>
 +
Generation of the user unique list (tsqw_Repository) statut 'NEW'
 +
<br><br>
 +
Query used:
 +
<pre>
 +
select distinct host_name, '$gvsqw_UserUniqueMysql'
 +
from tsqw_Repository
 +
where status = 'NEW'
 
order by host_name
 
order by host_name
 
</pre>
 
</pre>
 
<br>
 
<br>
 
<br><br>
 
<br><br>

Latest revision as of 22:53, 17 December 2012

auto

sqwmys_AdddbSQWareProfile.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_AdddbSQWareProfile.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_AdddbSQWareProfile.ksh.
Add dbSQWare profile

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
lvsqw_Instance=$(echo "$lvsqw_Instance"|cut -d':' -f1)
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
if [ -r ~/.profile ]
then
	if [ \$(grep -c 'gvsqw_MysBin' ~/.profile) -eq 0 ]
	then
		cat <<EOFCAT >>~/.profile

#dbSQWare
export gvsqw_Env=''
export gvsqw_MysBin=\\\$HOME/SQWareProduction/mysql/bin
lvsqw_IsTerminal=\\\$(tty 2>&1 >/dev/null;echo \\\$?)
if [ "\\\$lvsqw_IsTerminal" = "0" ] && [ -r \\\$gvsqw_MysBin/../etc/.profile_confort ]
then
	. \\\$gvsqw_MysBin/../etc/.profile_confort
fi

EOFCAT
	fi
elif [ -r ~/.bash_profile ]
then
	if [ \$(grep -c 'gvsqw_MysBin' ~/.bash_profile) -eq 0 ]
	then
		cat <<EOFCAT >>~/.bash_profile

#dbSQWare
export gvsqw_Env=''
export gvsqw_MysBin=\\\$HOME/SQWareProduction/mysql/bin
lvsqw_IsTerminal=\\\$(tty 2>&1 >/dev/null;echo \\\$?)
if [ "\\\$lvsqw_IsTerminal" = "0" ] && [ -r \\\$gvsqw_MysBin/../etc/.profile_confort ]
then
	. \\\$gvsqw_MysBin/../etc/.profile_confort
fi

EOFCAT
	fi
fi
EOFSSH
done <$gvsqw_TmpFile.lst

}


sqwmys_DeplScripts.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_DeplScripts.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_DeplScripts.ksh.
SQWareProduction deployment for Mysql

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p $gvsqw_RootSQWareProductionMysql/$gvsqw_VersDeplSQWareProductionMysql
cd $gvsqw_RootSQWareProductionMysql
rm -f $gvsqw_VersSQWareProductionMysql
ln -s $gvsqw_VersDeplSQWareProductionMysql $gvsqw_VersSQWareProductionMysql
EOFSSH
rsync -aHv $gvsqw_RsyncPathMysql --delete $gvsqw_RootSQWareProduction/mysql/$gvsqw_VersDeplSQWareProductionMysql $lvsqw_UserName@$lvsqw_HostName:$gvsqw_RootSQWareProductionMysql/.
done <$gvsqw_TmpFile.lst
}


sqwmys_GatherIndicatorsDist.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GatherIndicatorsDist.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GatherIndicatorsDist.ksh.
Mysql gather indicators in distant mode

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh $gvsqw_SshOptions $gvsqw_DistSQWareProdUser_mysql <<EOFSSH &
\$gvsqw_MysBin/sqwmys_GatherIndicators.ksh -I "$lvsqw_Instance" -Dist
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}


sqwmys_GatherIndicators.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GatherIndicators.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GatherIndicators.ksh.
Mysql gather indicators

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_MysBin/../tools/sqwmys_SetEnv.ksh $lvsqw_Instance
\$gvsqw_MysBin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A IndicDba
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}


sqwmys_Gen_dbSQWare.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_Gen_dbSQWare.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_Gen_dbSQWare.ksh.
generate dbSQWare files

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
lvsqw_Instance=$(echo "$lvsqw_Instance"|cut -d':' -f1)
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
. \$gvsqw_MysBin/../tools/sqwmys_SetEnv.ksh $lvsqw_Instance
if [ "\$MYSQL_SID" != "$lvsqw_Instance" ]
then
	export MYSQL_SID="$lvsqw_Instance"
fi
\$gvsqw_MysBin/../tools/sqwmys_GenerateCreateInstance.ksh -dbsOnly <<EOF
y
EOF
EOFSSH
done <$gvsqw_TmpFile.lst
}


sqwmys_GenSshKeysInstance.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GenSshKeysInstance.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GenSshKeysInstance.ksh.
ssh keys deployment for MySql

Action: 

lfsqw_Action()
{

echo "#########################################################################"
echo "#Create/Retrieve key for SQWareCentral"
mkdir -p $HOME/tmp
if [ -r $HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l $HOME/.ssh/id_dsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_dsa.pub)
elif [ -r $HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l $HOME/.ssh/id_rsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
else
	ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
fi
#Retrieve additionnal keys if found (your windows key for exemple)
if [ -r $HOME/.ssh/id_dbSQWare.pub ]
then
	echo "Id dbSQWare found:"
	ls -l $HOME/.ssh/id_dbSQWare.pub
	lvsqw_AdditionnalKey=$(cat $HOME/.ssh/id_dbSQWare.pub)
else
	lvsqw_AdditionnalKey=""
fi
chmod go-w $HOME
touch $HOME/.ssh/authorized_keys
chmod 700 $HOME/.ssh
chmod 600 $HOME/.ssh/authorized_keys
echo "${c_menu}#########################################################################"
echo "#create key if not found and put SQWareCentral key for distant users"
echo "Enter password when necessary${c_normal}"
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo ""
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -o StrictHostKeyChecking=no $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p \$HOME/tmp
if [ -r \$HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l \$HOME/.ssh/id_dsa.pub
	cat \$HOME/.ssh/id_dsa.pub
elif [ -r \$HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l \$HOME/.ssh/id_rsa.pub
	cat \$HOME/.ssh/id_rsa.pub
else
	ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
	cat \$HOME/.ssh/id_rsa.pub
fi
chmod go-w \$HOME
echo "$lvsqw_CentralKey" >>\$HOME/.ssh/authorized_keys
echo "$lvsqw_AdditionnalKey" >>\$HOME/.ssh/authorized_keys
chmod 700 \$HOME/.ssh
chmod 600 \$HOME/.ssh/authorized_keys
EOFSSH

done <$gvsqw_TmpFile.lst
}


sqwmys_GenSshKeys.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_GenSshKeys.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_GenSshKeys.ksh.
ssh keys deployment for MySql

Action: 

lfsqw_Action()
{

echo "#########################################################################"
echo "#Create/Retrieve key for SQWareCentral"
mkdir -p $HOME/tmp
if [ -r $HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l $HOME/.ssh/id_dsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_dsa.pub)
elif [ -r $HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l $HOME/.ssh/id_rsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
else
	ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
fi
#Retrieve additionnal keys if found (your windows key for exemple)
if [ -r $HOME/.ssh/id_dbSQWare.pub ]
then
	echo "Id dbSQWare found:"
	ls -l $HOME/.ssh/id_dbSQWare.pub
	lvsqw_AdditionnalKey=$(cat $HOME/.ssh/id_dbSQWare.pub)
else
	lvsqw_AdditionnalKey=""
fi
chmod go-w $HOME
touch $HOME/.ssh/authorized_keys
chmod 700 $HOME/.ssh
chmod 600 $HOME/.ssh/authorized_keys
echo "${c_menu}#########################################################################"
echo "#create key if not found and put SQWareCentral key for distant users"
echo "Enter password when necessary${c_normal}"
while read lvsqw_HostName lvsqw_UserName
do
echo ""
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -o StrictHostKeyChecking=no $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p \$HOME/tmp
if [ -r \$HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l \$HOME/.ssh/id_dsa.pub
	cat \$HOME/.ssh/id_dsa.pub
elif [ -r \$HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l \$HOME/.ssh/id_rsa.pub
	cat \$HOME/.ssh/id_rsa.pub
else
	ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
	cat \$HOME/.ssh/id_rsa.pub
fi
chmod go-w \$HOME
echo "$lvsqw_CentralKey" >>\$HOME/.ssh/authorized_keys
echo "$lvsqw_AdditionnalKey" >>\$HOME/.ssh/authorized_keys
chmod 700 \$HOME/.ssh
chmod 600 \$HOME/.ssh/authorized_keys
EOFSSH

done <$gvsqw_TmpFile.lst
}


sqwmys_PurgeAlert.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_PurgeAlert.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_PurgeAlert.ksh.
Mysql rotate error log

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_MysBin/../tools/sqwmys_SetEnv.ksh $lvsqw_Instance
\$gvsqw_MysBin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A PurgeAlert
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}


sqwmys_SlowQuery.ksh

Action script $gvsqw_RootexpdbCentral/action/mysql/auto/sqwmys_SlowQuery.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/auto_cust/sqwmys_SlowQuery.ksh.
Mysql rotate slow query log

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_MysBin/../tools/sqwmys_SetEnv.ksh $lvsqw_Instance
\$gvsqw_MysBin/sqwmys_RunJob.ksh -I $lvsqw_Instance -A SlowQuery
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}




etc

CrontabRef.cfg

Configuration file $gvsqw_RootexpdbCentral/action/mysql/etc/CrontabRef.cfg:
It contains the operating variables of SQWareCentral for mysql.
If you want to overload default variables,
put in $gvsqw_RootexpdbCentral/action/mysql/etc_cust/CrontabRef.cfg
your modifications (only variables you want to modify).

File content: 







Variable explanation:










sqwc_DbRefVar.cfg


Configuration file $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_DbRefVar.cfg:

It contains the operating variables of SQWareCentral for mysql.

If you want to overload default variables,

put in $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_DbRefVar.cfg

your modifications (only variables you want to modify).



File content:



export gvsqw_DbRefType_mysql='mysql'
export gvsqw_DbRefPort_mysql='InstancePortForMysql'
export gvsqw_DbRefInstance_mysql='InstanceNameForMysql'
export gvsqw_DbRefDatabase_mysql='DatabaseInstanceNameForMysql'
export gvsqw_DbRefUser_mysql='ConnexionUserForMysql'
export gvsqw_DbRefPass_mysql='ConnexionPassForMysql'







Variable explanation:



gvsqw_DbRefDatabase_mysql => 
gvsqw_DbRefInstance_mysql => 
gvsqw_DbRefPass_mysql => 
gvsqw_DbRefPort_mysql => 
gvsqw_DbRefType_mysql => 
gvsqw_DbRefUser_mysql => 








sqwc_ExpVar.cfg


Configuration file $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_ExpVar.cfg:

It contains the operating variables of SQWareCentral for mysql.

If you want to overload default variables,

put in $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_ExpVar.cfg

your modifications (only variables you want to modify).



File content:



export gvsqw_RootexpdbExpMysql="~$gvsqw_UserUniqueMysql/SQWareProduction/mysql"
export gvsqw_VersexpdbExpMysql='prod'
export gvsqw_VersDeplexpdbExpMysql='v3.1'
export gvsqw_Mail_mysql='dba'
export gvsqw_MailMsg_mysql='TrtMysqlAuto'







Variable explanation:



gvsqw_MailMsg_mysql => 
gvsqw_Mail_mysql => 
gvsqw_RootexpdbExpMysql => 
gvsqw_VersDeplexpdbExpMysql => 
gvsqw_VersexpdbExpMysql => 








sqwc_GlobalVar.cfg


Configuration file $gvsqw_RootexpdbCentral/action/mysql/etc/sqwc_GlobalVar.cfg:

It contains the operating variables of SQWareCentral for mysql.

If you want to overload default variables,

put in $gvsqw_RootexpdbCentral/action/mysql/etc_cust/sqwc_GlobalVar.cfg

your modifications (only variables you want to modify).



File content:



export gvsqw_Mail_mysql='default@dbsqware.com'
export gvsqw_MailMsg_mysql='TrtMysqlAuto'
export gvsqw_UserUniqueMysql='dbsqware'
export gvsqw_RootSQWareProductionMysql="~$gvsqw_UserUniqueMysql/SQWareProduction/mysql"
export gvsqw_VersSQWareProductionMysql='prod'
export gvsqw_VersDeplSQWareProductionMysql='v3.1'
export gvsqw_DistSQWareProdUser_mysql="$gvsqw_UserUniqueMysql@$gvsqw_Hostname"







Variable explanation:



gvsqw_DistSQWareProdUser_mysql => 
gvsqw_MailMsg_mysql => 
gvsqw_Mail_mysql => 
gvsqw_RootSQWareProductionMysql => 
gvsqw_UserUniqueMysql => 
gvsqw_VersDeplSQWareProductionMysql => 
gvsqw_VersSQWareProductionMysql => 








.profile_confort


File to put aliases $gvsqw_RootexpdbCentral/action/mysql/etc/.profile_confort:

Contains confort aliases of SQWareCentral for RDBMS mysql.



Alias explanation:



	  act_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/prod;ls -al'
	  etc_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/etc;ls -al'
	  lst_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/lst;ls -al'
	  auto_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/auto;ls -al'
	  cetc_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/etc_cust;ls -al'
	  clst_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/lst_cust;ls -al'
	  cauto_mys => 'cd $gvsqw_RootexpdbCentral/action/mysql/auto_cust;ls -al'
	  depl_mys => '$gvsqw_RootexpdbCentral/menu/sqwc_MenuAction.ksh mysql sqwmys_DeplScripts.ksh sqwmys_GenLstUnique.ksh'
	  repind_mys => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh mysql sqwmys_GatherIndicators.ksh sqwmys_GenLstRepind.ksh NOLOG'
	  repind_mys_dist => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh mysql sqwmys_GatherIndicatorsDist.ksh sqwmys_GenLstRepindDist.ksh NOLOG'
	  gen_mys => '$gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefMysql.ksh'
	  menu_mys => '$gvsqw_RootexpdbCentral/menu/sqwc_MenuAction.ksh mysql'











lst


sqwc_Extract_CMDB_Mysql.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_Extract_CMDB_Mysql.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwc_Extract_CMDB_Mysql.ksh.




Query used:



select concat('MYS;',ref.mysql_sid,';',ifnull(hst.host_name,'N.A'),';',
from tsqw_Repository ref left outer join tsqw_GenHisto hist
        on (ref.mysql_sid = hist.mysql_sid and hist.month = str_to_date(concat('01/',month(now()),'/',year(now())), '%d/%m/%Y'))
order by 1








sqwc_GenRefDbMysql.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefDbMysql.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwc_GenRefDbMysql.ksh.




Query used:



select concat('MYS;',ref.mysql_sid,';',vol.database_name,';',ref.env,';',
from tsqw_Repository ref, tsqw_GenHisto hist, tsqw_VolInstance inst, tsqw_VolDb vol
where hist.month = str_to_date(concat('01/',month(now()),'/',year(now())), '%d/%m/%Y')
    and hist.mysql_sid = ref.mysql_sid
    and hist.mysql_sid = vol.mysql_sid
    and hist.gather_date = vol.gather_date
    and hist.mysql_sid = inst.mysql_sid
    and hist.gather_date = inst.gather_date
order by 1








sqwc_GenRefMysql.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwc_GenRefMysql.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwc_GenRefMysql.ksh.




Query used:



select cast(virt_host_name as char(30)) "Virthost",
from tsqw_Repository
select cast(virt_host_name as char(30)) "Virthost",
from tsqw_RepositoryOther
order by 6,2,1,3,4








sqwmys_GenLstInstanceDist.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstInstanceDist.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstInstanceDist.ksh.

Generation of the list of Mysql instances for distant connection (tsqw_Repository)



Query used:



select virt_host_name, username, mysql_sid
from tsqw_Repository
where status  in ('DIST','MIX')
order by virt_host_name, username








sqwmys_GenLstInstance.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstInstance.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstInstance.ksh.

Generation of the list of Mysql instances (tsqw_Repository)



Query used:



select virt_host_name, username, mysql_sid
from tsqw_Repository
where status  in ('ON','MIX')
order by virt_host_name, username








sqwmys_GenLstInstanceNew.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstInstanceNew.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstInstanceNew.ksh.

Generation of the list of Mysql instances (tsqw_Repository) status 'NEW'



Query used:



select virt_host_name, username, mysql_sid
from tsqw_Repository
where status  = 'NEW'
order by virt_host_name, username








sqwmys_GenLstRepindDist.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstRepindDist.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstRepindDist.ksh.

Generation of the list for reprise of indicators for distant connection (tsqw_Repository/tsqw_GenHisto)



Query used:



select virt_host_name, username, mysql_sid
from tsqw_Repository ref
where ref.status in ('DIST','MIX')
and not exists ( select 1 from tsqw_GenHisto inf
where inf.gather_date = date(now())
and ref.mysql_sid = inf.mysql_sid)








sqwmys_GenLstRepind.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstRepind.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstRepind.ksh.

Generation of the list for reprise of indicators (tsqw_Repository/tsqw_GenHisto)



Query used:



select virt_host_name, username, mysql_sid
from tsqw_Repository ref
where ref.status in ('ON','MIX')
and not exists ( select 1 from tsqw_GenHisto inf
where inf.gather_date = date(now())
and ref.mysql_sid = inf.mysql_sid)








sqwmys_GenLstUnique.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstUnique.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstUnique.ksh.

Generation of the user unique list (tsqw_Repository)



Query used:



select distinct host_name, '$gvsqw_UserUniqueMysql'
from tsqw_Repository
where status  in ('ON','MIX')
select distinct host_name, '$gvsqw_UserUniqueMysql'
from tsqw_RepositoryOther
order by host_name








sqwmys_GenLstUniqueNew.ksh


List generator $gvsqw_RootexpdbCentral/action/mysql/lst/sqwmys_GenLstUniqueNew.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/mysql/lst_cust/sqwmys_GenLstUniqueNew.ksh.

Generation of the user unique list (tsqw_Repository) statut 'NEW'



Query used:



select distinct host_name, '$gvsqw_UserUniqueMysql'
from tsqw_Repository
where status = 'NEW'
order by host_name
















					

						Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareCentral:mysql&oldid=3533"