Rows missing after online conversion from MyISAM to InnoDB

Question

We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down.

Now that the conversion is done, a lot of intermittent rows seem to be missing. Is this possibly due to operations during conversion? Or is the issue somewhere else?

Answer 1

Performing an ALTER to change storage engines won't make rows disappear. However, let me offer some advice since you said you're 'database noobs' in your question.

When modifying existing schema or doing anything that could affect data, here's some basic advice:

1. Make a backup first.
2. Have a change plan.
3. Test your plan on an offline host.
4. Have a test plan to compare before and after data.
5. Schedule and take a downtime.
6. Take a backup and snapshot immediately after your downtime goes into effect and you verify traffic has stopped.
7. If you're running MYISAM, use 'CHECK TABLE' to evaluate what you're dealing with before you ALTER.
8. Copy the table locally in addition to your backup, just in case.
9. Proceed with caution, enable "--show warnings" and other output so you have the full picture as you make your changes.
10. If the data is important to you, hire a DBA, even if just to consult during the migration so you have a seasoned veteran by your side.

There's probably a lot more I could get into, but the above will provide you with options when something goes wrong.

As far as your missing data/rows, there's no way to know w/o a "before/after" snapshot to compare. You can compare against your latest backup to at least verify that much.

Answer 2

One of the best ways to convert MyISAM to InnoDB without a whole lot of downtime has just one prerequisite: Use a Replication Slave.

Here is a bird's eye view of the plan

1. Create Replication Master/Slave Setup
2. Convert every MyISAM table on the slave to InnoDB
3. Point your app to the Slave

Sounds simple? There are a lot of details behind this.

Create Replication Master/Slave Setup

There is a slick way to create a Slave without a lot of disturbance to the Master. I wrote two posts:

• Two ways to create a slave using rysnc in ServerFault
• Create a Slave from another Slave in StackOverflow.

Rather than detail how to use rsync, please read those two posts.

Convert every MyISAM table on the slave to InnoDB

On the DB Slave, you can the following SQL Statement:

For MySQL 5.5:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema NOT IN
('information_schema','mysql','performance_schema');

Version for MySQL prior to MySQL 5.5

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema NOT IN
('information_schema','mysql');

Using the output from the query, you have a conversion script for the slave.

You must put these two lines at the top of the script:

SET SQL_LOG_BIN = 0;
STOP SLAVE;

The script will first disable binary logging (if you configured the slave to have binary logs), stop replication, and the convert each MyISAM table to InnoDB.

Here is how to create that script and execute it:

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema')"
INNODB_CONV_SCRIPT=MassConvertMyISAMTablesToInnoDB.sql
echo "SET SQL_LOG_BIN = 0;" > ${INNODB_CONV_SCRIPT}
echo "STOP SLAVE;" >> ${INNODB_CONV_SCRIPT}
mysql -h(IP of Master) -u... -p... --skip-column-names -A -e"${SQL}" >> ${INNODB_CONV_SCRIPT}
echo "START SLAVE;" >> ${INNODB_CONV_SCRIPT}
mysql -h(IP of Slave) -u... -p... --skip-column-names -A < ${INNODB_CONV_SCRIPT}

Point your app to the Slave

Perform SELECT queries from the Slave. If you are satisfied with data content on the Slave, feel free to point your app to the slave as follows:

1. On the Slave, run SHOW SLAVE STATUS\G and make sure Seconds_Behind_Master is 0
2. On the Slave, mysqldump -h(IP of Slave) -u... -p... --single-transaction --routines --triggers --all-databases > MySQLBackup.sql (Hey, a backup would good right about now)
3. On the Master, run service mysql stop (Downtime starts)
4. Repeat Step 1
5. Point your app to the Slave (Downtime ends on app's first connection)

If you made to this point unscathed, CONGRATULATIONS !!!

ADDED BONUS: If you setup Master/Master Replication (aka Circular Replication) instead of Master/Slave, you can do this instead:

1. On the Slave, run SHOW SLAVE STATUS\G and make sure Seconds_Behind_Master is 0
2. On the Slave, mysqldump -h(IP of Slave) -u... -p... --single-transaction --routines --triggers --all-databases > MySQLBackup.sql (Hey, a backup would good right about now)
3. Point your app to the Slave (Downtime starts and ends on app's first connection)
4. On the new Master, run STOP SLAVE;
5. On the new Master, run CHANGE MASTER TO MASTER_HOST='';

What you now have is Master/Slave in reverse. The New Master has InnoDB data and the old Master is now a slave with MyISAM data. If you split reads and writes, reads can go from the Slave (reads are faster from MyISAM than InnoDB) and writes go to the Master (transactional support for InnoDB). Like Hannah Montana sings, you get the best of both worlds (Yes, I have two daughters that love the show) !!!

ANOTHER ADDED BONUS: Because the Master is now InnoDB, you can do mysqldump from the Master without downtime and without interfering with transactions. Only drawback is increase CPU and disk I/O. You could therefore to a mysqldump of table structures only on the Master (InnoDB) and a mysqldump of the data only on the slave (Such a dump will have no references to InnoDB or MyISAM. It will just be data) plus a mysqldump of the table structures for the slave to have the MyISAM layout.

The possibilities can go on because of this new setup...

UPDATE 2011-08-27 19:50 EDT

My apologies. I did not fully read the question. You performed the converseion already.

Only if you already had binary logging turned on, and you have a prior a backup, you could

• restore /var/lib/mysql to another location, like /var/lib/mysql2
• run service mysql stop
• run service mysql start --datadir=/var/lib/mysql2
• mysqldump the database from that backup to /root/olddata.sql
• run mysqlbinlog against all binary logs in /var/lib/mysql (not /var/lib/mysql2) from the point-in-time since the last backup into /root/changes.sql
• Load changes.sql into mysql (since it is still pointing at /var/lib/mysql2)

This should catch everything that was recorded and the conversion should should kick in. Again, this is all contigent on you already had binary logging turned on prior to last backup. Otherwise, my condolences.