les sites de phcha.com, rencontre, broderie et documentation

(36) Réplication entre 2 serveurs MYSQL

Configurations du master et du slave
Création des certificats
Compte de réplication
Réplication circulaire
Reprise de la réplication

Configurations du master et du slave [haut]

Le master et le slave tournent sur des ports différents et sont installés dans des arborescences distinctes. Cela permet de faire tourner les deux serveurs sur la même machine.

Le master est en écoute sur le port par défaut 3306 et a été configuré dans l'arborescence /files/mysql.

Éditer my.cnf (voir le résultat de la commande /files/mysql/libexec/mysqld --help --verbose pour placer ce fichier).

Les deux fichiers my.cnf sont les suivants.

[mysqld]
port=3306
socket=/tmp/mysql.sock
log-bin=mysql-bin
binlog-ignore-db=mysql
# server-id doit etre unique
server-id=1
ssl
ssl-ca=/files/mysql/openssl/cacert.pem
ssl-cert=/files/mysql/openssl/server-cert.pem
ssl-key=/files/mysql/openssl/server-key.pem

# auto_increment_increment      = 10
# auto_increment_offset         = 1

[mysql]
port=3306
socket=/tmp/mysql.sock
ssl
ssl-ca=/files/mysql/openssl/cacert.pem
ssl-cert=/files/mysql/openssl/client-cert.pem
ssl-key=/files/mysql/openssl/client-key.pem

Le slave est en écoute sur le port sur le port 3307 et a été configuré dans l'arborescence /files/mysql3307. Son fichier de configuration est my.cnf (voir le résultat de la commande /files/mysql/libexec/mysqld --help --verbose pour placer ce fichier). La valeur de server-id doit être unique parmi l'ensemble des machines intervenantes.

[mysqld]
port=3307
socket=/tmp/mysql3307.sock
log-bin=mysql-bin
binlog-ignore-db=mysql
#log-bin-index = /var/log/mysql/log-bin.index
#log-error = /var/log/mysql/error.log
# server-id doit etre unique
server-id=2
#master-host = mastersite.com
#master-port = 3306
#master-user = repl
#master-password = my_pwd
#relay-log = /var/log/mysql/relay.log
#relay-log-info-file = /var/log/mysql/relay-log.info
#relay-log-index = /var/log/mysql/relay-log.index
master-ssl
master-ssl-ca=/files/mysql3307/openssl/cacert.pem
master-ssl-cert=/files/mysql3307/openssl/server-cert.pem
master-ssl-key=/files/mysql3307/openssl/server-key.pem
ssl
ssl-ca=/files/mysql3307/openssl/cacert.pem
ssl-cert=/files/mysql3307/openssl/server-cert.pem
ssl-key=/files/mysql3307/openssl/server-key.pem

# auto_increment_increment      = 10
# auto_increment_offset         = 2

[mysql]
port=3307
socket=/tmp/mysql3307.sock
ssl
ssl-ca=/files/mysql3307/openssl/cacert.pem
ssl-cert=/files/mysql3307/openssl/client-cert.pem
ssl-key=/files/mysql3307/openssl/client-key.pem

Les variables auto_increment_increment et auto_increment_offset permettent à plusieurs serveurs (moins de serveurs que la valeur de auto_increment_increment) de se répliquer circulairement sans conflit des colonnes de type auto_increment. Chacun des serveurs doit définir un auto_increment_offset augmenté de 1 par rapport à un autre. Voir cet article pour les détails.

Création des certificats [haut]

On utilise SSL pour sécuriser le flux de réplication. Les certificats sont générés en suivant la documentation mysql résumée ci-dessous. L'option -days 3600 est ajoutée lors de la génération du certificat de l'autorité.

# cd /files/mysql
# setenv DIR `pwd`/openssl
# setenv PRIV $DIR/private

# mkdir $DIR $PRIV $DIR/newcerts
# cp /files/misc/ssl/ssl/openssl.cnf $DIR
# replace ./demoCA $DIR -- $DIR/openssl.cnf
# touch $DIR/index.txt
# echo "01" > $DIR/serial

