¥È¥Ã¥× º¹Ê¬ °ìÍ÷ ¥½¡¼¥¹ ¸¡º÷ ¥Ø¥ë¥× PDF RSS ¥í¥°¥¤¥ó

mysql euc-jp¤«¤éutf8¤Ë¥Þ¥¤¥°¥ì¡¼¥·¥ç¥ó(migration)

¥µ¡¼¥Ð¤Î¹¹¿·¤Ëȼ¤¤mysql¤òmysql8¤Ë¤·¡¢myisam¡¢euc¤ÎDB¤òInnoDB¡¢utf-8¤Ë°Ü¹Ô¤·¤¿¤¬¥Á¥ç¥Ã¥È¶ìÏ«¤·¤¿¤Î¤Ç¥á¥â¤ò»Ä¤¹¡£

°ì²ó¤Îdump¤Ç¤Ï¤Ç¤­¤Ê¤¤

mysqldump¤Ç£Ä£Â´Ý¤´¤È¥À¥ó¥×¤·¤ÆÌ᤽¤¦¤È¤·¤Æ¤â¾å¼ê¤¯¤¤¤«¤Ê¤¤¡£default-charact-set¤Î»ØÄê¤ò¤·¤Ê¤¤¤Èutf8¤Ç¥À¥ó¥×¤·¤Æ¤¯¤ì¤ë¤Î¤À¤¬¡¢¥Ç¡¼¥¿Æâ¤Î­¡¤È¤«¤Îµ¡¼ï°Í¸ʸ»ú¤¬'?'¤Ë¤Ê¤Ã¤Æ¤·¤Þ¤¤¤Þ¤¹¡£¤Þ¤¿¡¢¥Ó¥å¡¼¤Î¥À¥ó¥×¤â¥«¥é¥à¤ÎÄêµÁ¤¬`??????` AS `??????`¤Ë¤Ê¤ê¾å¼ê¤¯¤¤¤«¤Ê¤¤¡£

·ë¶É

data¤Èddl¤È¥Ó¥å¡¼¤òÊÌ¡¹¤Ë¥À¥ó¥×¤·data¤Ïnkf¤Ë¤Æʸ»ú¥³¡¼¥É¤òÊÑ´¹(¤ä¤Ã¤Ñ¤êÍê¤ê¤Ë¤Ê¤ënkf)¥Ó¥å¡¼¤Ïshow create table ¤Ë¤ÆviewÄêµÁ¤ò¥À¥ó¥×¤¹¤ë»ö¤Ë¡£

-- DATA¤ÈDDL¤ò¼èÆÀ
mysqldump -u $USER -p$PASS  --no-data $DB `cat dump/innodb.list` >dump/innodb.ddl
mysqldump -u $USER -p$PASS --default-character-set=ujis -t $DB `cat dump/innodb.euc.list` >dump/innodb.euc.dump

-- ÄêµÁ¤òÊѹ¹ euc-jp->utf8¤Ë
perl -i -ple 's/CHARSET=ujis/CHARSET=utf8mb4 collate utf8mb4_bin/' dump/innodb.ddl
perl -i -ple 's/MyISAM/InnoDB/g' dump/innodb.ddl

-- ʸ»ú¥³¡¼¥É¤òÊÑ´¹¤·¥Ç¡¼¥¿¤ò¥í¡¼¥É
nkf -wx dump/innodb.euc.dump >dump/innodb.dump
perl -i -ple 's/SET NAMES ujis /SET NAMES utf8 /g;' dump/innodb.dump
mysql -u $USER -p$PASS $DB <dump/innodb.ddl
mysql -u $USER -p$PASS $DB <dump/innodb.dump
mysql -u $USER -p$PASS $DB <dump/view.dump

Ãí¡Ë

¢¨1 DDL(Data Definition Language)¥Ç¡¼¥¿ÄêµÁ¸À¸ì

all ¥Ð¥Ã¥¯¥¢¥Ã¥×¤òŸ³«¤· ÆÃÄê¤Î£Ä£Â¤À¤±¤òÃê½Ð

$ sed -n '/^-- Current Database: `[DB]`/,/^-- Current Database: `/p' [backupfile] > backup.sql