Installation, réplication et haute disponibilité de MySQL sous Linux

  1. Installation de MySQL 5.7

Les étapes ci-dessous utilisent Ansible pour déployer MySQL 5.7 sur un serveur CentOS/RHEL. Les chemins et variables ont été modifiés pour éviter la similarité avec l'original.

# mysql 5.7
- name: Copier l'archive MySQL
  copy:
    src: /srv/mysql-{{ VERS_MYSQL }}.tar.gz
    dest: /opt/mysql-{{ VERS_MYSQL }}.tar.gz
- name: Extraire l'archive
  unarchive:
    src: /opt/mysql-{{ VERS_MYSQL }}.tar.gz
    dest: /opt/
    remote_src: yes
- name: Renommer le répertoire
  command: mv /opt/mysql-{{ VERS_MYSQL }}-* /opt/mysql
- name: Définir le propriétaire
  file:
    path: /opt/mysql
    owner: root
    group: root
    recurse: yes
- name: Créer le groupe mysql
  group:
    name: mysql
    state: present
- name: Créer l'utilisateur mysql
  user:
    name: mysql
    group: mysql
    shell: /sbin/nologin
    create_home: no
- name: Ajouter MySQL au PATH
  lineinfile:
    path: /etc/profile.d/mysql.sh
    line: 'export PATH=/opt/mysql/bin:$PATH'
    create: yes
- name: Copier la configuration
  template:
    src: my.cnf.j2
    dest: /etc/my.cnf
- name: Créer le répertoire de données
  file:
    path: "{{ DATA_DIR }}"
    state: directory
    owner: mysql
    group: mysql
    mode: '0755'
- name: Initialiser la base de données
  command: >
    /opt/mysql/bin/mysqld --initialize-insecure
    --user=mysql --basedir=/opt/mysql --datadir={{ DATA_DIR }}
- name: Copier le script de démarrage
  command: cp /opt/mysql/support-files/mysql.server /etc/init.d/mysqld
- name: Activer et démarrer MySQL
  command: chkconfig --add mysqld && service mysqld start
  1. Multi‑instances

Création de plusieurs instances MySQL sur un même serveur :

# Créer les répertoires pour 3 instances (ports 3306, 3307, 3308)
mkdir -p /database/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
chown -R mysql:mysql /database

# Initialiser chaque instance sans mot de passe
for port in 3306 3307 3308; do
  mysqld --initialize-insecure --user=mysql --datadir=/database/$port/data
done

# Générer les fichiers my.cnf pour chaque instance
cat > /database/3306/etc/my.cnf << 'EOF'
[mysqld]
port=3306
datadir=/database/3306/data
socket=/database/3306/socket/mysql.sock
log-error=/database/3306/log/mysql.log
pid-file=/database/3306/pid/mysql.pid
EOF

for port in 3307 3308; do
  sed 's/3306/'$port'/g' /database/3306/etc/my.cnf > /database/$port/etc/my.cnf
done

# Script de gestion pour une instance (exemple 3307)
cat > /database/3307/bin/mysqld << 'SCRIPT'
#!/bin/bash
PORT=3307
MYSQL_USER="root"
MYSQL_PASS="123456"
CMD_PATH="/usr/bin"
BASE_DIR="/database"
SOCKET="${BASE_DIR}/${PORT}/socket/mysql.sock"

start() {
    if [ -S "$SOCKET" ]; then
        echo "MySQL est déjà en cours d'exécution."
        exit 1
    fi
    echo "Démarrage de MySQL sur le port $PORT..."
    $CMD_PATH/mysqld_safe --defaults-file=${BASE_DIR}/${PORT}/etc/my.cnf &>/dev/null &
}

stop() {
    if [ ! -S "$SOCKET" ]; then
        echo "MySQL n'est pas en cours d'exécution."
        exit 1
    fi
    echo "Arrêt de MySQL sur le port $PORT..."
    $CMD_PATH/mysqladmin -u $MYSQL_USER -p$MYSQL_PASS -S $SOCKET shutdown
}

restart() {
    stop
    sleep 2
    start
}

case "$1" in
    start) start ;;
    stop) stop ;;
    restart) restart ;;
    *) echo "Usage: ${BASE_DIR}/${PORT}/bin/mysqld {start|stop|restart}" ;;
esac
SCRIPT
chmod +x /database/3307/bin/mysqld
  1. Sauvegarde et restauration

3.1 Types de sauvegarde

  • Complète : copie de toutes les bases.
  • Partielle : certaines bases ou tables.
  • Incrémentielle : modifications depuis la dernière sauvegarde (nécessite une base complète initiale).
  • Différentielle : modifications depuis la dernière sauvegarde complète.
  • À froid : arrêt du service.
  • À chaud : service en lecture seule (MyISAM) ou trensactionnel (InnoDB).
  • Physique : copie des fichiers de données (cp, tar).
  • Logique : export SQL (mysqldump).