Generation of Certificate Authority(CA).

# openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem -days 3600 -config $DIR/openssl.cnf

Create server request and key. Le fichier server-key.pem contient la clé ssl sécurisée par la passphrase et fait office de clé privée (extension .key). Le fichier server-req.pem doit être signé par le CA (extension .req ou .csr).

# openssl req -new -keyout $DIR/server-key.pem -out $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf

Remove the passphrase from the key (optional).

# openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem

Sign server cert. Le fichier server-cert.pem fait office de clé publique (extension .crt).

# openssl ca  -policy policy_anything -out $DIR/server-cert.pem -config $DIR/openssl.cnf -infiles $DIR/server-req.pem

Create client request and key.

Veiller à donner une valeur de Common Name différente de celle donnée pour la clé du serveur.

# openssl req -new -keyout $DIR/client-key.pem -out $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf

Remove a passphrase from the key (optional).

# openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem

Sign client cert.

# openssl ca  -policy policy_anything -out $DIR/client-cert.pem -config $DIR/openssl.cnf -infiles $DIR/client-req.pem

Copier les fichiers openssl.cnf, server-cert.pem, server-key.pem et cacert.pem dans l'arborescence du client.

Éditer le fichier openssl.cnf juste copié pour modifier si besoin la variable dir.

Pour vérifier que tout est correct, on peut utiliser le client/serveur fourni dans la distribution, il suffit de lancer le serveur dans une fenêtre et le client dans une autre, puis de taper des caractères dans la fenêtre du client pour les voir s'afficher dans celle du serveur.

# openssl s_server -cert $DIR/server-cert.pem -key $DIR/server-key.pem -CAfile $DIR/cacert.pem

# openssl s_client -cert $DIR/client-cert.pem -key $DIR/client-key.pem -CAfile $DIR/cacert.pem

# openssl verify -purpose sslserver -CAfile $DIR/cacert.pem $DIR/server-cert.pem 
$DIR/server-cert.pem: OK
# openssl verify -purpose sslclient -CAfile $DIR/cacert.pem $DIR/client-cert.pem 
$DIR/client-cert.pem: OK
Compte de réplication [haut]

La vérification que le support SSL est présent détermine la création du compte de réplication. Il faut avoir compilé la distribution avec l'option --with-openssl et aussi avoir ajouté les lignes relatives à ssl dans le fichier my.cnf.

mysql> SHOW VARIABLES LIKE "%ssl%" ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
+---------------+-------+

La première étape consiste à ajouter un compte de réplication sur les serveurs. Faire cette manipulation sur toutes les machines permet d'intervertir facilement leurs rôles. Pour une réplication sécurisée, on doit ajouter une des 3 commandes REQUIRE à la commande de création du compte et augmenter les fichiers my.cnf.

master mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT
    ON *.*
    TO 'repl'@'slave_host'
    IDENTIFIED BY 'my_pwd';
master mysql> FLUSH PRIVILEGES ;

slave mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT
    ON *.*
    TO 'repl'@'master_host'
    IDENTIFIED BY 'my_pwd';
slave mysql> FLUSH PRIVILEGES ;

master mysql> GRANT REPLICATION ...
        REQUIRE SSL
        REQUIRE X509
        REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';

Méthode 1

Copier la base complète (avec mysqldump par exemple) depuis le master vers le slave et noter les informations retournées par le commande suivante.

master mysql> FLUSH TABLES WITH READ LOCK;
master mysql> SHOW MASTER STATUS ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      188 |              |                  |
+------------------+----------+--------------+------------------+

master# mysqldump --user=root --password=my_pwd --extended-insert=FALSE --databases db1 db2 db3 > /tmp/backup.sql
master mysql> UNLOCK TABLES ;

Sur le slave, initialiser la réplication. L'option --master-data de la commande mysqldump n'est pas utilisée ici, il faut donc la commande SQL associée en remplacement pour indiquer le point de reprise.

