【Docker】MySQL CLI完全ガイド｜接続3パターン・mysqldump実戦・CSV取り込み・文字化け回避・mycli/usql・SSHトンネル・エラー対処

# 対話モード
docker compose exec db mysql -u root -p

# データベース指定＋ワンライナー
docker compose exec db mysql -u root -proot mydb -e "SHOW TABLES"

# ストリーム入力（SQLファイル流し込み）
docker compose exec -T db mysql -u root -proot mydb < schema.sql

# MySQL 8.0 以降ならcharset明示
docker compose exec db mysql --default-character-set=utf8mb4 -u root -p mydb

services:
  db:
    image: mysql:8.0
    ports:
      - "3306:3306"         # ホストの3306に公開
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: mydb

# 事前にmysql-clientをインストール（Mac）
brew install mysql-client

# ホストから接続（-h 必須、-P 付きポート指定）
mysql -h 127.0.0.1 -P 3306 -u root -p

# 注意：MySQLは"localhost"指定だとUnix socket探しに行く
# → 127.0.0.1 や IPアドレス指定が必須

# スクリプトでパスワードをenv経由
export MYSQL_PWD=root
mysql -u root mydb -e "SELECT * FROM users"

# Docker内部で使う場合
docker compose exec -T db bash -c \
  "MYSQL_PWD=root mysql -u root mydb -e 'SELECT COUNT(*) FROM users'"

# 警告抑制（警告メッセージがうざい場合）
export MYSQL_PWD=root
mysql -u root 2>/dev/null

# ~/.my.cnf（chmod 600必須）
[client]
user=root
password=root
host=127.0.0.1
port=3306
default-character-set=utf8mb4

chmod 600 ~/.my.cnf

# 以降パスワード不要
mysql
mysql -e "SHOW DATABASES"

# 別ファイルを指定したい場合
mysql --defaults-file=./my.cnf -e "SELECT 1"

services:
  db:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mysql_root
      MYSQL_DATABASE: mydb
    secrets:
      - mysql_root

secrets:
  mysql_root:
    file: ./secrets/mysql_root.txt      # .gitignore対象

# もっとも一般的なやり方
docker compose exec -T db mysql -u root -proot mydb < schema.sql

# gz圧縮ファイルからそのまま
gunzip -c backup.sql.gz | docker compose exec -T db mysql -u root -proot mydb

# 複数ファイルを連結して流す
cat 01_schema.sql 02_seed.sql 03_views.sql | \
  docker compose exec -T db mysql -u root -proot mydb

# ファイルをコンテナにコピーしてから
docker compose cp ./schema.sql db:/tmp/
docker compose exec db mysql -u root -proot mydb -e "source /tmp/schema.sql"

# 対話モード内で
docker compose exec db mysql -u root -proot mydb
mysql> source /tmp/schema.sql
mysql> \. /tmp/schema.sql          # ショートカット

#!/usr/bin/env bash
# scripts/setup-db.sh
set -euo pipefail

docker compose exec -T db mysql -u root -proot <<EOF
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4;
USE mydb;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255) UNIQUE
) ENGINE=InnoDB;

INSERT INTO users (name, email) VALUES ('Taro', 'taro@example.com');
EOF

echo "Database initialized successfully"

# 基本（InnoDB＋ロックフリー＋ルーチン・トリガー込み）
docker compose exec db mysqldump \
  -u root -proot \
  --single-transaction \
  --routines --triggers --events \
  --set-gtid-purged=OFF \
  mydb > backup_$(date +%Y%m%d).sql

# 圧縮（サイズ削減）
docker compose exec db mysqldump -u root -proot --single-transaction mydb | \
  gzip > backup_$(date +%Y%m%d).sql.gz

# 全DB一括（運用サーバー向け）
docker compose exec db mysqldump -u root -proot \
  --single-transaction --all-databases \
  --routines --triggers --events \
  > all_backup.sql

