Configurer la réplication maître/esclave entre deux serveurs MySQL

Ce guide est testé sur:

• Debian 7.0 Wheezy

Prérequis

Ce guide nécessite:

• deux serveur MySQL, installés comme décrit par Installer et configurer MySQL sur Debian.

Configuration du serveur maître

Paramètres

Renseignez le nom de l'utilisateur MySQL qui sera dédié à la réplication:

REPLICATION_USER="replication"

Renseignez le nom de l'interface réseau utilisée pour communiquer avec le serveur esclave:

NET_DEV="eth0"

Préparation de l'environnement

Installez les logiciels nécessaires:

command apt-get install apg

Configuration

Activez la restauration point-in-time et le journal des requêtes lentes (et autre améliorations):

command wget 'https://raw.github.com/biapy/howto.biapy.com/master/mysql/base-optimisations.cnf' \
    --quiet --no-check-certificate --output-document='/etc/mysql/conf.d/base-optimisations.cnf'

Autorisez l'accès au serveur maître depuis le réseau:

command sed -i -e 's/^bind-address.*/#\0/' '/etc/mysql/my.cnf'

Assignez l'ID n°1 au serveur maître:

command sed -i -e 's/^#*server-id.*/server-id = 1/' '/etc/mysql/my.cnf'

Rechargez la configuration:

command service mysql restart

Générez le mot de passe du compte dédié à la réplication:

REPLICATION_PASSWORD="$(command apg -q -a 0 -n 1 -M NCL)"

Créez l'utilisateur dédié à la réplication:

command echo "GRANT REPLICATION SLAVE ON *.* TO '${REPLICATION_USER}'@'%' IDENTIFIED BY '${REPLICATION_PASSWORD}';" \
    | command mysql --user='root' --password

Détectez l'adresse IP du serveur maître:

REPLICATION_IP="$(command ifconfig "${NET_DEV}" \
    | command grep 'inet ' \
    | command sed -e 's/^.*inet [^:]*:\([^ ]*\) .*$/\1/')"

Détectez le mot de passe de l'utilisateur debian-sys-maint:

MAINTENANCE_PASSWORD="$(command grep --max-count=1 'password' '/etc/mysql/debian.cnf' \
    | command sed -e 's/^.* = //g')"

Initialisation de la réplication

Attention: à partir de cette étape, le fonctionnement normal du serveur est interrompu.

Figez le contenu des tables en bloquant les opérations d'écriture:

command echo "FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;" \
    | command mysql --user='root' --password

Récupérez les informations de statut du journal binaire:

MASTER_STATUS="$(command echo "SHOW MASTER STATUS;" \
    | command mysql --batch --skip-column-names \
        --skip-line-numbers --skip-pager \
        --user='root' --password)"

Analysez les informations de statut:

BINLOG_FILE="$(command echo ${MASTER_STATUS} | command cut --delimiter=' ' --fields=1)"
BINLOG_POSITION="$(command echo ${MASTER_STATUS} | command cut --delimiter=' ' --fields=2)"

Exportez les données du serveur maître:

command mysqldump --all-databases --events --user=root --password > '/tmp/master.sql'

Rétablissez le fonctionnement normal du serveur en autorisant à nouveau les opérations d'écriture:

command echo "UNLOCK TABLES;" \
    | command mysql --user='root' --password

Paramètres du serveur esclave

Utilisez les paramètres fournis par la commande suivante pour configurer le serveur esclave:

echo "REPLICATION_IP='${REPLICATION_IP}'
REPLICATION_USER='${REPLICATION_USER}'
REPLICATION_PASSWORD='${REPLICATION_PASSWORD}'
MAINTENANCE_PASSWORD='${MAINTENANCE_PASSWORD}'
BINLOG_FILE='${BINLOG_FILE}'
BINLOG_POSITION='${BINLOG_POSITION}'
"

Configuration du serveur esclave

Paramètres

Renseignez l'IP du serveur maître:

REPLICATION_IP='xx.xx.xx.xx'

Renseignez le nom de l'utilisateur dédié à la réplication sur le serveur maître:

REPLICATION_USER='replication'

Renseignez le mot de passe de l'utilisateur dédié à la réplication sur le serveur esclave:

REPLICATION_PASSWORD='some-password'

Renseignez le mot de passe de l'utilisateur de maintenance:

MAINTENANCE_PASSWORD='some-password'

Renseignez le nom du fichier actuel du journal binaire:

BINLOG_FILE='mysql-bin.000007'

Renseignez la position actuelle du journal binaire:

BINLOG_POSITION='107'

Import des données du serveur maître

Copiez l'export SQL du serveur maître sur le serveur esclave:

command scp -C ${REPLICATION_IP}:'/tmp/master.sql' '/tmp/master.sql'

Remplacez les données du serveur esclave par les données du serveur maître:

command mysql --user='root' --password < '/tmp/master.sql'

Configuration

Assignez l'ID n°2 au serveur esclave:

command sed -i -e 's/^#*server-id.*/server-id = 2/' '/etc/mysql/my.cnf'

Mettez à jour le mot de passe de l'utilisateur de maintenance:

command sed -i \
    -e "s/password = .*\$/password = ${MAINTENANCE_PASSWORD}/g" \
  '/etc/mysql/debian.cnf'

Rechargez la configuration:

command service mysql restart

Initialisation de la réplication

Arrêtez la réplication:

command echo 'STOP SLAVE;' \
    | command mysql --user=root --password

Renseignez le statut du serveur maître:

command echo "CHANGE MASTER TO
MASTER_HOST='${REPLICATION_IP}',
MASTER_USER='${REPLICATION_USER}',
MASTER_PASSWORD='${REPLICATION_PASSWORD}',
MASTER_PORT=3306,
MASTER_LOG_FILE='${BINLOG_FILE}',
MASTER_LOG_POS=${BINLOG_POSITION};" \
    | command mysql --user=root --password

Démarrez la réplication:

command echo 'START SLAVE;' \
    | command mysql --user=root --password

Remerciements

• Merci à William's blog pour Réplication MySQL maître-esclave.