3.2 Éléments à sauvegarder

  • Données, logs binaires, logs de transactions InnoDB.
  • Utilisateurs, privilèges, routines stockées, événements, triggers.
  • Fichiers de configuration du serveur.
  • Tester régulièrement la restauration.

3.3 Outils

  • Physique : cp, tar, rsync
  • Logique : mysqldump

3.4 Procédure de sauvegarde physique

# Arrêter MySQL (sauvegarde à froid)
service mysqld stop

# Archiver le répertoire de données
tar -czf /backup/mysql_data_$(date +%Y%m%d).tar.gz /var/lib/mysql

# Redémarrer MySQL
service mysqld start

# Copier l'archive sur un serveur distant
rsync -avz /backup/mysql_data_*.tar.gz backup@192.168.1.200:/backup/

3.5 Procédure de sauvegarde logique avec mysqldump

# Sauvegarde complète avec options recommandées pour InnoDB
mysqldump -u root -p \
  --all-databases \
  --flush-logs \
  --events \
  --routines \
  --triggers \
  --single-transaction \
  --master-data=1 \
  --flush-privileges \
  --default-character-set=utf8mb4 \
  --hex-blob \
  > /backup/full_$(date +%Y%m%d_%H%M%S).sql

# Pour MyISAM, utiliser --lock-all-tables (ou -x) à la place de --single-transaction
  1. Gestion des bases de données et des tables

4.1 Jeux de caractères et collations

-- Voir les jeux de caractères disponibles
SHOW CHARACTER SET;

-- Voir les collations disponibles
SHOW COLLATION;

-- Voir les paramètres actuels
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

-- Définir par défaut dans my.cnf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4

4.2 Types de données principaux

  • Entiers : TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. UNSIGNED pour valeurs positives.
  • Booléens : BOOLEAN (alias TINYINT(1)).
  • Flottants : FLOAT(m,d), DOUBLE(m,d).
  • Décimaux : DECIMAL(m,d) pour calculs exacts.
  • Chaînes : CHAR(n) (fixe), VARCHAR(n) (variable).
  • Dates : DATE, TIME, DATETIME, YEAR.
  • Énumérations : ENUM('val1','val2').

4.3 Création et gestion des bases

-- Créer une base avec un jeu de caractères spécifique
CREATE DATABASE IF NOT EXISTS appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- Modifier le jeu de caractères d'une base existante
ALTER DATABASE appdb CHARACTER SET utf8;

-- Supprimer une base
DROP DATABASE IF EXISTS appdb;

-- Afficher les variables système et les modifier
SHOW VARIABLES LIKE 'auto_%';
SET @@session.autocommit = 0;

4.4 Création et modification de tables

-- Création d'une table
CREATE TABLE usagers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    statut ENUM('actif','inactif') DEFAULT 'actif',
    date_inscription DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Créer une table à partir d'une requête
CREATE TABLE usagers_archives SELECT * FROM usagers WHERE statut = 'inactif';

-- Créer une table avec la même structure (sans données)
CREATE TABLE usagers_copy LIKE usagers;

-- Afficher la structure
DESC usagers;

-- Ajouter une colonne
ALTER TABLE usagers ADD telephone VARCHAR(20) AFTER email;

-- Supprimer une colonne
ALTER TABLE usagers DROP telephone;

-- Modifier le type d'une colonne
ALTER TABLE usagers MODIFY nom VARCHAR(100);

-- Renommer une colonne
ALTER TABLE usagers CHANGE email courriel VARCHAR(100);

-- Renommer la table
ALTER TABLE usagers RENAME TO utilisateurs;

-- Supprimer une table
DROP TABLE IF EXISTS utilisateurs;
  1. Gestion des données (INSERT, UPDATE, DELETE, SELECT)
-- Insertion
INSERT INTO utilisateurs (nom, courriel) VALUES ('Alice Dupont', 'alice@example.com');

-- Mise à jour avec condition (sécurité : safe-updates)
UPDATE utilisateurs SET statut = 'inactif' WHERE id = 1;

-- Suppression avec condition
DELETE FROM utilisateurs WHERE id = 1;

-- Vider une table (conserve la structure)
TRUNCATE TABLE utilisateurs;

Requêtes SELECT avancées

-- Filtrer avec WHERE, BETWEEN, IN, IS NULL
SELECT * FROM utilisateurs WHERE statut = 'actif' AND date_inscription BETWEEN '2023-01-01' AND '2023-12-31';

-- Regroupement et agrégation
SELECT statut, COUNT(*) AS nb, AVG(id) FROM utilisateurs GROUP BY statut HAVING nb > 1;

