Technique/MySQL/Installation

Présentation

Cette page a pour objet de décrire comment installer de A à Z un serveur MySQL depuis un CentOS 7.

Contexte

Cette page n'est pas encore finalisée.

Installation à partir d'un serveur CentOS 7

Ajout de l'autorité de certification interne

Initialisation des certificats d'autorité racine :

update-ca-trust enable

Récupération du certificat de l'autorité de certification interne dans le dossier /etc/pki/ca-trust/source/anchors et import du certificat précédemment déposé avec la commande :

update-ca-trust extract

Ajout du dépôt EPEL

yum -y install epel-release
yum makecache
yum -y update
yum -y install yum-utils bind-utils yum-cron wget bash-completion lsof nmon net-tools dos2unix deltarpm vim sg3_utils open-vm-tools sysstat samba-client zip
init 6

Modification du umask

sed -i -e 's/umask 022/umask 007/g' -e 's/umask 002/umask 007/g' /etc/bashrc
sed -i -e 's/umask 022/umask 007/g' -e 's/umask 002/umask 007/g' /etc/csh.cshrc
sed -i -e 's/umask 022/umask 007/g' -e 's/umask 002/umask 007/g' /etc/profile
sed -i -e 's/umask 022/umask 007/g' -e 's/umask 002/umask 007/g' /etc/init.d/functions
sed -i -e 's@Subsystem\tsftp\t/usr/libexec/openssh/sftp-server@\#Subsystem\tsftp\t/usr/libexec/openssh/sftp-server\nSubsystem\tsftp\tinternal-sftp -u 0007@g' /etc/ssh/sshd_config
systemctl restart sshd

Suppression des pilotes inutiles

yum -y remove alsa-* ivtv-* iwl*firmware aic94xx-firmware

Désactivation de la wifi

nmcli radio all off

Désactivation de l'IPv6

echo "NETWORKING_IPV6=no" >> /etc/sysconfig/network
echo "IPV6INIT=no" >> /etc/sysconfig/network
echo "net.ipv6.conf.all.disable_ipv6 = 1" >> /etc/sysctl.conf
echo "net.ipv6.conf.default.disable_ipv6 = 1" >> /etc/sysctl.conf
sysctl -p
sed -i 's/#AddressFamily any/AddressFamily inet/g' /etc/ssh/sshd_config
systemctl restart sshd
sed -i 's/OPTIONS=""/OPTIONS="-4"/g' /etc/sysconfig/chronyd
systemctl restart chronyd

Désactivation du selinux

setenforce 0
sed -i -e 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

Suppression des utilisateurs inutiles

userdel -r adm
userdel -r ftp
userdel -r games
userdel -r lp
groupdel games

Augmenter l'historique des commandes de 1000 à 5000 lignes

sed -i 's/HISTSIZE=.*/HISTSIZE=5000/g' /etc/profile

Ajout de l'option "clean_requirements_on_remove=1" pour supprimer automatiquement les dépendances non-utilisées lors de la désinstallation d'un paquet

yum-config-manager --setopt=clean_requirements_on_remove=1 --save

Installation de msmtp

yum install mailx
ecat <<EOF >> /etc/mail.rc
# Define SMTP server
set smtp=smtp://smtp.mydomain.com:25

EOF

Tester avec la commande suivante :

echo "test" | mail -s "Test e-mail" myaddress@mydomain.com

Paramétrage NTP

yum -y install ntpdate && ntpdate ntp.mydomain.com && systemctl enable ntpdate.service

Ajout du disque sous /data

pvcreate /dev/sdb
vgcreate vg_data /dev/sdb
lvcreate -l 100%FREE vg_data -n lv_data
mkfs.ext4 /dev/vg_data/lv_data
mkdir /data
echo "/dev/mapper/vg_data-lv_data /data                 ext4    defaults        0 0" >> /etc/fstab
mount /data

Ajout du dépôt MySQL

yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum makecache
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
yum makecache

Installation de MySQL

yum -y install mysql-community-server

Configuration de MySQL

Création des dossiers hébergeant les logs et les bases :

mkdir -p /data/{mysql,logs}
chown mysql:mysql /data/mysql
chmod 777 /data/logs

Personnalisation de la configuration MySQL :

