|
|
| Line 1: |
Line 1: |
| − | ==Generalties==
| |
| − | <br>
| |
| − | {{Warning | Before anything, don't go further if you don't have read the first section [[General:Concepts|"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.<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===
| |
| − | There will potentially be one or two connection cases to handle.
| |
| − | * "local" for Unix machines (or those with Cygwin) where you'll be running MySQL on the machine hosting the instance (SSH connection from dbsqware@sqwarebox).
| |
| − | * "remote" for cases where you don't have SSH access to the machine (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 MySQL/MariaDBinstance==
| |
| − |
| |
| − | ===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 sqwmys_GlobalVar.cfg!<br/>
| |
| − | We'll use a variable, MYSQL_SID, which has no value for MySQL/MariaDB but will identify the instance in dbSQWare.<br/>
| |
| − | By convention, we often use MYS "_" APPLI "_" ENV.<br/>
| |
| − | Example: MYS_THISAPPS_PRD or MYS_THISOTHERAPPS_DEV ...<br/>
| |
| − | But nothing is imposed; you choose what "suits you"! You just need to set the MYSQL_SID variable in the MySQL account profile with the desired value.<br/>
| |
| − |
| |
| − | =====Explanation of statuses=====
| |
| − | *An "open" instance will be handled in "local" mode, meaning we will connect with mysql locally on the machine hosting the instance (ssh access from dbsqware@sqwarebox), status "ON".<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/>
| |
| − | Resume of statuses :<br/>
| |
| − | {| align="center" {{Prettytable}}
| |
| − | |-
| |
| − | | '''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.<br/>
| |
| − | [[File:Admin dbSQWare.png||admin|Admin dbSQWare]]
| |
| − |
| |
| − | <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:DeclarationInstanceMySQL.png|admin|Add a new instance by duplicate an other one.]]
| |
| − | Explanation of the fields :<br/>
| |
| − | {| align="center" {{Prettytable}}
| |
| − | |-
| |
| − | | '''Fields'''
| |
| − | | '''Description'''
| |
| − | |-
| |
| − | | Db Alias
| |
| − | | Unique key that identifies the instance in dbSQWare (no value for MySQL/MariaDB).
| |
| − | |-
| |
| − | | Dbms Name
| |
| − | | Type of SGBD
| |
| − | |-
| |
| − | | Virtual Host
| |
| − | | Virtual Host (same as Host if not clustered)
| |
| − | .
| |
| − | |-
| |
| − | | Host Name
| |
| − | | Hostname of the instance
| |
| − | |-
| |
| − | | User Name
| |
| − | | User associated with the instance
| |
| − | |-
| |
| − | | Port
| |
| − | | Listening port of the instance.
| |
| − | |-
| |
| − | | 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:
| |
| − |
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : dbsqware@sqwarebox
| |
| − | gen_all
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ===Specific procedure for the "local" part (status ON)===
| |
| − | /!\ Only instances that will be in the "ON" status !<br/>
| |
| − |
| |
| − | ====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.<br>
| |
| − | <br>
| |
| − | bash:
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : mysql@my_mysql_host
| |
| − | type bash
| |
| − | #or
| |
| − | which bash
| |
| − | </syntaxhighlight>
| |
| − | rsync:
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : mysql@my_mysql_host
| |
| − | type rsync
| |
| − | #or
| |
| − | which rsync
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | =====Installation=====
| |
| − | Adapt to your machine type (use sudo if you are not root).<br/>
| |
| − | <br>
| |
| − | RedHat / CentOS / ... :
| |
| − | <syntaxhighlight lang="sh" line># From : root@my_mysql_host
| |
| − | yum install -y bash rsync
| |
| − | #or
| |
| − | dnf install -y bash rsync</syntaxhighlight>
| |
| − | Ubuntu / Debian / ... :
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − |
| |
| − | # From : root@my_mysql_host
| |
| − | apt install -y bash rsync
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====Unix user modification====
| |
| − | Adapt to your machine type (sudo if you're not root).
| |
| − | Modify the homedir (not mandatory, but avoids ending up in /var/lib/mysql):
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : root@my_mysql_host
| |
| − | mkdir -p /home/mysql
| |
| − | chown mysql:mysql /home/mysql
| |
| − | chmod 750 /home/mysql
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | ##Stop instance (service mysql/mariadb stop -- or -- systemctl stop mysql/mariadb)
| |
| − | usermod -d /home/mysql mysql -s /bin/bash
| |
| − | ##Start instance (service mysql/mariadb start -- or -- systemctl start mysql/mariadb)
| |
| − |
| |
| − | #Or directly vi /etc/passwd !
| |
| − | example : mysql:x:1004:989::/home/mysql:/bin/bash
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | Assign a passwd to the mysql user (otherwise the key exchange connection will not work!):<syntaxhighlight lang="sh" line>
| |
| − | # From : root@my_mysql_host
| |
| − | passwd mysql
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====Creating a specific MySQL/MariaDB account for dbSQWare (for local connection)====
| |
| − | Adapt to your needs depending on what you want to do...<br/>
| |
| − |
| |
| − | Example for local connection :
| |
| − | <syntaxhighlight lang="sql" line>
| |
| − | grant all privileges on *.* to 'DBSDBA'@'localhost' IDENTIFIED BY 'DoNotSetThisPassword!' with grant option;
| |
| − |
| |
| − | /* or */
| |
| − | create user 'DBSDBA'@'localhost' IDENTIFIED BY 'DoNotSetThisPassword!';
| |
| − | grant all privileges on *.* to 'DBSDBA'@'localhost' with grant option;
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : mysql@my_mysql_host
| |
| − | #Then, create the .my.cnf file to allow connection without using passwd
| |
| − | echo "[client]
| |
| − | password = DoNotSetThisPassword!
| |
| − | " >> $HOME/.my.cnf
| |
| − | chmod 600 $HOME/.my.cnf
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====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 mysql Unix account)
| |
| − | *A manual one that you apply on each mysql Unix account
| |
| − | /!\ The password of your mysql Unix account must have been initialized, otherwise key-based authentication will not work!
| |
| − |
| |
| − | =====Deployment of SSH key(s): batch method=====
| |
| − | <syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
| |
| − | menu_mys GenDeplSshKeys_SQWareCentral GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − | Verify that this is indeed the list you want to deploy, then choose option 1 ...<br/>
| |
| − | => Enter the Unix password when prompted !
| |
| − |
| |
| − | =====Deployment of SSH key(s): manual method=====
| |
| − | <syntaxhighlight lang="sh" line># From : mysql@my_mysql_host
| |
| − | if [ ! -r $HOME/.ssh/id_rsa ]
| |
| − | then
| |
| − | ssh-keygen -t rsa -N ''''''' -f $HOME/.ssh/id_rsa
| |
| − | fi
| |
| − | chmod go-w $HOME
| |
| − | echo "ssh-rsa AAAAB......XSPpdV11 dbsqware@sqwarebox" >>$HOME/.ssh/authorized_keys
| |
| − | chmod 700 $HOME/.ssh
| |
| − | chmod 600 $HOME/.ssh/authorized_keys
| |
| − | </syntaxhighlight>
| |
| − | => Put the correct key in the "echo" (the one from dbsqware@sqwarebox)
| |
| − |
| |
| − | ====SSH connection test from SQWareCentral====
| |
| − | <syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
| |
| − | menu_mys TestSshConnection GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====Test of prerequisites on the target host (bash+rsync) via SSH from SQWareCentral====
| |
| − | <syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
| |
| − | menu_mys TestSysPrerequisites GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====Deployment of SQWareProduction====
| |
| − | <syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
| |
| − |
| |
| − | #If deployment on "unique" user
| |
| − | menu_mys DeplScripts GenLstUniqueNew
| |
| − |
| |
| − | #If deployment on "instance" user, the one specified in SQWareRepository
| |
| − | menu_mys DeplScripts GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | For "unique" user, it is the "standard" user. By default, we set it to "mysql", 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 mysql Unix account
| |
| − |
| |
| − | =====Adding the dbSQWare environment: batch method=====
| |
| − | <syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
| |
| − | menu_mys AdddbSQWareProfile GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − | Check that this is indeed the list you want to deploy, then choose 1 ...<br/>
| |
| − | => 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
| |
| − | <syntaxhighlight lang="sh" line># From : mysql@my_mysql_host
| |
| − |
| |
| − | #dbSQWare
| |
| − | export gvsqw_MysBin=$HOME/SQWareProduction/mysql/bin
| |
| − | export gvsqw_Env='PRD'
| |
| − | 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
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | =====Adding the variable MYSQL_SID=====
| |
| − | Add the MYSQL_SID variable in the ~/.profile or ~/.bash_profile
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : mysql@my_mysql_host
| |
| − | export MYSQL_SID=MYS_INST_PRD
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====Test sendmail (Non mandatory)====
| |
| − | <syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
| |
| − | menu_mys TestSendmail GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ===="Manual" connection test to the MySQL/MariaDB instance ("local" connection)====
| |
| − | /!\ Only instances that will be in "ON" status!
| |
| − | <syntaxhighlight lang="sh" line># From : dbsqware@sqwarebox
| |
| − | menu_mys TestInstConnectionOnNoMail GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ===Specific Procedure for the "Remote" Port (DIST Status)===
| |
| − |
| |
| − | ====Creating a Specific MySQL/MariaDB Account (dbSQWare) (For Remote Connections)====
| |
| − | Example for a "Remote" Connection
| |
| − | <syntaxhighlight lang="sql" line>
| |
| − | grant all privileges on *.* to 'DBSDBA'@'%' IDENTIFIED BY 'DoNotSetThisPassword!' with grant option;
| |
| − |
| |
| − | /* or */
| |
| − | create user 'DBSDBA'@'%' IDENTIFIED BY 'DoNotSetThisPassword!';
| |
| − | grant all privileges on *.* to 'DBSDBA'@'%' with grant option;
| |
| − | flush privileges;
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | You can enter any username and password you like!<br/>
| |
| − | This account must be registered under the Unix account mysql@sqwarebox...<br/>
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | vi /home/mysql/.passwd_mysql
| |
| − | DbAlias;UserName;Passwd;HostName;Port
| |
| − | MYS_INST_PRD;DBSDBA;DoNotSetThisPassword!;srvdbmys01;3306
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====Test "remote" connection====
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : dbsqware@sqwarebox
| |
| − | menu_mys TestInstConnectionDistNoMail GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ==Starting the Instance==
| |
| − | ===Generating the Default SQWareProduction Configuration===
| |
| − | /!\ Only instances that will be in "ON" status!
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : dbsqware@sqwarebox
| |
| − | menu_mys GenDefConf_SQWareProduction GenLstInstanceNew
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ===Update SQWare Repository===
| |
| − | In the dbSQWare repository via the administration console, update the status of the new MySQL/MariaDB instance (currently NEW) to the desired value (ON, DIST).
| |
| − |
| |
| − | ===Testing the "Recovery" of Indicators===
| |
| − | At this point, you can test the recovery of missing indicators (in principle, only the instances you have just integrated!).
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From : dbsqware@sqwarebox
| |
| − | ## for those with status "ON"
| |
| − | repind_mys
| |
| − | ## for those with status "DIST"
| |
| − | repind_mys_dist
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ===Operation (stats, backups, etc.) for "ON" instances===
| |
| − | ====Generating SQWareProduction configs====
| |
| − | If you followed the instance insertion procedure, this part is already done by step "menu_mys GenDefConf_SQWareProduction GenLstInstanceNew".<br/>
| |
| − | You can check with the next chapter.<br/>
| |
| − | <br/>
| |
| − | Connect to the machine that has the “MySQL/MariaDB” instance and type the following commands: <br/>
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | # From dbsqware@vmyswarebox
| |
| − | c MYS_INST_PRD
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | #Source de l'env si multi-instance (from mysql@my_mysql_host)
| |
| − | e MYS_INST_PRD
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | too
| |
| − | ./sqwmys_GenerateCreateInstance.ksh -dbsOnly
| |
| − | </syntaxhighlight>
| |
| − | Vérifier que ce qui est proposé est bon et tapez « y » si c’est le cas.<br/>
| |
| − | A ce moment, vous avez généré les fichiers de configuration par défaut de SQWareProduction.
| |
| − |
| |
| − | ====Explanations====
| |
| − | The previous steps created some default files. ($HOME/MYS_INST_PRD/sqwConfig)
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | #Enter the instance name
| |
| − | e MYS_INST_PRD
| |
| − | cfg
| |
| − | cat sqwmys_Jobs.cfg
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | #############################################
| |
| − | #IndicDba: Gather DBAs indicators
| |
| − | IndicDba:$gvsqw_MysBin/sqwmys_GatherIndicators.ksh -I MYS_INST_PRD
| |
| − |
| |
| − | #Dump: backup all databases with mysqlDump
| |
| − | Dump:$gvsqw_MysBin/sqwmys_DumpAllDatabases.ksh -I MYS_INST_PRD -Exec
| |
| − |
| |
| − | #Optimize : optimize tables for all databases
| |
| − | Optimize:$gvsqw_MysBin/sqwmys_OptimizeAllDatabases.ksh -I MYS_INST_PRD -Exec
| |
| − |
| |
| − | #Analyse : analyze tables for all databases
| |
| − | Analyze:$gvsqw_MysBin/sqwmys_AnalyzeAllDatabases.ksh -I MYS_INST_PRD -Exec
| |
| − |
| |
| − | #XtraFull: Backup full with XtraBackup
| |
| − | XtraFull:$gvsqw_MysBin/sqwmys_XtraBackup.ksh -I MYS_INST_PRD -Type full -Exec
| |
| − |
| |
| − | #XtraInc: Backup incremental with XtraBackup
| |
| − | XtraInc:$gvsqw_MysBin/sqwmys_XtraBackup.ksh -I MYS_INST_PRD -Type inc -Exec
| |
| − |
| |
| − | #MariaFull: Backup full with MariaBackup
| |
| − | MariaFull:$gvsqw_MysBin/sqwmys_MariaBackup.ksh -I MYS_INST_PRD -Type full -Exec
| |
| − |
| |
| − | #MariaInc: Backup incremental with MariaBackup
| |
| − | MariaInc:$gvsqw_MysBin/sqwmys_MariaBackup.ksh -I MYS_INST_PRD -Type inc -Exec
| |
| − |
| |
| − | #PurgeAlert: Rotate error log
| |
| − | PurgeAlert:$gvsqw_MysBin/sqwmys_PurgeAlert.ksh -I MYS_INST_PRD
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | This is a configuration file used by the sqwmys_RunJob.ksh script to execute actions.<br/>
| |
| − | <br/>
| |
| − | File for a default cron job $HOME/CrontabRef_MYS_INST_PRD:
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | ## mm(0-59) hh(0-23) dd(1-31) MM(0-12) DAY(0-sunday, 1-monday, ...) command
| |
| − | ## MYS_INST_PRD
| |
| − | #Purge alert
| |
| − | 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'
| |
| − | # Dump
| |
| − | 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'
| |
| − | # Optimize
| |
| − | 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'
| |
| − | # Analyze
| |
| − | 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'
| |
| − | # XtraBackup
| |
| − | #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'
| |
| − | #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'
| |
| − | # MariaBackup
| |
| − | #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'
| |
| − | #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'
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====Startup====
| |
| − | Setting up cron:
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | mv $HOME/CrontabRef_MYS_INST_PRD $HOME/CrontabRef
| |
| − | vi $HOME/CrontabRef
| |
| − | crontab $HOME/CrontabRef
| |
| − | crontab -l
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | Update the file CrontabRef :
| |
| − | <syntaxhighlight lang="sh" line>
| |
| − | crontab -l > $HOME/CrontabRef
| |
| − | cat $HOME/CrontabRef
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | ====Useful commands====
| |
| − | alias :
| |
| − | e DBALIAS => source env for the instance
| |
| − | bin, etc, too ... => to go to the standard SQWareProduction tree
| |
| − | cbin, cetc, ctoo ... => to go to the custom SQWareProduction tree
| |
| − | log => SQWareProduction logs
| |
| − | dmp => go to the backup paths
| |
| − |
| |
| − | Some script options:
| |
| − | -h => online help
| |
| − | -s => online examples
| |
| − | -Exec => when present; if not set, only outputs commands without executing them.
| |
| − |
| |
| − | Examples :
| |
| − | 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 !
| |
