{"id":56,"date":"2012-09-12T16:46:46","date_gmt":"2012-09-12T15:46:46","guid":{"rendered":"http:\/\/www.bocciolesi.fr\/tutoriels-et-cours\/?p=56"},"modified":"2017-02-24T14:19:35","modified_gmt":"2017-02-24T13:19:35","slug":"replication-mysql","status":"publish","type":"post","link":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/","title":{"rendered":"R\u00e9plication MySql – Introduction"},"content":{"rendered":"
\"image_pdf\"Enregistrer en PDF<\/span><\/a><\/a><\/div>

I\u25baNous allons voir dans cet article diff\u00e9rents cas de r\u00e9plication MySql .<\/strong><\/span><\/p>\n

I – La r\u00e9plication classique UniDirectionnelle:<\/span><\/h2>\n

Les bases de donn\u00e9es du serveur Ma\u00eetre (N\u00b01) sont recopi\u00e9es en temps r\u00e9el vers le serveur Esclave (N\u00b02)<\/p>\n

\"Ordi\"<\/a><\/p>\n

Comment se g\u00e8re un tel mod\u00e8le :<\/h2>\n

Cas N\u00b01 :<\/strong>
\nLes requ\u00eates d’\u00e9criture se font sur le serveur N\u00b01
\nLes requ\u00eates de lecture se font sur le serveur N\u00b02
\nCe qui permet de r\u00e9partir les charges\u00a0\u00a0en lecture \u00a0et en \u00e9criture<\/strong><\/p>\n

Cas N\u00b02 :<\/strong>
\nLe serveur N\u00b01 est consid\u00e9r\u00e9 comme le serveur en production
\nLe serveur N\u00b02 est consid\u00e9r\u00e9 comme un Backup Server<\/strong><\/p>\n

Cas N\u00b03 :<\/strong>
\nMix des 2…<\/p>\n

————————————————————————————————————–<\/p>\n

Si il y a des conflits d’insert avec des cl\u00e9s identiques (1062<\/strong>. \u00ab\u00a0DUPLICATE ENTRY FOR…)\u00a0, la r\u00e9plication va s’arr\u00eater net et l’on risque de perdre beaucoup d’informations de sauvagrde.
\nSi le serveur ma\u00eetre s’arr\u00eate pour des raisons x (dump ou lock …1053<\/strong>. \u00ab\u00a0Query partially completed on the master …), la r\u00e9plication s’arr\u00eate \u00e9galement sur le serveur esclave et ne reprends pas.<\/p>\n

Il vaut mieux les ignorer automatiquement avec la directive :
\n<\/span>slave-skip-errors=1062,1053<\/span><\/strong><\/p>\n

Ou mieux g\u00e9rer l’incr\u00e9mentation des cl\u00e9s(ID) diff\u00e9remment sur chaque serveur<\/span><\/strong><\/p>\n

Sur le serveur 1 :<\/pre>\n
auto_increment_increment      = 10\r\nauto_increment_offset         = 1<\/pre>\n
Sur le serveur 2 :<\/span><\/span><\/pre>\n
auto_increment_increment      = 10\r\nauto_increment_offset         = 2<\/pre>\n
\u00a0<\/span><\/p>\n
II – La r\u00e9plication Crois\u00e9e:<\/h2>\n
 <\/p>\n
\"Ordi2\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Dans ce cas de figure, les deux serveurs sont \u00e0 la fois Ma\u00eetre et Esclave et la synchronisation se fait dans les deux sens;<\/p>\n
Optimisation de la charge et S\u00e9curisation des donn\u00e9es !<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
I\u25baNous allons voir dans cet article diff\u00e9rents cas de r\u00e9plication MySql . I – La r\u00e9plication classique UniDirectionnelle: Les bases de donn\u00e9es du serveur Ma\u00eetre (N\u00b01) sont recopi\u00e9es …<\/p>\n","protected":false},"author":1,"featured_media":1547,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37,5,39],"tags":[18],"class_list":["post-56","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mariadb","category-mysql","category-tutoriels","tag-replication-mysql"],"yoast_head":"\nR\u00e9plication MySql - Introduction - Tutoriels et Cours<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"R\u00e9plication MySql - Introduction - Tutoriels et Cours\" \/>\n<meta property=\"og:description\" content=\"I\u25baNous allons voir dans cet article diff\u00e9rents cas de r\u00e9plication MySql . I – La r\u00e9plication classique UniDirectionnelle: Les bases de donn\u00e9es du serveur Ma\u00eetre (N\u00b01) sont recopi\u00e9es ...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Tutoriels et Cours\" \/>\n<meta property=\"article:published_time\" content=\"2012-09-12T15:46:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-02-24T13:19:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"424\" \/>\n\t<meta property=\"og:image:height\" content=\"283\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Michel BOCCIOLESI\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Michel BOCCIOLESI\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/\",\"url\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/\",\"name\":\"R\u00e9plication MySql - Introduction - Tutoriels et Cours\",\"isPartOf\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg\",\"datePublished\":\"2012-09-12T15:46:46+00:00\",\"dateModified\":\"2017-02-24T13:19:35+00:00\",\"author\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#\/schema\/person\/6bdfe0a1263b803645c69e3d5748dabe\"},\"breadcrumb\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#primaryimage\",\"url\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg\",\"contentUrl\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg\",\"width\":424,\"height\":283,\"caption\":\"Cours et tutoriels MySQL et Maria DB\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"R\u00e9plication MySql – Introduction\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#website\",\"url\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/\",\"name\":\"Tutoriels et Cours\",\"description\":\"Michel Bocciolesi | Formateur Consultant en Informatique\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#\/schema\/person\/6bdfe0a1263b803645c69e3d5748dabe\",\"name\":\"Michel BOCCIOLESI\",\"description\":\"Michel Bocciolesi est consultant et formateur sp\u00e9cialis\u00e9 en Informatique.\",\"sameAs\":[\"https:\/\/www.mb-creation-web.fr\"],\"url\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/author\/michel\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"R\u00e9plication MySql - Introduction - Tutoriels et Cours","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/","og_locale":"fr_FR","og_type":"article","og_title":"R\u00e9plication MySql - Introduction - Tutoriels et Cours","og_description":"I\u25baNous allons voir dans cet article diff\u00e9rents cas de r\u00e9plication MySql . I – La r\u00e9plication classique UniDirectionnelle: Les bases de donn\u00e9es du serveur Ma\u00eetre (N\u00b01) sont recopi\u00e9es ...","og_url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/","og_site_name":"Tutoriels et Cours","article_published_time":"2012-09-12T15:46:46+00:00","article_modified_time":"2017-02-24T13:19:35+00:00","og_image":[{"width":424,"height":283,"url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg","type":"image\/jpeg"}],"author":"Michel BOCCIOLESI","twitter_misc":{"\u00c9crit par":"Michel BOCCIOLESI","Dur\u00e9e de lecture estim\u00e9e":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/","url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/","name":"R\u00e9plication MySql - Introduction - Tutoriels et Cours","isPartOf":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#primaryimage"},"image":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#primaryimage"},"thumbnailUrl":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg","datePublished":"2012-09-12T15:46:46+00:00","dateModified":"2017-02-24T13:19:35+00:00","author":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#\/schema\/person\/6bdfe0a1263b803645c69e3d5748dabe"},"breadcrumb":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/"]}]},{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#primaryimage","url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg","contentUrl":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg","width":424,"height":283,"caption":"Cours et tutoriels MySQL et Maria DB"},{"@type":"BreadcrumbList","@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/replication-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/"},{"@type":"ListItem","position":2,"name":"R\u00e9plication MySql – Introduction"}]},{"@type":"WebSite","@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#website","url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/","name":"Tutoriels et Cours","description":"Michel Bocciolesi | Formateur Consultant en Informatique","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Person","@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#\/schema\/person\/6bdfe0a1263b803645c69e3d5748dabe","name":"Michel BOCCIOLESI","description":"Michel Bocciolesi est consultant et formateur sp\u00e9cialis\u00e9 en Informatique.","sameAs":["https:\/\/www.mb-creation-web.fr"],"url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/author\/michel\/"}]}},"_links":{"self":[{"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/posts\/56","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/comments?post=56"}],"version-history":[{"count":36,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/posts\/56\/revisions"}],"predecessor-version":[{"id":1632,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/posts\/56\/revisions\/1632"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/media\/1547"}],"wp:attachment":[{"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/media?parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/categories?post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/tags?post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}