Créer un serveur MySQL de réplication (slave) des données existantes


<p>MySQL Master-Slave Replication est une procédure permettant de répliquer en temps réel les données d&#8217;un serveur MySQL vers un autre. Nous allons voir ensemble comment mettre cette combinaison en place.</p>



<p>Nous ne sommes pas à l&#8217;abri d&#8217;un incident sur nos serveurs de production et cela même avec un backup journalier de nos bases de données.&nbsp;</p>



<p>Après le tragique incendie qui s&#8217;est déclaré dans la nuit du 9 mars sur le site d&#8217;OVHCloud à Strasbourg, qui a détruit intégralement un datacenter et une portion d&#8217;un autre. Je me suis dit qu&#8217;il serait judicieux de faire un tutoriel pour expliquer comment j&#8217;ai réussi à surmonter cette crise, et qui pourrait vous permettre d&#8217;en faire autant et d&#8217;avoir une réplication MySQL de vos données sur deux serveurs en temps réel.</p>



<h2 id="h-mise-en-situation"><strong>Mise en situation</strong></h2>



<p>Prenons un exemple concret, nous sommes le 9 mars 2021, il est 01h32 et nous avons un serveur web avec MySQL qui est sauvegardé tous les jours à 3h00 du matin sur un autre serveur (ce qui est très bien ;)).&nbsp;</p>



<p>Soudain, notre serveur commence à suffoquer et sentir le bruler sur les petits fils de son câble d&#8217;alimentation. Vous le savez, nous le savons. Un <a href="https://www.journaldunet.com/web-tech/cloud/1498567-incendie-chez-ovh-de-nombreuses-sauvegardes-irrecuperables/">tragique incendie</a> est en train de se déclarer dans la salle de notre serveur.&nbsp;</p>



<figure class="wp-block-image alignwide size-large"><img decoding="async" width="1024" height="241" src="https://i0.wp.com/backend.inrage.fr/wp-content/uploads/2021/03/surchauffe-ovh-serveur.jpg?resize=1024%2C241&#038;ssl=1" alt="montée en température serveur OVH" class="wp-image-4441" srcset="https://i0.wp.com/backend.inrage.fr/wp-content/uploads/2021/03/surchauffe-ovh-serveur.jpg?resize=1024%2C241&amp;ssl=1 1024w, https://i0.wp.com/backend.inrage.fr/wp-content/uploads/2021/03/surchauffe-ovh-serveur.jpg?resize=300%2C71&amp;ssl=1 300w, https://i0.wp.com/backend.inrage.fr/wp-content/uploads/2021/03/surchauffe-ovh-serveur.jpg?resize=768%2C181&amp;ssl=1 768w, https://i0.wp.com/backend.inrage.fr/wp-content/uploads/2021/03/surchauffe-ovh-serveur.jpg?w=1261&amp;ssl=1 1261w" sizes="(max-width: 1000px) 100vw, 1000px" data-recalc-dims="1" /><figcaption>Exemple de montée en température serveur OVH</figcaption></figure>



<p>Notre petit serveur passe en mode Super Saiyan, et tant bien que mal à mettre ses ventilateurs à 4500 RPM pour se refroidir. Mais il est déjà trop tard&#8230;</p>


	<div class="wp-block-jetpack-gif">
		<figure>
							<div class="wp-block-jetpack-gif-wrapper" style="padding-top:56%">
					<iframe src="https://giphy.com/embed/QMHoU66sBXqqLqYvGO" title="this is fine"></iframe>
				</div>
								</figure>
	</div>
	


<p id="h-restauration-du-serveur"><strong>La restauration du serveur</strong></p>



<p>De mon côté, après de longues heures d&#8217;attente et de scroll sur Twitter pour savoir ce qu&#8217;il est en train de se passer. Je me rends compte que notre serveur est perdu à tout jamais #RIP</p>



<p>On prend de l&#8217;avance, on achète un nouveau VPS, on réinstalle tout le serveur web et là on réimporte notre sauvegarde de notre base de données (Big Up Bacula ;)) du 8 mars 2021 à 03h12.&nbsp;</p>



<p><strong>WOAH ! </strong>24 heures de delta perdu, de clients perdus, de commandes perdues.</p>



