MySQL
Réplication
Recherche…
Remarques
La réplication est utilisée pour copier des données [de sauvegarde] depuis un serveur de base de données MySQL vers un ou plusieurs serveurs de base de données MySQL.
Master - Le serveur de base de données MySQL, qui sert les données à copier
Slave - Le serveur de base de données MySQL copie les données servies par Master
Avec MySQL, la réplication est asynchrone par défaut. Cela signifie que les esclaves n'ont pas besoin d'être connectés en permanence pour recevoir les mises à jour du maître. Par exemple, si votre esclave est éteint ou n'est pas connecté au maître et que vous changez d'esclave ou que vous vous connectez ultérieurement au maître, il se synchronisera automatiquement avec le maître.
Selon la configuration, vous pouvez répliquer toutes les bases de données, les bases de données sélectionnées ou même les tables sélectionnées dans une base de données.
Formats de réplication
Il existe deux types principaux de formats de réplication
Statement Based Replication (SBR) - réplique des instructions SQL entières. Dans ce cas, le maître écrit des instructions SQL dans le journal binaire. La réplication du maître sur l'esclave fonctionne en exécutant les instructions SQL sur l'esclave.
Row Based Replication (RBR) - réplique uniquement les lignes modifiées. Dans ce cas, le maître écrit des événements dans le journal binaire pour indiquer comment les lignes de la table sont modifiées. La réplication du maître sur l'esclave fonctionne en copiant les événements représentant les modifications apportées aux lignes de la table à l'esclave.
Vous pouvez également utiliser une troisième variété, MBR (Mixed Based Replication) . Dans ce cas, la journalisation basée sur l'instruction et sur la ligne est utilisée. Le journal sera créé en fonction de ce qui est le plus approprié pour le changement.
Le format basé sur les instructions était le format par défaut dans les versions de MySQL antérieures à 5.7.7. En MySQL 5.7.7 et versions ultérieures, le format basé sur les lignes est le format par défaut.
Maître - Configuration de la réplication esclave
Considérons 2 serveurs MySQL pour la configuration de la réplication, l’un étant un maître et l’autre un esclave.
Nous allons configurer le maître pour qu'il conserve un journal de chaque action effectuée sur celui-ci. Nous allons configurer le serveur esclave qu'il devrait regarder le journal sur le maître et chaque fois que des changements se produisent dans le journal sur le maître, il devrait faire la même chose.
Configuration principale
Tout d'abord, nous devons créer un utilisateur sur le maître. Cet utilisateur va être utilisé par Slave pour créer une connexion avec le maître.
CREATE USER 'user_name'@'%' IDENTIFIED BY 'user_password';
GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'%';
FLUSH PRIVILEGES;
Modifiez user_name et user_password fonction de votre nom d'utilisateur et de votre mot de passe.
Maintenant, le my.inf (my.cnf sous Linux) doit être modifié. Incluez les lignes suivantes dans la section [mysqld].
server-id = 1
log-bin = mysql-bin.log
binlog-do-db = your_database
La première ligne est utilisée pour attribuer un identifiant à ce serveur MySQL.
La deuxième ligne indique à MySQL de commencer à écrire un journal dans le fichier journal spécifié. Sous Linux, cela peut être configuré comme log-bin = /home/mysql/logs/mysql-bin.log . Si vous commencez la réplication sur un serveur MySQL dans lequel la réplication a déjà été utilisée, assurez-vous que ce répertoire est vide de tous les journaux de réplication.
La troisième ligne est utilisée pour configurer la base de données pour laquelle nous allons écrire le journal. Vous devez remplacer your_database par le nom de votre base de données.
Assurez-vous que skip-networking n'a pas été activé et redémarrez le serveur MySQL (Master)
Configuration d'esclave
my.inf fichier my.inf doit également être édité dans Slave. Incluez les lignes suivantes dans la section [mysqld].
server-id = 2
master-host = master_ip_address
master-connect-retry = 60
master-user = user_name
master-password = user_password
replicate-do-db = your_database
relay-log = slave-relay.log
relay-log-index = slave-relay-log.index
La première ligne est utilisée pour attribuer un identifiant à ce serveur MySQL. Cet identifiant doit être unique.
La deuxième ligne est l'adresse IP du serveur maître. Changez cela en fonction de l'IP de votre système maître
La troisième ligne est utilisée pour définir une limite de relance en secondes.
Les deux lignes suivantes indiquent le nom d'utilisateur et le mot de passe de l'esclave, en utilisant le lien avec le maître.
La ligne suivante définit la base de données à répliquer.
Les deux dernières lignes utilisées pour attribuer des noms de fichier relay-log et relay-log-index .
Assurez-vous que le skip-networking n'a pas été activé et redémarrez le serveur MySQL (esclave)
Copier des données sur un esclave
Si des données sont constamment ajoutées au maître, nous devrons empêcher tout accès à la base de données sur le maître afin que rien ne puisse être ajouté. Cela peut être réalisé en exécutant l'instruction suivante dans Master.
FLUSH TABLES WITH READ LOCK;
Si aucune donnée n'est ajoutée au serveur, vous pouvez ignorer l'étape ci-dessus.
Nous allons prendre la sauvegarde des données du maître en utilisant mysqldump
mysqldump your_database -u root -p > D://Backup/backup.sql;
Modifiez your_database répertoire de base de your_database et de sauvegarde en fonction de votre configuration. Vous aurez maintenant un fichier appelé backup.sql à l'emplacement indiqué.
Si votre base de données n'existe pas dans votre esclave, créez-la en exécutant ce qui suit
CREATE DATABASE `your_database`;
Maintenant, nous devons importer la sauvegarde dans le serveur MySQL Slave.
mysql -u root -p your_database <D://Backup/backup.sql
--->Change `your_database` and backup directory according to your setup
Démarrer la réplication
Pour démarrer la réplication, il est nécessaire de trouver le nom du fichier journal et la position du journal dans le maître. Donc, lancez ce qui suit dans Master
SHOW MASTER STATUS;
Cela vous donnera une sortie comme ci-dessous
+---------------------+----------+-------------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+-------------------------------+------------------+
| mysql-bin.000001 | 130 | your_database | |
+---------------------+----------+-------------------------------+------------------+
Ensuite, exécutez ce qui suit dans Slave
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='user_name',
MASTER_PASSWORD='user_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=130;
SLAVE START;
D'abord, nous arrêtons l'esclave. Ensuite, nous lui disons exactement où regarder dans le fichier journal maître. Pour le nom MASTER_LOG_FILE et MASTER_LOG_POS , utilisez les valeurs obtenues en exécutant la commande SHOW MASTER STATUS sur le maître.
Vous devez modifier l'adresse IP du maître dans MASTER_HOST et modifier l'utilisateur et le mot de passe en conséquence.
L'esclave va maintenant attendre. L'état de l'esclave peut être consulté en exécutant ce qui suit
SHOW SLAVE STATUS;
Si vous avez précédemment exécuté FLUSH TABLES WITH READ LOCK dans Master, libérez les tables de lock by run de la manière suivante:
UNLOCK TABLES;
Maintenant, le maître garde un journal pour chaque action effectuée et le serveur esclave examine le journal du maître. Chaque fois que des changements se produisent dans le journal sur le maître, Slave réplique cela.
Erreurs de réplication
Chaque fois qu'il y a une erreur lors de l'exécution d'une requête sur l'esclave, MySQL arrête automatiquement la réplication pour identifier le problème et le corriger. Cela est principalement dû au fait qu’un événement a provoqué une duplication de la clé ou qu’une ligne n’a pas été trouvée et qu’elle ne peut être ni mise à jour ni supprimée. Vous pouvez ignorer ces erreurs, même si cela n'est pas recommandé
Pour ignorer une seule requête qui suspend l'esclave, utilisez la syntaxe suivante
SET GLOBAL sql_slave_skip_counter = N;
Cette instruction ignore les N prochains événements du maître. Cette instruction est valide uniquement lorsque les threads esclaves ne sont pas en cours d'exécution. Sinon, il génère une erreur.
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;
Dans certains cas, cela va bien. Mais si l'instruction fait partie d'une transaction à plusieurs instructions, elle devient plus complexe, car ignorer l'instruction générant l'erreur provoquera l'ignition de la transaction entière.
Si vous voulez ignorer plus de requêtes produisant le même code d'erreur et si vous êtes sûr que sauter ces erreurs ne rendra pas votre esclave incohérent et que vous voulez tout ignorer, vous ajouterez une ligne pour ignorer ce code d'erreur dans my.cnf .
Par exemple, vous pouvez ignorer toutes les erreurs en double que vous pourriez avoir
1062 | Error 'Duplicate entry 'xyz' for key 1' on query
Ensuite, ajoutez ce qui suit à votre my.cnf
slave-skip-errors = 1062
Vous pouvez également ignorer d'autres types d'erreurs ou tous les codes d'erreur, mais veillez à ne pas ignorer ces erreurs si vous ignorez ces erreurs. Voici la syntaxe et les exemples
slave-skip-errors=[err_code1,err_code2,...|all]
slave-skip-errors=1062,1053
slave-skip-errors=all
slave-skip-errors=ddl_exist_errors