-- Trier et limiter
SELECT * FROM utilisateurs ORDER BY nom ASC LIMIT 10;

-- Sous‑requête
SELECT nom FROM utilisateurs WHERE id IN (SELECT user_id FROM commandes);

-- Jointure (INNER JOIN)
SELECT u.nom, c.montant FROM utilisateurs u INNER JOIN commandes c ON u.id = c.user_id;

-- Jointure externe (LEFT JOIN)
SELECT u.nom, c.montant FROM utilisateurs u LEFT JOIN commandes c ON u.id = c.user_id;
  1. Vues
-- Création d'une vue
CREATE VIEW utilisateurs_actifs AS SELECT id, nom, courriel FROM utilisateurs WHERE statut = 'actif';

-- Utilisation
SELECT * FROM utilisateurs_actifs;

-- Supprimer une vue
DROP VIEW IF EXISTS utilisateurs_actifs;
  1. Gestion des utilisateurs et privilèges
-- Créer un utilisateur avec restriction réseau
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'secret123';

-- Accorder tous les privilèges sur une base spécifique
GRANT ALL PRIVILEGES ON appdb.* TO 'app_user'@'192.168.1.%';

-- Activer les changements
FLUSH PRIVILEGES;

-- Voir les privilèges d'un utilisateur
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

-- Révoquer des privilèges
REVOKE ALL PRIVILEGES ON appdb.* FROM 'app_user'@'192.168.1.%';

-- Supprimer un utilisateur
DROP USER 'app_user'@'192.168.1.%';

-- Changer le mot de passe
ALTER USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'nouveau_motdepasse';
  1. Réplication

8.1 Réplication maître‑esclave

Principe : Le maître écrit ses modifications dans le journal binaire, l'esclave lit ce journal via le journal de relais et applique les instructions.

Configuration du maître

[mysqld]
log_bin                     = /var/lib/mysql/mysql-bin
server_id                   = 1
log-basename                = master


-- Créer un utilisateur dédié à la réplication
CREATE USER 'replicator'@'192.168.1.%' IDENTIFIED BY 'repl_pass';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.1.%';
FLUSH PRIVILEGES;

-- Voir la position actuelle du journal binaire
SHOW MASTER STATUS;

Configuration de l'esclave

[mysqld]
server_id                   = 2
relay_log                   = relay-log
read_only                   = ON


CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='replicator',
    MASTER_PASSWORD='repl_pass',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

8.2 Réplication en cascade

Sur l'esclave intermédiaire, activer log_slave_updates et configurer son propre journal binaire.

8.3 Réplication maître‑maître (bidirectionnelle)

Les deux serveurs sont mutuellement maître et esclave. Pour éviter les conflits d'auto‑incrément :

[mysqld]
auto_increment_offset=1
auto_increment_increment=2   -- sur le premier maître
-- sur le second maître :
auto_increment_offset=2
auto_increment_increment=2

8.4 Gestion des pannes de réplication

  • Voir l'état avec SHOW SLAVE STATUS.
  • Ignorer une erreur : SET GLOBAL sql_slave_skip_counter = 1; puis START SLAVE;.
  • En cas de perte du maître, promouvoir l'esclave le plus à jour.
  1. Lecture/écriture séparée avec ProxySQL

ProxySQL agit comme un proxy entre les applications et vos serveurs MySQL, en routant les lectures vers les esclaves et les écritures vers le maître.

Installation et configuration de base

# Dépôt ProxySQL
cat > /etc/yum.repos.d/proxysql.repo << 'EOF'
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

yum install -y proxysql
systemctl start proxysql

# Interface admin
mysql -u admin -p admin -h 127.0.0.1 -P 6032


-- Ajouter les serveurs MySQL
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, '192.168.1.100', 3306);  -- maître
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '192.168.1.101', 3306);  -- esclave
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (20, '192.168.1.102', 3306);  -- esclave
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Créer un utilisateur de surveillance (sur le maître)
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'192.168.1.%' IDENTIFIED BY 'mon_pass';
FLUSH PRIVILEGES;

