Merge branch 'master' of https://forge.cadoles.com/Cadoles/eole-mariadb

README.md

@@ -6,7 +6,81 @@ mise en clustter
 
 # How-to for Eole 2.6.2 (using this repo dico and templates)
 
-### FIXME
+  Start with 3 fresh eolebase 2.6.2 instancied.
+
+  One for the Clustter Leader, another for the cluster Node another for the Arbitrator, the arbitrator can be replaced by another node.
+
+  The order is very important, Never configure a Node before the Leader ! The Leader must be the first one.
+
+## Add the Cadoles repository on all the nodes and the Leader
+
+  GenConfig  [Mode Expert] -> Dépôts Tiers
+
+ * Libellé du dépôt : Cadoles dev
+ * Déclaration du dépôt : deb [ arch=all ] https://vulcain.cadoles.com 2.6.2-dev main
+ * Méthode de récupération de la clé publique du dépôt : URL de la clé
+ * URL de la clé : https://vulcain.cadoles.com/cadoles.gpg
+
+## Add the MariaDB repository on all the nodes and the Leader
+
+GenConfig (Mode Expert) -> Dépôt tiers :
+ * Libellé du dépôt : MariaDB
+ * Déclaration du dépôt :  deb [arch=amd64] http://mariadb.mirrors.ovh.net/MariaDB/repo/10.2/ubuntu xenial main
+ * Méthode de récupération de la clé publique du dépôt : serveur de clés
+ * URL du serveur de clés : hkp://keyserver.ubuntu.com:80
+ * Empreinte de la clé : 0xF1656F24C74CD1D8
+
+## Configure the Leader :
+
+GenConfig (Mode Normal) -> Services :
+ * Activer le serveur de base de données MariaDB : oui
+
+GenConfig (Mode Normal) -> Database :
+ * Activer la mise en grappe MariaDB Galera ? : oui
+
+GenConfig (Mode Normal) -> Database cluster :
+ * Rôle dans la grappe : Leader
+ * Interface réseau dédiée à la grappe BDD : eth0
+ * Nom du noeud local : <THE NAME YOU WANT>
+ * Membre de la grappe BDD :
+   * Membre de la grappe BDD : <NAME OF THE LEADER>
+   * Adresse IP : <IP OF THE LEADER>
+
+   * Membre de la grappe BDD : <NAME OF THE ARBITRATOR OR NODE>
+   * Adresse IP : <IP OF THE NODE OR ARBITRATOR>
+ * *Nom de la grappe : <NAME OF THE CLUSTER>
+
+## Reconfigure Leader !
+  Run reconfigure command !
+
+## Configure the Nodes !
+
+GenConfig (Mode Normal) -> Services :
+ * Activer le serveur de base de données MariaDB : oui
+
+GenConfig (Mode Normal) -> Database :
+ * Activer la mise en grappe MariaDB Galera ? : oui
+
+GenConfig (Mode Normal) -> Database cluster :
+ * Rôle dans la grappe : Node
+ * Interface réseau dédiée à la grappe BDD : eth0
+ * Nom du noeud local : <THE NAME YOU WANT>
+ * Membre de la grappe BDD :
+   * Membre de la grappe BDD : <NAME OF THE LEADER>
+   * Adresse IP : <IP OF THE LEADER>
+
+   * Membre de la grappe BDD : <NAME OF THE ARBITRATOR OR NODE>
+   * Adresse IP : <IP OF THE NODE OR ARBITRATOR>
+ * *Nom de la grappe : <NAME OF THE CLUSTER>
+
+## Reconfigure the Nodes !
+  Run reconfigure command on each node one by one.
+
+## Configure the Arbitrator if needed !
+
+  If you have only 2 nodes you need to setup an arbitrator, for this follow the tutorial in the eole-galera-arbitrator project page https://forge.cadoles.com/Cadoles/eole-galera-arbitrator
+
+
 
 # How-to for Eole 2.6.2 (From scratch)
 

debian/compat

@@ -1 +0,0 @@
-9

debian/control

@@ -1,18 +0,0 @@
-Source: eole-mariadb
-Section: web
-Priority: optional
-Maintainer: Cadoles <eole@ac-dijon.fr>
-Build-Depends: debhelper (>= 9)
-Standards-Version: 3.9.3
-Homepage: https://forge.cadoles.com/Cadoles/eole-mariadb
-Vcs-Git: https://forge.cadoles.com/Cadoles/eole-mariadb.git
-Vcs-Browser: https://forge.cadoles.com/Cadoles/eole-mariadb
-
-Package: eole-mariadb
-Architecture: all
-Depends: ${misc:Depends}, mariadb-server
-Conflicts: eole-mysql
-Provides: eole-mysql
-Description: Dictionnaires et templates pour la configuration d'un serveur MariaDB, testée uniquement avec eolebase
- .
- Pour toute information complémentaire, veuillez vous rendre sur la forge Cadoles.

