Convert non-UTF-8 MySQL database to UTF-8

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

目的

Database upgrades may fail due to a wrong character set usage. This article explains how to convert a non-UTF-8 MySQL database into the recommended UTF-8 database.

データベースの変更を行う場合は必ず事前にバックアップを取得してください。

ソリューション

  1. Let's suppose the database is using latin1 character set and latin1_general_cs collation:

    CREATE DATABASE dbname CHARACTER SET latin1 COLLATE latin1_general_cs;
  2. Make sure that Fisheye / Crucible is not running;
  3. Connect to the database being used by Fisheye / Crucible;
  4. Create a database dump:

    mysqldump --opt -u root -p<secret> dbname > dbname.sql
  5. Open the resulting dbname.sql file and replace all occurrences of latin1_general_cs by utf8_bin and all occurrences of latin1 by utf8, through find / replace, then save changes.
  6. Restore dbname.sql. There are two possibilities here:

    1. Restore into a new database name, if you want to keep the original database untouched. This consists in:

      1. Creating a new database, this time using the correct character set and collation:

        CREATE DATABASE newdbname CHARACTER SET utf8 COLLATE utf8_bin;
      2. Restoring the dump:

        mysql -u root -p<secret> newdbname < dbname.sql
    2. Restore into the existing database name, if you're sure that the manual changes in dbname.sql through find / replace are correct. This consists in:
      1. Deleting the current database dbname:

        DROP DATABASE dbname;
      2. Creating it again, this time using the correct character set and collation:

        CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin;
      3. Restoring the dump:

        mysql -u root -p<secret> dbname < dbname.sql
  7. Restart MySQL service

  8. Start Fisheye / Crucible

 

最終更新日 2018 年 11 月 2 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.