{"id":12,"date":"2012-07-04T10:39:28","date_gmt":"2012-07-04T09:39:28","guid":{"rendered":"http:\/\/www.bocciolesi.fr\/tutoriels-et-cours\/?p=12"},"modified":"2017-02-24T14:24:54","modified_gmt":"2017-02-24T13:24:54","slug":"installer-plusieurs-instances-de-mysql","status":"publish","type":"post","link":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/","title":{"rendered":"MySql en mode Multi Instances sur un m\u00eame serveur"},"content":{"rendered":"
\"image_pdf\"Enregistrer en PDF<\/span><\/a><\/a><\/div>

I\u25ba\u00a0Installer\u00a0plusieurs\u00a0instances de MySQL sur le m\u00eame serveur peut \u00eatre int\u00e9ressante pour optimiser l’acc\u00e8s au serveur et g\u00e9rer les bases de donn\u00e9es de plusieurs clients en h\u00e9bergement mutualis\u00e9 :<\/strong><\/span><\/p>\n

Cela peut \u00eatre aussi int\u00e9ressant de pouvoir\u00a0ajuster finement des\u00a0param\u00e8tres tels que\u00a0max_connections<\/code>\u00a0<\/span><\/strong>pour chacune des instances de MySQL en fonction des applications et\/ou des clients.<\/p>\n

On peut \u00e9galement faire cohabiter plusieurs serveurs avec plusieurs versions diff\u00e9rentes.<\/p>\n

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

?\u00a0M\u00e9thode sous LINUX avec MYSQLD_MULTI (script PERL)<\/span><\/strong><\/p>\n

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

    \n
  1. Le serveur MySql\u00a0doit\u00a0\u00eatre correctement install\u00e9 et bien configur\u00e9<\/span><\/span><\/li>\n
  2. Stopper le service : \/etc\/init.d\/mysql stop<\/strong><\/li>\n
  3. Il va falloir cr\u00e9er les\u00a0r\u00e9pertoires\u00a0qui vont stocker toutes les\u00a0informations\u00a0propres \u00e0 chaque instance de\u00a0serveur( ces infos par d\u00e9faut sont stock\u00e9s sous \/var\/lib\/mysql )
    \nmkdir \/var\/lib\/mysql1 \/var\/lib\/mysql2 \/var\/lib\/mysql3<\/strong>
    \n<\/span>
    \nMise \u00e0 jour des propri\u00e9taires et des\u00a0<\/span>droits
    \nchown -R mysql:mysql \/var\/lib\/mysql1 \/var\/lib\/mysql2 \/var\/lib\/mysql3<\/span><\/strong>
    \nchmod 700\u00a0\u00a0\/var\/lib\/mysql1 \/var\/lib\/mysql2 \/var\/lib\/mysql3\u00a0<\/strong><\/span><\/span>Copie des fichiers (R=r\u00e9cursive et p=parent)<\/span>
    \ncp -Rp \/var\/lib\/mysql\/mysql\/ var\/lib\/mysql1\/<\/span><\/strong>
    \n cp -Rp \/var\/lib\/mysql\/mysql\/ var\/lib\/mysql2\/<\/span><\/strong>
    \ncp -Rp \/var\/lib\/mysql\/mysql\/ var\/lib\/mysql3\/
    \n<\/strong>
    \n<\/span><\/li>\n
  4. \u00a0Nous allons maintenant g\u00e9n\u00e9rer le fichier de conf de mysql_multi : ( avec l’utilitaire mysql_multi )<\/li>\n<\/ol>\n

    mysqld_multi –example > \/etc\/mysql\/multi_my.cnf<\/span>
    \n<\/strong><\/strong>#———————————————————————————————–<\/span><\/p>\n

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

    [mysqld1]<\/span><\/strong>
    socket = \/tmp\/mysql.sock1<\/span>
    #chemin du socket (diff\u00e9rent pour chaque serveur)<\/span>
    port = 3309<\/span>
    pid-file = \/var\/lib\/mysql1\/hostname.pid1<\/span>
    datadir = \/var\/lib\/mysql1<\/span>
    #r\u00e9pertoire de stockage pour chaque serveur<\/span>
    language = \/usr\/share\/mysql\/english<\/span>
    user = tux<\/span>
    #propri\u00e9taire du processus<\/span><\/p>\n

    [mysqld2]<\/span><\/strong>
    socket = \/tmp\/mysql.sock2<\/span>
    port = 3307<\/span>
    pid-file = \/var\/lib\/mysql2\/hostname.pid2<\/span>
    datadir = \/var\/lib\/mysql2<\/span>
    language = \/usr\/share\/mysql\/english<\/span>
    user = tux<\/span><\/p>\n

    [mysqld3]<\/span><\/strong>
    \nsocket = \/tmp\/mysql.sock6
    \nport = 3311
    \npid-file = \/var\/lib\/mysql3\/hostname.pid3
    \ndatadir = \/var\/lib\/mysql3
    \nlanguage = \/usr\/share\/mysql\/french
    \nuser = tux
    \n#———————————————————————————–<\/p>\n

      \n
    1. Nous allons maintenant d\u00e9marrer ou arr\u00eater les instances de MySql
      \nmysqld_multi –defaults-file=\/etc\/mysql\/multi_my.cnf report<\/span>
      \n<\/strong>Reporting MySQL servers
      \nMySQL server from group: mysqld1 is not running
      \nMySQL server from group: mysqld2 is not running
      \nMySQL server from group: mysqld3 is not running<\/li>\n
    2. mysqld_multi –defaults-file=\/etc\/mysql\/multi_my.cnf start 1,2,3<\/span>
      \nmysqld_multi –defaults-file=\/etc\/mysql\/multi_my.cnf start 1-3<\/span>
      \nmysqld_multi –defaults-file=\/etc\/mysql\/multi_my.cnf stop 1,3<\/span><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/li>\n
    3. Se connecter \u00e0 ces instances de MySql :Pour se connecter \u00e0 MySql par d\u00e9faut, nous utilisons la commande :
      \nmysql -uLogin -p (ex mysql -uroot -p)Pour se\u00a0connecter\u00a0aux diff\u00e9rentes instances du Server MySql, nous devons sp\u00e9cifier le socket<\/span> <\/strong>pr\u00e9cis :ex:mysql -uroot –socket=\/tmp\/mysql.sock1 -p<\/span><\/strong><\/strong>Ce socket est bien s\u00fbr d\u00e9fini dans \/etc\/mysql\/multi_my.cnf<\/li>\n<\/ol>\n

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

      ?\u00a0M\u00e9thode sous WINDOWS<\/strong><\/span><\/p>\n

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

      On cr\u00e9e deux r\u00e9pertoires data pouvant contenir les databases respectivement sous \/MYSQL\/MY1 et\u00a0\u00a0\/MYSQL\/MY2.<\/p>\n

      ces r\u00e9pertoires contiennent \u00e9galement un fichier my.ini qui sera propre au d\u00e9marrage de l\u2019occurrence du serveur concern\u00e9. le port doit obligatoirement \u00eatre diff\u00e9rent …<\/p>\n

      On peut ensuite se connecter aux multiples instances du serveur MySQL en utilisant le client de son choix.<\/p>\n

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

       <\/p>\n

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

      I\u25ba\u00a0Installer\u00a0plusieurs\u00a0instances de MySQL sur le m\u00eame serveur peut \u00eatre int\u00e9ressante pour optimiser l’acc\u00e8s au serveur et g\u00e9rer les bases de donn\u00e9es de plusieurs clients en h\u00e9bergement mutualis\u00e9 : …<\/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":[14,15],"class_list":["post-12","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mariadb","category-mysql","category-tutoriels","tag-mysql-multi-instances","tag-mysqld_multi"],"yoast_head":"\nMySql en mode Multi Instances sur un 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\/installer-plusieurs-instances-de-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySql en mode Multi Instances sur un m\u00eame serveur - Tutoriels et Cours\" \/>\n<meta property=\"og:description\" content=\"I\u25ba\u00a0Installer\u00a0plusieurs\u00a0instances de MySQL sur le m\u00eame serveur peut \u00eatre int\u00e9ressante pour optimiser l’acc\u00e8s au serveur et g\u00e9rer les bases de donn\u00e9es de plusieurs clients en h\u00e9bergement mutualis\u00e9 : ...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Tutoriels et Cours\" \/>\n<meta property=\"article:published_time\" content=\"2012-07-04T09:39:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-02-24T13:24:54+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\/installer-plusieurs-instances-de-mysql\/\",\"url\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/\",\"name\":\"MySql en mode Multi Instances sur un m\u00eame serveur - Tutoriels et Cours\",\"isPartOf\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg\",\"datePublished\":\"2012-07-04T09:39:28+00:00\",\"dateModified\":\"2017-02-24T13:24:54+00:00\",\"author\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#\/schema\/person\/6bdfe0a1263b803645c69e3d5748dabe\"},\"breadcrumb\":{\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-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\/installer-plusieurs-instances-de-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySql en mode Multi Instances sur un 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":"MySql en mode Multi Instances sur un 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\/installer-plusieurs-instances-de-mysql\/","og_locale":"fr_FR","og_type":"article","og_title":"MySql en mode Multi Instances sur un m\u00eame serveur - Tutoriels et Cours","og_description":"I\u25ba\u00a0Installer\u00a0plusieurs\u00a0instances de MySQL sur le m\u00eame serveur peut \u00eatre int\u00e9ressante pour optimiser l’acc\u00e8s au serveur et g\u00e9rer les bases de donn\u00e9es de plusieurs clients en h\u00e9bergement mutualis\u00e9 : ...","og_url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/","og_site_name":"Tutoriels et Cours","article_published_time":"2012-07-04T09:39:28+00:00","article_modified_time":"2017-02-24T13:24:54+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\/installer-plusieurs-instances-de-mysql\/","url":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/","name":"MySql en mode Multi Instances sur un m\u00eame serveur - Tutoriels et Cours","isPartOf":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/#primaryimage"},"image":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/#primaryimage"},"thumbnailUrl":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-content\/uploads\/2013\/08\/bases-de-donnees.jpg","datePublished":"2012-07-04T09:39:28+00:00","dateModified":"2017-02-24T13:24:54+00:00","author":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/#\/schema\/person\/6bdfe0a1263b803645c69e3d5748dabe"},"breadcrumb":{"@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-mysql\/"]}]},{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/installer-plusieurs-instances-de-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\/installer-plusieurs-instances-de-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/"},{"@type":"ListItem","position":2,"name":"MySql en mode Multi Instances sur un 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\/12","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=12"}],"version-history":[{"count":41,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/posts\/12\/revisions"}],"predecessor-version":[{"id":1635,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/posts\/12\/revisions\/1635"}],"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=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/categories?post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bocciolesi.fr\/tutoriels-et-cours\/wp-json\/wp\/v2\/tags?post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

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