この記事の要点

接続は mysql -u ユーザ -p。リモートは -h ホスト -P ポート を追加 一覧系は SHOW DATABASES / SHOW TABLES / DESCRIBE テーブル 管理は CREATE DATABASE / GRANT / FLUSH PRIVILEGES。本番では GRANT の対象を最小権限に バックアップは mysqldump -u root -p db > dump.sql、リストアは SOURCE dump.sql メンテは mysqladmin status と mysqlcheck --auto-repair

接続コマンド

# 基本: ユーザー指定で対話接続 (パスワードはプロンプト)
mysql -u root -p

# DB を指定して接続
mysql -u app -p mydb

# リモート
mysql -h db.example.com -P 3306 -u app -p mydb

# ソケット接続 (Unix)
mysql -S /var/run/mysqld/mysqld.sock -u root -p

# パスワードを直接指定 (履歴に残るので推奨しない)
mysql -u root -pSecret123

# SQL ファイルを実行
mysql -u root -p mydb < init.sql

# 単発クエリ
mysql -u root -p -e "SHOW DATABASES;"

SHOW 系コマンド (一覧表示)

-- データベース一覧
SHOW DATABASES;

-- 現在のデータベース確認
SELECT DATABASE();

-- データベースを選択
USE mydb;

-- テーブル一覧
SHOW TABLES;

-- カラム一覧
SHOW COLUMNS FROM users;
DESCRIBE users;
DESC users;

-- インデックス一覧
SHOW INDEX FROM users;

-- テーブル定義 (CREATE 文)
SHOW CREATE TABLE users;

-- 実行中スレッド
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- ユーザー一覧
SELECT user, host FROM mysql.user;

-- ストレージエンジン一覧
SHOW ENGINES;

-- 変数 (設定)
SHOW VARIABLES LIKE 'max_connections';

-- ステータス
SHOW STATUS LIKE 'Threads_connected';

-- 文字コード
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

データベース作成・削除

-- 作成 (UTF-8 推奨)
CREATE DATABASE mydb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- 存在しなければ作成
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4;

-- 削除
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

テーブル作成・変更

CREATE TABLE users (
    id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email     VARCHAR(255) NOT NULL UNIQUE,
    name      VARCHAR(100) NOT NULL,
    age       INT,
    status    ENUM('active', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- カラム追加
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN birthday DATE AFTER age;

-- カラム変更
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;
ALTER TABLE users CHANGE old_name new_name VARCHAR(100);

-- カラム削除
ALTER TABLE users DROP COLUMN phone;

-- インデックス追加・削除
CREATE INDEX idx_name ON users(name);
DROP INDEX idx_name ON users;

-- テーブル名変更
RENAME TABLE users TO members;

-- テーブル削除
DROP TABLE users;
TRUNCATE TABLE users;   -- 全データ削除 (AUTO_INCREMENT もリセット)

ユーザーと権限

-- ユーザー作成
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'app'@'localhost' IDENTIFIED BY 'StrongPass123!';

-- 権限付与 (最小権限が原則)
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';

-- 権限剥奪
REVOKE INSERT ON mydb.* FROM 'app'@'%';

-- 権限の反映
FLUSH PRIVILEGES;

-- 権限の確認
SHOW GRANTS FOR 'app'@'%';

-- パスワード変更
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass456!';

-- ユーザー削除
DROP USER 'app'@'%';

SELECT / INSERT / UPDATE / DELETE

-- SELECT
SELECT id, name FROM users WHERE age >= 20 ORDER BY id DESC LIMIT 10 OFFSET 0;

-- 集約
SELECT status, COUNT(*) AS cnt FROM users GROUP BY status HAVING cnt > 5;

-- JOIN
SELECT u.name, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.status = 'active';

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

-- 複数行 INSERT
INSERT INTO users (email, name) VALUES
    ('a@example.com', 'Taro'),
    ('b@example.com', 'Jiro');

-- 重複時更新 (UPSERT)
INSERT INTO users (id, email, name) VALUES (1, 'a@example.com', 'Taro')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- UPDATE
UPDATE users SET status = 'banned' WHERE id = 5;

-- DELETE
DELETE FROM users WHERE status = 'banned';

バックアップとリストア

# 単一 DB のダンプ
mysqldump -u root -p mydb > dump.sql

# 全 DB ダンプ
mysqldump -u root -p --all-databases > all.sql

# 構造のみ
mysqldump -u root -p --no-data mydb > schema.sql

# データのみ
mysqldump -u root -p --no-create-info mydb > data.sql

# 圧縮しながら
mysqldump -u root -p mydb | gzip > dump.sql.gz

# リストア (シェルから)
mysql -u root -p mydb < dump.sql

# 圧縮ファイルから直接
gunzip -c dump.sql.gz | mysql -u root -p mydb

# MySQL 内から
mysql> USE mydb;
mysql> SOURCE /path/to/dump.sql;

管理ツール

# サーバー状態
mysqladmin -u root -p status
mysqladmin -u root -p extended-status
mysqladmin -u root -p version

# プロセス
mysqladmin -u root -p processlist
mysqladmin -u root -p kill 12345

# シャットダウン
mysqladmin -u root -p shutdown

# パスワード変更
mysqladmin -u root -p password "NewPassword"

# テーブルチェック・修復
mysqlcheck -u root -p --all-databases
mysqlcheck -u root -p --auto-repair --optimize mydb

# テーブル統計の最適化
mysqlcheck -u root -p --analyze mydb

# クライアントのインポートツール
mysqlimport -u root -p mydb /path/to/users.txt

トランザクション

-- トランザクション開始
START TRANSACTION;
-- または BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- 確定
COMMIT;

-- ロールバック
ROLLBACK;

-- 自動コミット制御
SET autocommit = 0;   -- 手動コミットモード
SET autocommit = 1;   -- デフォルト

MySQL クライアント内部コマンド (\ から始まる)

コマンド	意味
\q / exit / quit	終了
\s	サーバー状態
\u dbname	DB 切り替え (USE と同じ)
\h	ヘルプ
\c	入力中のクエリをキャンセル
\G	結果を縦表示 (1 行ずつ見やすく)
\T file	出力をファイルに記録 (tee)
source file.sql	SQL ファイル実行

FAQ

Q: MySQL と MariaDB のコマンドは同じ？
A: 基本コマンドはほぼ同一。ストレージエンジンや JSON 関数の挙動など細部で差があります。SHOW・SELECT・mysqldump はそのまま使えます。

Q: パスワードを履歴に残さない方法は？
A: ~/.my.cnf に [client] password=... を書き chmod 600 するか、 mysql_config_editor を使います。

Q: 大量データを高速にインポートしたい
A: LOAD DATA INFILE が INSERT より圧倒的に速い。インポート前に SET autocommit=0; SET foreign_key_checks=0; で高速化できます。