GaleraCluster

#!/bin/sh

# Agrandir le disque /
sudo yum -y install cloud-utils
sudo growpart /dev/sda 1
sudo resize2fs /dev/sda1

# install Virtual box & Vagrant
URL_VIRTUALBOX="https://download.virtualbox.org/virtualbox/6.1.4/VirtualBox-6.1-6.1.4_136177_el7-1.x86_64.rpm"
URL_VAGRANT="https://releases.hashicorp.com/vagrant/2.2.7/vagrant_2.2.7_x86_64.rpm"
sudo yum -y update

sudo yum –y install gcc dkms make qt libgomp patch kernel-headers kernel-devel binutils glibc-headers glibc-devel font-forge

sudo yum -y install $URL_VIRTUALBOX 
sudo /sbin/vboxconfig
vboxmanage --version

sudo yum -y install $URL_VAGRANT
vagrant plugin install vagrant-hostmanager
vagrant plugin install vagrant-persistent-storage

vagrant up --provider=virtualbox
sudo sysctl net.ipv6.conf.all.forwarding=1
sysctl net.ipv6.bindv6only

sudo iptables --flush
sudo iptables -L

# install rundeck
sudo yum install java-1.8.0-openjdk java-1.8.0-openjdk-devel -y
sudo rpm -Uvh http://repo.rundeck.org/latest.rpm
sudo yum -y install rundeck
sudo service rundeckd start


# Configuration de SELINUX
set -x


perl -i -pe 's/=enforcing/=permissive/g' /etc/sysconfig/selinux

cat /etc/sysconfig/selinux

setenforce 0


# configuration limits Linux
echo "# Nombre de fichier ouvert par un processus mysql
mysql soft nofile 2048
mysql hard nofile 65536
# Nombre de processus l'utilisateur mysql
mysql soft nproc 2047
mysql hard nproc 16384
# Nombre de processus l'utilisateur root
root soft nproc 2047
root hard nproc 16384"> /tmp/mariadb.conf
sudo cp /tmp/mariadb.conf /etc/security/limits.d/
sudo chown root.root /etc/security/limits.d/mariadb.conf

echo "
# Commence à swap quand < 10% de la RAM est disponible
vm.swappiness=10
# Nombre de slot TCP (peut produire des erreurs)
sunrpc.tcp_slot_table_entries = 128
# Nombre de requête Asynchrone sur le système de fichier
fs.aio-max-nr = 1048576">/tmp/mariadb.conf
sudo cp /tmp/mariadb.conf /etc/sysctl.d/
sudo chown root.root /etc/sysctl.d/mariadb.conf

# Rechargement des fichiers de configuration sysctl
sudo sysctl -p
sudo sysctl -p /etc/sysctl.d/mariadb.conf
sudo sysctl -a| grep swap

# Autoriser tous les accès TCP au niveau Linux
sudo /sbin/iptables --flush
sudo /sbin/iptables -L

# NTP
yum -y install ntpdate
ntpdate -vqd fr.pool.ntp.org

Quelques liens:
•	https://downloads.mariadb.org/mariadb/repositories/ 
•	https://www.percona.com/doc/percona-repo-config/percona-release.html
•	https://dev.mysql.com/downloads/repo/yum/

# Installation MariaDB à partir du repository Yum MAriaDB
echo "# MariaDB 10.4 CentOS repository list - created 2020-04-12 09:19 UTC # http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB 
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1" | sudo tee -a /etc/yum.repos.d/mariadb.repo

sudo yum -y update
sudo yum -y install MariaDB-server MariaDB-client MariaDB-backup galera-4 nmap-ncat.x86_64 socat rsync lftp pigz

# Installation MariaDB  à partir du repository YUM Percona
sudo yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

sudo percona-release enable tools release
* Enabling the Percona Tools repository
<*> All done!

sudo yum -y install percona-toolkit sysbench mysqltuner

# Installation mysql-utilities à partir du repository APT MySQL Oracle
sudo yum -y install mysql-connector-python
sudo yum -y install mysql-utilities

# Installation de MySQLTuner
sudo yum -y install git
git clone https://github.com/major/MySQLTuner-perl.git
sudo cp -r MySQLTuner-perl /usr/local
sudo chown root.root /usr/local/MySQLTuner-perl

#Tuer les processus YUM
ps -edf | grep yum | grep -v grep | awk '{ print $2 }' | xargs -n 10 kill -9 || true

systemctl start mariadb
systemctl enable mariadb
systemctl status mariadb
netstat -ltpn
ps -edf | grep -i mysql

echo "
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.user where user ='';
FLUSH PRIVILEGES;

show databases;
select user, host from mysql.user;
" > /tmp/secure_mariadb.sql

mysql < /tmp/secure_mariadb.sql




=============================================
PASSWD="@option.default_pass@"

echo "
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('${PASSWD}');
CREATE OR REPLACE USER 'root'@'192.168.33.16%' IDENTIFIED BY '${PASSWD}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.33.16%';
CREATE OR REPLACE USER 'root'@'10.0.%' IDENTIFIED BY '${PASSWD}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.%';
CREATE OR REPLACE USER 'root'@'galera%' IDENTIFIED BY '${PASSWD}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'galera%';

