{"id":4845,"date":"2012-05-30T09:03:18","date_gmt":"2012-05-30T07:03:18","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4845"},"modified":"2012-06-03T12:12:47","modified_gmt":"2012-06-03T10:12:47","slug":"getting-rid-of-huge-ibdata-file-no-dump-required-part-ii","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/getting-rid-of-huge-ibdata-file-no-dump-required-part-ii","title":{"rendered":"Getting rid of huge ibdata file, no dump required, part II"},"content":{"rendered":"

This post continues Getting rid of huge ibdata file, no dump required, part I<\/a>, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once<\/em>.<\/p>\n

In previous part we put aside the issue of foreign keys. We address this issue now.<\/p>\n

What if my InnoDB tables have foreign keys?<\/h4>\n

MyISAM does not support them, so you can’t just ALTER<\/strong> an InnoDB table to MyISAM and back into InnoDB, and expect everything to work.<\/p>\n

Alas, this calls for additional steps (i.e. additional ALTER<\/strong> commands). However, these still fall well under the concept of “do it one table at a time, then take time to recover your breath and replication lag”<\/em>.<\/p>\n

Save , drop and restore your Foreign Keys setup<\/h4>\n

You can use common_schema<\/a>‘s\u00a0 sql_foreign_keys<\/a> to get the full listing and create definition of your foreign keys. For example, assume we use the sakila<\/strong> database:<\/p>\n

\n
SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila' INTO OUTFILE '\/somewhere\/safe\/create_foreign_keys.sql'<\/pre>\n<\/blockquote>\n
(replace TABLE_SCHEMA=’sakila’<\/strong> with whatever you want).<\/p>\n
A sample output would be something like this (note: no semicolon on end of line<\/em>):<\/p>\n
\n
ALTER TABLE `sakila`.`address` ADD CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `sakila`.`city` (`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE\r\nALTER TABLE `sakila`.`city` ADD CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `sakila`.`country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE\r\nALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE\r\nALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE\r\nALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE\r\nALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE\r\n...<\/pre>\n<\/blockquote>\n
Once the above is in a safe place, you will want to DROP all of your foreign keys. Again, using common_schema<\/em>:<\/p>\n
\n
SELECT drop_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila';\r\n+-----------------------------------------------------------------------------------+\r\n| drop_statement\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-----------------------------------------------------------------------------------+\r\n| ALTER TABLE `sakila`.`address` DROP FOREIGN KEY `fk_address_city`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`city` DROP FOREIGN KEY `fk_city_country`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`customer` DROP FOREIGN KEY `fk_customer_address`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`customer` DROP FOREIGN KEY `fk_customer_store`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film` DROP FOREIGN KEY `fk_film_language`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ALTER TABLE `sakila`.`film` DROP FOREIGN KEY `fk_film_language_original`\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ...                                                                               |\r\n+-----------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
You don’t want to issue all these at once: do them one at a time, and wait for your slave to catch up.<\/p>\n
Once this is done, you can move on to the steps described in Part I of this post: converting tables to MyISAM, shutting down, removing InnoDB files, then converting back to InnoDB.<\/p>\n
And then, taking breath again, you must re-import the foreign keys. Use the ADD CONSTRAINT<\/strong> commands you have saved earlier on. Again, one at a time, wait for slave to catch up.<\/p>\n
To reiterate, for each table you would take the following steps:<\/p>\n
    \n
  1. Make sure the FK definition is safely stored somewhere<\/li>\n
  2. STOP SLAVE<\/li>\n
  3. Drop all table’s foreign keys: ALTER TABLE … DROP FOREIGN KEY …, DROP FOREIGN KEY …<\/li>\n
  4. START SLAVE<\/li>\n
  5. Wait for slave to catch up<\/li>\n
  6. STOP SLAVE<\/li>\n
  7. ALTER TABLE … ENGINE=MyISAM (*)<\/li>\n
  8. START SLAVE<\/li>\n
  9. Wait for slave to catch up<\/li>\n<\/ol>\n
    (*) Altering to\u00a0MyISAM drops FK constraints, so the above could actually be done in one step. I’m cautious and illustrate in two.<\/p>\n
    Once all tables are altered, and InnoDB tablespace is removed, restoration is as follows: for each table,<\/p>\n
      \n
    1. STOP SLAVE<\/li>\n
    2. ALTER TABLE … ENGINE=InnoDB [create options]<\/li>\n
    3. START SLAVE<\/li>\n
    4. Wait for slave to catch up<\/li>\n
    5. STOP SLAVE<\/li>\n
    6. ALTER TABLE … ADD CONSTRAINT …, ADD CONSTRAINT …(+)<\/li>\n
    7. START SLAVE<\/li>\n
    8. Wait for slave to catch up<\/li>\n<\/ol>\n
      (+) Alas, you can’t convert to InnoDB and add constraints at the same time…<\/p>\n
      This is not entirely safe<\/h4>\n
      A MyISAM slave to an InnoDB master with foreign keys is a tricky business. It really depends on the type of foreign keys you have and the use you make of them. See Impact of foreign keys absence on replicating slaves<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
      This post continues Getting rid of huge ibdata file, no dump required, part I, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once. In previous part we put aside the issue of foreign keys. We address this issue now. What […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[74,14,13,65],"class_list":["post-4845","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-hack","tag-innodb","tag-myisam","tag-refactoring"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1g9","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4845","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4845"}],"version-history":[{"count":20,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4845\/revisions"}],"predecessor-version":[{"id":4933,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4845\/revisions\/4933"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4845"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4845"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4845"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}