sed -i -e 's@datadir=/var/lib/mysql@datadir=/data/mysql@g' -e 's@log-error=/var/log/mysqld.log@log-error=/data/logs/mysqld.log@g' /etc/my.cnf
echo "explicit_defaults_for_timestamp=1" >> /etc/my.cnf
echo "skip-ssl=1" >> /etc/my.cnf
echo "bind_address=0.0.0.0" >> /etc/my.cnf
echo "skip-name-resolve=1" >> /etc/my.cnf
echo "query_cache_size=0" >> /etc/my.cnf
echo "innodb_log_file_size=16777216" >> /etc/my.cnf
echo "character-set-server=utf8" >> /etc/my.cnf
echo "collation-server=utf8mb4_general_ci" >> /etc/my.cnf

Ouverture du port MySQL dans le pare-feu :

firewall-cmd --add-service=mysql
firewall-cmd --add-service=mysql --permanent

Démarrage et réinitialisation du mot de passe root de MySQL :

systemctl start mysqld
export MYSQL_PWD=$(grep 'temporary password' /data/logs/mysqld.log | awk '{print $NF}')
export MYSQL_NEW_PWD="xxxxxxxxxxxxx"
mysql --connect-expired-password -uroot -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '$MYSQL_NEW_PWD';"

Ajout des comptes d'administration :

export MYSQL_PWD=$MYSQL_NEW_PWD
mysql -uroot -e "UNINSTALL PLUGIN validate_password;"
mysql -uroot -e "GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin_user'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'xxxxxxxxxxxxx' WITH GRANT OPTION;"
mysql -uroot -e "GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'phpmyadmin_controluser_user'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'xxxxxxxxxxxxx';"
mysql -uroot -e "GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' IDENTIFIED BY 'xxxxxxxxxxxxx';"
mysql -uroot -e "GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix_user'@'localhost' IDENTIFIED BY 'xxxxxxxxxxxxx';"

Ajout des tables pour le stockage de configurations PhpMyAdmin :

wget -q https://raw.githubusercontent.com/phpmyadmin/phpmyadmin/RELEASE_4_9_4/sql/create_tables.sql
mysql -uroot < create_tables.sql
rm -f create_tables.sql

Ajout de l'outil mysqltuner

yum -y install perl-Data-Dumper
wget http://mysqltuner.pl/ -O /usr/bin/mysqltuner
chmod u+x /usr/bin/mysqltuner

Installation de Webmin

cat <<EOF > /etc/yum.repos.d/webmin.repo
[Webmin]
name=Webmin Distribution Neutral
#baseurl=https://download.webmin.com/download/yum
mirrorlist=https://download.webmin.com/download/yum/mirrorlist
enabled=1
EOF
wget https://download.webmin.com/jcameron-key.asc
rpm --import jcameron-key.asc
yum -y install webmin perl-Authen-PAM
/etc/rc.d/init.d/webmin stop
find /etc -type l -name *webmin -exec unlink {} \;
cat <<EOF > /usr/lib/systemd/system/webmin.service
[Unit]
Description=Webmin
Requires=local-fs.target
After=basic.target
Conflicts=shutdown.target
[Service]
Type=oneshot
RemainAfterExit=yes
ExecStart=/etc/webmin/start
ExecStop=/etc/webmin/stop
ExecReload=/etc/webmin/reload
[Install]
WantedBy=multi-user.target
EOF
chmod o+r /usr/lib/systemd/system/webmin.service
systemctl daemon-reload
sed -i -e 's/ssl=1/ssl=0/g' /etc/webmin/miniserv.conf
sed -i -e 's/ipv6=1/ipv6=0/g' /etc/webmin/miniserv.conf
firewall-cmd --permanent --new-service=webmin
firewall-cmd --permanent --service=webmin --set-description="Webmin is a web-based interface for system administration for Unix"
firewall-cmd --permanent --service=webmin --set-short=webmin
firewall-cmd --permanent --service=webmin --add-port=10000/tcp
firewall-cmd --reload
firewall-cmd --add-service=webmin --permanent
systemctl start webmin.service
systemctl enable webmin.service

Ajout Ansible

