Current situation is this:

  • I have a large legacy DB using only MyISAM tables, the size of whole DB is 4GiB, one of the tables contains over 10 million rows, so it is pretty large.

  • The database is used heavily and downtime should be as low as possible, no more than 30 minutes (less is better).

  • All files are not under LVM (so, snapshot is not possible).

I want to set up simple master-slave replication and migrate all tables to InnoDB. What is the best plan for this migration? As I said, it should have low downtime and be as safe as possible

P.S. Currently I have no easily restorable backups, so adding somewhere a step of backing up is a good idea. But I think with master-slave replication backups will be easy. Also, please include what might go wrong during this plan.

  • Can you easily switch your app connector to work on master or slave? Do you already activated binary logs? – Maxime Fouilleul Dec 21 '12 at 13:56
  • I haven't done anything yet, just planning. Don't want to mess up. Yes, DB server can be easily switched in app (It's django app, so changing config should just work). – evgeniuz Dec 21 '12 at 14:00


Here a sample of config to setup your replication :

On Master in your my.cnf add :

server-id                       = 1
log-bin                        = /database/bin-log/mysql-bin
binlog_cache_size              = 16M
slave_compressed_protocol       = 1
binlog_format                  = MIXED
max_binlog_size                 = 100M

On your slave in my.cnf add :

server-id                       = 2
relay-log-space-limit           = 50G
relay-log                       = /database/relay-log/relay-bin
relay-log-index                 = /database/relay-log/relay-bin.index
read_only                       = 1

Now you have two solutions, dump or binary copy.

In both case you should made your transformation (myisam to InnoDB) on your slave slave and switch your slave to master. Also you should active binlogs to catch the master position.

First solution, you can make a dump of your master ( with the master-data option to catch master position) On your .sql dump file, search and replace ENGINE=myisam to InnoDB and load thé dump to thé slave server. You have all tables on InnoDB engine :) after started réplication and catching up your the late : "second behind master= 0 " it´s time to switch slave to master ( with a new dump (now from slave to master) and take care to switch the conf also).

The second solution is the same but with a cold backup, stop mysql, copy datadir (4gb Will probably done in ~ 10mins dépends on your solution : scp, usb keys...) start your slave and alter each table with engine=innodb. Like thé first one, when it´s done, you should switch master/slave and alter ex-master tables.

Caution with "alter table engine=InnoDB" Mysql Will create a InnoDB table and copy data in it so your table size is tablesize x 2 (when innodb table is dobe, mysql drop myisam table)

I'm on my phone so sorry if there are many strange word... :)

(I didn't detailed replication set up cause it's not the subject but we let me know if you want some help.)

| improve this answer | |
  • The best will be to merge the both technics. Prepare a clean slave with an dump + search/replace (engine=myisam to innodb). Switch master/slave stop new master and make a cold backup to new slave. We ll talk about it on monday if you want :) – Maxime Fouilleul Dec 21 '12 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.