mysqldumpで取得したdumpファイルをインポートする手順｜文字化けとエラー対処まで

この記事のポイント

・mysql DB名 < backup.sql がインポートの基本コマンド形式
・--default-character-set=utf8mb4 の指定で文字化けを防ぐ
・「ERROR 1049」はDB未作成が原因、先にCREATE DATABASEを実行
・外部キーエラーは foreign_key_checks=0 の設定で回避できる

# dumpファイルの先頭10行を確認する
head -10 /tmp/backup.sql

-- MySQL dump 10.13  Distrib 8.0.36, for Linux (x86_64)
--
-- Host: localhost    Database: myapp_db
-- ------------------------------------------------------
-- Server version       8.0.36

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

# インポート先サーバーのMySQLバージョンを確認する
mysql --version

mysql  Ver 8.0.36 Distrib 8.0.36, for Linux (x86_64) using  EditLine wrapper

# MySQLに接続してデータベースを作成する
mysql -u root -p

# MySQLプロンプト内で実行する
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SHOW DATABASES;
EXIT;

# 基本のインポートコマンド
mysql -u root -p myapp_db < /tmp/backup.sql

# 文字コードを明示してインポートする（推奨）
mysql -u root -p --default-character-set=utf8mb4 myapp_db < /tmp/backup.sql

# MySQLに接続して確認する
mysql -u root -p

# データベースを選択してテーブル一覧を確認する
USE myapp_db;
SHOW TABLES;

# テーブルの行数を確認する（例: usersテーブル）
SELECT COUNT(*) FROM users;
EXIT;

+--------------------+
| Tables_in_myapp_db |
+--------------------+
| orders             |
| products           |
| users              |
+--------------------+
3 rows in set (0.00 sec)

# dumpファイル内のSET NAMESを確認する
grep 'SET NAMES' /tmp/backup.sql

/*!40101 SET NAMES utf8mb4 */;

# utf8mb4のdumpをインポートする場合
mysql -u root -p --default-character-set=utf8mb4 myapp_db < /tmp/backup.sql

# 古いシステムからのdumpでutf8の場合
mysql -u root -p --default-character-set=utf8 myapp_db < /tmp/backup.sql

# MySQLサーバーの文字コード設定を確認する
mysql -u root -p -e "SHOW VARIABLES LIKE 'character%';"

+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
+--------------------------+---------+

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

ERROR 1049 (42000): Unknown database 'myapp_db'

# データベースを作成してからインポートする
mysql -u root -p -e "CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"
mysql -u root -p --default-character-set=utf8mb4 myapp_db < /tmp/backup.sql

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x8A...' for column 'comment' at row 1

# テーブルの文字コードをutf8mb4に変換する
mysql -u root -p myapp_db -e "ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"

ERROR 1215 (HY000): Cannot add foreign key constraint

# foreign_key_checksを一時無効にしてインポートする
mysql -u root -p myapp_db << 'SQL'
SET foreign_key_checks=0;
SOURCE /tmp/backup.sql;
SET foreign_key_checks=1;
SQL

# max_allowed_packetを拡張してインポートする
mysql -u root -p --max_allowed_packet=1G --default-character-set=utf8mb4 myapp_db < /tmp/backup_large.sql

[mysqld]
max_allowed_packet=1G
net_read_timeout=600
net_write_timeout=600