{"id":34,"date":"2012-10-05T14:24:10","date_gmt":"2012-10-05T13:24:10","guid":{"rendered":"http:\/\/www.bocciolesi.fr\/tutoriels-et-cours\/?p=34"},"modified":"2017-02-24T14:17:52","modified_gmt":"2017-02-24T13:17:52","slug":"phpmyadmin-et-mysql_multi","status":"publish","type":"post","link":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/","title":{"rendered":"R\u00e9plication MySql avec PhpMyAdmin en multi_instances sur le m\u00eame serveur"},"content":{"rendered":"
\"image_pdf\"Enregistrer en PDF<\/span><\/a><\/a><\/div>

I\u25baNous allons voir dans cet article comment configurer deux instances de MySql sur le m\u00eame serveur et les lier en r\u00e9plication Master vers Esclave …<\/span><\/strong><\/p>\n

Pr\u00e9-requis : MySql et Apache doivent \u00eatre correctement install\u00e9s et bien\u00a0configur\u00e9s. Les paquets PHP5 et PHP-MySQL sont requis<\/p>\n

Installation de PhpMyAdmin :<\/strong><\/span><\/h1>\n
    \n
  1. R\u00e9cup\u00e9rer l’archive sur le site de www.phpmyadmin.net<\/a>\u00a0<\/span><\/li>\n
  2. D\u00e9s-archivez\u00a0l\u00e0 plusieurs fois (autant de fois qu’il y a d’instances de MySql install\u00e9es)
    \nexemple :
    \n[root@\/var\/www] ls
    \ntotal 16K
    \n-rw-r–r– 1 root root 276 4 f\u00e9vr. 13:31 index.html
    \ndrwxr-xr-x 8 tux linux 4,0K 4 f\u00e9vr. 12:50 phpmyadmin<\/strong>
    \ndrwxr-xr-x 8 tux linux 4,0K 4 f\u00e9vr. 13:55 phpmyadmin1<\/strong>
    \ndrwxr-xr-x 8 tux linux 4,0K 4 f\u00e9vr. 14:17 phpmyadmin2<\/strong><\/strong><\/strong><\/li>\n
  3. Modifier les fichiers de conf. de phpmyadmin config.inc.php en ajoutant ces infos :
    \n$cfg[‘PmaAbsoluteUri’] = ‘http:\/\/localhost\/phpmyadmin1\/’;
    \n$cfg[‘Servers’][$i][‘port’] = ‘3307’;
    \n$cfg[‘Servers’][$i][‘socket’] = ‘\/tmp\/mysql.sock1’;
    \n$cfg[‘Servers’][$i][‘connect_type’] = ‘socket’;
    \n$cfg[‘Servers’][$i][‘auth_type’] = ‘http’;Les Sockets, ports et dataDir correpondent aux infos pr\u00e9cis\u00e9es dans \/etc\/mysql\/multi_my.cnf.replicant<\/strong>Les tests se font\u00a0avec\u00a0le navigateur en localhost :http:\/\/localhost\/phpmyadmin1 ou\u00a0http:\/\/localhost\/phpmyadmin2 (apr\u00e8s avoir stopp\u00e9 le serveur mysql principal)<\/strong><\/li>\n<\/ol>\n

    Conf de \/etc\/mysql\/multi_my.cnf.replicant<\/strong><\/span><\/h1>\n

    [root@\/etc\/mysql] cat multi_my.cnf.replicant<\/em><\/p>\n

    [mysqld_multi]<\/strong>
    mysqld = \/usr\/bin\/mysqld_safe<\/span>
    mysqladmin = \/usr\/bin\/mysqladmin<\/span>
    user = root<\/span>
    password = debian<\/span><\/p>\n

    #————–Serveur Ma\u00eetre ———————<\/strong>
    [mysqld1]<\/strong>
    socket = \/tmp\/mysql.sock1<\/span>
    port = 3307<\/span>
    pid-file = \/var\/lib\/mysql1\/hostname.pid1<\/span>
    datadir = \/var\/lib\/mysql1<\/span>
    language = \/usr\/share\/mysql\/french<\/span>
    user = mysql<\/span><\/p>\n

    #————replication ma\u00eetre—————————–<\/strong><\/p>\n

    server-id=1
    \nlog-bin = \/var\/log\/mysql\/mysql_master_bin.log
    \nbinlog_do_db=centrale<\/p>\n

    # log-bin et binlog-do-db servent respectivement \u00e0 indiquer qu’un fichier journal binaire des requ\u00eates SQL
    \n# doit \u00eatre cr\u00e9\u00e9 sur le serveur ma\u00eetre (il sera utilis\u00e9 par l’esclave pour rejouer les requ\u00eates)
    \n# Et \u00e0 indiquer que ce fichier journal doit \u00eatre utilis\u00e9 par les bases de donn\u00e9es centrale etc …<\/p>\n

    auto_increment_increment = 10
    \nauto_increment_offset = 1
    \n#pour \u00e9viter les conflits de cl\u00e9 unique<\/p>\n

    #—————Logs——————————–<\/strong>
    \nlog_error = \/var\/log\/mysql\/master_error.log
    \nexpire_logs_days = 20
    \nmax_binlog_size = 200M<\/p>\n

    #————–Serveur Esclave———————-<\/strong>
    \n[mysqld2]
    \nsocket = \/tmp\/mysql.sock2
    \nport = 3308
    \npid-file = \/var\/lib\/mysql2\/hostname.pid2
    \ndatadir = \/var\/lib\/mysql2
    \nlanguage = \/usr\/share\/mysql\/french
    \nuser = mysql<\/p>\n

    #————–r\u00e9plication esclave——————<\/strong>
    \nserver-id=2
    \nmaster-host = 127.0.0.1
    \nmaster-port = 3307
    \nmaster-user = replicant
    \nmaster-password = replicator
    \nreport-host=localhost
    \nmaster-connect-retry=60
    \n#indique combien de tentatives successives de connexion pour la r\u00e9plication seront effectu\u00e9es
    \nreplicate-do-db=centrale
    \n#nom de la base \u00e0 r\u00e9pliquer
    \n#—————logs—————————–<\/strong>
    \nlog_error = \/var\/log\/mysql\/slave_error.log<\/p>\n

    #—————Fichiers relay——————-<\/strong>
    \nrelay-log = \/var\/log\/mysql\/slave.relay_log
    \n#relay-log-index = \/var\/log\/mysql\/slave.relay_log_index
    \nexpire_logs_days = 20<\/p>\n

    # Le serveur esclave, devra aller lire le fichier journal binaire du ma\u00eetre pour cr\u00e9\u00e9 pour ces bases.
    \n# Il les copiera automatiquement dans un fichier tampon, ou fichier \u00ab?relais?\u00bb (relay)
    \n# en terminologie MySQL, et il en jouera les requ\u00eates.<\/p>\n

    auto_increment_increment = 10
    \nauto_increment_offset = 2<\/p>\n

    D\u00e9marrage des instances mySql<\/span><\/strong><\/h1>\n

    mysqld_multi –defaults-file=\/etc\/mysql\/multi_my.cnf.replicant start 1,2<\/p>\n

    mysqld_multi –defaults-file=\/etc\/mysql\/multi_my.cnf.replicant report<\/p>\n

    Visualisation dans PhpMyAdmin<\/span><\/strong><\/h1>\n

    Si la config est bonne, on obtient ceci …<\/p>\n

    Sur l’esclave :<\/p>\n

    \"Capture2\"<\/a> \"Capture1\"<\/a><\/p>\n

    sur le ma\u00eetre.<\/p>\n

    Pour charger la 1\u00e8re fois du ma\u00eetre vers l’esclave et synchroniser le tout, utilisez\u00a0<\/span><\/p>\n

    Ensuite les MAJ se feront automatiquement dans le sens M–>E<\/p>\n

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

     <\/p>\n

     <\/p>\n

     <\/p>\n

     <\/p>\n

     <\/p>\n

    ————————————————————————————————————————<\/span><\/strong><\/p>\n

    M\u00e9thode avec WINDOWS<\/span><\/strong><\/p>\n

    ————————————————————————————————————————<\/span><\/strong><\/p>\n

    Voici la configuration de my.ini de la 1\u00e8re instance de MySQL en ma\u00eetre :<\/p>\n

    Les options master-host master-user master-password sont devenus obsol\u00e8tes avec les versions de mysql5.5, il faut cr\u00e9er le user replicant sue le serveur ma\u00eetre et \u00e0 la connection du ma\u00eetre\/esclave , un fichier master.info sera cr\u00e9\u00e9…<\/p>\n

     <\/p>\n

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

     <\/p>\n

    Voici la conf de l’esclave :<\/p>\n

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

     <\/p>\n

    NB : s’il y a des erreurs de I\/O, supprimmer le auto.cnf qui contient l’UUID des serveurs et empechent de synchroniser les donn\u00e9es.<\/p>\n

     <\/p>\n

    Michel BOCCIOLESI<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

    I\u25baNous allons voir dans cet article comment configurer deux instances de MySql sur le m\u00eame serveur et les lier en r\u00e9plication Master vers Esclave … Pr\u00e9-requis : MySql …<\/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":[16],"class_list":["post-34","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mariadb","category-mysql","category-tutoriels","tag-phpmyadmin-mysqld_multi"],"yoast_head":"\nR\u00e9plication MySql avec PhpMyAdmin en multi_instances sur le m\u00eame serveur - 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\/phpmyadmin-et-mysql_multi\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"R\u00e9plication MySql avec PhpMyAdmin en multi_instances sur le m\u00eame serveur - Tutoriels et Cours\" \/>\n<meta property=\"og:description\" content=\"I\u25baNous allons voir dans cet article comment configurer deux instances de MySql sur le m\u00eame serveur et les lier en r\u00e9plication Master vers Esclave … Pr\u00e9-requis : MySql ...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/\" \/>\n<meta property=\"og:site_name\" content=\"Tutoriels et Cours\" \/>\n<meta property=\"article:published_time\" content=\"2012-10-05T13:24:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-02-24T13:17:52+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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/\",\"url\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/\",\"name\":\"R\u00e9plication MySql avec PhpMyAdmin en multi_instances sur le m\u00eame serveur - Tutoriels et Cours\",\"isPartOf\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg\",\"datePublished\":\"2012-10-05T13:24:10+00:00\",\"dateModified\":\"2017-02-24T13:17:52+00:00\",\"author\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#\/schema\/person\/6bdfe0a1263b803645c69e3d5748dabe\"},\"breadcrumb\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/#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\/phpmyadmin-et-mysql_multi\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"R\u00e9plication MySql avec PhpMyAdmin en multi_instances sur le m\u00eame serveur\"}]},{\"@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 avec PhpMyAdmin en multi_instances sur le m\u00eame serveur - 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\/phpmyadmin-et-mysql_multi\/","og_locale":"fr_FR","og_type":"article","og_title":"R\u00e9plication MySql avec PhpMyAdmin en multi_instances sur le m\u00eame serveur - Tutoriels et Cours","og_description":"I\u25baNous allons voir dans cet article comment configurer deux instances de MySql sur le m\u00eame serveur et les lier en r\u00e9plication Master vers Esclave … Pr\u00e9-requis : MySql ...","og_url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/","og_site_name":"Tutoriels et Cours","article_published_time":"2012-10-05T13:24:10+00:00","article_modified_time":"2017-02-24T13:17:52+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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/","url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/","name":"R\u00e9plication MySql avec PhpMyAdmin en multi_instances sur le m\u00eame serveur - Tutoriels et Cours","isPartOf":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/#primaryimage"},"image":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/#primaryimage"},"thumbnailUrl":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg","datePublished":"2012-10-05T13:24:10+00:00","dateModified":"2017-02-24T13:17:52+00:00","author":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#\/schema\/person\/6bdfe0a1263b803645c69e3d5748dabe"},"breadcrumb":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/"]}]},{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/phpmyadmin-et-mysql_multi\/#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\/phpmyadmin-et-mysql_multi\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/"},{"@type":"ListItem","position":2,"name":"R\u00e9plication MySql avec PhpMyAdmin en multi_instances sur le m\u00eame serveur"}]},{"@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\/34","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=34"}],"version-history":[{"count":34,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/posts\/34\/revisions"}],"predecessor-version":[{"id":1630,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/posts\/34\/revisions\/1630"}],"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=34"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/categories?post=34"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/tags?post=34"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}