slave mysql> STOP SLAVE ;
slave# mysql --user=root --password=my_pwd < /tmp/backup.sql
slave mysql> CHANGE MASTER TO MASTER_HOST='master_host',
        MASTER_USER='repl',
        MASTER_PASSWORD='my_pwd',
        MASTER_LOG_FILE='mysql-bin.000001',
        MASTER_LOG_POS=188,
        MASTER_PORT=3306 ;
slave mysql> START SLAVE ;
slave mysql> SHOW SLAVE STATUS ;

Les informations ci-dessus sont sauvegardées dans le fichier master.info qui est prioritaire par rapport aux infos de my.cnf, certaines lignes du fichier ou colonnes correspondantes de la table peuvent y être vides (par exemple Master_SSL_CA_File lorsque la réplication ne se fait pas par SSL). En cas de changement de compte de réplication (par exemple en cas de passage à un compte SSL), il faut effacer le fichier et le recréer par la commande CHANGE MASTER qui donnera de nouvelles valeurs par défaut correctes ou utiliser une commande plus détaillée. On peut aussi utiliser la commande RESET SLAVE pour modifier master.info et initialiser la réplication totalement.

CHANGE MASTER TO master_def [, master_def] ...

master_def:
    MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = count
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'

Pour une connexion par SSL, il faut ajouter les déclarations de MASTER_SSL, MASTER_SSL_CERT et MASTER_SSL_KEY.

Régulierement, il faudra faire tourner les logs et supprimer les anciens fichiers dès qu'ils auront été lus par la contrepartie.

mysql> FLUSH LOGS;

Méthode 2

Une autre méthode pour initialiser la réplication issue de cet article. Les informations sont mises dans le fichier my.cnf ou dans le fichier contenant les bases à l'aide de l'option --master-data de la commande mysqldump. Elle n'utilise donc pas CHANGE MASTER.

...
master-host = master
master-port = 3306
master-user = repl
master-password = my_pwd
...

Pour initialiser le contenu de la base sur le slave, il faut supprimer les accès à la base pendant un moment.

master mysql> SHOW VARIABLES LIKE 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

master mysql> SET GLOBAL max_connections = 0;
master mysql> SHOW PROCESSLIST;
master mysql> KILL ...

master# mysqldump --user=root --password=my_pwd --extended-insert=FALSE --master-data --databases db1 db2 db3 > /tmp/backup.sql

master mysql> SET GLOBAL max_connections = 100;

slave# mysql --user=root --password=my_pwd < /tmp/backup.sql

Pour vérifier les droits d'accès depuis le slave, on peut se connecter sur le master en utilisant le compte créé ci-dessus.

master mysql> STATUS;

Il faut ensuite démarrer la réplication.

slave mysql> START SLAVE;
slave mysql> SHOW SLAVE STATUS;

Régulierement, il faudra faire tourner les logs et supprimer les anciens fichiers dès qu'ils auront été lus par la contrepartie. Différentes méthodes.

mysql> FLUSH LOGS;

master mysql> SHOW MASTER LOGS ;
master mysql> PURGE MASTER LOGS TO 'mysql-bin.010' ;
master mysql> PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26' ;
Réplication circulaire [haut]

Voici le premier des deux fichiers my.cnf. Les fichiers master.info sont initialisés sur chaque machine par la commande CHANGE MASTER comme vu plus haut.

[mysqld]
port=3311
socket=/tmp/mysql3311-circ.sock
log-bin=mysql-bin
binlog-ignore-db=mysql
# server-id doit etre unique
server-id=3311
auto_increment_increment=10
auto_increment_offset=1
#log-slave-updates
#replicate-same-server-id=0
master-ssl
master-ssl-ca=/files/mysql3311-circ/openssl/cacert.pem
master-ssl-cert=/files/mysql3311-circ/openssl/server-cert.pem
master-ssl-key=/files/mysql3311-circ/openssl/server-key.pem
ssl
ssl-ca=/files/mysql3311-circ/openssl/cacert.pem
ssl-cert=/files/mysql3311-circ/openssl/server-cert.pem
ssl-key=/files/mysql3311-circ/openssl/server-key.pem

