{"id":643,"date":"2013-01-02T08:24:16","date_gmt":"2013-01-02T13:24:16","guid":{"rendered":"http:\/\/blog.xfloyd.net\/?p=643"},"modified":"2013-01-30T10:30:14","modified_gmt":"2013-01-30T15:30:14","slug":"mysql-dealing-with-ibdata1","status":"publish","type":"post","link":"https:\/\/xfloyd.net\/blog\/?p=643","title":{"rendered":"MySQL Dealing with ibdata1"},"content":{"rendered":"

As you may have noticed in your MySQL\u2019s data directory (in Debian\/Ubuntu \u2013 \/var\/lib\/mysql<\/em>) lies a file called \u2018ibdata1\u2032. It holds almost all the InnoDB data (it\u2019s not a transaction log) of the MySQL instance and could get quite big. By default this file has a initial size of 10Mb and it automatically extends. Unfortunately, by design InnoDB data files cannot be\u00a0shrinked. That\u2019s why DELETEs, TRUNCATEs, DROPs, etc. can\u2019t will not reclaim the space used by the file. Instead any freed regions are marked as unused and can be used later. Theoretically speaking the file could reach the maximum size allowed by the filesystem if no limit is set in the my.cnf file (in Debian\/Ubuntu it\u2019s located in \/etc\/mysql\/my.cnf<\/em>). Guess what ? It\u2019s not set by default.<\/p>\n

<\/p>\n

This method requires much more space and time but it\u2019s maybe the easiest one. So here it is:<\/p>\n

    \n
  1. Dump all databases by calling:
    \n

    \/usr\/bin\/mysqldump \u2013\u2013extended-insert \u2013\u2013all-databases \u2013\u2013add-drop-database \u2013\u2013disable-keys \u2013\u2013flush-privileges \u2013\u2013quick \u2013\u2013routines \u2013\u2013triggers > all-databases.sql<\/p><\/blockquote>\n<\/li>\n

  2. Stop the MySQL server;<\/li>\n
  3. Rename or remove (in case you\u2019ve already backed it up) the MySQL data directory and create an empty one with the same name and permissions;<\/li>\n
  4. Make the appropriate changes in my.cnf<\/em>;<\/li>\n
  5. Re-initialize the database with the following command (replace the \u2018mysqld<\/em>\u2018 with the login of the user your MySQL server runs as):
    \n

    sudo -u mysqld<\/strong> mysql_install_db<\/p><\/blockquote>\n<\/li>\n

  6. Start the MySQL server;<\/li>\n
  7. Get into the \u2018mysql<\/em>\u2018 console and type:
    \n

    SET FOREIGN_KEY_CHECKS=0;
    \nSOURCE all-databases.sql;
    \nSET FOREIGN_KEY_CHECKS=1;<\/p><\/blockquote>\n<\/li>\n

  8. Restart the MySQL server.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

    As you may have noticed in your MySQL\u2019s data directory (in Debian\/Ubuntu \u2013 \/var\/lib\/mysql) lies a file called \u2018ibdata1\u2032. It holds almost all the InnoDB data (it\u2019s not a transaction log) of the MySQL instance and could get quite big. By default this file has a initial size of 10Mb and it automatically extends. Unfortunately, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/643"}],"collection":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=643"}],"version-history":[{"count":3,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/643\/revisions"}],"predecessor-version":[{"id":645,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/643\/revisions\/645"}],"wp:attachment":[{"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=643"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=643"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xfloyd.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=643"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}