Réplication MySQL par tunnel ssh

• A propos de ce document
• Préparation du serveur maître (MASTER) pour la connection par tunnel
• Préparation du serveur esclave (SLAVE) pour la connection par tunnel
• Tunnel ssh
  • Test
  • Automatisation et sécurisation
    • Autoriser seulement une commande pour cette clef ssh
    • Lancer le tunnel au demarrage et le maintenir ouvert
• Mise en place de la réplication

A propos de ce document

Ce document a pour but principal de garder une trace de ce que j'ai mis en place dans le cadre de mon travail pour que je puisse m'en resservir au besoin. S'il peut vous être utile tant mieux. Cependant, je ne détaille pas grand chose et il vous faudra un minimum de connaissances de unix/linux, de ssh et de mysql pour pouvoir vous en sortir.

Préparation du serveur maître (MASTER) pour la connection par tunnel

Pour pouvoir créer un tunnel ssh, il faut que le serveur mysql maître est un port ouvert (3306 par défaut). Cependant nous n'avons pas besoin que ce port soit visible depuis l'extérieur de la machine. Nous allons donc l'ouvrir seulement pour la boucle locale (lo = loopback interface = IP 127.0.0.1).

• Vérifier le fichier de configuration /etc/my.cnf

[mysqld]
# _DO NOT_ skip networking comment-out if not already done
# skip-networking
# Bind to loopback only
bind-address=127.0.0.1
# identify the server as number 1
server-id=1

• Redémarrer le serveur mysql: /etc/init.d/mysqld restart
• Dans mysql, créer un utilisateur pour la réplication

GRANT SELECT, RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'cloner'@'127.0.0.1' IDENTIFIED BY 'slavepassword';

• Créer un utilisateur système avec le moins de droits possibles qu'on utilisera pour le tunnel ssh. (Je force le numéro du groupe et de l'utilisateur à 10000 par pure convention personnelle)

groupadd -g 10000 mysqltunnel
useradd -u 10000 -d /home/mysqltunnel -g mysqltunnel mysqltunnel
passwd mysqltunnel
mkdir /home/mysqltunnel/.ssh
chmod -R 700 /home/mysqltunnel
chown -R mysqltunnel.mysqltunnel /home/mysqltunnel

Préparation du serveur esclave (SLAVE) pour la connection par tunnel

Le serveur esclave pour sa part n'utilisera qu'un socket pour sa propre connection. Il faut donc désactiver le réseau. Ceci nous permettra d'utiliser le port 3306 des 2 coté du tunnel ssh

• Vérifier le fichier de configuration my.cnf. Il faut un identifiant server différent du maitre (et d'autre esclaves éventuels), ignorer la base de données information-schema et éventuellement donner un nom d'hote de réplication

[mysqld]
#...
# Your usual config here
# ...

# Replication setup
server_id=2
report-host=backupServer1
replicate-ignore-db=information-schema

• Redémarrer le serveur mysql: /etc/init.d/mysqld restart
• Créer un utilisateur avec le moins de droits possibles qu'on utilisera pour le tunnel ssh. (Je force le numéro du groupe et de l'utilisateur à 10000 par pure convention personnelle)

groupadd -g 10000 mysqltunnel
useradd -u 10000 -d /home/mysqltunnel -g mysqltunnel mysqltunnel
passwd mysqltunnel
mkdir /home/mysqltunnel/.ssh
chmod -R 700 /home/mysqltunnel
chown -R mysqltunnel.mysqltunnel /home/mysqltunnel

• Générer une clef ssh sans mot de passe qui nous permettra de nous connecter sur la machine distante:

 su - mysqltunnel
 ssh-keygen -t dsa
 cat .ssh/id_dsa.pub | ssh mastersql.server.com "cat - >> .ssh/authorized_keys2"

Tunnel ssh

Test

Sur le serveur esclave

su - mysqltunnel
ssh -L 3306:127.0.0.1:3306 master.server.com

La connection doit s'effectuer sans demande de mot de passe. Pour tester la connection mysql, ouvrir un autre terminal

mysql -h 127.0.0.1 -u user -p

Attention: l'utilisateur sur le serveur maitre doit avoir des droit défini pour une connection depuis l'hote 127.0.0.1 (et pas seulement localhost).

