latin1のDBからdumpしたデータをutf8のDBに移行するよー
- 移行元 DB:hoge
mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ mysql> 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/mysql/charsets/ | +--------------------------+----------------------------+
残念ながらdefaultがlatin1ですが、DB自体はutf8で作っているのでuse hogeした後見ると
mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | utf8_general_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+
となっています。
- 以降先 DB:hoge
mysql> show variables like 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+
根っからのUTF8仕様。
というわけでまずdefault charsetをlatin1に指定してdump
mysqldump -u username -p hoge --default-character-set=latin1 > backup.sql
dumpしたデータをlatin1対応のエディタ(サクラエディタにしました)で開いて、SET NAMES追加
-- MySQL dump 10.11 -- -- Host: localhost Database: hoge -- ------------------------------------------------------ # ↑このメッセージがあるあたりに追加 SET NAMES utf8;
保存して、リストア
mysql -u username -p hoge < backup.sql
元々latin1の設定になってるmysqlにutf8のDBを作るとdumpでcharset指定してやらないとうまくいかないんですね。
無事に移行できました。