{"id":2441,"date":"2018-01-23T15:34:58","date_gmt":"2018-01-23T14:34:58","guid":{"rendered":"https:\/\/pcll.ac-dijon.fr\/eole\/?p=2441"},"modified":"2018-01-23T15:34:58","modified_gmt":"2018-01-23T14:34:58","slug":"reduire-taille-fichier-ibdata1-de-mysql-eole-2-5","status":"publish","type":"post","link":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/","title":{"rendered":"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5"},"content":{"rendered":"<p>InnoDB est un moteur de stockage pour les syst\u00e8mes de gestion de base de donn\u00e9es MySQL et MariaDB.<br \/>\nSur certaines versions du serveur MySQL toutes les bases de donn\u00e9es sont stock\u00e9es dans un seul et m\u00eame fichier <strong>\/var\/lib\/mysql\/ibdata1<\/strong>. La taille de ce fichier augmente inexorablement et celui-ci ne peut \u00eatre purg\u00e9. Il est pourtant possible de r\u00e9partir le stockage dans plusieurs fichiers.<\/p>\n<p>La solution suivante permet de param\u00e9trer le serveur MySQL afin qu&rsquo;il cr\u00e9e un fichier <strong>.ibd<\/strong> par table pour chacune des bases de donn\u00e9es de <strong>\/var\/lib\/mysql\/<\/strong>. Chacun de ces fichiers pourra faire l&rsquo;objet d&rsquo;un nettoyage \u00e0 chaud.<\/p>\n<h2>Mise en \u0153uvre<\/h2>\n<p>La proc\u00e9dure s&rsquo;applique uniquement aux modules EOLE 2.5. Sur les version 2.6 d&rsquo;EOLE un fichier <strong>.ibd<\/strong> est cr\u00e9\u00e9 par d\u00e9faut pour chaque table.<\/p>\n<p>Mise en \u0153uvre \u00e0 l&rsquo;aide d&rsquo;un script bash.<\/p>\n<p>Pr\u00e9-requis :<\/p>\n<ul>\n<li>les valeurs <strong>innodb_log_file_size<\/strong> et <strong>innodb_buffer_pool_size<\/strong> doivent \u00eatre adapt\u00e9es au serveur ;<\/li>\n<li><strong>innodb_log_file_size<\/strong> doit \u00eatre \u00e9gal \u00e0 <strong>25%<\/strong> de la valeur du param\u00e8tre <strong>innodb_buffer_pool_size<\/strong>.<\/li>\n<\/ul>\n<pre>#!\/bin\/bash\r\n\r\ninnodb_log_file_size=500M\r\ninnodb_buffer_pool_size=2G\r\n\r\nMYSQLPWD=$(pwgen -1)\r\nmysql_pwd.py $MYSQLPWD\r\n\r\nDATABASES=`mysql -u root -p$MYSQLPWD -e 'use mysql; select Db from db where Db NOT IN (\"performance_schema\", \"mysql\",\"information_schema\");' |awk '!\/Db\/ {print $NF}'|sort|uniq`\r\nmysqldump -hlocalhost -uroot -p$MYSQLPWD --databases $DATABASES &gt; dump.sql\r\nfor database in $DATABASES;do\r\n      mysql -u root -p$MYSQLPWD -e \"drop database $database;\"\r\ndone\r\n\r\nservice mysql stop\r\n\r\ncat &gt; \/etc\/mysql\/conf.d\/mysqld_innodb.cnf &lt;&lt; EOF\r\n[mysqld]\r\ninnodb_file_per_table\r\ninnodb_flush_method=O_DIRECT\r\ninnodb_log_file_size=${innodb_log_file_size}\r\ninnodb_buffer_pool_size=${innodb_buffer_pool_size}\r\nEOF\r\n\r\nrm -f \/var\/lib\/mysql\/{ibdata1,ib_logfile0,ib_logfile1}\r\n\r\nservice mysql start\r\n\r\nmysql -uroot -p $MYSQLPWD &lt; dump.sql\r\n<\/pre>\n<p>Le r\u00e9pertoire <strong>\/var\/lib\/mysql\/[BDD]<\/strong> doit contenir un fichier <strong>.ibd<\/strong> et un fichier <strong>.frm<\/strong> par table.<\/p>\n<p>Le script est disponible dans l&rsquo;espace de contribution EOLE 2.5 : <a href=\"http:\/\/eole.ac-dijon.fr\/documentations\/2.5\/contributions\/ibdata.sh\">http:\/\/eole.ac-dijon.fr\/documentations\/2.5\/contributions\/ibdata.sh<\/a><\/p>\n<h2>Nettoyage des tables<\/h2>\n<p>L&rsquo;ex\u00e9cution de la commande <strong>OPTIMIZE TABLE mabdd.&lt;nomDeLaTable&gt;<\/strong> permet de r\u00e9duire la taille du fichier <strong>.ibd<\/strong> correspondant.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>InnoDB est un moteur de stockage pour les syst\u00e8mes de gestion de base de donn\u00e9es MySQL et MariaDB. Sur certaines versions du serveur MySQL toutes les bases de donn\u00e9es sont stock\u00e9es dans un seul et m\u00eame fichier \/var\/lib\/mysql\/ibdata1. La taille de ce fichier augmente inexorablement et celui-ci ne peut \u00eatre purg\u00e9. Il est pourtant possible [&hellip;]<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[10,79,78],"class_list":["post-2441","post","type-post","status-publish","format-standard","hentry","category-technique","tag-eole-2-5","tag-mysql","tag-script"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5 - EOLE<\/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:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5 - EOLE\" \/>\n<meta property=\"og:description\" content=\"InnoDB est un moteur de stockage pour les syst\u00e8mes de gestion de base de donn\u00e9es MySQL et MariaDB. Sur certaines versions du serveur MySQL toutes les bases de donn\u00e9es sont stock\u00e9es dans un seul et m\u00eame fichier \/var\/lib\/mysql\/ibdata1. La taille de ce fichier augmente inexorablement et celui-ci ne peut \u00eatre purg\u00e9. Il est pourtant possible [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/\" \/>\n<meta property=\"og:site_name\" content=\"EOLE\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-23T14:34:58+00:00\" \/>\n<meta name=\"author\" content=\"laurent\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@PoleEole\" \/>\n<meta name=\"twitter:site\" content=\"@PoleEole\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"laurent\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/\"},\"author\":{\"name\":\"laurent\",\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/#\/schema\/person\/58102531540daa7a02ab663657494be0\"},\"headline\":\"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5\",\"datePublished\":\"2018-01-23T14:34:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/\"},\"wordCount\":250,\"keywords\":[\"EOLE 2.5\",\"MySQL\",\"script\"],\"articleSection\":[\"Technique\"],\"inLanguage\":\"fr-FR\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/\",\"url\":\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/\",\"name\":\"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5 - EOLE\",\"isPartOf\":{\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/#website\"},\"datePublished\":\"2018-01-23T14:34:58+00:00\",\"author\":{\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/#\/schema\/person\/58102531540daa7a02ab663657494be0\"},\"breadcrumb\":{\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/pcll.ac-dijon.fr\/eole\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/#website\",\"url\":\"https:\/\/pcll.ac-dijon.fr\/eole\/\",\"name\":\"EOLE\",\"description\":\"Ensemble Ouvert Libre Evolutif\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/pcll.ac-dijon.fr\/eole\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/#\/schema\/person\/58102531540daa7a02ab663657494be0\",\"name\":\"laurent\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/pcll.ac-dijon.fr\/eole\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e5ec36c5b6b3976247ec36be3f512a59d7ac277d1b452c769d0729210cc8db9b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e5ec36c5b6b3976247ec36be3f512a59d7ac277d1b452c769d0729210cc8db9b?s=96&d=mm&r=g\",\"caption\":\"laurent\"},\"url\":\"https:\/\/pcll.ac-dijon.fr\/eole\/author\/laurent\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5 - EOLE","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:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/","og_locale":"fr_FR","og_type":"article","og_title":"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5 - EOLE","og_description":"InnoDB est un moteur de stockage pour les syst\u00e8mes de gestion de base de donn\u00e9es MySQL et MariaDB. Sur certaines versions du serveur MySQL toutes les bases de donn\u00e9es sont stock\u00e9es dans un seul et m\u00eame fichier \/var\/lib\/mysql\/ibdata1. La taille de ce fichier augmente inexorablement et celui-ci ne peut \u00eatre purg\u00e9. Il est pourtant possible [&hellip;]","og_url":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/","og_site_name":"EOLE","article_published_time":"2018-01-23T14:34:58+00:00","author":"laurent","twitter_card":"summary_large_image","twitter_creator":"@PoleEole","twitter_site":"@PoleEole","twitter_misc":{"\u00c9crit par":"laurent","Dur\u00e9e de lecture estim\u00e9e":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/#article","isPartOf":{"@id":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/"},"author":{"name":"laurent","@id":"https:\/\/pcll.ac-dijon.fr\/eole\/#\/schema\/person\/58102531540daa7a02ab663657494be0"},"headline":"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5","datePublished":"2018-01-23T14:34:58+00:00","mainEntityOfPage":{"@id":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/"},"wordCount":250,"keywords":["EOLE 2.5","MySQL","script"],"articleSection":["Technique"],"inLanguage":"fr-FR"},{"@type":"WebPage","@id":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/","url":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/","name":"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5 - EOLE","isPartOf":{"@id":"https:\/\/pcll.ac-dijon.fr\/eole\/#website"},"datePublished":"2018-01-23T14:34:58+00:00","author":{"@id":"https:\/\/pcll.ac-dijon.fr\/eole\/#\/schema\/person\/58102531540daa7a02ab663657494be0"},"breadcrumb":{"@id":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/pcll.ac-dijon.fr\/eole\/reduire-taille-fichier-ibdata1-de-mysql-eole-2-5\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/pcll.ac-dijon.fr\/eole\/"},{"@type":"ListItem","position":2,"name":"R\u00e9duire la taille du fichier ibdata1 de MySQL sur EOLE 2.5"}]},{"@type":"WebSite","@id":"https:\/\/pcll.ac-dijon.fr\/eole\/#website","url":"https:\/\/pcll.ac-dijon.fr\/eole\/","name":"EOLE","description":"Ensemble Ouvert Libre Evolutif","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/pcll.ac-dijon.fr\/eole\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"fr-FR"},{"@type":"Person","@id":"https:\/\/pcll.ac-dijon.fr\/eole\/#\/schema\/person\/58102531540daa7a02ab663657494be0","name":"laurent","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/pcll.ac-dijon.fr\/eole\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e5ec36c5b6b3976247ec36be3f512a59d7ac277d1b452c769d0729210cc8db9b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e5ec36c5b6b3976247ec36be3f512a59d7ac277d1b452c769d0729210cc8db9b?s=96&d=mm&r=g","caption":"laurent"},"url":"https:\/\/pcll.ac-dijon.fr\/eole\/author\/laurent\/"}]}},"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/posts\/2441","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/comments?post=2441"}],"version-history":[{"count":20,"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/posts\/2441\/revisions"}],"predecessor-version":[{"id":2472,"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/posts\/2441\/revisions\/2472"}],"wp:attachment":[{"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/media?parent=2441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/categories?post=2441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pcll.ac-dijon.fr\/eole\/wp-json\/wp\/v2\/tags?post=2441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}