Encoding & Backups

The other day I bumped into a very strange issue with MySQL on Windows. Just a quick warning, what follows is pretty geeky…

I run mysqldump as I normally would on a Linux box:

C:\mysqldump -u username -p database_name > output.sql

I thought it was all ok. So I went to restore it on another server (another Windows server).

C:\mysql -u root -p < output.sql

Nothing…

It just sat there for hours… doing nothing. I checked in after about 24 hours and found that it had returned with something like:

ERROR 2006 (HY000) at line 1: MySQL server has gone away

Now, there’s a few results about this on Google etc and they mention increasing the max_allowed_packet setting in my.cnf. There’s some information that this setting is actually smaller by default than the setting used for mysqldump.

However, what I found was something far more subtle…

When using Powershell (I also used the standard CMD) I hadn’t realised that it uses different encoding for the redirection operator (>). It in fact was trying to use UTF-16 LE not UTF-8 like mysql was expecting.

The first clue was the size of the backup which, compared to some slightly older backups, was about twice the size. When I opened the backup file and found the encoding it was indeed set to UTF-16 LE.

After switching and re-saving the backup it worked and the restore took under 10 minutes.

So, what’s the best way of handling this?

Use the Powershell’s out-file

C:\mysqldump -u user -p database | out-file -Encoding utf8 C:\output.sql

Then on restore use:

C:\cat C:\output.sql | mysql -u user -p database

You can also set 2 options in your my.cnf which might help things along:

[mysqld]
max_allowed_packet=64M
innodb_flush_log_at_trx_commit=0

Don’t forget to switch back innodb_flush_log_at_trx_commit when you’re done (normally set to 1).