[mysql]
port=3311
socket=/tmp/mysql3311-circ.sock
ssl
ssl-ca=/files/mysql3311-circ/openssl/cacert.pem
ssl-cert=/files/mysql3311-circ/openssl/client-cert.pem
ssl-key=/files/mysql3311-circ/openssl/client-key.pem

[mysqladmin]
port=3311
socket=/tmp/mysql3311-circ.sock

Voici le second fichier my.cnf. Les directives log-slave-updates et replicate-same-server-id=0 doivent être ajoutées lorsque la réplication circulaire se fait sur plus de 2 serveurs. À noter l'ajout des directives auto_increment_increment et auto_increment_offset qui contraignent les valeurs des colonnes auto_increment pour que deux serveurs secondaires l'un de l'autre ne puissent insérer la même valeur.

[mysqld]
port=3312
socket=/tmp/mysql3312-circ.sock
log-bin=mysql-bin
binlog-ignore-db=mysql
# server-id doit etre unique
server-id=3312
auto_increment_increment=10
auto_increment_offset=2
#log-slave-updates
#replicate-same-server-id=0
master-ssl
master-ssl-ca=/files/mysql3312-circ/openssl/cacert.pem
master-ssl-cert=/files/mysql3312-circ/openssl/server-cert.pem
master-ssl-key=/files/mysql3312-circ/openssl/server-key.pem
ssl
ssl-ca=/files/mysql3312-circ/openssl/cacert.pem
ssl-cert=/files/mysql3312-circ/openssl/server-cert.pem
ssl-key=/files/mysql3312-circ/openssl/server-key.pem

[mysql]
port=3312
socket=/tmp/mysql3312-circ.sock
ssl
ssl-ca=/files/mysql3312-circ/openssl/cacert.pem
ssl-cert=/files/mysql3312-circ/openssl/client-cert.pem
ssl-key=/files/mysql3312-circ/openssl/client-key.pem

[client]
port=3312
socket=/tmp/mysql3312-circ.sock
Reprise de la réplication [haut]

On suppose le scénario suivant : pour une raison inconnue, la réplication est stoppée et ne veut pas repartir (on a un crash du serveur slave à chaque tentative de relance. On décide alors d'installer une nouvelle version de mysql en conservant les bases.

On commence par noter l'état de la réplication car il faut poursuivre à partir de là plutôt que d'initialiser une réplication depuis rien. Cette seconde méthode nécessite en effet de stopper le serveur. Par exemple :

fichier mysql-bin.000050
position 186597

Pour simplifier la suite, il faut faire tourner les logs afin de créer un point de reprise. En effet chaque nouveau fichier commence par une commande de selection de base (use), ce qui doit être la première commande pour reprendre une réplication :

master> FLUSH LOGS;

Un fichier mysql-bin.000051 existe maintenant, mais avant de le jouer, il faut répliquer le fichier mysql-bin.000050 depuis la position 186597 jusqu'à sa fin.

master# mysqlbinlog ./mysql-bin.000050 > /tmp/1

Globalement, on veut supprimer du fichier /tmp/1, toutes les lignes avant la ligne # at 186597, mais ce n'est pas aussi simple car il faut garder la dernière commande de sélection de base ainsi que les ordres de modification des variables INSERT_ID et TIMESTAMP qui doivent rester avec leur commande sql associée.

Dans vi, se positionner avec /^# at 186597 et marquer cette position initiale par mi, rechercher la dernière sélection de base par ?^use, supprimer toutes les lignes précédentes par kma:1d'a, retourner au début de la réplication par 'i. Il reste à examiner les lignes qui précèdent et décider de les garder (SET) ou de les supprimer (UPDATE 1,'ig/^UPDATE/d, INSERT, DELETE, #, LOAD DATA, ...).

Puis on supprime toutes les lignes # ... restantes avec grep. Le fichier peut maintenant être copié sur le serveur slave et y être joué.

slave> SOURCE /tmp/1

La réplication peut maintenant se poursuivre à la position 4 du fichier mysql-bin.000051.

slave> CHANGE MASTER TO MASTER_LOG_FILE ='mysql-bin.000051', MASTER_LOG_POS=4;
slave> START SLAVE;
slave> SHOW SLAVE STATUS;