Automatisation et sécurisation

A ce point, nous pouvons nous connecter depuis la machine esclave vers la machine maître sans utiliser de mot de passe et établir un tunnel. Il faut maintenant rendre ce processus automatique pour que le tunnel soit créé au boot de la machine et qu'il reste actif. Il nous faut également limiter ce que peut faire l'utilisateur sur la machine maître au cas ou sa clef privée serait compromise.

Autoriser seulement une commande pour cette clef ssh

Si l'utilisateur se connecte à l'aide de sa clef ssh (et donc sans aucun mot de passe), nous allons le contraindre du côté du serveur maître à n'exécuter qu'une seule commande. On cré un script côté maître nommé tunnel_only qu'on enregiste dans /home/mysqltunnel/bin et qui contient:

#!/bin/bash
# Check that we are running inside a shell process or exit.
# This should only happen when the script is still running
# while the parent ssh process as been killed.
parentPID="ps -p $$ -o ppid="
while [ `$parentPID` -ne 1 ]
do
        sleep 3600
done

On modifie ensuite le fichier home/mysqltunnel.ssh/authorized_keys2. On transforme la ligne qui ressemble à ceci

ssh-dss AAAAB3NzaC1kc3MAAACBAI54tV4dA3EpUeIGVX23KjEmQn37moqLNxIDjp5WNZMBai3rD80BzI5Fir0Uy
voJPhWPCCeGsmwDD/PosEzvBZtwTxhQXT8NjKlXVcKYGXk54vLitXnGQiRLql5yoWhMAyjiBbhTCJAfADaWvo2wKh
A3bLwsJwdwxDCrrrMulHwPAAAAFQCQLMB8nk+/4f5BQccE1dQG1GHfNwAAAIEAgXclcf/ecYWA+Es2icicZP+Q1AH
1ds0RZEq765o9yaEYn9riCq15Avx3QNmTywkmNUy2P+tUpiceoNVTuE52SoeIiUOpYvGz05HwI6KMGIbLDa8mS5kC
nfrxevkOuEMbdOgPJpPhoQt6mPuYuOk0JUKny3QdinX/Nbs9Befk9uYAAACASNzBvAaH9cMtdtgoTwfIieXu4+dIw
lQVKxwg2odBNQaafGwt7zUOWx+G2hPcamNmFR3BaG4egWdoyxghAa01ASqIAY++tGY5IUmPgf30eMd3fvPY6y61yc
A5la3X5PZ2vtsI9vtOzGzXKe/Ylf6qNeydnLKF40QE4UguUFLyCRY= mysqltunnel@mysqlslave.host.com

Comme cela

command="/home/mysqltunnel/bin/tunnel_only" ssh-dss AAAAB3NzaC1kc3MAAACBAI54tV4dA3EpUeIGV
X23KjEmQn37moqLNxIDjp5WNZMBai3rD80BzI5Fir0UyvoJPhWPCCeGsmwDD/PosEzvBZtwTxhQXT8NjKlXVcKYGX
k54vLitXnGQiRLql5yoWhMAyjiBbhTCJAfADaWvo2wKhA3bLwsJwdwxDCrrrMulHwPAAAAFQCQLMB8nk+/4f5BQcc
E1dQG1GHfNwAAAIEAgXclcf/ecYWA+Es2icicZP+Q1AH1ds0RZEq765o9yaEYn9riCq15Avx3QNmTywkmNUy2P+tU
piceoNVTuE52SoeIiUOpYvGz05HwI6KMGIbLDa8mS5kCnfrxevkOuEMbdOgPJpPhoQt6mPuYuOk0JUKny3QdinX/N
bs9Befk9uYAAACASNzBvAaH9cMtdtgoTwfIieXu4+dIwlQVKxwg2odBNQaafGwt7zUOWx+G2hPcamNmFR3BaG4egW
doyxghAa01ASqIAY++tGY5IUmPgf30eMd3fvPY6y61ycA5la3X5PZ2vtsI9vtOzGzXKe/Ylf6qNeydnLKF40QE4Ug
uUFLyCRY= mysqltunnel@mysqlslave.host.com

