Mysql – Latin1 to UTF8 on large MySQL database, minimal downtime

myisamMySQLreplication

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 VARCHAR and TEXT columns which are currently set to the latin1 character set and latin1_ci collation.
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.

Best Answer

Promoting a slave would probably be my preferred route. As you pointing out any selects on MyISAM tables would require table level locks. There is one tool that might be able to help, pt-table-sync. It's primary purpose is to find gaps and differences in existing master slave relationships.

A nice thing about it is it does this in nice "chunks". Think of it kind of like antilock breaks. The chunk size is configurable but you could, for example go through doing 1000 rows at a time, minimizing lock times and letting things flow through. I haven't used it to fully repopulate a slave from scratch though although I'd give that a look. Once you have a full copy, do another run to catch new stuff, updates that have come in. Do a flush table with read locks, do a final table sync. run show master status to get the binary log position to start slaving from, unlock tables.

Oh, if you don't have binary logging you'll at least need a master bounce and cnf change unfortunately.

Another approach that might be much simpler depending your write downtime tolerance. You're all myisam, you gave the size in rows by what's the disk footprint in MB or GB? Figure out how long it would take to transfer that size between your machines (hopefully there both in the same local network). You could do flush table with readlock, again still show master status, then just rsync the .MY* files over to your new DB's datadir.

One final alternative, depending on how you're setup: Can you do an LVM or other kind of filesystem snapshot? This would be the best way to minimize downtime. You flush tables with readlock, show master status, start snapshot, then unlock the tables to allow full read write activity to flow through. The difference here is you'll copy the snapshot you started. You'll just need to feel confident the write activity won't exceed the snapshot size you allocate before the copy finishes.

What ever method, before promotion I would verify the character set conversions went as desired. They can be a real pain to reverse.

I would also recommend upgrading to innodb if possible to make it possible to use xtrabackup in a non blocking fashion in the future.

Related Solutions

MySQL – How to Convert Control Characters from Latin1 to UTF-8

I'm not certain. I tried to start out be reproducing your problem but the alter worked fine for me.

test > CREATE TABLE `bar` (  `content` text ) ENGINE=MyISAM DEFAULT CHARSET=latin1;  INSERT INTO bar VALUES (0x8081828384858687898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F);
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

test > ALTER TABLE bar CHANGE content content TEXT CHARACTER SET UTF8;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

test > select * from bar;
+---------------------------------+
| content                         |
+---------------------------------+
| ����������������������������� |
+---------------------------------+
1 row in set (0.00 sec)

test > set names utf8;
Query OK, 0 rows affected (0.00 sec)

test > select * from bar;
+---------------------------------------------------------------------------------+
| content                                                                         |
+---------------------------------------------------------------------------------+
| €‚ƒ„…†‡‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Here's my related char settings

test > show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Edit

My char settings before running set names utf8

test > show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Version

test > select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.1.41-3ubuntu12.10-log |
+-------------------------+
1 row in set (0.00 sec)

MySql: Change from latin1 to utf8

Setting the default character set and collation is completely safe. This will ensure that future DDL changes will use utf8, but will not affect existing columns that use latin1. Those will have to be converted to utf8. Make a backup of the data, because there are risks of data corruption (one example).

And for completeness, I will point out that adding the changes in the my.cnf will require a server restart.

Best Answer

Related Solutions

MySQL – How to Convert Control Characters from Latin1 to UTF-8

MySql: Change from latin1 to utf8

Related Question