-
-
Save jmrenouard/4569dd112aa0ca8a25da674ac94c7631 to your computer and use it in GitHub Desktop.
#!/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.sqlmysql < /tmp/secure_mariadb.sql
-
description: CHANGEMENT DE MOT DE PASSE
script: |
set -xPASSWD="@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.sqlmysql < /tmp/secure_root_user.sql
echo "
[mysql]
user=root
password=${PASSWD}
" > /root/.my.cnfchmod 600 /root/.my.cnf
keepgoing: false
strategy: node-first
uuid: 70a9e717-eed1-4b2d-b126-a207b2942011
-
- label: MOT DE PASSE
sudo fallocate -l 5G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
sudo sed -i '#swapfile#d' /etc/fstab
echo "/swapfile swap swap defaults 0 0" | sudo tee -a /etc/fstab