<p>Cela va générer des conflits d&#8217;ID dans tous les sens avec nos marketplaces, car ces IDs n&#8217;existent pas encore chez nous, mais déjà chez eux !</p>



<p>Comment aurait-t-on pu réduire ce temps ? Je ne vais pas faire un backup de mes bases de données toutes les 30 minutes, non ?</p>



<p>Alors oui (on peut), mais non. C&#8217;est là que la réplication MySQL est une très bonne pratique pour éviter ce genre de problèmes.&nbsp;</p>



<p>Synchroniser en temps réel un serveur MySQL sur un ou plusieurs serveurs en plus de nos sauvegardes journalières c&#8217;est la clé de la sérénité !</p>



<h2 id="h-comment-cr-er-un-serveur-mysql-de-r-plication-pour-notre-base-de-donn-es"><strong>Comment créer un serveur MySQL de réplication pour notre base de données</strong></h2>



<p>Dans ma configuration actuelle, je possède deux serveurs MySQL, un serveur maitre (master) inr-dbm-01 et un serveur secondaire (slave) inr-dbs-01 :&nbsp;</p>



<ul><li>inr-dbm-01 (master // OVH VPS) avec l&#8217;IP : 10.20.30.40</li><li>inr-dbs-01 (slave // Online VPS) avec l&#8217;IP : 50.60.70.80</li></ul>



<p>inr-dbm-01 fait office de serveur MySQL principal pour la production, où mes sites sont hébergés, et inr-dbs-01 quant à lui est le serveur secondaire qui réplique en temps réel inr-dbm-01.</p>



<p>Dès qu&#8217;une opération est effectuée sur inr-dbm-01, inr-dbs-01 reçoit aussi l&#8217;information !</p>



<h2 id="h-configuration-du-serveur-principal">Configuration du serveur principal</h2>



<p>Nous allons devoir prévenir nos clients qu&#8217;un temps de maintenance de quelques minutes va interrompre l&#8217;écriture sur notre serveur MySQL principal, car nous allons passer les bases de données en lecture seule le temps de la sauvegarde de cette dernière.</p>



<p>Ce temps de maintenance va dépendre de la taille de votre base de données bien évidemment.</p>



<h3 id="h-1-mise-jour-de-la-configuration-mysql">1/ Mise à jour de la configuration MySQL</h3>



<p>Pour MySQL, éditez le fichier <code>/etc/mysql/mysql.conf.d/mysqld.conf</code> et sous le groupe <code>[mysqld]</code> supprimez les commentaires et ajoutez :</p>



<p>PS : Si vous êtes sur MariaDB, le fichier de configuration se trouve ici : <code>/etc/mysql/mariadb.conf.d/50-server.cnf</code></p>



<pre class="wp-block-code"><code>&#91;mysqld]

bind-address = 0.0.0.0
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log</code></pre>



<p>Cela va permettre à la base de données de recevoir des connexions sur tout le monde (bind-address); il était configuré sur 127.0.0.1 avant. </p>



<p>Le server-id devra être unique entre ce serveur et le serveur enfant que l&#8217;on regardera un peu plus bas.</p>



<p>Et le <code>log_bin</code> va permettre d&#8217;activer les logs au format binaire pour la communication entre nos deux serveurs.</p>



<h3 id="h-2-red-marrage-du-service">2/ Redémarrage du service</h3>



<p>Pour appliquer les changements, nous devons redémarrer notre serveur MySQL : </p>



<pre class="wp-block-code"><code>systemctl restart mysql</code></pre>



<h3 id="h-3-cr-ation-d-un-utilisateur-pour-la-r-plication">3/ Création d&#8217;un utilisateur pour la réplication</h3>



<p>Nous allons créer un utilisateur qui sera dédié à la réplication des données sur notre serveur secondaire.</p>



<pre class="wp-block-code"><code>root@inr-dbm-01: ~ # mysql -uroot -p

mysql&gt; CREATE USER 'replicator'@'50.60.70.80' IDENTIFIED BY 'Sl@v3p@$$W0RD';

mysql&gt; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'50.60.70.80';</code></pre>



<p><code>replicator</code> est le nom de l&#8217;utilisateur et <code>50.60.70.80</code> est l&#8217;adresse IP de notre serveur secondaire (inr-dbs-01). Changez ces valeurs par les vôtres.</p>



<h3 id="h-4-lecture-seule-de-votre-serveur-mysql-principal">4/ Lecture seule de votre serveur MySQL principal</h3>



<p>Nous allons verrouiller (READ-ONLY) la lecture et l&#8217;écriture (INSERT / UPDATES, &#8230;) de toutes les tables nous permettant de faire une sauvegarde (snapshot) de notre base de données.</p>



<pre class="wp-block-code"><code>root@inr-dbm-01: ~ # mysql -uroot -p

mysql&gt; FLUSH TABLES WITH READ LOCK;</code></pre>



<p>Attention, le verrouillage de nos tables s&#8217;arrête si vous fermer le client MySQL cli ou si vous tapez UNLOCK TABLES. Gardez bien le lock jusqu&#8217;à la fin de la sauvegarde de votre base de données.</p>



<h3 id="h-5-position-du-log-pour-la-r-plication">5/ Position du log pour la réplication</h3>



<p>Nous allons devoir sauvegarder les valeurs de <code>File</code> et <code>Position</code> ce qui va nous permettre plus tard d&#8217;initialiser la réplication de notre serveur MySQL secondaire de réplication.</p>



<pre class="wp-block-code"><code>root@inr-dbm-01: ~ # mysql -uroot -p

mysql&gt; SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.003577 | 141378624 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+</code></pre>



<h3 id="h-6-sauvegarde-de-notre-base-de-donn-es">6/ Sauvegarde de notre base de données</h3>



<p>Nous sauvegarder l&#8217;intégralité de notre base de données dans un fichier <code>dump.sql</code></p>



<pre class="wp-block-code"><code>mysqldump -uroot -p --all-databases --single-transaction --triggers --routines &gt; dump.sql</code></pre>



<h3 id="h-7-d-blocage-de-notre-base-de-donn-es">7/ Déblocage de notre base de données</h3>



<p>Une fois que notre sauvegarde est terminée, nous allons pouvoir retirer le READ-ONLY de notre base de données : </p>



<pre class="wp-block-code"><code>root@inr-dbm-01: ~ # mysql -uroot -p

mysql&gt; UNLOCK TABLES;</code></pre>



<p>Votre base de données est désormais réutilisable en production ! Et grâce à la position du log de l&#8217;étape 5, notre serveur secondaire pourra se repositionner correctement pour effectuer les opérations de différences entre les deux bases de données.</p>



<h3 id="h-8-transfert-de-notre-base-de-donn-es-sur-notre-serveur-secondaire">8/ Transfert de notre base de données sur notre serveur secondaire</h3>



<p>Le plus gros du travail est fait, il ne nous reste plus qu&#8217;à transférer notre sauvegarde vers notre serveur MySQL secondaire : </p>



<pre class="wp-block-code"><code>root@inr-dbm-01: ~ # scp dump.sql root@50.60.70.80:/tmp/</code></pre>



<p>Vous avez bien mérité un bon café !</p>


	<div class="wp-block-jetpack-gif">
		<figure>
							<div class="wp-block-jetpack-gif-wrapper" style="padding-top:57%">
					<iframe src="https://giphy.com/embed/l2JI4zgwXw5IfyssE" title="coffee"></iframe>
				</div>
								</figure>
	</div>
	


<h2 id="h-configuration-du-serveur-secondaire">Configuration du serveur secondaire</h2>



<p>Comme pour le serveur principal, nous allons devoir faire quelques ajustements au niveau de la configuration de MySQL, de restaurer la base de données et de démarrer le processus de réplication.</p>



<h3 id="h-1-mise-jour-de-la-configuration-mysql-1">1/ Mise à jour de la configuration MySQL</h3>



<p>Pour MySQL, éditez le fichier <code>/etc/mysql/mysql.conf.d/mysqld.conf</code> et sous le groupe <code>[mysqld]</code> supprimez les commentaires et ajoutez :</p>



<p>PS : Si vous êtes sur MariaDB, le fichier de configuration se trouve ici : <code>/etc/mysql/mariadb.conf.d/50-server.cnf</code></p>



<pre class="wp-block-code"><code>&#91;mysqld]
bind-address = 10.20.30.40
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log</code></pre>



<p>Comme pour le serveur principal, nous avons défini les 3 lignes de configuration, server-id est unique et différente de inr-dbm-01 (dans notre cas 2) et nous avons verrouillé MySQL à l&#8217;écoute de notre serveur principal (car on ne veut pas que tout le monde écoute dessus)</p>



<h3 id="h-2-red-marrage-de-mysql-sur-le-serveur-secondaire-de-r-plication">2/ Redémarrage de MySQL sur le serveur secondaire de réplication</h3>



<pre class="wp-block-code"><code>systemctl restart mysql</code></pre>



<h3 id="h-3-communication-entre-le-secondaire-et-primaire">3/ Communication entre le secondaire et primaire</h3>



<pre class="wp-block-code"><code>root@inr-dbs-01: ~ # mysql -uroot -p

mysql&gt; STOP SLAVE;

mysql&gt; CHANGE MASTER TO
    -&gt; MASTER_HOST='10.20.30.40',
    -&gt; MASTER_USER='replicator',
    -&gt; MASTER_PASSWORD='Sl@v3p@$$W0RD',
    -&gt; MASTER_LOG_FILE='mysql-bin.003577',
    -&gt; MASTER_LOG_POS=141378624;

mysql&gt; START SLAVE;</code></pre>



<p>Pensez à changer les valeurs par les vôtres.</p>



<h3>4/ Vérification du fonctionnement </h3>



<p>Pour vérifier que tout fonctionne et que votre serveur MySQL secondaire est bien connecté au serveur principal : </p>



<pre class="wp-block-code"><code>mysql&gt; SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.20.30.40
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.003577
          Read_Master_Log_Pos: 229958895
               Relay_Log_File: mysql02-relay-bin.000002
                Relay_Log_Pos: 11083560
        Relay_Master_Log_File: mysql-bin.003577
             Slave_IO_Running: Yes &lt;-- #IMPORTANT#
            Slave_SQL_Running: Yes &lt;-- #IMPORTANT#
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 152461864
              Relay_Log_Space: 88580800
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 1575 &lt;-- #IMPORTANT#
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: ee9774f5-8552-11e8-b6cb-00505684dbd9
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:</code></pre>



<p>Vous devez vérifier quelques paramètres pour être sûr que tout fonctionne correctement :</p>



<ul><li><strong>Slave_IO_Running</strong> : Yes</li><li><strong>Slave_SQL_Running</strong> : Yes</li><li><strong>Seconds_Behind_Master</strong> : 1575 (si ce chiffre n&#8217;est pas à 0, nous devons attendre qu&#8217;il le soit pour nous assurer que la synchronisation est terminée)</li></ul>



<h2 id="h-en-conclusion"><strong>En conclusion</strong></h2>



<p>Vous savez désormais répliquer en temps réel votre serveur de base de données en temps réel et cela vous permet de réduire le delta entre votre sauvegarde et le serveur principal.</p>



<p>Ce qui est arrivé à OVH le 9 mars 2021 peut arriver à n&#8217;importe qui et à n&#8217;importe quel moment. Je ne le répèterais jamais assez ! mais faites vos backups et testez-les !</p>



<p>Si vous ne savez pas le faire, ou si vous n&#8217;avez pas le temps de le faire, sachez qu&#8217;il existe beaucoup de sociétés d&#8217;infogérance qui peut prendre en charge ces opérations.</p>



<p>Depuis des années, je travaille avec la société <a href="https://www.dutiko.com/">Dutiko</a> qui est spécialisée dans l&#8217;infogérance 24/7 depuis plus de 11 ans !</p>



<p>Ils infogèrent mes serveurs et cela m&#8217;a permis de sauver ma petite société à mainte reprise des flammes, des erreurs humaines (les miennes ;)) ou celle de mes clients et des problèmes matériels !</p>



<p>Ce qui me permet de me concentrer sur mon travail de <a href="https://backend.inrage.fr/prestations/maintenance-site-internet/maintenance-ponctuelle/">développeur Freelance et de maintenance</a></p>


Créer un serveur MySQL de réplication (slave) des données existantes

Articles reliés

Contact Form 7 : Utiliser l’URL pour définir la valeur d’un select ou radio

Blocs Gutenberg avec Sage 9 sur WordPress