Si la réplication s'opère sur un serveur esclave qui a un adresse IP fixe, on peut également augmenter la sécurité en n'autorisant les connections avec cette clef que depuis cette seule adresse IP

from="192.168.56.98",command="/home/mysqltunnel/bin/tunnel_only" ssh-dss AAAAB3NzaC1kc3MAAACBAI54tV4dA3EpUeIGV
X23KjEmQn37moqLNxIDjp5WNZMBai3rD80BzI5Fir0UyvoJPhWPCCeGsmwDD/PosEzvBZtwTxhQXT8NjKlXVcKYGX
k54vLitXnGQiRLql5yoWhMAyjiBbhTCJAfADaWvo2wKhA3bLwsJwdwxDCrrrMulHwPAAAAFQCQLMB8nk+/4f5BQcc
E1dQG1GHfNwAAAIEAgXclcf/ecYWA+Es2icicZP+Q1AH1ds0RZEq765o9yaEYn9riCq15Avx3QNmTywkmNUy2P+tU
piceoNVTuE52SoeIiUOpYvGz05HwI6KMGIbLDa8mS5kCnfrxevkOuEMbdOgPJpPhoQt6mPuYuOk0JUKny3QdinX/N
bs9Befk9uYAAACASNzBvAaH9cMtdtgoTwfIieXu4+dIwlQVKxwg2odBNQaafGwt7zUOWx+G2hPcamNmFR3BaG4egW
doyxghAa01ASqIAY++tGY5IUmPgf30eMd3fvPY6y61ycA5la3X5PZ2vtsI9vtOzGzXKe/Ylf6qNeydnLKF40QE4Ug
uUFLyCRY= mysqltunnel@mysqlslave.host.com

Lancer le tunnel au demarrage et le maintenir ouvert

Sur la machine esclave, on cré un script open_tunnel qui sera lancé par root (qu'on peut enregistrer dans /root/bin). Ce script lance la connection ssh avec le tunnel et la relance automatiquement si le process meurt.

#!/bin/bash
while `true`; do
    echo `date`: Openning mysql slave-to-master ssh tunnel
        su - mysqltunnel -c "ssh -i /home/mysqltunnel/.ssh/id_dsa -L 3306:127.0.0.1:3306 mysqlmaster.server.com"
        sleep 1
    echo `date`: Connection lost...
done

Pour que le script soit lancé automatiquement au démarrage, on ajoute la ligne suivante dans /etc/rc.local

# Launch mysql slave-to-master tunnel script
# and log output to log file
/root/bin/open_tunnel > /var/log/mytunnel.log 2>&1 &

Note: ne pas oublier de configurer logrotate pour que le fichier de log ne grossisse pas.

Mise en place de la réplication

Voir http://dev.mysql.com/doc/mysql/en/Replication.html pour une description detaillée

1. Commencer par s'assurer que mysql utilise le log binaire. Le fichier de configuration my.cnf doit contenir une ligne du style:

bin-log=/path/to/binlog

1. Créer un snapshot sur le serveur maitre:
2. Se connecter sur le serveur mysql maitre, bloquer toutes les tables en écriture et récupérer la position courante du log binaire:

flush tables with read lock;
show master status

Bien prendre note du log binaire courant et de la position.

• Tout en laissant la connection mysql ouverte (et qui bloque le serveur en écriture), créer un snapshot du répertoire de données:

cd /var/lib/mysql && tar zcf mysqlMaster.tar.gz .

• sur le terminal mysql, débloquer l'écriture:

unlock tables;

• copier le snapshot sur le serveur esclave et le decompresser

cd /var/lib/mysql
/etc/init.d/mysql stop
scp mysqlmaster.server.com:/var/lib/mysql/mysqlMaster.tar.gz .
tar zxf mysqlMaster.tar.gz
rm mysqlMaster.tar.gz
/etc/init.d/mysql start

• Configurer le serveur mysql esclave et démarer la réplication. Dans un shell mysql:

      CHANGE MASTER TO
      MASTER_HOST='127.0.0.1',
      MASTER_USER='cloner',
      MASTER_PASSWORD='slavepassword',
      MASTER_LOG_FILE='mysql-bin.000378',
      MASTER_LOG_POS=5776631;

      SLAVE START;

      SHOW SLAVE STATUS;