# スキーマのみ（データなし）
docker compose exec db mysqldump -u root -proot --no-data mydb > schema.sql

# データのみ（スキーマなし）
docker compose exec db mysqldump -u root -proot --no-create-info mydb > data.sql

# 特定テーブルのみ
docker compose exec db mysqldump -u root -proot mydb users orders > partial.sql

# WHERE条件付き（最近30日分だけ）
docker compose exec db mysqldump -u root -proot mydb events \
  --where="created_at >= NOW() - INTERVAL 30 DAY" > recent.sql

# 基本
docker compose exec -T db mysql -u root -proot mydb < backup.sql

# gzip圧縮ファイルから
gunzip -c backup.sql.gz | docker compose exec -T db mysql -u root -proot mydb

# 進捗表示したい場合（pv併用）
pv backup.sql | docker compose exec -T db mysql -u root -proot mydb

# 大規模データの高速化設定（インポート中のみ無効化）
docker compose exec -T db mysql -u root -proot <<EOF
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
SET AUTOCOMMIT=0;
source /path/to/backup.sql;
COMMIT;
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;
EOF

# CSVファイルをコンテナにコピー
docker compose cp ./users.csv db:/tmp/

# コンテナ内からLOAD DATA
docker compose exec db mysql -u root -proot mydb <<EOF
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
IGNORE 1 ROWS                   -- ヘッダー行スキップ
(name, email, created_at);
EOF

# LOCALキーワードでホストから直接（--local-infile=1が必要）
docker compose exec -T db mysql --local-infile=1 -u root -proot mydb <<EOF
LOAD DATA LOCAL INFILE '/path/inside/container/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;
EOF

# ファイル名"users.csv"→"users"テーブルに自動マップ
docker compose cp ./users.csv db:/tmp/
docker compose exec db mysqlimport \
  --fields-terminated-by="," \
  --lines-terminated-by="\n" \
  --ignore-lines=1 \
  -u root -proot mydb /tmp/users.csv

docker compose exec db mysql -u root -proot mydb <<EOF
SELECT * FROM users
INTO OUTFILE '/tmp/users_export.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\\n';
EOF

# ホストへ取り出し
docker compose cp db:/tmp/users_export.csv ./

# --batch で TSV形式出力
docker compose exec db mysql -u root -proot mydb --batch -e \
  "SELECT name, email FROM users" | \
  sed 's/\t/,/g' > users_export.csv

# もう少し安全（ヘッダー付き＋CSV escape）
docker compose exec db mysql -u root -proot mydb --batch --quick \
  -e "SELECT name, email FROM users" \
  | python3 -c \
  "import csv, sys; w=csv.writer(sys.stdout); [w.writerow(line.split(chr(9))) for line in sys.stdin]" \
  > users_export.csv

services:
  db:
    image: mysql:8.0
    command: >
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
      --skip-character-set-client-handshake
    environment:
      MYSQL_DATABASE: mydb
      MYSQL_ROOT_PASSWORD: root
      LANG: C.UTF-8
      LC_ALL: C.UTF-8
    volumes:
      - db_data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/conf.d/custom.cnf:ro

volumes:
  db_data:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake

# 現在の文字コード設定を確認
docker compose exec db mysql -u root -proot -e \
  "SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%';"

# 結果例（全てutf8mb4になっているべき）
# character_set_client     utf8mb4
# character_set_connection utf8mb4
# character_set_database   utf8mb4
# character_set_results    utf8mb4
# character_set_server     utf8mb4

# インストール（Mac/Linux）
brew install mycli            # macOS
pip install mycli              # Python環境

# Docker MySQLに接続（ホスト経由）
mycli -h 127.0.0.1 -P 3306 -u root -p mydb

# 機能
# - スマート自動補完（テーブル名・カラム名・キーワード）
# - シンタックスハイライト
# - マルチライン編集
# - 履歴検索（Ctrl+R）

