Difference between revisions of "FAQ:NewInstanceMySQL"

From Wiki_dbSQWare
Jump to: navigation, search
m (→‎General operating procedure d'intégration for integrating a new MySQL/MariaDB instance)
m
Line 8: Line 8:
  
 
===Limits of this section===
 
===Limits of this section===
−
Ce chapitre ne traite que de l'ajout d'une nouvelle instance sur un socle déjà paramétré et fonctionnel.<br>
+
This chapter only deals with adding a new instance to a base that is already configured and functional.<br>
−
Utilisez les portions qui concernent votre environnement et utilisez la méthode match ou manuelle au choix !<br>
+
Use the portions that apply to your environment, and choose either the automatic or manual method as desired<br>
  
−
===General operating procedure for integrating a new MySQL/MariaDB instance===
+
===General operating procedure for integrating a new Oracle instance===
−
There will potentially be 1 or 2 connections cases to process
+
There will potentially be 1 or 2 connection cases to process
−
* <span style="color: red">"local" </span> for Unix machines (or those with Cygwin) where a "mysql" will be performed on the machine hosting the instance (via SSH connection from dbsqware@sqwarebox)
+
* <span style="color: red">"local" </span> for Unix machines (or those with Cygwin) where a "/ as sysdba" will be performed on the machine hosting the instance (via SSH connection from dbsqware@sqwarebox)
−
* "Distant" in cases where SSH access to the machine is not available (Windows, RDS, etc.)
+
* "Distant", for generating AWRs (if needed), or in cases where SSH access to the machine is not available (Windows, RDS, etc.)
  
 
Classic steps:
 
Classic steps:
 +
 
*Setting up the environment
 
*Setting up the environment
 +
 
*Checking system prerequisites for the "local" part (bash + rsync)
 
*Checking system prerequisites for the "local" part (bash + rsync)
 +
 
*Manual tests
 
*Manual tests
 +
 
*Deployment of the instance
 
*Deployment of the instance
  
−
You will see that these steps can be handled one by one or in batches !
+
You will see that these steps can be handled one by one or in batches!
  
−
==Intégration d'une instance MySQL/MariaDB==
+
==Integration of an Oracle instance==
  
−
===Mise en place de l'environnement===
+
===Setting up the environment===
−
====Général====
 
−
=====Explication du "DbAlias" (la clé unique dbSQWare)=====
 
−
La clé dbSQWare doit être unique et raccord avec ce qui aura été paramétré pour la variable gvsqw_DbAlias dans sqwmys_GlobalVar.cfg !<br/>
 
−
On va utiliser une variable, MYSQL_SID qui n'a aucune valeur pour MySQL/MariaDB mais qui va identifier l'instance dans dbSQWare.<br/>
 
−
Par "convention", on utilise souvent MYS "_" APPLI "_" ENV.<br/>
 
−
Exemple : MYS_MONAPPLI_PRD ou MYS_DEUXAPPLI_DEV ...<br/>
 
−
Mais rien n'est imposé, vous choisissez ce qui "vous arrange" ! Il faudra juste positionner la variable MYSQL_SID dans le profile du compte mysql avec la valeur souhaitée.<br/>
 
  
−
=====Explication des statuts=====
+
====General====
−
*Une instance sera traitée en mode "local", c’est-à-dire que l’on va se connecter en "mysql" en local sur la machine qui porte l’instance (accès ssh depuis dbsqware@sqwarebox), statut "ON".<br/>
+
 
−
*Une instance où l’on n’aurait pas d'accès unix local serait gérée en mode "distant" (style Windows, RDS, ...), statut "DIST".<br/>
+
=====Explanation of "DbAlias" (the unique dbSQWare key)=====
−
*Le statut "OFF" fait "disparaître" l’instance de la visu web.<br/>
+
The dbSQWare key must be unique and consistent with what has been configured for the gvsqw_DbAlias variable in sqwora_GlobalVar.cfg!<br/>
−
*Le statut "NEW" est utilisé comme statut intermédiaire entre le début et la fin du paramétrage d'une nouvelle instance.<br/>
+
There are more or less 3 cases...<br/>
−
*Tous les autres statuts permettent d’afficher l’instance dans le référentiel (notamment "KEEP") mais ne seront pas pris en compte par les traitements automatiques.<br/>
+
*Your environment is "standalone" and your ORACLE_SID are unique across the entire environment => gvsqw_DbAlias='$ORACLE_SID'
 +
*Your environment is "standalone" but you have "duplicates" of ORACLE_SID => gvsqw_DbAlias='$ORACLE_SID:$(hostname|cut -d '.' -f1)'
 +
*You have RAC and/or DG => gvsqw_DbAlias='$gvsqw_DB_UNIQUE_NAME:$gvsqw_HOST_UNIQUE_NAME' (gvsqw_DB_UNIQUE_NAME, remove the node number!)
 +
 
 +
+ any other case that might occur or that "suits you"!<br/>
 +
 
 +
=====Explanation of statuses=====
 +
*An "open" instance will be handled in "local" mode, meaning we will connect via "/ as sysdba" locally on the machine hosting the instance (ssh access from dbsqware@sqwarebox), status "ON".<br/>
 +
*A "closed" instance, like dataguard, will be handled in "local" mode, meaning we will connect via "/ as sysdba" locally on the machine hosting the instance (ssh access from dbsqware@sqwarebox), status "DG".<br/>
 +
*An instance where we don’t have local unix access will be managed in "remote" mode (like Windows, RDS, ...), status "DIST".<br/>
 +
*The "OFF" status makes the instance "disappear" from the web view.<br/>
 +
*The "NEW" status is used as an intermediate status between the beginning and the end of setting up a new instance.<br/>
 +
*All other statuses allow the instance to be displayed in the repository (especially "KEEP") but will not be taken into account by the automatic processes.<br/>
 
<br/>
 
<br/>
−
Résumé des statuts :<br/>
+
Resume of statuses :<br/>
 
{| align="center" {{Prettytable}}
 
{| align="center" {{Prettytable}}
 
|-
 
|-
−
| '''Statut'''  
+
| '''Status'''  
 
| '''Description'''  
 
| '''Description'''  
 
|-
 
|-
 
| ON
 
| ON
−
| gestion locale sous unix (le plus courant)
+
| locale under Unix (the most common)
 
|-
 
|-
 
| DIST
 
| DIST
−
| gestion uniquement à distance
+
| remote-only management
 
|-
 
|-
 
| KEEP
 
| KEEP
−
| statut temporaire, lors d'une maintenance par exemple ou avant suppression complète
+
| temporary status, during maintenance for example or before complete deletion
 
|-
 
|-
 
| NEW
 
| NEW
−
| statut temporaire, lors du paramétrage
+
| temporary status, during configuration
 
|-
 
|-
 
| OFF
 
| OFF
−
| suppression visuelle de l'instance (sans suppression de son paramétrage)
+
| visual removal of the instance (without deleting its configuration)
 
|-
 
|-
 
| XXX
 
| XXX
−
| non pris en charge ...
+
| not supported...
 
|}
 
|}
  
−
====Déclaration de l'instance dans SQWareRepository avec SQWareWeb====
+
====Instance declaration in SQWareRepository with SQWareWeb====
−
Nous allons déclarer la nouvelle instance depuis l'interface d'administration de SQWareWeb.<br/>
+
We will declare the new instance from the SQWareWeb administration interface.<br/>
−
[[Fichier:Admin_dbSQWare.png||admin|Lien vers la console d'administration]]<br/>
+
[[File:Admin dbSQWare.png||admin|Admin dbSQWare]]
  
−
Déclarez la nouvelle instance en statut "NEW" (Ajout ou Duplicate)<br/>
+
<br/>
−
[[Fichier:AjoutInstance.png||admin|Ajout d'une instance]]
 
−
[[Fichier:DuplicateInstance.png||admin|Ajout par duplication d'une instance]]<br/>
 
−
[[Fichier:DeclarationInstanceMySQL.png|adminMySQL|Déclaration de la nouvelle instance MySQL/MariaDB]]
 
  
−
Explication des champs :<br/>
+
Declare the new instance with the status "NEW" (Add or Duplicate).<br/>
 +
[[File:AjoutInstance.png||admin|Ajout d'une instance]]
 +
[[File:DuplicateInstance.png||admin|Ajout par duplication d'une instance]]<br/>
 +
[[File:DeclarationInstanceOracle.png||admin|Ajout par duplication d'une instance]]<br/>
 +
Explanation of the fields :<br/>
 
{| align="center" {{Prettytable}}
 
{| align="center" {{Prettytable}}
 
|-
 
|-
−
| '''Champ'''  
+
| '''Fields'''  
 
| '''Description'''  
 
| '''Description'''  
 
|-
 
|-
 
| Db Alias
 
| Db Alias
−
| Clé unique qui identifie l'instance dans dbSQWare (pas de valeur pour MySQL/MariaDB)
+
| Unique key that identifies the instance in dbSQWare (will be used as SERVICE_NAME for generating the tnsname.ora, for the part before the ":" if present).
 
|-
 
|-
 
| Dbms Name
 
| Dbms Name
−
| Type de SGBD
+
| Type of SGBD
 
|-
 
|-
 
| Virtual Host
 
| Virtual Host
−
| Host virtuel (meme que Host si pas cluster)
+
| Virtual host (same as Host if not a cluster, will be used for generating the tnsname.ora).
 
|-
 
|-
 
| Host Name
 
| Host Name
−
| Hostname de l'instance
+
| Hostname of the instance
 
|-
 
|-
 
| User Name
 
| User Name
−
| User qui porte l'instance
+
| User associated with the instance
 
|-
 
|-
 
| Port
 
| Port
−
| Port d'écoute de l'instance
+
| Listening port of the instance (will be used for generating the tnsname.ora).
 
|-
 
|-
 
| Comments
 
| Comments
−
| Une courte description de ce que porte l'instance
+
| A brief description of what the instance hosts
 
|-
 
|-
 
| Status
 
| Status
−
| Instance statut (cf plus haut pour les explications)
+
| Instance status (see above for explanations).
 
|-
 
|-
 
| Contact
 
| Contact
−
| Un contact si besoin
+
| A contact if needed
 
|-
 
|-
 
| Environnement
 
| Environnement
−
| Environnement de l'instance (PRD, PPR, REC, DEV, TST, ...)
+
| Instance environment (PRD, PPR, REC, DEV, TST, ...)
 
|-
 
|-
 
| Client
 
| Client
−
| Utilisé uniquement pour filtrer (mettre un nom de client et/ou de département et/ou service ...
+
| Used only for filtering (enter a client name and/or department and/or service...)
 
|-
 
|-
 
| GlobalHost
 
| GlobalHost
−
| Champ libre dans lequel on met souvent l'hôte de hyperviseur par exemple
+
| Free field in which the hypervisor host is often entered, for example.
 
|-
 
|-
 
| Custom1
 
| Custom1
−
| Champ libre 1
+
| Free field 1
 
|-
 
|-
 
| Custom2
 
| Custom2
−
| Champ libre 2
+
| Free field 2
 
|}
 
|}
−
=====Regénérer les fichiers de référence de SQWareCentral=====
+
=====Regenerate the reference files of SQWareCentral.=====
−
Tapez la commande suivante qui va générer les fichiers de référence :
+
Type the following command which will generate the reference files:
 +
 
 
<syntaxhighlight lang="sh" line>
 
<syntaxhighlight lang="sh" line>
 
# From : dbsqware@sqwarebox
 
# From : dbsqware@sqwarebox
Line 131: Line 142:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
===Procédure spécifique pour la partie "locale" (statut ON)===
+
===Specific procedure for the "local" part (status ON)===
−
/!\ Uniquement que les instances qui seront en statut "ON" !<br/>
+
/!\ Only instances that will be in the "ON" status !<br/>
  
−
====Vérification des prérequis système pour la partie "locale" (bash + rsync)====
+
====Verification of system prerequisites for the "local" part (bash + rsync)====
−
SQWareProduction étant écrit essentiellement en shell bash et étant synchronisé depuis SQWareCentral avec rsync, il nous faut donc "bash" et "rsync" installés !
+
Since SQWareProduction is mainly written in bash shell and synchronized from SQWareCentral using rsync, we therefore need "bash" and "rsync" to be installed !
  
−
=====Vérification=====
+
=====Verification=====
−
Adaptez à votre nom de user et nom de machine.<br>
+
Adapt to your username and machine name.<br>
 
<br>
 
<br>
 
bash:
 
bash:
 
<syntaxhighlight lang="sh" line>
 
<syntaxhighlight lang="sh" line>
−
# From : mysql@my_mysql_host
+
# From : oracle@my_oracle_host
 
type bash
 
type bash
 
#ou
 
#ou
Line 149: Line 160:
 
rsync:
 
rsync:
 
<syntaxhighlight lang="sh" line>
 
<syntaxhighlight lang="sh" line>
−
# mysql@my_mysql_host
+
# From : oracle@my_oracle_host
 
type rsync
 
type rsync
 
#ou
 
#ou
Line 156: Line 167:
  
 
=====Installation=====
 
=====Installation=====
−
Adaptez à votre type de machine (sudo si vous n'êtes pas root).<br/>
+
Adapt to your machine type (use sudo if you are not root).<br/>
 
<br>
 
<br>
 
RedHat / CentOS / ... :
 
RedHat / CentOS / ... :
−
<syntaxhighlight lang="sh" line>
+
<syntaxhighlight lang="sh" line># From : root@my_oracle_host
−
# From : root@my_mysql_host
 
 
yum install -y bash rsync
 
yum install -y bash rsync
 
#ou
 
#ou
−
dnf install -y bash rsync
+
dnf install -y bash rsync</syntaxhighlight>
−
</syntaxhighlight>
 
−
 
 
 
Ubuntu / Debian / ... :
 
Ubuntu / Debian / ... :
 
<syntaxhighlight lang="sh" line>
 
<syntaxhighlight lang="sh" line>
−
# From : root@my_mysql_host
+
 
 +
# From : root@my_oracle_host
 
apt install -y bash rsync
 
apt install -y bash rsync
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
====Modification user unix ====
+
====Deployment of SSH key(s) from SQWareCentral to the target machine(s)====
−
Adaptez à votre type de machine (sudo si vous n'êtes pas root).<br/>
+
There are two ways to proceed:
−
Modification du homedir (pas obligatoire mais évite de se retrouver dans /var/lib/mysql) :
+
*An automatic one (from dbsqware@sqwarebox, but it requires you to know the password of the oracle Unix account)
−
<syntaxhighlight lang="sh" line>
+
*A manual one that you apply on each oracle Unix account
−
# From : root@my_mysql_host
+
/!\ The password of your oracle Unix account must have been initialized, otherwise key-based authentication will not work!
−
mkdir -p /home/mysql
 
−
chown mysql:mysql /home/mysql
 
−
chmod 750 /home/mysql
 
−
</syntaxhighlight>
 
−
 
 
−
<syntaxhighlight lang="sh" line>
 
−
##Stop instance (service mysql/mariadb stop -- ou -- systemctl stop mysql/mariadb)
 
−
usermod -d /home/mysql mysql -s /bin/bash
 
−
##Start instance (service mysql/mariadb start -- ou -- systemctl start mysql/mariadb)
 
−
 
 
−
#Ou directement vi /etc/passwd !
 
−
exemple : mysql:x:1004:989::/home/mysql:/bin/bash
 
−
</syntaxhighlight>
 
−
 
 
−
Affectez un passwd au user mysql (sinon la connexion par échange de clés ne fonctionnera pas !) :
 
−
<syntaxhighlight lang="sh" line>
 
−
# From : root@my_mysql_host
 
−
passwd mysql
 
−
</syntaxhighlight>
 
−
 
 
−
====Création d'un compte MySQL/MariaDB spécifique dbSQWare (Pour connexion locale)====
 
−
Adaptez aux besoins en fonction de ce que vous voulez faire ...<br/>
 
−
 
 
−
Exemple pour une connexion "locale"
 
−
<syntaxhighlight lang="sql" line>
 
−
grant all privileges on *.* to 'DBSDBA'@'localhost' IDENTIFIED BY 'NePasMettreCePassword!' with grant option;
 
−
 
 
−
/* ou */
 
−
create user 'DBSDBA'@'localhost' IDENTIFIED BY 'NePasMettreCePassword!';
 
−
grant all privileges on *.* to 'DBSDBA'@'localhost' with grant option;
 
−
</syntaxhighlight>
 
−
 
 
−
<syntaxhighlight lang="sh" line>
 
−
# From : mysql@my_mysql_host
 
−
#Puis, création du fichier .my.cnf pour permettre la connexion sans passer de passwd
 
−
echo "[client]
 
−
password = NePasMettreCePassword!
 
−
" >> $HOME/.my.cnf
 
−
chmod 600 $HOME/.my.cnf
 
−
</syntaxhighlight>
 
−
 
 
−
====Déploiement de(s) clé(s) ssh de SQWareCentral vers la/les machine(s) cible(s)====
 
−
Il existe deux manières de procéder :
 
−
*Une automatique (depuis dbsqware@sqwarebox mais vous oblige à connaitre le mdp du compte unix mysql)
 
−
*Une manuelle que vous appliquez sur chaque compte unix mysql
 
−
/!\ Le mdp de votre compte mysql unix doit avoir été initialisé, si non, la connexion par échange de clé ne fonctionnera pas !
 
  
−
=====Déploiement de(s) clé(s) ssh : méthode batch=====
+
=====Deployment of SSH key(s): batch method=====
−
<syntaxhighlight lang="sh" line>
+
<syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
−
# From : dbsqware@sqwarebox
+
menu_ora GenDeplSshKeys_SQWareCentral GenLstInstanceNew
−
menu_mys GenDeplSshKeys_SQWareCentral GenLstInstanceNew
 
 
</syntaxhighlight>
 
</syntaxhighlight>
−
Vérifiez que c'est bien la liste que vous souhaitez déployer, puis choix 1 ...<br/>
+
Verify that this is indeed the list you want to deploy, then choose option 1 ...<br/>
−
=> Entrez le mdp unix quand il vous est demandé !
+
=> Enter the Unix password when prompted !
  
−
=====Déploiement de(s) clé(s) ssh : méthode manuelle=====
+
=====Deployment of SSH key(s): manual method=====
−
<syntaxhighlight lang="sh" line>
+
<syntaxhighlight lang="sh" line># From : oracle@my_oracle_host
−
# From : mysql@my_mysql_host
 
 
if [ ! -r $HOME/.ssh/id_rsa ]
 
if [ ! -r $HOME/.ssh/id_rsa ]
 
then
 
then
Line 244: Line 205:
 
chmod 600 $HOME/.ssh/authorized_keys
 
chmod 600 $HOME/.ssh/authorized_keys
 
</syntaxhighlight>
 
</syntaxhighlight>
−
=> Mettre la bonne clé dans le "echo" (celle de dbsqware@sqwarebox)
+
=> Put the correct key in the "echo" (the one from dbsqware@sqwarebox)
  
−
====Test de connexion ssh depuis SQWareCentral====
+
====SSH connection test from SQWareCentral====
−
<syntaxhighlight lang="sh" line>
+
<syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
−
# From : dbsqware@sqwarebox
+
menu_ora TestSshConnection GenLstInstanceNew
−
menu_mys TestSshConnection GenLstInstanceNew
 
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
====Test des prérequis sur l'hote cible (bash+rsync) ssh depuis SQWareCentral====
+
====Test of prerequisites on the target host (bash+rsync) via SSH from SQWareCentral====
−
<syntaxhighlight lang="sh" line>
+
<syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
−
# From : dbsqware@sqwarebox
+
menu_ora TestSysPrerequisites GenLstInstanceNew
−
menu_mys TestSysPrerequisites GenLstInstanceNew
 
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
====Déploiement de SQWareProduction====
+
====Deployment of SQWareProduction====
−
<syntaxhighlight lang="sh" line>
+
<syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
−
# From : dbsqware@sqwarebox
 
  
−
#Si depl sur user "unique"
+
#If deployment on "unique" user
−
menu_mys DeplScripts GenLstUniqueNew
+
menu_ora DeplScripts GenLstUniqueNew
  
−
#Si depl sur user "de l'instance", celui spécifié dans SQWareRepository
+
#If deployment on "instance" user, the one specified in SQWareRepository
−
menu_mys DeplScripts GenLstInstanceNew
+
menu_ora DeplScripts GenLstInstanceNew
 
</syntaxhighlight>
 
</syntaxhighlight>
−
Pour user "unique" c'est l'utilisateur "standard", par défaut nous le réglons sur "mysql", et il peut être modifié dans SQWareCentral.
 
  
−
====Ajout de l'environnement dbSQWare dans ~/.profile ou ~/.bash_profile====
+
For "unique" user, it is the "standard" user. By default, we set it to "oracle", and it can be modified in SQWareCentral.
−
Une nouvelle fois, il existe deux manières de procéder :
+
 
−
*Une automatique (depuis dbsqware@sqwarebox)
+
====Adding the dbSQWare environment to ~/.profile or ~/.bash_profile====
−
*Une manuelle que vous appliquez sur chaque compte unix mysql
+
Once again, there are two ways to proceed:
 +
 
 +
*An automatic one (from dbsqware@sqwarebox)
 +
*A manual one that you apply on each oracle Unix account
  
−
=====Ajout de l'environnement dbSQWare : méthode batch=====
+
=====Adding the dbSQWare environment: batch method=====
−
<syntaxhighlight lang="sh" line>
+
<syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
−
# From : dbsqware@sqwarebox
+
menu_ora AdddbSQWareProfile GenLstInstanceNew
−
menu_mys AdddbSQWareProfile GenLstInstanceNew
 
 
</syntaxhighlight>
 
</syntaxhighlight>
−
Vérifiez que c'est bien la liste que vous souhaitez déployer, puis choix 1 ...<br/>
+
Check that this is indeed the list you want to deploy, then choose 1 ...<br/>  
−
=> Dans ~/.profile ou ~/.bash_profile, changez la variable suivante avec la valeur adaptée : gvsqw_Env='XXX'
+
=> In ~/.profile or ~/.bash_profile, change the following variable with the appropriate value: gvsqw_Env='XXX'
  
−
=====Ajout de l'environnement dbSQWare : méthode manuelle=====
+
=====Adding the dbSQWare environment: manual method=====  
−
Ajoutez les lignes suivantes dans le ~/.profile ou ~/.bash_profile
+
Add the following lines to ~/.profile or ~/.bash_profile
−
<syntaxhighlight lang="sh" line>
+
<syntaxhighlight lang="sh" line># From : oracle@my_oracle_host
−
# From : mysql@my_mysql_host
 
  
 
#dbSQWare
 
#dbSQWare
−
export gvsqw_MysBin=$HOME/SQWareProduction/mysql/bin
+
export gvsqw_OraBin=$HOME/SQWareProduction/oracle/bin
 
export gvsqw_Env='PRD'
 
export gvsqw_Env='PRD'
 
lvsqw_IsTerminal=$(tty 2>&1 >/dev/null;echo $?)
 
lvsqw_IsTerminal=$(tty 2>&1 >/dev/null;echo $?)
−
if [ "$lvsqw_IsTerminal" = "0" ] && [ -r $gvsqw_MysBin/../etc/.profile_confort ]
+
if [ "$lvsqw_IsTerminal" = "0" ] && [ -r $gvsqw_OraBin/../etc/.profile_confort ]
 
then
 
then
−
. $gvsqw_MysBin/../etc/.profile_confort
+
. $gvsqw_OraBin/../etc/.profile_confort
 
fi
 
fi
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
=====Ajout de la variable MYSQL_SID=====
+
====Test sendmail (Non mandatory)====
−
Ajoutez la variable MYSQL_SID dans le ~/.profile ou ~/.bash_profile
+
<syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
−
<syntaxhighlight lang="sh" line>
+
menu_ora TestSendmail GenLstInstanceNew
−
# From : mysql@my_mysql_host
 
−
export MYSQL_SID=MYS_INST_PRD
 
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
====Test sendmail (non obligatoire)====
+
====Manual connection tests to the Oracle instance====
−
<syntaxhighlight lang="sh" line>
+
The goal is to test the automatic connection methods to the Oracle instance.
−
# From : dbsqware@sqwarebox
+
* "local", for instances that will be in "ON" status
−
menu_mys TestSendmail GenLstInstanceNew
+
* "remote", to generate AWR (possibly) or for cases where there is no SSH access to the machine (Windows, RDS, etc...)
−
</syntaxhighlight>
+
 
 +
=====Test connection "local"=====
 +
/!\ Only instances that will be in "ON" status!
  
−
====Test de connexion "manuel" à l'instance MySQL/MariaDB (connexion "locale")====
+
<syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
−
/!\ Uniquement que les instances qui seront en statut "ON" !
+
menu_ora TestInstConnectionOnNoMail GenLstInstanceNew
−
<syntaxhighlight lang="sh" line>
 
−
# From : dbsqware@sqwarebox
 
−
menu_mys TestInstConnectionOnNoMail GenLstInstanceNew
 
 
</syntaxhighlight>
 
</syntaxhighlight>
−
 
 
===Procédure spécifique pour la partie "distante" (statut DIST)===
 
===Procédure spécifique pour la partie "distante" (statut DIST)===
  

Revision as of 09:56, 28 April 2025

Work In Progress

Contents

Generalties


Nuvola apps important.png
Warning:
Before anything, don't go further if you don't have read the first section "dbSQWare Concepts " !.

In this chapter, we will consider that SQWareCentral has been installed on dbsqware@sqwarebox.

Limits of this section

This chapter only deals with adding a new instance to a base that is already configured and functional.
Use the portions that apply to your environment, and choose either the automatic or manual method as desired

General operating procedure for integrating a new Oracle instance

There will potentially be 1 or 2 connection cases to process

  • "local" for Unix machines (or those with Cygwin) where a "/ as sysdba" will be performed on the machine hosting the instance (via SSH connection from dbsqware@sqwarebox)
  • "Distant", for generating AWRs (if needed), or in cases where SSH access to the machine is not available (Windows, RDS, etc.)

Classic steps:

  • Setting up the environment
  • Checking system prerequisites for the "local" part (bash + rsync)
  • Manual tests
  • Deployment of the instance

You will see that these steps can be handled one by one or in batches!

Integration of an Oracle instance

Setting up the environment

General

Explanation of "DbAlias" (the unique dbSQWare key)

The dbSQWare key must be unique and consistent with what has been configured for the gvsqw_DbAlias variable in sqwora_GlobalVar.cfg!
There are more or less 3 cases...

  • Your environment is "standalone" and your ORACLE_SID are unique across the entire environment => gvsqw_DbAlias='$ORACLE_SID'
  • Your environment is "standalone" but you have "duplicates" of ORACLE_SID => gvsqw_DbAlias='$ORACLE_SID:$(hostname|cut -d '.' -f1)'
  • You have RAC and/or DG => gvsqw_DbAlias='$gvsqw_DB_UNIQUE_NAME:$gvsqw_HOST_UNIQUE_NAME' (gvsqw_DB_UNIQUE_NAME, remove the node number!)

+ any other case that might occur or that "suits you"!

Explanation of statuses
  • An "open" instance will be handled in "local" mode, meaning we will connect via "/ as sysdba" locally on the machine hosting the instance (ssh access from dbsqware@sqwarebox), status "ON".
  • A "closed" instance, like dataguard, will be handled in "local" mode, meaning we will connect via "/ as sysdba" locally on the machine hosting the instance (ssh access from dbsqware@sqwarebox), status "DG".
  • An instance where we don’t have local unix access will be managed in "remote" mode (like Windows, RDS, ...), status "DIST".
  • The "OFF" status makes the instance "disappear" from the web view.
  • The "NEW" status is used as an intermediate status between the beginning and the end of setting up a new instance.
  • All other statuses allow the instance to be displayed in the repository (especially "KEEP") but will not be taken into account by the automatic processes.


Resume of statuses :

Status Description
ON locale under Unix (the most common)
DIST remote-only management
KEEP temporary status, during maintenance for example or before complete deletion
NEW temporary status, during configuration
OFF visual removal of the instance (without deleting its configuration)
XXX not supported...

Instance declaration in SQWareRepository with SQWareWeb

We will declare the new instance from the SQWareWeb administration interface.
Admin dbSQWare


Declare the new instance with the status "NEW" (Add or Duplicate).
Ajout d'une instance Ajout par duplication d'une instance
Ajout par duplication d'une instance
Explanation of the fields :

Fields Description
Db Alias Unique key that identifies the instance in dbSQWare (will be used as SERVICE_NAME for generating the tnsname.ora, for the part before the ":" if present).
Dbms Name Type of SGBD
Virtual Host Virtual host (same as Host if not a cluster, will be used for generating the tnsname.ora).
Host Name Hostname of the instance
User Name User associated with the instance
Port Listening port of the instance (will be used for generating the tnsname.ora).
Comments A brief description of what the instance hosts
Status Instance status (see above for explanations).
Contact A contact if needed
Environnement Instance environment (PRD, PPR, REC, DEV, TST, ...)
Client Used only for filtering (enter a client name and/or department and/or service...)
GlobalHost Free field in which the hypervisor host is often entered, for example.
Custom1 Free field 1
Custom2 Free field 2
Regenerate the reference files of SQWareCentral.

Type the following command which will generate the reference files: 

1 # From : dbsqware@sqwarebox
2 gen_all

Specific procedure for the "local" part (status ON)

/!\ Only instances that will be in the "ON" status !

Verification of system prerequisites for the "local" part (bash + rsync)

Since SQWareProduction is mainly written in bash shell and synchronized from SQWareCentral using rsync, we therefore need "bash" and "rsync" to be installed !

Verification

Adapt to your username and machine name.

bash: 

1 # From : oracle@my_oracle_host
2 type bash
3 #ou
4 which bash

rsync: 

1 # From : oracle@my_oracle_host
2 type rsync
3 #ou
4 which rsync
Installation

Adapt to your machine type (use sudo if you are not root).

RedHat / CentOS / ... : 

1 # From : root@my_oracle_host
2 yum install -y bash rsync
3 #ou
4 dnf install -y bash rsync

Ubuntu / Debian / ... : 

1 # From : root@my_oracle_host
2 apt install -y bash rsync

Deployment of SSH key(s) from SQWareCentral to the target machine(s)

There are two ways to proceed:

  • An automatic one (from dbsqware@sqwarebox, but it requires you to know the password of the oracle Unix account)
  • A manual one that you apply on each oracle Unix account

/!\ The password of your oracle Unix account must have been initialized, otherwise key-based authentication will not work!

Deployment of SSH key(s): batch method
1 # From : dbsqware@sqwarebox
2 menu_ora GenDeplSshKeys_SQWareCentral GenLstInstanceNew

Verify that this is indeed the list you want to deploy, then choose option 1 ...
=> Enter the Unix password when prompted !

Deployment of SSH key(s): manual method
1 # From : oracle@my_oracle_host
2 if [ ! -r $HOME/.ssh/id_rsa ]
3 then
4 ssh-keygen -t rsa -N ''''''' -f $HOME/.ssh/id_rsa
5 fi
6 chmod go-w $HOME
7 echo "ssh-rsa AAAAB......XSPpdV11 dbsqware@sqwarebox" >>$HOME/.ssh/authorized_keys
8 chmod 700 $HOME/.ssh
9 chmod 600 $HOME/.ssh/authorized_keys

=> Put the correct key in the "echo" (the one from dbsqware@sqwarebox)

SSH connection test from SQWareCentral

1 # From : dbsqware@sqwarebox
2 menu_ora TestSshConnection GenLstInstanceNew

Test of prerequisites on the target host (bash+rsync) via SSH from SQWareCentral

1 # From : dbsqware@sqwarebox
2 menu_ora TestSysPrerequisites GenLstInstanceNew

Deployment of SQWareProduction

1 # From : dbsqware@sqwarebox
2 
3 #If deployment on "unique" user
4 menu_ora DeplScripts GenLstUniqueNew
5 
6 #If deployment on "instance" user, the one specified in SQWareRepository
7 menu_ora DeplScripts GenLstInstanceNew

For "unique" user, it is the "standard" user. By default, we set it to "oracle", and it can be modified in SQWareCentral.

Adding the dbSQWare environment to ~/.profile or ~/.bash_profile

Once again, there are two ways to proceed:

  • An automatic one (from dbsqware@sqwarebox)
  • A manual one that you apply on each oracle Unix account
Adding the dbSQWare environment: batch method
1 # From : dbsqware@sqwarebox
2 menu_ora AdddbSQWareProfile GenLstInstanceNew

Check that this is indeed the list you want to deploy, then choose 1 ...
=> In ~/.profile or ~/.bash_profile, change the following variable with the appropriate value: gvsqw_Env='XXX'

Adding the dbSQWare environment: manual method

Add the following lines to ~/.profile or ~/.bash_profile 

 1 # From : oracle@my_oracle_host
 2 
 3 #dbSQWare
 4 export gvsqw_OraBin=$HOME/SQWareProduction/oracle/bin
 5 export gvsqw_Env='PRD'
 6 lvsqw_IsTerminal=$(tty 2>&1 >/dev/null;echo $?)
 7 if [ "$lvsqw_IsTerminal" = "0" ] && [ -r $gvsqw_OraBin/../etc/.profile_confort ]
 8 then
 9 . $gvsqw_OraBin/../etc/.profile_confort
10 fi

Test sendmail (Non mandatory)

1 # From : dbsqware@sqwarebox
2 menu_ora TestSendmail GenLstInstanceNew

Manual connection tests to the Oracle instance

The goal is to test the automatic connection methods to the Oracle instance.

  • "local", for instances that will be in "ON" status
  • "remote", to generate AWR (possibly) or for cases where there is no SSH access to the machine (Windows, RDS, etc...)
Test connection "local"

/!\ Only instances that will be in "ON" status! 

1 # From : dbsqware@sqwarebox
2 menu_ora TestInstConnectionOnNoMail GenLstInstanceNew

Procédure spécifique pour la partie "distante" (statut DIST)

Création d'un compte MySQL/MariaDB spécifique dbSQWare (Pour connexion distante)

Exemple pour une connexion "distante" 

1 grant all privileges on *.* to 'DBSDBA'@'%' IDENTIFIED BY 'NePasMettreCePassword!' with grant option;
2 
3 /* ou */
4 create user 'DBSDBA'@'%' IDENTIFIED BY 'NePasMettreCePassword!';
5 grant all privileges on *.* to 'DBSDBA'@'%' with grant option;
6 flush privileges;

Vous pouvez mettre le nom et le mot de passe que vous souhaitez !
Ce compte devra être déclaré sur le compte unix mysql@sqwarebox ...

1  vi /home/mysql/.passwd_mysql
2  DbAlias;UserName;Passwd;HostName;Port
3  MYS_APPLI_PRD;DBSDBA;NePasMettreCePassword!;srvdbmys01;3306

Test connexion "distante"

1 # From : dbsqware@sqwarebox
2 menu_mys TestInstConnectionDistNoMail GenLstInstanceNew

Mise en exploitation de l'instance

Génération de la configuration par défaut SQWareProduction

/!\ Uniquement que les instances qui seront en statut "ON" ! 

1 # From : dbsqware@sqwarebox
2 menu_mys GenDefConf_SQWareProduction GenLstInstanceNew

Mise à jour de SQWareRepository

Dans le repository dbSQWare via la console d’administration, mettre à jour le statut de la nouvelle instance MySQL/MariaDB (à NEW actuellement) avec la valeur souhaitée (ON, DIST)

Test de "reprise" des indicateurs

A ce moment là, vous pouvez tester la reprise des indicateurs manquants (en principe, uniquement les instances que vous venez d'intégrer !) 

1 # From : dbsqware@sqwarebox
2 ## Pour les statuts "ON"
3 repind_mys
4 ## Pour les statuts "DIST"
5 repind_mys_dist

Exploitation (stats, backups, …) pour les instance "ON"

Génération des conf SQWareProduction

Si vous avez suivi la procédure d’insertion de l’instance, cette partie est déjà fait par l’étape "menu_mys GenDefConf_SQWareProduction GenLstInstanceNew".
Vous pouvez vérifier avec le chapitre suivant.

Se connecter à la machine qui porte l'instance « MySQL/MariaDB » et taper les commandes suivantes :

1 # From dbsqware@vmyswarebox
2 c MYS_INST_PRD

1 #Source de l'env si multi-instance (from mysql@my_mysql_host)
2 e MYS_INST_PRD

1 too
2 ./sqwmys_GenerateCreateInstance.ksh -dbsOnly

Vérifier que ce qui est proposé est bon et tapez « y » si c’est le cas.
A ce moment, vous avez généré les fichiers de configuration par défaut de SQWareProduction.

Explications

Les étapes précédentes ont créé certains fichiers par défaut. ($HOME/MYS_INST_PRD/sqwConfig) 

1 #Entrez le nom de l’instance
2 e MYS_INST_PRD
3 cfg
4 cat sqwmys_Jobs.cfg

 1 #############################################
 2 #IndicDba: Gather DBAs indicators
 3 IndicDba:$gvsqw_MysBin/sqwmys_GatherIndicators.ksh -I MYS_INST_PRD
 4 
 5 #Dump: backup all databases with mysqlDump
 6 Dump:$gvsqw_MysBin/sqwmys_DumpAllDatabases.ksh -I MYS_INST_PRD -Exec
 7 
 8 #Optimize : optimize tables for all databases
 9 Optimize:$gvsqw_MysBin/sqwmys_OptimizeAllDatabases.ksh -I MYS_INST_PRD -Exec
10 
11 #Analyse : analyze tables for all databases
12 Analyze:$gvsqw_MysBin/sqwmys_AnalyzeAllDatabases.ksh -I MYS_INST_PRD -Exec
13 
14 #XtraFull: Backup full with XtraBackup
15 XtraFull:$gvsqw_MysBin/sqwmys_XtraBackup.ksh -I MYS_INST_PRD -Type full -Exec
16 
17 #XtraInc: Backup incremental with XtraBackup
18 XtraInc:$gvsqw_MysBin/sqwmys_XtraBackup.ksh -I MYS_INST_PRD -Type inc -Exec
19 
20 #MariaFull: Backup full with MariaBackup
21 MariaFull:$gvsqw_MysBin/sqwmys_MariaBackup.ksh -I MYS_INST_PRD -Type full -Exec
22 
23 #MariaInc: Backup incremental with MariaBackup
24 MariaInc:$gvsqw_MysBin/sqwmys_MariaBackup.ksh -I MYS_INST_PRD -Type inc -Exec
25 
26 #PurgeAlert: Rotate error log
27 PurgeAlert:$gvsqw_MysBin/sqwmys_PurgeAlert.ksh -I MYS_INST_PRD

C’est un fichier de paramétrage qui est utilisé par le script sqwmys_RunJob.ksh pour exécuter des actions.

Fichier pour une cron par défaut $HOME/CrontabRef_MYS_INST_PRD : 

 1 ## mm(0-59) hh(0-23) dd(1-31) MM(0-12) DAY(0-sunday, 1-monday, ...) command
 2 ## MYS_INST_PRD
 3 #Purge alert
 4 00 08 * * 0 bash -c '. $HOME/.profile MYS_INST_PRD;$gvsqw_MysBin/sqwmys_RunJob.ksh -I MYS_INST_PRD -A PurgeAlert > $HOME/tmp/PurgeAlert_MYS_INST_PRD.log 2>&1'
 5 # Dump
 6 00 20 * * * bash -c '. $HOME/.profile MYS_INST_PRD;$gvsqw_MysBin/sqwmys_RunJob.ksh -I MYS_INST_PRD -A Dump > $HOME/tmp/Dump_MYS_INST_PRD.log 2>&1'
 7 # Optimize
 8 00 04 * * 6 bash -c '. $HOME/.profile MYS_INST_PRD;$gvsqw_MysBin/sqwmys_RunJob.ksh -I MYS_INST_PRD -A Optimize > $HOME/tmp/Optimize_MYS_INST_PRD.log 2>&1'
 9 # Analyze
10 00 05 * * 6 bash -c '. $HOME/.profile MYS_INST_PRD;$gvsqw_MysBin/sqwmys_RunJob.ksh -I MYS_INST_PRD -A Analyze > $HOME/tmp/Analyze_MYS_INST_PRD.log 2>&1'
11 # XtraBackup
12 #00 21 * * 6   bash -c '. $HOME/.profile MYS_INST_PRD;$gvsqw_MysBin/sqwmys_RunJob.ksh -I MYS_INST_PRD -A XtraFull > $HOME/tmp/XtraFull_MYS_INST_PRD.log 2>&1'
13 #00 21 * * 1-5 bash -c '. $HOME/.profile MYS_INST_PRD;$gvsqw_MysBin/sqwmys_RunJob.ksh -I MYS_INST_PRD -A XtraInc > $HOME/tmp/XtraInc_MYS_INST_PRD.log 2>&1'
14 # MariaBackup
15 #00 21 * * 6   bash -c '. $HOME/.profile MYS_INST_PRD;$gvsqw_MysBin/sqwmys_RunJob.ksh -I MYS_INST_PRD -A MariaFull > $HOME/tmp/MariaFull_MYS_INST_PRD.log 2>&1'
16 #00 21 * * 1-5 bash -c '. $HOME/.profile MYS_INST_PRD;$gvsqw_MysBin/sqwmys_RunJob.ksh -I MYS_INST_PRD -A MariaInc > $HOME/tmp/MariaInc_MYS_INST_PRD.log 2>&1'

Mise en exploitation

Mise en place de la cron : 

1 mv $HOME/CrontabRef_MYS_INST_PRD $HOME/CrontabRef
2 vi $HOME/CrontabRef
3 crontab $HOME/CrontabRef
4 crontab -l

Mise à jour du fichier CrontabRef  : 

1 crontab -l > $HOME/CrontabRef
2 cat $HOME/CrontabRef

Commandes utiles

alias intéressants : 

e DBALIAS => source l'env pour l'instance
bin, etc, too ... => pour aller dans arbo SQWareProduction standard
cbin, cetc, ctoo ... => pour aller dans arbo SQWareProduction custom
log => logs SQWareProduction
dmp => on va dans les chemins de backup

Quelques options des scripts : 

-h => aide en ligne
-s => exemples en ligne
-Exec => lorsque elle est présente, si non settée ne sort que les commandes sans les exécuter

Exemples : 

mysql@my_mysql_host:$HOME/admin/MYS_INST_PRD/logs/DumpAllDatabases (MYS_DBA_PRD) $ bin
total 100
drwxr-x---  2 dbsqware dba 4096 Sep  6 14:09 .
drwxr-x--- 15 dbsqware dba  187 Aug 16 15:50 ..
-rwxr-x---  1 dbsqware dba 2957 Aug 16 15:50 sqwmys_AnalyzeAllDatabases.ksh
-rwxr-x---  1 dbsqware dba 2514 Aug 16 15:50 sqwmys_AnalyzeDatabase.ksh
-rwxr-x---  1 dbsqware dba 2608 Aug 16 15:50 sqwmys_CheckAllDatabases.ksh
-rwxr-x---  1 dbsqware dba 2257 Aug 16 15:50 sqwmys_CheckDatabase.ksh
-rwxr-x---  1 dbsqware dba 5418 Aug 16 15:50 sqwmys_CopyDumpAllDatabases.ksh
-rwxr-x---  1 dbsqware dba 5324 Aug 16 15:50 sqwmys_CopyDumpDatabase.ksh
-rwxr-x---  1 dbsqware dba 5610 Sep  6 10:45 sqwmys_CopyMariaBackup.ksh
-rwxr-x---  1 dbsqware dba 5594 Sep  6 14:08 sqwmys_CopyXtraBackup.ksh
-rwxr-x---  1 dbsqware dba 3248 Aug 16 15:50 sqwmys_DumpAllDatabases.ksh
-rwxr-x---  1 dbsqware dba 3541 Aug 16 15:50 sqwmys_DumpDatabase.ksh
lrwxrwxrwx  1 dbsqware dba   36 Jan  2  2022 sqwmys_ExecSQL.ksh -> ../../generic/bin/sqwgen_ExecSQL.ksh
-rwxr-x---  1 dbsqware dba 2455 Aug 16 15:50 sqwmys_GatherIndicators.ksh
-rwxr-x---  1 dbsqware dba 2454 Aug 16 15:50 sqwmys_GatherStructure.ksh
-rwxr-x---  1 dbsqware dba 3043 Aug 16 15:50 sqwmys_MariaBackup.ksh
lrwxrwxrwx  1 dbsqware dba   38 Jan  2  2022 sqwmys_NetBackup.ksh -> ../../generic/bin/sqwgen_NetBackup.ksh
-rwxr-x---  1 dbsqware dba 2959 Aug 16 15:50 sqwmys_OptimizeAllDatabases.ksh
-rwxr-x---  1 dbsqware dba 2522 Aug 16 15:50 sqwmys_OptimizeDatabase.ksh
lrwxrwxrwx  1 dbsqware dba   40 Jan  2  2022 sqwmys_ParallelRun.ksh -> ../../generic/bin/sqwgen_ParallelRun.ksh
-rwxr-x---  1 dbsqware dba 2252 Aug 16 15:50 sqwmys_PurgeAlert.ksh
-rwxr-x---  1 dbsqware dba 3855 Aug 16 15:50 sqwmys_RestoreAllDatabases.ksh
-rwxr-x---  1 dbsqware dba 3463 Aug 16 15:50 sqwmys_RestoreDatabase.ksh
-rwxr-x---  1 dbsqware dba 3825 Aug 16 15:50 sqwmys_RotateLogSlowQuery.ksh
lrwxrwxrwx  1 dbsqware dba   40 Jan  2  2022 sqwmys_RsyncBackup.ksh -> ../../generic/bin/sqwgen_RsyncBackup.ksh
lrwxrwxrwx  1 dbsqware dba   35 Jan  2  2022 sqwmys_RunJob.ksh -> ../../generic/bin/sqwgen_RunJob.ksh
-rwxr-x---  1 dbsqware dba 3025 Aug 16 15:50 sqwmys_XtraBackup.ksh

mysql@my_mysql_host:$HOME/SQWareProduction/mysql/bin (MYS_INST_PRD) $ 
./sqwmys_DumpAllDatabases.ksh -h

Sourcing sqwmys_Global.lib v2023.06 SQWareProduction for MySQL (dbSQWare) ...

Usage: sqwmys_DumpAllDatabases.ksh [-h] -I instance [+ options]

DESCRIPTION
   sqwmys_DumpAllDatabases.ksh dump databases in parallel
SUPPORT
   MySql   supported versions: 5.0 <= v <= 8
   MariaDB supported versions: 5.5 <= v <= 11.6

PARAMETERS
     -I  instance       : Target instance to mysqldump.
OPTIONS
     -h  help           : Display the full usage.
     -s                 : Display samples of usage.
     -P  Nb threads     : Number of threads in parallel (by default 2).
     -RD directory      : Directory to write dump file (default /backups/mysql/$MYSQL_SID).
     -ID listdb         : List of databases to be dumped (by default all).
     -ED listdb         : List of databases to exclude from dump.
     -IL likeclause     : Like clause to generate databases list (example: 'db%').
     -EL likeclause     : Like clause to exclude databases (example: 'nodb%').
     -HI Nb_Generations : Number of generations to keep (by default 2).
     -WCD  whereclause  : Where clause to generate databases list.
     -AGR  Nb sec       : Nb of seconds between two runs (by default 1s).
     -AGE  Nb sec       : Nb of seconds between two checks of end (by default 10s).
     -FRT  return_code  : Force return code value on error.
     -Opt  option       : Additional options for mysqldump.
     -Dist              : For distant connection to database (change $gvsqw_ConnectString to $gvsqw_DistConnectString ).
     -AddMail email     : Email address to add at 'xxx'.
     -SendReport        : Send execution log report.
     -NoMail            : Deactivate sendmail on error xxx (by default, send on error).
     -Locale   locale   : Force Locale for help display (fr,en).
     -Consistent        : Lock for consistent mode (by default, no lock).
     -Exec              : Execute commands (default, display generated commands)


Enjoy !

Retrieved from "https://wiki.dbsqware.com/index.php?title=FAQ:NewInstanceMySQL&oldid=32634"