CREATE OR REPLACE USER 'galera'@'%' IDENTIFIED BY 'galera';
GRANT ALL PRIVILEGES ON *.* TO 'galera'@'%';
CREATE OR REPLACE USER 'galera'@'localhost' IDENTIFIED BY 'galera';
GRANT ALL PRIVILEGES ON *.* TO 'galera'@'localhost';
" > /tmp/secure_root_user.sql

mysql < /tmp/secure_root_user.sql

echo "
[mysql]
user=root
password=${PASSWD}
" > /root/.my.cnf

chmod 600 /root/.my.cnf
# ajouter la ligne dans /etc/my.cnf.d/server.cnf
bind-address=0.0.0.0

perl -i -pe 's/^#(bind-address=0.0.0.0)/$1/' /etc/my.cnf.d/server.cnf





-----------------------
test accès

for i in $(seq 1 4); do
  echo "Galera$i ACCeS ROOT"
  mysql -uroot -hgalera$i -psecret mysql -e 'select host, user from mysql.user'
  if [ $? -ne 0 ]; then
     echo "Erreur Root galer$i"
     exit 127
  fi
  echo "Galera$i ACCeS GALERA"
  mysql -ugalera -hgalera$i -pgalera mysql -e 'select host, user from mysql.user'
  if [ $? -ne 0 ]; then
     echo "Erreur Galere galer$i"
     exit 128
  fi
 done
 echo "Tous les acces sont OK !!!!!!!!!!"
 exit 0

======== configuration galera 1
[server]
innodb_buffer_pool_size=200M
innodb_log_file_size=100M
innodb_flush_log_at_trx_commit=0
max_connections=1024
innodb_file_per_table
server_id=1
log-bin 
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0

wsrep_on=on
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

wsrep_cluster_name="easycluster"

wsrep_node_name=galera1
wsrep_node_address=192.168.33.161

#wsrep_cluster_address="gcomm://192.168.33.162,192.168.33.163"
wsrep_cluster_address="gcomm://"
#wsrep_slave_threads=2
# Debian Maria Backup est bien la version 2 du script
wsrep_sst_method=mariabackup
wsrep_sst_auth=galera:galera

======== configuration galera 2
[server]
innodb_buffer_pool_size=200M
innodb_log_file_size=100M
innodb_flush_log_at_trx_commit=0
max_connections=1024
innodb_file_per_table
server_id=1
log-bin 
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0

wsrep_on=on
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

wsrep_cluster_name="easycluster"
wsrep_node_name=galera2
wsrep_node_address=192.168.33.162

wsrep_cluster_address="gcomm://192.168.33.161,192.168.33.163"
#wsrep_cluster_address="gcomm://"

#wsrep_slave_threads=2
# Debian Maria Backup est bien la version 2 du script

wsrep_sst_method=mariabackup
wsrep_sst_auth=galera:galera

======== configuration galera 3
[server]
innodb_buffer_pool_size=200M
innodb_log_file_size=100M
innodb_flush_log_at_trx_commit=0
max_connections=1024
innodb_file_per_table
server_id=1
log-bin 
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0

wsrep_on=on
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

wsrep_cluster_name="easycluster"
wsrep_node_name=galera3
wsrep_node_address=192.168.33.163

wsrep_cluster_address="gcomm://192.168.33.161,192.168.33.162"
#wsrep_cluster_address="gcomm://"

wsrep_sst_method=mariabackup
wsrep_sst_auth=galera:galera

db_list()
{
    mysql -Nrs -e 'show databases'
}

db_tables()
{
    mysql -Nrs -e 'show tables' ${1:-"mysql"}
}

db_count()
{
    for tbl in $(db_tables ${1:-"mysql"}); do
        echo -ne "$tbl\t"
        mysql -Nrs -e "select count(*) from $tbl" ${1:-"mysql"}
    done | sort -nr -k2 | column -t
}

my_cluster_state()
{
 mysql -e "show status like '%wsrep%';" | grep -E '(wsrep_last_committed|cluster_status|connected|ready|state_comment|cluster_size|state_uuid|conf)'| column -t
}

MariaDB [(none)]> install soname 'wsrep_info';
Query OK, 0 rows affected (0.475 sec)

MariaDB [(none)]> select * from information_schema.wsrep_status;
+------------+-------------+----------------+--------------+--------------------------------------+---------------------+-----------------+------------------+
| NODE_INDEX | NODE_STATUS | CLUSTER_STATUS | CLUSTER_SIZE | CLUSTER_STATE_UUID                   | CLUSTER_STATE_SEQNO | CLUSTER_CONF_ID | PROTOCOL_VERSION |
+------------+-------------+----------------+--------------+--------------------------------------+---------------------+-----------------+------------------+
|          2 | synced      | primary        |            4 | 86b694d1-7f0f-11ea-a347-031abd7d1fa9 |                1862 |              34 |                4 |
+------------+-------------+----------------+--------------+--------------------------------------+---------------------+-----------------+------------------+
1 row in set (0.004 sec)