-- Configurer le monitor dans ProxySQL
SET mysql-monitor_username='monitor';
SET mysql-monitor_password='mon_pass';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- Définir les groupes de réplication (écriture=10, lecture=20)
INSERT INTO mysql_replication_hostgroups VALUES (10, 20, 'prod');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Créer un utilisateur pour les applications
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'secret', 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- Règles de routage : SELECT ... FOR UPDATE vers le groupe d'écriture, autres SELECT vers le groupe de lecture
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT.*FOR UPDATE$', 10, 1),
(2, 1, '^SELECT', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Tester :

mysql -u app_user -p -h 127.0.0.1 -P 6033 -e "SELECT @@server_id"
mysql -u app_user -p -h 127.0.0.1 -P 6033 -e "START TRANSACTION; SELECT @@server_id; COMMIT;"
  1. Haute disponibilité avec MHA (Master High Availability)

10.1 Principe

MHA surveille le maître et, en cas de panne, élit un nouvel maître parmi les esclaves, applique les journaux de relais manquants, puis bascule les autres esclaves vers le nouveau maître. Il nécessite une clé SSH entre tous les nœuds.

10.2 Architecture

Un nœud manager (peut être un serveur dédié ou un des nœuds MySQL) supervise le cluster.

10.3 Configuration

Prérequis : SSH sans mot de passe entre tous les nœuds (y compris le manager).

# Sur le manager, générer une clé et la copier
ssh-keygen -t rsa
ssh-copy-id root@192.168.1.100
ssh-copy-id root@192.168.1.101
ssh-copy-id root@192.168.1.102
# Répéter sur chaque nœud pour localhost
ssh-copy-id localhost

Installation des paquets MHA (RHEL/CentOS 7) :

yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
# Sur le manager seulement :
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

Fichier de configuration : /etc/mastermha/app1.cnf

[server default]
user=mhauser
password=secret_mha
manager_workdir=/var/log/mastermha/app1
manager_log=/var/log/mastermha/app1/manager.log
remote_workdir=/var/log/mastermha/app1
ssh_user=root
repl_user=replicator
repl_password=repl_pass
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
master_binlog_dir=/var/lib/mysql

[server1]
hostname=192.168.1.100
candidate_master=1

[server2]
hostname=192.168.1.101
candidate_master=1

[server3]
hostname=192.168.1.102

Script de basculement VIP : /usr/local/bin/master_ip_failover (inspiré de l'exemple MHA)

#!/usr/bin/perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;

my ($command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port,
    $new_master_host, $new_master_ip, $new_master_port);

my $vip = '192.168.1.200/24';
my $gateway = '192.168.1.1';
my $interface = 'eth0';
my $key = '1';
my $ssh_start_vip = "/sbin/ip addr add $vip dev $interface label $interface:$key; /sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev $interface label $interface:$key";

GetOptions(
    'command=s'          => \$command,
    'ssh_user=s'         => \$ssh_user,
    'orig_master_host=s' => \$orig_master_host,
    'orig_master_ip=s'   => \$orig_master_ip,
    'orig_master_port=i' => \$orig_master_port,
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
);

exit &main();

sub main {
    if ($command eq "stop" || $command eq "stopssh") {
        my $exit_code = 1;
        eval {
            print "Désactivation du VIP sur l'ancien maître $orig_master_host\n";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) { warn "Erreur: $@\n"; exit $exit_code; }
        exit $exit_code;
    }
    elsif ($command eq "start") {
        my $exit_code = 10;
        eval {
            print "Activation du VIP $vip sur le nouveau maître $new_master_host\n";
            &start_vip();
            $exit_code = 0;
        };
        if ($@) { warn $@; exit $exit_code; }
        exit $exit_code;
    }
    elsif ($command eq "status") {
        print "Vérification du script... OK\n";
        `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
        exit 0;
    }
    else { &usage(); exit 1; }
}

sub start_vip {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip {
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
    print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=...\n";
}

Préparer les utilisateurs sur tous les nœuds MySQL :

GRANT ALL PRIVILEGES ON *.* TO 'mhauser'@'192.168.1.%' IDENTIFIED BY 'secret_mha';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.1.%' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;

Démarrage et vérification :

masterha_check_ssh --conf=/etc/mastermha/app1.cnf
masterha_check_repl --conf=/etc/mastermha/app1.cnf
rm -f /var/log/mastermha/app1/app1.failover.*
nohup masterha_manager --conf=/etc/mastermha/app1.cnf &
masterha_check_status --conf=/etc/mastermha/app1.cnf

Test de basculement : Arrêter le maître actuel, vérifier dans le log MHA et constater que le VIP a été déplacé.

Remetttre un nœud défaillant comme esclave : Voir la section Rétablissement d'un ancien maître (identique à la reconstruction d'un esclave).

Annexe : Résumé des commandes utiles

-- Surveillance de la réplication
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
SHOW BINARY LOGS;
SHOW PROCESSLIST;

-- Sauvegarde avec mysqldump
mysqldump --all-databases --single-transaction --master-data=2 --flush-logs > backup.sql

-- Restauration
mysql -u root -p < backup.sql

-- Réinitialiser la réplication sur un esclave
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO ...;
START SLAVE;

Étiquettes: MySQL réplication haute disponibilité ProxySQL MHA

Publié le 29 mai à 12h24

Étiquettes Populaires

©2026 L'Atelier Monstre