Let me lay out the basic scenario for the problem I'm up against.
- I'm running MySQL 5.1 and have a single database with 2 large MyISAM tables (9M and 20M rows respectively).
- Each of these tables has several
TEXTcolumns which are currently set to the
latin1character set and
- These tables are actively read / written to, so we want to avoid as much downtime as possible.
I'm confident in actually converting the data between the two character sets (using
ALTER TABLE to convert columns to their binary counterparts, then back to the correct original column type in the correct charset). What I'm not sure about is how to go about the conversion without locking the entire table for the duration (which could take hours / days due to the number of rows and indexes).
One idea is to set up replication to a slave; turn off the replication; convert everything; re-enable replication and let the slave catch up; then finally promote the slave to master.
However, this doesn't solve the issue of how to set up replication in the first place. From what I've read, even mysqlhotcopy & xtrabackup still require table locks for MyISAM tables.
Is there another option I've missed, or am I going to have to turn off my entire application in order to set this up? Thanks for any advice.