debian/copyright

@@ -1,44 +0,0 @@
-Format: http://www.debian.org/doc/packaging-manuals/copyright-format/1.0/
-Upstream-Name: {PROJECT}
-Source: {URL}
-
-Files: *
-Copyright: YEAR {UPSTREAM} {AUTHOR} <{MAIL}>
-License: {UPSTREAM LICENSE}
-
-Files: debian/*
-Copyright: 2012 Équipe EOLE <eole@ac-dijon.fr>
-License: CeCILL-2
-
-License: {UPSTREAM LICENSE}
- {TEXT OF THE LICENSE}
-
-License: CeCILL-2
- This software is governed by the CeCILL-2 license under French law and
- abiding by the rules of distribution of free software.  You can  use,
- modify and or redistribute the software under the terms of the CeCILL-2
- license as circulated by CEA, CNRS and INRIA at the following URL
- "http://www.cecill.info";.
- .
- As a counterpart to the access to the source code and  rights to copy,
- modify and redistribute granted by the license, users are provided only
- with a limited warranty  and the software's author,  the holder of the
- economic rights,  and the successive licensors  have only  limited
- liability.
- .
- In this respect, the user's attention is drawn to the risks associated
- with loading,  using,  modifying and/or developing or reproducing the
- software by the user in light of its specific status of free software,
- that may mean  that it is complicated to manipulate,  and  that  also
- therefore means  that it is reserved for developers  and  experienced
- professionals having in-depth computer knowledge. Users are therefore
- encouraged to load and test the software's suitability as regards their
- requirements in conditions enabling the security of their systems and/or
- data to be ensured and,  more generally, to use and operate it in the
- same conditions as regards security.
- .
- The fact that you are presently reading this means that you have had
- knowledge of the CeCILL-2 license and that you accept its terms.
- .
- On Eole systems, the complete text of the CeCILL-2 License can be found
- in '/usr/share/common-licenses/CeCILL-2-en'.

debian/gbp.conf

@@ -1,3 +0,0 @@
-# Set per distribution debian tag
-[DEFAULT]
-debian-tag = debian/eole/%(version)s

debian/rules

@@ -1,8 +0,0 @@
-#!/usr/bin/make -f
-# -*- makefile -*-
-
-# Uncomment this to turn on verbose mode.
-#export DH_VERBOSE=1
-
-%:
-	dh $@

debian/source/format

@@ -1 +0,0 @@
-3.0 (native)

dicos/25_bdd_server.xml

@@ -30,8 +30,11 @@
            <variable name="dbEnable" type='oui/non' description='Activer le serveur de base de données MariaDB'>
               <value>non</value>
            </variable>
+           <variable name="dbEnableBackup" type='oui/non' description='Activer la sauvegarde sur le serveur de base de données MariaDB'>
+              <value>non</value>
+           </variable>
         </family>
-		<family name="DataBase">
+        <family name="Database">
             <variable name='dbEnableCluster' type='oui/non' description="Activer la mise en grappe MariaDB Galera ?">
                 <value>non</value>
             </variable> 
@@ -54,10 +57,19 @@
         </family>
 
         <!-- Tunning -->
-		<family name="DataBase Tunning" mode="expert">
+        <family name="Database Tunning" mode="expert">
             <variable name="dbInnoDBBufferPoolPercentage" type="number" description="Pourcentage de mémoire à dédier à MariaDB">
                 <value>20</value>
             </variable>
+            <variable name="dbInnoDBLogBufferSize" type='number' description="Taille du tampon utilisé pour écrire les fichiers de transaction innodb sur le disque (en Mo)">
+                <value>8</value>
+            </variable>
+            <variable name='dbInnoDBLogFileSize' type='number' description="Taille des fichiers de transaction InnoDB">
+                <value>6</value>
+            </variable>
+            <variable name='dbInnoDBLockWaitTimeout' type='number' description="Nombre de millisecondes d'attente avant de fermer la connection en lock (innodb_lock_wait_timeout)">
+                <value>120</value>
+            </variable>
             <variable name="dbMaxConnections" type="number" description="Nombre de connection maximum pour le serveur de bases de données (max_connections)">
                 <value>100</value>
             </variable>
@@ -94,6 +106,12 @@
             <variable name="dbAutoOptimizeAndRepare" type="oui/non" description="Activer l'autoréparation et optimisation de toutes les bases de données">
                 <value>non</value>
             </variable>
+            <variable name="dbMaxAllowedPacket" type='number' description="Taille maximum d'un paquet (en Mo)">
+                <value>32</value>
+            </variable>
+            <variable name='dbInteractiveTimeout' type='number' description="Nombre de secondes d'attente avant de fermer la connection en mode interactif">
+                <value>28800</value>
+            </variable>
         </family>
 
         <family name="Database Cluster">
@@ -162,8 +180,9 @@
 
         <condition name='disabled_if_in' source="dbEnable">
             <param>non</param>
-			<target type='family'>Database Cluster</target>
             <target type='family'>Database</target>
+            <target type='family'>Database Cluster</target>
+            <target type='family'>Database Tunning</target>
             <target type='service_accesslist'>dbCluster</target>
             <target type='filelist'>dbTunning</target>
             <target type='filelist'>dbMariaDB</target>
@@ -197,14 +216,6 @@
             <target type='variable'>dbSlowQueryLogTime</target>
             <target type='variable'>dbSlowQueryLogFile</target>
         </condition>
-
-		<condition name='disabled_if_in' source="dbClusterPosition">
-			<param>Node</param>
-			<target type='variable'>dbCreateFixAdmin</target>
-			<target type='variable'>accName</target>
-			<target type='variable'>accLimits</target>
-			<target type='variable'>accLimitTarget</target>
-		</condition>
     </constraints>
     <help/>
 </creole>

eole-mariadb.mk

@@ -1 +1,3 @@
 creolefuncs_DATA_DIR := $(DESTDIR)/usr/share/creole/funcs
+schedule_extra_DATA_DIR := $(DESTDIR)/usr/share/eole/creole/extra/schedule/
+schedule_config_DATA_DIR := $(DESTDIR)/usr/share/eole/schedule/

postservice/25-bdd-admin

@@ -20,12 +20,11 @@ then
     sqlFile=$(mktemp -p /root)
 
     password=$(pwgen -1 10)
-    /usr/share/eole/sbin/mariaPwd ${password}
+    /usr/share/eole/sbin/mariadbPwd ${password}
 
     accounts=($(CreoleGet accName))
     limits="$(CreoleGet accLimitTarget '')"
     accLimits=(${limits// /})
-    index=0
 
     previousUsers=($(awk -F ':' '{print $1}' ${readerfile} ))
 
@@ -40,15 +39,22 @@ then
         then
             hostsList+=(${accLimits[${index}]//,/ })
         else
-            ipAddr=($(CreoleGet ip_my_dist))
+            ipAddr=($(CreoleGet ipMariaDBLimit))
-            ipMask=($(CreoleGet mask_my_dist))
+            ipMask=($(CreoleGet maskMariaDBLimit))
-            if [[ ${ipMask} == "255.255.255.255" ]]
+            ix=0
+            for ip in ${ipAddr[@]}
+            do
+                if [[ ${ipMask[${ix}]} == "255.255.255.255" ]]
                 then
-                hostsList+=("${ipAddr}")
+                    hostsList+=("${ip}")
                 else
-                hostsList+=("${ipAddr}/${ipMask}")
+                    hostsList+=("${ip}/${ipMask[${ix}]}")
                 fi
+                ix=$((ix+1))
+            done
         fi
+
+        index=0
         for hst in ${hostsList[@]}
         do
             SQL="DROP USER IF EXISTS '${user}'@'${hst}';"

posttemplate/25-bdd-cluster

@@ -9,7 +9,11 @@ if [[ ${db} == "oui" ]]
 then
     if [[ ${cluster} == "oui" ]]
     then
-		[[ ${role} == "Leader"  ]] && galera_new_cluster
+		if [[ ${role} == "Leader"  ]]
+		then
+			service mariadb stop
+		    galera_new_cluster
+		fi
     fi
 fi
 

preservice/25-bdd-init

@@ -4,6 +4,11 @@
 # and secure MariaDB installation
 #
 
+INITDONE="/etc/eole/.galera"
+
+[[ "$(CreoleGet dbEnable)" == "non" ]] && exit 0
+[[ -e ${INITDONE} ]] && exit 0
+
 ROLE=$(CreoleGet dbClusterPosition 'Leader')
 SECURE_CMD="mysql_secure_installation"
 PASSWORD=$(pwgen -1 10)
@@ -15,7 +20,7 @@ logfile="${logdir}/maria-init.log"
 
 if [[ ${ROLE} == "Leader" ]]
 then
-   service mariadb start
+   service mysql start
    ${passwd} ${PASSWORD}
 
    # Run secure installation script
@@ -28,7 +33,15 @@ y
 y
 y
 __EOF__
-   service mariadb stop
+   if [[ ${?} -eq 0 ]]
+   then
+	   service mysql stop
+	   touch ${INITDONE}
+	   exit ${?}
+   else
+	   service mysql stop
+	   exit ${?}
+   fi
 else
    exit 0
 fi

schedule_config/config.sh

@@ -0,0 +1,7 @@
+# Configuration commune aux scripts schedule
+# Configuration de base modifiée pour copier dans le partage nfs plutôt que /home
+
+SAVDIR=/mnt/sauvegardes/
+# pour que l'affichage de [ ok ] soit ok
+export TERM='dumb'
+umask 0077

schedule_extra/01_mysql.xml

@@ -0,0 +1,19 @@
+<?xml version="1.0" encoding="utf-8"?>
+
+<creole>
+    <variables>
+        <family name="mysql" description="Exportation des bases de données MySQL">
+            <variable name="description" type="string" hidden="True"><value>Exportation des bases MySQL</value></variable>
+            <variable name="day" type="schedule" description="Périodicité d'exécution"><value>daily</value></variable>
+            <variable name="mode" type="schedulemod" hidden="True"><value>pre</value></variable>
+        </family>
+    </variables>
+    <constraints>
+        <fill name='calc_multi_condition' target='schedule.mysql.day'>
+            <param>non</param>
+	    <param type='eole' name='condition_1'>dbEnableBackup</param>
+            <param name='match'>none</param>
+            <param name='mismatch'>daily</param>
+        </fill>
+    </constraints>
+</creole>

schedule_scripts/mysql

@@ -0,0 +1,27 @@
+#!/bin/bash
+
+set -e
+
+DESC="Exportation des bases MySQL"
+
+. /usr/share/eole/schedule/config.sh
+
+MYSQLSAVDIR=$SAVDIR/sql
+OPTION="--lock-tables"
+
+rm -f $MYSQLSAVDIR/*.sql
+mkdir -p $MYSQLSAVDIR
+
+CMD="mysql --defaults-file=/etc/mysql/mariadb.cnf -e 'show databases' | grep -v '^Database$'"
+DATABASES=$(CreoleRun "$CMD" mysql)
+for databasename in $DATABASES; do
+    case "$databasename" in
+        information_schema|performance_schema|bareos)
+            continue
+            ;;
+        *)
+            CMD="mysqldump --defaults-file=/etc/mysql/mariadb.cnf --databases $databasename --flush-privileges --create-options -Q -c $OPTION 2>/dev/null"
+            CreoleRun "$CMD" mysql > $MYSQLSAVDIR/$databasename.sql
+            ;;
+    esac
+done

tmpl/tunning.cnf

@@ -3,18 +3,29 @@ innodb_file_per_table=1
 skip-name-resolve
 
 innodb_buffer_pool_size=%%getMemoryPercentage(%%getVar('dbInnoDBBufferPoolPercentage', 10))
+innodb_log_buffer_size=%%{dbInnoDBLogBufferSize}M
+innodb_log_file_size=%%{dbInnoDBLogFileSize}M
+innodb_lock_wait_timeout=%%{dbInnoDBLockWaitTimeout}
+
 max_connections=%%dbMaxConnections
 thread_cache_size=%%dbThreadCacheSize
 
 %if %%dbCacheQueryEnable == 'oui'
 query_cache_type = 1
 query_cache_limit = %%getVar('dbQueryCacheLimit')
-query_cache_min_res_unit = %%getVar('dbQueryCacheMinResUint')
+query_cache_min_res_unit = %%getVar('dbQueryCacheMinRestUnit')
 query_cache_size = %%getVar('dbQueryCacheSize')
+%else
+query_cache_type = 0
+query_cache_size = 0
 %end if
 
 tmp_table_size = %%dbTmpTableSize
 max_heap_table_size = %%dbTmpTableSize
+max_allowed_packet = %%{dbMaxAllowedPacket}M
+
+wait_timeout = %%dbWaitTimeout
+interactive_timeout = %%{dbInteractiveTimeout}
 
 %if %%dbEnableSlowQueryLogs == "oui"
 slow-query-log = 1
@@ -22,5 +33,4 @@ slow-query-log-file = %%getVar('dbSlowQueryLogFile')
 long_query_time = %%getVar('dbSlowQueryLogTime')
 %end if
 
-wait_timeout = %%dbWaitTimeout