{"id":198,"date":"2013-08-07T03:47:46","date_gmt":"2013-08-07T03:47:46","guid":{"rendered":"https:\/\/digitalchild.info\/?p=198"},"modified":"2013-08-07T03:47:46","modified_gmt":"2013-08-07T03:47:46","slug":"reclaim-disk-space-from-innodb-with-mysql","status":"publish","type":"post","link":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/","title":{"rendered":"Reclaim Disk Space from InnoDB with MySQL"},"content":{"rendered":"

After writing the article yesterday about removing a slave from a MySQL replication setup<\/a>. I was digging around the mysql data directory and noticed that the innodb<\/em> data file hadn’t reduced in size after I dropped the database. There is a reason for this. MySQL will not garbage collect the old data as it’s all one container. You need to change how MySQL handles the storage of database data files and to do that you have to hit the big reset button on your MySQL server.<\/p>\n

This is the official way to reclaim space from deleted databases using innodb tables. You can\u00a0read the full explanation here<\/a>\u00a0but I’ll give you a quick run down.<\/p>\n

    \n
  1. Backup all your existing databases with mysqldump.<\/li>\n
  2. Drop all databases except the mysql database<\/li>\n
  3. Stop the MySQL server<\/li>\n
  4. Change the config file to include the following in the [mysql] section of the config file :\u00a0innodb_file_per_table<\/strong><\/li>\n
  5. Restart MySQL server<\/li>\n
  6. Import all databases again<\/li>\n<\/ol>\n

    So yeah, I would be encouraging EVERYONE to add this line to all mysql servers you setup before you load it up with databases. I read that from MySQL 5.6 and onwards this option on by default. With this option enabled each database lives in its own set of files and when the database is dropped the files are garbage collected.<\/p>\n","protected":false},"excerpt":{"rendered":"

    After writing the article yesterday about removing a slave from a MySQL replication setup. I was digging around the mysql data directory and noticed that the innodb data file hadn’t reduced in size after I dropped the database. There is a reason for this. MySQL will not garbage collect the …<\/p>\n

    Reclaim Disk Space from InnoDB with MySQL<\/span> Read More \u00bb<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"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":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[5],"tags":[47,67,128,195],"yoast_head":"\nReclaim Disk Space from InnoDB with MySQL - Random Adult<\/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:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Reclaim Disk Space from InnoDB with MySQL\" \/>\n<meta property=\"og:description\" content=\"After writing the article yesterday about removing a slave from a MySQL replication setup. I was digging around the mysql data directory and noticed that the innodb data file hadn’t reduced in size after I dropped the database. There is a reason for this. MySQL will not garbage collect the … Reclaim Disk Space from InnoDB with MySQL Read More \u00bb\" \/>\n<meta property=\"og:url\" content=\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Random Adult\" \/>\n<meta property=\"article:published_time\" content=\"2013-08-07T03:47:46+00:00\" \/>\n<meta name=\"author\" content=\"digitalchild\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dcwhatwhat\" \/>\n<meta name=\"twitter:site\" content=\"@dcwhatwhat\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"digitalchild\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\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\":\"Article\",\"@id\":\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/\"},\"author\":{\"name\":\"digitalchild\",\"@id\":\"https:\/\/randomadult.local\/#\/schema\/person\/b0b954202434291ace51b4e7a0a81c04\"},\"headline\":\"Reclaim Disk Space from InnoDB with MySQL\",\"datePublished\":\"2013-08-07T03:47:46+00:00\",\"dateModified\":\"2013-08-07T03:47:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/\"},\"wordCount\":230,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/randomadult.local\/#organization\"},\"keywords\":[\"databases\",\"filesystem\",\"mysql\",\"sysadmin\"],\"articleSection\":[\"Geek\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/\",\"url\":\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/\",\"name\":\"Reclaim Disk Space from InnoDB with MySQL - Random Adult\",\"isPartOf\":{\"@id\":\"https:\/\/randomadult.local\/#website\"},\"datePublished\":\"2013-08-07T03:47:46+00:00\",\"dateModified\":\"2013-08-07T03:47:46+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/randomadult.local\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Reclaim Disk Space from InnoDB with MySQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/randomadult.local\/#website\",\"url\":\"https:\/\/randomadult.local\/\",\"name\":\"Random Adult\",\"description\":\"Ramblings of a random adult\",\"publisher\":{\"@id\":\"https:\/\/randomadult.local\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/randomadult.local\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/randomadult.local\/#organization\",\"name\":\"Random Adult\",\"url\":\"https:\/\/randomadult.local\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/randomadult.local\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/randomadult.local\/wp-content\/uploads\/2020\/01\/logo.png\",\"contentUrl\":\"https:\/\/randomadult.local\/wp-content\/uploads\/2020\/01\/logo.png\",\"width\":320,\"height\":134,\"caption\":\"Random Adult\"},\"image\":{\"@id\":\"https:\/\/randomadult.local\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/twitter.com\/dcwhatwhat\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/randomadult.local\/#\/schema\/person\/b0b954202434291ace51b4e7a0a81c04\",\"name\":\"digitalchild\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/randomadult.local\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e0331079f6730910bc7760cb8f781237?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e0331079f6730910bc7760cb8f781237?s=96&d=mm&r=g\",\"caption\":\"digitalchild\"},\"url\":\"https:\/\/randomadult.local\/author\/digitalchild\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Reclaim Disk Space from InnoDB with MySQL - Random Adult","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:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/","og_locale":"en_US","og_type":"article","og_title":"Reclaim Disk Space from InnoDB with MySQL","og_description":"After writing the article yesterday about removing a slave from a MySQL replication setup. I was digging around the mysql data directory and noticed that the innodb data file hadn’t reduced in size after I dropped the database. There is a reason for this. MySQL will not garbage collect the … Reclaim Disk Space from InnoDB with MySQL Read More \u00bb","og_url":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/","og_site_name":"Random Adult","article_published_time":"2013-08-07T03:47:46+00:00","author":"digitalchild","twitter_card":"summary_large_image","twitter_creator":"@dcwhatwhat","twitter_site":"@dcwhatwhat","twitter_misc":{"Written by":"digitalchild","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/#article","isPartOf":{"@id":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/"},"author":{"name":"digitalchild","@id":"https:\/\/randomadult.local\/#\/schema\/person\/b0b954202434291ace51b4e7a0a81c04"},"headline":"Reclaim Disk Space from InnoDB with MySQL","datePublished":"2013-08-07T03:47:46+00:00","dateModified":"2013-08-07T03:47:46+00:00","mainEntityOfPage":{"@id":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/"},"wordCount":230,"commentCount":0,"publisher":{"@id":"https:\/\/randomadult.local\/#organization"},"keywords":["databases","filesystem","mysql","sysadmin"],"articleSection":["Geek"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/","url":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/","name":"Reclaim Disk Space from InnoDB with MySQL - Random Adult","isPartOf":{"@id":"https:\/\/randomadult.local\/#website"},"datePublished":"2013-08-07T03:47:46+00:00","dateModified":"2013-08-07T03:47:46+00:00","breadcrumb":{"@id":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/randomadult.local\/reclaim-disk-space-from-innodb-with-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/randomadult.local\/"},{"@type":"ListItem","position":2,"name":"Reclaim Disk Space from InnoDB with MySQL"}]},{"@type":"WebSite","@id":"https:\/\/randomadult.local\/#website","url":"https:\/\/randomadult.local\/","name":"Random Adult","description":"Ramblings of a random adult","publisher":{"@id":"https:\/\/randomadult.local\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/randomadult.local\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/randomadult.local\/#organization","name":"Random Adult","url":"https:\/\/randomadult.local\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/randomadult.local\/#\/schema\/logo\/image\/","url":"https:\/\/randomadult.local\/wp-content\/uploads\/2020\/01\/logo.png","contentUrl":"https:\/\/randomadult.local\/wp-content\/uploads\/2020\/01\/logo.png","width":320,"height":134,"caption":"Random Adult"},"image":{"@id":"https:\/\/randomadult.local\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/twitter.com\/dcwhatwhat"]},{"@type":"Person","@id":"https:\/\/randomadult.local\/#\/schema\/person\/b0b954202434291ace51b4e7a0a81c04","name":"digitalchild","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/randomadult.local\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e0331079f6730910bc7760cb8f781237?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e0331079f6730910bc7760cb8f781237?s=96&d=mm&r=g","caption":"digitalchild"},"url":"https:\/\/randomadult.local\/author\/digitalchild\/"}]}},"_links":{"self":[{"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/posts\/198"}],"collection":[{"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/comments?post=198"}],"version-history":[{"count":0,"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/posts\/198\/revisions"}],"wp:attachment":[{"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/media?parent=198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/categories?post=198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/randomadult.local\/wp-json\/wp\/v2\/tags?post=198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}