# インストール
brew install xo/xo/usql       # macOS

# URL形式で接続
usql mysql://root:root@127.0.0.1:3306/mydb
usql postgres://user:pass@localhost/db
usql sqlite3:./local.db

# コマンド
\d users          # テーブル構造表示（psql風）
\dt               # テーブル一覧
\dn               # データベース一覧
\i schema.sql     # SQLファイル実行
\q                # 終了

# 1. 拡張インストール
#    Database Client (cweijan.vscode-mysql-client2)

# 2. 左サイドバーのDatabaseアイコン → Add Connection
#    Type: MySQL
#    Host: 127.0.0.1
#    Port: 3306
#    User: root
#    Password: root

# 3. 機能
#    - テーブル一覧／スキーマGUI表示
#    - .sql ファイルから直接実行（Ctrl+Alt+E）
#    - 結果をCSV/JSONエクスポート
#    - ER図生成

#!/usr/bin/env bash
# scripts/migrate.sh
set -euo pipefail

DB_NAME="${DB_NAME:-mydb}"
DB_USER="${DB_USER:-root}"
DB_PASS="${DB_PASS:-root}"
MIGRATIONS_DIR="./migrations"

# Docker composeが起動しているか確認
if ! docker compose ps db | grep -q "running"; then
  echo "Starting DB container..."
  docker compose up -d db
  # healthcheck待機
  until docker compose exec db mysqladmin ping -h localhost --silent; do
    echo "Waiting for DB..."
    sleep 2
  done
fi

# migrations ディレクトリのSQLを順次実行
for file in $(ls $MIGRATIONS_DIR/*.sql | sort); do
  echo "Applying: $file"
  docker compose exec -T db mysql \
    -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$file"
done

echo "Migration complete"

#!/usr/bin/env bash
# scripts/backup-daily.sh
set -euo pipefail

BACKUP_DIR="./backups"
DATE=$(date +%Y%m%d_%H%M)
FILENAME="$BACKUP_DIR/mydb_$DATE.sql.gz"

mkdir -p $BACKUP_DIR

# ダンプ＋gzip
docker compose exec -T db mysqldump \
  --single-transaction \
  --routines --triggers --events \
  -u root -proot mydb | gzip > "$FILENAME"

# 7日以上前のバックアップを削除
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup saved: $FILENAME"

# トンネル開設（ローカル13306 → サーバーSSH経由 → DBの3306）
ssh -N -L 13306:localhost:3306 user@prod-server.example.com &

# ローカルからmysqlクライアントで接続
mysql -h 127.0.0.1 -P 13306 -u root -p

# mycliで接続（より快適）
mycli -h 127.0.0.1 -P 13306 -u root -p

# 終了時（バックグラウンドプロセス停止）
kill %1

# ~/.ssh/config
Host prod-db
  HostName prod-server.example.com
  User deploy
  IdentityFile ~/.ssh/deploy_key
  LocalForward 13306 localhost:3306

# 以降
ssh -N prod-db &
mysql -h 127.0.0.1 -P 13306 -u root -p

# 原因1: パスワード間違い
# 原因2: 接続元ホスト制限（root@'localhost'のみ許可等）
# 原因3: MySQL 8のデフォルト認証プラグイン

# ユーザー一覧と接続元を確認
docker compose exec db mysql -u root -proot <<EOF
SELECT user, host, plugin FROM mysql.user;
EOF

# 外部接続も許可（'%' ワイルドカード）
docker compose exec db mysql -u root -proot <<EOF
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
FLUSH PRIVILEGES;
EOF

# NG：パイプ／リダイレクト時に-tが邪魔
docker compose exec db mysql -u root -p mydb < backup.sql

# OK：-T で TTY無効化
docker compose exec -T db mysql -u root -proot mydb < backup.sql

# 注：docker run / docker exec も同じく -i と -t の使い分けに注意