{"id":1602,"date":"2017-09-29T16:39:18","date_gmt":"2017-09-29T14:39:18","guid":{"rendered":"https:\/\/www.h-hennes.fr\/blog\/?p=1602"},"modified":"2017-09-29T16:39:18","modified_gmt":"2017-09-29T14:39:18","slug":"mise-en-place-dune-replication-mysql","status":"publish","type":"post","link":"https:\/\/www.h-hennes.fr\/blog\/2017\/09\/29\/mise-en-place-dune-replication-mysql\/","title":{"rendered":"Mise en place d&rsquo;une r\u00e9plication mysql"},"content":{"rendered":"<p>Dans le cadre de site \u00e0 forts traffics, de la pr\u00e9paration d&rsquo;une migration ou d&rsquo;une politique de sauvegarde, il est possible de mettre en place une r\u00e9plication mysql.<\/p>\n<p>Nous allons voir ensemble comment mettre en place une r\u00e9plication de type <strong>master \/ slave<\/strong> sur des distribution de type debian \/ ubuntu.<\/p>\n<p>Il est possible de jouer la r\u00e9plication sur autant de serveur slaves que souhait\u00e9s.<\/p>\n<p><span style=\"text-decoration: underline;\">Cette proc\u00e9dure n\u00e9cessitera des red\u00e9marages de mysql<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Pr\u00e9paration du serveur master.<\/strong><\/span><\/p>\n<p>Pour commencer il est n\u00e9cessaire de cr\u00e9er un utilisateur mysql :<\/p>\n<p>Pour plus de s\u00e9curit\u00e9 nous restreindrons l&rsquo;acc\u00e8s \u00e0 une ip particuli\u00e8re via la commande suivante a \u00e9x\u00e9cuter avec un utilisateur ayant tous les droits.<\/p>\n<pre lang=\"sql\">CREATE USER 'my-replication-user'@'hostname-de-votre-serveur' IDENTIFIED BY 'some_pass';\r\n<\/pre>\n<p>Il faut ensuite ajouter \u00e0 cet utilisateur les droits de r\u00e9plication :<\/p>\n<pre lang=\"sql\">GRANT REPLICATION SLAVE ON *.* TO 'my-replication-user'@'hostname-de-votre-serveur';\r\n<\/pre>\n<p>Il faudra ensuite activer la conservation des fichiers de logs sur le serveur.<br \/>\nPour cela il faut rajouter la configuration suivante dans le fichier <em>\/etc\/mysql\/my.cnf<\/em><\/p>\n<pre lang=\"bash\">[mysqld]\r\nlog-bin=mysql-bin\r\nserver-id=1\r\nexpire_logs_days=2\r\n<\/pre>\n<p>Nous conserverons les logs binaires uniquement 2 jours<\/p>\n<p>Il faut \u00e9galement autoriser les connexion \u00e0 mysql depuis l&rsquo;ext\u00e9rieur si ce n&rsquo;est pas encore le cas<br \/>\nVous pouvez le visualiser via la commande suivante :<\/p>\n<pre lang=\"bash\">netstat -lpn | grep mysql<\/pre>\n<p>Il faut bien s&rsquo;assurer que le serveur \u00e9coute sur 0.0.0.0<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1606\" src=\"https:\/\/www.h-hennes.fr\/blog\/wp-content\/uploads\/2017\/09\/netstat.jpg\" alt=\"NetStat mysql\" width=\"777\" height=\"68\" srcset=\"https:\/\/www.h-hennes.fr\/blog\/wp-content\/uploads\/2017\/09\/netstat.jpg 777w, https:\/\/www.h-hennes.fr\/blog\/wp-content\/uploads\/2017\/09\/netstat-300x26.jpg 300w, https:\/\/www.h-hennes.fr\/blog\/wp-content\/uploads\/2017\/09\/netstat-768x67.jpg 768w\" sizes=\"auto, (max-width: 777px) 100vw, 777px\" \/><\/p>\n<p>Si ce n&rsquo;est pas le cas, il faut changer la valeur du param\u00e8tre bind-address dans le fichier <em>\/etc\/mysql\/my.cnf<\/em><\/p>\n<pre lang=\"bash\">bind-address = 0.0.0.0 \r\n<\/pre>\n<p>Red\u00e9marer ensuite mysql pour prendre en compte le changement de configuration<\/p>\n<pre lang=\"bash\">sudo service mysql restart<\/pre>\n<p>Il faut ensuite faire un dump de la base de donn\u00e9es \u00e0 r\u00e9pliquer via la commande suivante :<\/p>\n<pre lang=\"bash\">mysqldump -u root -p dbname --single-transaction --master-data=1 | gzip &gt; dump-replication.sql.gz\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Pr\u00e9paration du serveur slave :<\/strong><\/span><\/p>\n<p>Dans le fichier \/etc\/mysql\/my.cnf rajouter un serveur id diff\u00e9rente de celui de prod<\/p>\n<pre lang=\"bash\">server-id=2\r\n<\/pre>\n<p>Il faut ensuite cr\u00e9er votre base de donn\u00e9es sur le serveur slave.<br \/>\nEt importer le dump pr\u00e9c\u00e9dent.<\/p>\n<pre lang=\"bash\">zcat dump-replication.sql.gz | mysql -u root -p dbname\r\n<\/pre>\n<p>Connectez vous ensuite \u00e0 mysql<br \/>\nEx\u00e9cuter ensuite la commande suivante pour configurer les information de la r\u00e9plication<\/p>\n<pre lang=\"bash\">CHANGE MASTER TO\u00a0MASTER_HOST='master-host-name-or-ip', MASTER_USER='replication-user', MASTER_PASSWORD='mypassword';\r\n<\/pre>\n<p>Puis de d\u00e9marrer la r\u00e9plication via la commande :<\/p>\n<pre lang=\"mysql\">Start slave;\r\n<\/pre>\n<p>Vous pouvez ensuite voir si la r\u00e9plication \u00e0 d\u00e9marr\u00e9 via la commande mysql suivante :<\/p>\n<pre lang=\"mysql\">SHOW PROCESSLIST;\r\n<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1607\" src=\"https:\/\/www.h-hennes.fr\/blog\/wp-content\/uploads\/2017\/09\/slave-status-1.jpg\" alt=\"mysql show slave status\" width=\"780\" height=\"108\" srcset=\"https:\/\/www.h-hennes.fr\/blog\/wp-content\/uploads\/2017\/09\/slave-status-1.jpg 780w, https:\/\/www.h-hennes.fr\/blog\/wp-content\/uploads\/2017\/09\/slave-status-1-300x42.jpg 300w, https:\/\/www.h-hennes.fr\/blog\/wp-content\/uploads\/2017\/09\/slave-status-1-768x106.jpg 768w\" sizes=\"auto, (max-width: 780px) 100vw, 780px\" \/><\/p>\n<p>Vous pouvez voir le statut de la synchronisation via la commande<\/p>\n<pre lang=\"mysql\">SHOW SLAVE STATUS;\r\n<\/pre>\n<p>Le statut de la colonne <em>Slave_IO_State<\/em> doit \u00eatre \u00e0 \u00ab\u00a0Waiting for master to send event\u00a0\u00bb<br \/>\nVous pouvez \u00e9galement voir la colonne \u00ab\u00a0<em>Seconds_behind_master<\/em>\u00a0\u00bb qui devra \u00eatre \u00e0 0 une fois que tout sera synchronis\u00e9.<\/p>\n<p>Et voila la r\u00e9plication est en place \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dans le cadre de site \u00e0 forts traffics, de la pr\u00e9paration d&rsquo;une migration ou d&rsquo;une politique de sauvegarde, il est possible de mettre en place une r\u00e9plication mysql. Nous allons voir ensemble comment mettre en place une r\u00e9plication de type master \/ slave sur des distribution de type debian \/ ubuntu. Il est possible de [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[254],"tags":[500,75,499,501],"class_list":["post-1602","post","type-post","status-publish","format-standard","hentry","category-serveurs-dedies","tag-master","tag-mysql","tag-replication","tag-slave"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/posts\/1602","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/comments?post=1602"}],"version-history":[{"count":6,"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/posts\/1602\/revisions"}],"predecessor-version":[{"id":1611,"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/posts\/1602\/revisions\/1611"}],"wp:attachment":[{"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/media?parent=1602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/categories?post=1602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h-hennes.fr\/blog\/wp-json\/wp\/v2\/tags?post=1602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}