DBに保存されている文字を全置換してしまう方法(保存データ内のドメイン変更方法)

変更用のコードを書くのが正解だけど面倒なんで全置換しちゃえってやつ。

気持ち/環境

• [気持ち] 変更用のコードを書くなんて面倒. 事故ってもなんとかできる(色んな意味で).
• [環境] MySQL5.6, InnoDB, DB自体は3GB位、

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8
character_set_filesystem    utf8mb4
character_set_results   utf8mb4
character_set_server    utf8mb4
character_set_system    utf8

やりたいこと

• ドメイン変更などの影響を受け、DBの中のドメインを変更したい

手順

① DBバックアップ

以下情報で、全テーブルか特定のテーブルか選んで実施。
特定の方が安全。でもチェックが面倒。

サンプル情報.txt

ホスト: nikkiawsome.jp
User: writer
PW: （有り）
DB: Nikki
主に置換したいテーブル: Blog
置換前のドメイン:nikkiawsome.jp
置換後のドメイン:b1.nikkiawesome.jp

全テーブルの場合

$ mysqldump --default-character-set=utf8mb4 -h nikkiawsome.jp -u writer -p Nikki > backup.sql

特定のテーブルの場合

$ mysqldump --default-character-set=utf8mb4 -h nikkiawsome.jp -u writer -p Nikki Blog > backup.sql

② 全置換

$ cat backup.sql | sed -e 's/nikkiawsome.jp/b1.nikkiawesome.jp/g' > ./backup_after.sql

③ 戻す

$ mysql --default-character-set=utf8mb4 -h nikkiawesome.jp -u writer -p Nikki < backup_after.sql

[おまけ] 置換具合チェック

$ diff backup.sql backup_after.sql > diff.txt

リスク, 注意点

• DBのエンコードがutf8mb4とかのときはSequelProなどのGUIツール使ったSQL吐き出しはしないほうがいい
  • SequelProでバックアップとったら、SQL吐き出し時にUTF8mb4に対応してなくてデータ飛ばした（ ﾟᆸﾟ ）
    • エクスポートするときに、エンコード指定するところないと思う。
    • SJISでも同様の問題があったようなので、やっぱりちょっと怪しいかもしれないです。
      • SequelProのドキュメントや資料未確認なので怪しいかもー程度
• ランダムな文字列に置換前の文字列が含まれていると死ぬかも知れない
  • 特定のテーブルのみ置換すること
  • また事前に目視でいいのでチェックすると吉。
    • 以下SQLは、画像アップロード時にランダムな文字列をファイル名としてサーバーに保存している場合に目視するやつ。

check.sql

SELECT
    id, photo
FROM
    Blog
WHERE
    photo REGEXP "google\.com"
ORDER BY date DESC
LIMIT 10;