In previous part we put aside the issue of foreign keys. We address this issue now.

What if my InnoDB tables have foreign keys?

MyISAM does not support them, so you can’t just ALTER an InnoDB table to MyISAM and back into InnoDB, and expect everything to work.

Alas, this calls for additional steps (i.e. additional ALTER 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”.

Save , drop and restore your Foreign Keys setup

You can use common_schema‘s  sql_foreign_keys to get the full listing and create definition of your foreign keys. For example, assume we use the sakila database:

SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila' INTO OUTFILE '/somewhere/safe/create_foreign_keys.sql'

(replace TABLE_SCHEMA=’sakila’ with whatever you want).

A sample output would be something like this (note: no semicolon on end of line):

ALTER TABLE `sakila`.`address` ADD CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `sakila`.`city` (`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`city` ADD CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `sakila`.`country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER 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
...

Once the above is in a safe place, you will want to DROP all of your foreign keys. Again, using common_schema:

SELECT drop_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila';
+-----------------------------------------------------------------------------------+
| drop_statement                                                                    |
+-----------------------------------------------------------------------------------+
| ALTER TABLE `sakila`.`address` DROP FOREIGN KEY `fk_address_city`                 |
| ALTER TABLE `sakila`.`city` DROP FOREIGN KEY `fk_city_country`                    |
| ALTER TABLE `sakila`.`customer` DROP FOREIGN KEY `fk_customer_address`            |
| ALTER TABLE `sakila`.`customer` DROP FOREIGN KEY `fk_customer_store`              |
| ALTER TABLE `sakila`.`film` DROP FOREIGN KEY `fk_film_language`                   |
| ALTER TABLE `sakila`.`film` DROP FOREIGN KEY `fk_film_language_original`          |
| ...                                                                               |
+-----------------------------------------------------------------------------------+

You don’t want to issue all these at once: do them one at a time, and wait for your slave to catch up.

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.

And then, taking breath again, you must re-import the foreign keys. Use the ADD CONSTRAINT commands you have saved earlier on. Again, one at a time, wait for slave to catch up.

To reiterate, for each table you would take the following steps:

1. Make sure the FK definition is safely stored somewhere
2. STOP SLAVE
3. Drop all table’s foreign keys: ALTER TABLE … DROP FOREIGN KEY …, DROP FOREIGN KEY …
4. START SLAVE
5. Wait for slave to catch up
6. STOP SLAVE
7. ALTER TABLE … ENGINE=MyISAM (*)
8. START SLAVE
9. Wait for slave to catch up

(*) Altering to MyISAM drops FK constraints, so the above could actually be done in one step. I’m cautious and illustrate in two.

Once all tables are altered, and InnoDB tablespace is removed, restoration is as follows: for each table,

1. STOP SLAVE
2. ALTER TABLE … ENGINE=InnoDB [create options]
3. START SLAVE
4. Wait for slave to catch up
5. STOP SLAVE
6. ALTER TABLE … ADD CONSTRAINT …, ADD CONSTRAINT …(+)
7. START SLAVE
8. Wait for slave to catch up

(+) Alas, you can’t convert to InnoDB and add constraints at the same time…

This is not entirely safe

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.

To quickly reiterate, you can only delete the ibdata1 file when no InnoDB tables exist. Delete this file with an existing InnoDB table, even a table in its own tablespace, and nothing ever works anymore.

The problem with the dump-based solution

The impact of doing a logical dump is often overwhelming. Well, the dump may be tolerable, but the restore is much longer. The real pain is that you can’t do this one table at a time: you have to destroy everything before dropping the ibdata1 file; you then have to import everything.

Perhaps the most common scenario is that we do the changes on a slave, so as not to completely shut down our database. This is nice; no one is aware of the shutdown process. However, Huston, we have a problem: we need to make sure we can keep up the binary logs on the master for the duration of the entire process.

A semi-solution for binary logs

You may get by by keeping the SQL_IO_THREAD running on the slave while dump is taken (SQL thread is better turned off). If you’re careful, you could do the same after restarting the database: you should still be able to acquire relay logs. With row based replication becoming more common, the problem of binary logs disk space returns: the logs (rather, log entries) are just so much larger!

Either way, the process can takes long days, at the end of which your slave is up, but lags for long days behind.

Wishful thought: do it one table at a time

If we could do it one table at a time, and assuming our dataset is fairly split among several tables (i.e. not all of our 500GB of data is in one huge table), life would be easier: we could work on a single table, resume replication, let the slave catch up, then do the same for the next table.

How? Didn’t we just say one can only drop the ibdata1 file when no InnoDB tables exist?

Solution: do it one table at a time

I’m going to illustrate what seems like a longer procedure. I will later show why it is not, in fact, longer.

The idea is to first convert all your tables to MyISAM (Yay! A use for MyISAM!). That is, convert your tables one table at a time, using normal ALTER TABLE t ENGINE=MyISAM.

Please let go of the foreign keys issue right now. I will address it later, there’s a lot to be addressed.

So, on a slave:

1. STOP SLAVE
2. One ALTER TABLE … ENGINE=MyISAM
   
3. START SLAVE again
4. Wait for slave catch up
5. GOTO 1

What do we end up with? A MyISAM only database. What do we do with it? Why, convert it back to InnoDB, of course!

But, before that, we:

1. Shut MySQL down
2. Delete ibdata1 file, ib_logfile[01] (i.e. delete all InnoDB files)
3. Start MySQL

A new ibdata1 file, and new transaction log files will be created. Note: the new ibdata1 file is small. Mission almost accomplished.

We then:

1. STOP SLAVE
2. Do one ALTER TABLE … ENGINE=InnoDB [ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 …]
   
3. START SLAVE again
4. Wait for slave catch up
5. GOTO 1

What do we end up with? An InnoDB only database, with true file per table, and a small ibdata1 file. Space recovered!

The advantage of this method

The thing is, we resume replication after each table alteration. This means breaking the lag period into many smaller periods. While the total runtime does not reduce, we do reduce the maximum lag time. And this makes for easier recovery: no need to store multitudes of binary logs!

So what about the foreign keys?

Phew. Continued next post.