MariaDB [(none)]> select * from information_schema.wsrep_membership;
+-------+--------------------------------------+---------+---------+
| INDEX | UUID                                 | NAME    | ADDRESS |
+-------+--------------------------------------+---------+---------+
|     0 | 49157a7f-7f1e-11ea-8a89-47c3d189e651 | garb    |         |
|     1 | 86b374de-7f0f-11ea-9df8-869ad278d1e4 | galera1 | AUTO    |
|     2 | a0cfdf82-7f25-11ea-8d49-26a38d8750be | galera3 | AUTO    |
|     3 | a7754483-7f1f-11ea-b7b3-377cdb4c8c83 | galera2 | AUTO    |
+-------+--------------------------------------+---------+---------+
4 rows in set (0.001 sec)

MariaDB [(none)]> 


my_cluster_state() {  
  (
  mysql -e "show status like '%wsrep%'"
  mysql -e "show variables like 'auto%'"
  mysql -e "show variables like 'wsrep_node%'"
  mysql -e "show variables like 'wsrep_cluster%'"
  ) | grep -E '(wsrep_last_committed|wsrep_node|wsresp_cluster_a|cluster_status|connected|ready|state_comment|cluster_size|state_uuid|conf|auto_)'| column -t; 
  
  }

[root@galera2 mysql]# cat /etc/rsyslog.d/mariadb.conf 
if $programname == 'mysqld' or $programname == 'mariadb' then /var/log/mariadb.log
& ~
[root@galera2 mysql]# 


http://downloadfiles.idera.com/products/IderaSQLDiagnosticManagerForMySQL-Linux-x64-rpm.zip

https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck

sudo wget -O /usr/bin/clustercheck https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck
sudo chmod 755 /usr/bin/clustercheck

mysqlchk   9200/tcp /etc/services

/etc/xinetd.d/mysqlchk
# default: on
# description: mysqlchk
service mysqlchk
{
        disable = no
        flags = REUSE
        socket_type = stream
        port = 9200
        wait = no
        user = nobody
        server = /usr/bin/clustercheck
        server_args = root secret 0
        log_on_failure += USERID
        only_from = 0.0.0.0/0
        bind = 0.0.0.0
        per_source = UNLIMITED
}

service xinetd restart

• defaultTab: nodes
  description: ''
  executionEnabled: true
  group: MARIADB
  id: 70a9e717-eed1-4b2d-b126-a207b2942011
  loglevel: INFO
  name: Sécurisation MariaDB
  nodeFilterEditable: false
  nodefilters:
  dispatch:
  excludePrecedence: true
  keepgoing: false
  rankOrder: ascending
  successOnEmptyNodeFilter: false
  threadcount: '10'
  filter: galera.*
  nodesSelectedByDefault: true
  options:
  • label: MOT DE PASSE
    name: default_pass
    required: true
    plugins:
    ExecutionLifecycle: null
    scheduleEnabled: true
    sequence:
    commands:

    • description: Suppression database test & users anonymes
      script: |
      echo "
      DROP DATABASE IF EXISTS test;
      DELETE FROM mysql.user where user ='';
      FLUSH PRIVILEGES;

      show databases;
      select user, host from mysql.user;
      " > /tmp/secure_mariadb.sql

      mysql < /tmp/secure_mariadb.sql

    • description: CHANGEMENT DE MOT DE PASSE
      script: |
      set -x

      PASSWD="@option.default_pass@"

      echo "
      SET PASSWORD FOR 'root'@'localhost' = PASSWORD('${PASSWD}');
      CREATE OR REPLACE USER 'root'@'192.168.33.16%' IDENTIFIED BY '${PASSWD}';
      GRANT ALL PRIVILEGES ON . TO 'root'@'192.168.33.16%';
      CREATE OR REPLACE USER 'root'@'10.0.%' IDENTIFIED BY '${PASSWD}';
      GRANT ALL PRIVILEGES ON . TO 'root'@'10.0.%';
      CREATE OR REPLACE USER 'root'@'galera%' IDENTIFIED BY '${PASSWD}';
      GRANT ALL PRIVILEGES ON . TO 'root'@'galera%';

      CREATE OR REPLACE USER 'galera'@'%' IDENTIFIED BY 'galera';
      GRANT ALL PRIVILEGES ON . TO 'galera'@'%';
      CREATE OR REPLACE USER 'galera'@'localhost' IDENTIFIED BY 'galera';
      GRANT ALL PRIVILEGES ON . TO 'galera'@'localhost';
      " > /tmp/secure_root_user.sql

      mysql < /tmp/secure_root_user.sql

      echo "
      [mysql]
      user=root
      password=${PASSWD}
      " > /root/.my.cnf

      chmod 600 /root/.my.cnf
      keepgoing: false
      strategy: node-first
      uuid: 70a9e717-eed1-4b2d-b126-a207b2942011