useradd ansible -U -d /home/ansible -m
usermod -aG wheel ansible
printf 'ansible:$6$mygreatsecret$AZ/JaHQRRgiIuD3a/LH.BIiB8Q1Qw5RdPHJbqzeeXcj84o8TihShBbZoW0VtXXspAK0f0km8DRvjqStakax7b0' | chpasswd --encrypted
mkdir /home/ansible/.ssh
chmod 700 /home/ansible/.ssh
echo "ssh-rsa qagk5RkHRKE985lv2gZ4UTEfn7zZsngQW5HQEcR9ZMRK/e0o5ghy+RQpq/w4Hg/LbT+pfG3liXDP39xekOWzbcggWROb4ittFhA7NnU3At3FeEROUmWmpPH8aqUwQtpoNFY6qDJ0zsdIZn8NFTSxhxgXgTNIvkp7j6Omt4NdQcDiLQtAVafiNI0O7Euwualp8fZy64vL9lAR0Lgxf0PAwk8PwrIJaL3j8SyVr88ZzqB+Db8a/YYK+D2cXjZ1Fi4OKSwW6nQUBEeBBetWN0XkK6ObZ9XVepK9tO8iuWzQHj+Hr0iFAW3cD3cwwSZpjI5V ansible@myansibleserver.mydomain.com" >> /home/ansible/.ssh/authorized_keys
chmod 600 /home/ansible/.ssh/authorized_keys
chown -R ansible:ansible /home/ansible/.ssh

Configuration Shinken

useradd shinken -U -d /home/shinken -m
printf 'shinken:$6$mygreatsecret$mluY2XZHeHd0KdJn7wYU6OwVZKrOX0SlGeIDLb8UWE73uPe8.ZZT2elKOQ2QH/EuvQUZOBnhH.TMRLTci2.hi/' | chpasswd --encrypted
mkdir /home/shinken/.ssh
chmod 700 /home/shinken/.ssh
echo "ssh-rsa zGWtogJuIRY23//gGBGl0CFBX2V4roksaUq4/N9plVLFqMOhLwpumIOl9OYRKneRwtiA93JXhUAlBwr+8g9kSs28ZgoJpZipktbngbUegnMnmX7EHVVj9GwcjEUafae92wuWszkTJ7duTGokp/wUvzp41YLvROl1nHayWsrEeYz1lOXFW4PpHsozNgNLDPMlMV7RCWk7ltVU+HOkMgEdlO2DaU70CHK9wWZ+z6cFkg6OjxLIDSGcoXNCVnxeLYkWEhBZQBZCuIa+CJDDCV4Ijur15oLDJLu24bSkxmHVQCP7i9Hg87wb5 shinken@myshinkenserver.mydomain.com" >> /home/shinken/.ssh/authorized_keys
chmod 600 /home/shinken/.ssh/authorized_keys
chown -R shinken:shinken /home/shinken/.ssh

Installation Zabbix

Installation de l'agent

rpm -Uvh https://repo.zabbix.com/zabbix/4.4/rhel/7/x86_64/zabbix-release-4.4-1.el7.noarch.rpm
rpm -import http://repo.zabbix.com/RPM-GPG-KEY-ZABBIX-A14FE591
yum -y install zabbix-agent
sed -i -e 's/Server=127.0.0.1/Server=myzabbixserver/g' -e 's/ServerActive=127.0.0.1/ServerActive=myzabbixserver/g' -e 's/Hostname=Zabbix server/Hostname=mymysqlserver/g' -e 's@PidFile=/var/run/zabbix/zabbix_agentd.pid@PidFile=/run/zabbix/zabbix_agentd.pid@g' /etc/zabbix/zabbix_agentd.conf
firewall-cmd --add-service=zabbix-agent --permanent

Installation du template MySQL Zabbix

mkdir -p $(grep zabbix /etc/passwd|awk -F: '{print $6}')
chown zabbix:zabbix $(grep zabbix /etc/passwd|awk -F: '{print $6}')
sudo -u zabbix mysql_config_editor set --user=zbx_monitor
echo "MYSQL_PWD=xxxxxxxxxxxxxxxx" >> /etc/sysconfig/zabbix-agent
wget -q https://git.zabbix.com/projects/ZBX/repos/zabbix/raw/templates/db/mysql_agent/template_db_mysql.conf?at=refs%2Fheads%2Fmaster -O /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf
chmod o+r /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf

Suppression des informations sensibles

>/root/.bash_history