MySQL実践操作完全ガイド：データベース管理の基礎から応用まで【2025年版】

2025年7月24日 テクノロジー
MySQL データベース SQL データベース管理 サーバー管理 バックアップ セキュリティ 実践ガイド

目次

MySQLとは

MySQLは世界で最も人気のあるオープンソースのリレーショナルデータベース管理システム（RDBMS）です。Web アプリケーションの基盤として広く使用されており、高速性、信頼性、使いやすさで知られています。

MySQLの特徴

  • 高いパフォーマンス：大量のデータを効率的に処理
  • クロスプラットフォーム：Windows、Linux、macOSで動作
  • 豊富な機能：トランザクション、レプリケーション、パーティション
  • コミュニティサポート：活発な開発コミュニティ
  • 商用サポート：Oracle社による企業向けサポート

主要なストレージエンジン

エンジン 特徴 用途
InnoDB トランザクション対応、外部キー制約 OLTP、Webアプリケーション
MyISAM 高速読み取り、フルテキスト検索 読み取り中心のアプリケーション
Memory メモリ上にデータ保存 一時的なデータ、キャッシュ

ユーザー管理とログイン

MySQLへのログイン

MySQLサーバーにアクセスするための基本的なログイン方法を説明します。

基本的なログイン

# 一般ユーザーでログイン
mysql -u user -p

# rootユーザーでログイン
mysql -u root -p

# ホストを指定してログイン
mysql -h localhost -u user -p

# データベースを指定してログイン
mysql -u user -p database_name

ユーザー管理

MySQLのユーザー管理は、セキュリティの観点から非常に重要です。適切な権限設定を行いましょう。

ユーザーの確認

# 既存ユーザーの一覧表示
SELECT user, host FROM mysql.user;

# 現在のユーザー情報表示
SELECT USER(), CURRENT_USER();

ユーザーの作成

# 新しいユーザーを作成
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

# リモートアクセス可能なユーザーを作成
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'strong_password';

権限管理

ユーザーに適切な権限を付与することで、セキュリティを保ちながら必要な操作を許可できます。

権限の確認

# 特定ユーザーの権限表示
SHOW GRANTS FOR 'user'@'localhost';

# 現在のユーザーの権限表示
SHOW GRANTS;

権限の付与

# 特定データベースのすべての権限を付与
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

# 特定テーブルの選択権限のみ付与
GRANT SELECT ON database_name.table_name TO 'user'@'localhost';

# 複数の権限を付与
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'user'@'localhost';

# 権限の変更を反映
FLUSH PRIVILEGES;

パスワード変更

# ユーザーのパスワード変更
SET PASSWORD FOR 'user'@'localhost' = 'new_password';

# 自分のパスワード変更
SET PASSWORD = 'new_password';
セキュリティのポイント：
  • 強固なパスワードを使用する
  • 必要最小限の権限のみを付与する
  • 不要になったユーザーは削除する
  • 定期的にユーザー権限を見直す

データベース操作

データベースの作成と削除

MySQLでのデータベース管理の基本操作について説明します。

データベース作成

# 基本的なデータベース作成
CREATE DATABASE my_database;

# 文字コードを指定してデータベース作成
CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 存在しない場合のみ作成
CREATE DATABASE IF NOT EXISTS my_database;

データベース一覧表示

# すべてのデータベースを表示
SHOW DATABASES;

# パターンマッチでデータベースを検索
SHOW DATABASES LIKE 'my_%';

データベース選択

# 使用するデータベースを選択
USE my_database;

# 現在選択中のデータベースを確認
SELECT DATABASE();

データベース削除

# データベースを削除
DROP DATABASE my_database;

# 存在する場合のみ削除
DROP DATABASE IF EXISTS my_database;

バックアップとリストア

データベースのバックアップは定期的に行う重要な作業です。

データベースバックアップ

# 単一データベースのバックアップ
mysqldump -u root -p my_database > backup_database.sql

# すべてのデータベースをバックアップ
mysqldump -u root -p --all-databases > full_backup.sql

# 構造のみバックアップ（データなし）
mysqldump -u root -p --no-data my_database > schema_only.sql

# データのみバックアップ（構造なし）
mysqldump -u root -p --no-create-info my_database > data_only.sql

バックアップからリストア

# バックアップファイルからリストア
mysql -u root -p my_database < backup_database.sql

# 新しいデータベースを作成してリストア
mysql -u root -p -e "CREATE DATABASE restored_db;"
mysql -u root -p restored_db < backup_database.sql

テーブル操作

テーブルの作成

適切なテーブル設計は、パフォーマンスとデータ整合性の基盤となります。

基本的なテーブル作成

# ユーザーテーブルの作成例
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

外部キー制約付きテーブル

# 注文テーブル（ユーザーテーブルとの関連）
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

テーブル情報の確認

# データベース内のテーブル一覧表示
SHOW TABLES;

# テーブル構造の確認
DESCRIBE users;
DESC users;

# 詳細なテーブル情報表示
SHOW CREATE TABLE users;

# テーブルのカラム情報表示
SHOW COLUMNS FROM users;

テーブル構造の変更

カラムの追加・変更

# カラム追加
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

# カラム変更
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;

# カラム名変更
ALTER TABLE users CHANGE COLUMN phone phone_number VARCHAR(20);

# カラム削除
ALTER TABLE users DROP COLUMN phone_number;

インデックス管理

インデックスは検索性能を向上させる重要な要素です。

インデックスの作成

# 単一カラムインデックス
CREATE INDEX idx_email ON users(email);

# 複合インデックス
CREATE INDEX idx_status_date ON orders(status, order_date);

# ユニーク制約付きインデックス
CREATE UNIQUE INDEX idx_username ON users(username);

# 既存テーブルにユニーク制約を追加
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

インデックスの確認と削除

# テーブルのインデックス表示
SHOW INDEX FROM users;

# インデックス削除
DROP INDEX idx_email ON users;

テーブル削除

# テーブル削除
DROP TABLE orders;

# 存在する場合のみ削除
DROP TABLE IF EXISTS temp_table;

データ操作（CRUD）

データの挿入（CREATE）

テーブルにデータを追加する方法を詳しく説明します。

基本的なデータ挿入

# 単一レコード挿入
INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john@example.com', 'hashed_password');

# 複数レコード一括挿入
INSERT INTO users (username, email, password) VALUES
('alice', 'alice@example.com', 'password1'),
('bob', 'bob@example.com', 'password2'),
('charlie', 'charlie@example.com', 'password3');

重複を無視した挿入

# 重複がある場合は無視
INSERT IGNORE INTO users (username, email, password)
VALUES ('existing_user', 'existing@example.com', 'password');

# 重複がある場合は更新
INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john_new@example.com', 'new_password')
ON DUPLICATE KEY UPDATE
email = VALUES(email),
password = VALUES(password);

データの取得（READ）

効率的なデータ取得方法とクエリの最適化について説明します。

基本的なデータ取得

# 全データ取得
SELECT * FROM users;

# 特定カラムのみ取得
SELECT id, username, email FROM users;

# 件数制限
SELECT * FROM users LIMIT 10;

# オフセット指定（ページネーション）
SELECT * FROM users LIMIT 10 OFFSET 20;

条件付きデータ取得

# 単一条件
SELECT * FROM users WHERE username = 'john_doe';

# 複数条件
SELECT * FROM users WHERE created_at >= '2025-01-01' AND status = 'active';

# 範囲指定
SELECT * FROM users WHERE id BETWEEN 10 AND 20;

# リスト条件
SELECT * FROM users WHERE id IN (1, 5, 10, 15);

# パターンマッチング
SELECT * FROM users WHERE email LIKE '%@gmail.com';

# NULL チェック
SELECT * FROM users WHERE phone IS NULL;

並び替えとグループ化

# 昇順ソート
SELECT * FROM users ORDER BY created_at ASC;

# 降順ソート
SELECT * FROM users ORDER BY created_at DESC;

# 複数カラムでソート
SELECT * FROM users ORDER BY status ASC, created_at DESC;

# データ件数取得
SELECT COUNT(*) FROM users;

# グループ化して集計
SELECT status, COUNT(*) as count FROM users GROUP BY status;

# 条件付きグループ化
SELECT status, COUNT(*) as count FROM users
GROUP BY status
HAVING count > 5;

データの更新（UPDATE）

# 単一レコード更新
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;

# 複数カラム更新
UPDATE users
SET email = 'updated@example.com',
    updated_at = NOW()
WHERE username = 'john_doe';

# 条件付き一括更新
UPDATE users
SET status = 'inactive'
WHERE created_at < '2024-01-01';

データの削除（DELETE）

# 単一レコード削除
DELETE FROM users WHERE id = 1;

# 条件付き削除
DELETE FROM users WHERE status = 'inactive';

# 範囲削除
DELETE FROM users WHERE id BETWEEN 10 AND 15;

# リスト削除
DELETE FROM users WHERE id IN (6, 7, 8);

# 全データ削除（構造は残す）
DELETE FROM users;

# 全データ削除（高速、AUTO_INCREMENTリセット）
TRUNCATE TABLE users;
データ操作時の注意点：
  • UPDATE、DELETE文では必ずWHERE句を使用する
  • 重要なデータ操作前にはバックアップを取る
  • トランザクションを使用して操作の安全性を確保する
  • 大量データの操作は分割して実行する

高度なクエリ操作

結合（JOIN）操作

複数テーブルからデータを取得する結合操作について説明します。

内部結合（INNER JOIN）

# ユーザーと注文の結合
SELECT u.username, u.email, o.order_date, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

# 3つのテーブルを結合
SELECT u.username, o.order_date, oi.product_name, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id;

外部結合（LEFT/RIGHT JOIN）

# 左結合：すべてのユーザーを表示（注文がなくても）
SELECT u.username, u.email, o.order_date, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

# 右結合：すべての注文を表示（ユーザーが削除されていても）
SELECT u.username, u.email, o.order_date, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

サブクエリ

# 平均注文金額より高い注文
SELECT * FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

# 注文履歴があるユーザー
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

# 最新注文日を持つユーザー
SELECT u.*,
       (SELECT MAX(order_date) FROM orders o WHERE o.user_id = u.id) as last_order
FROM users u;

集計関数とウィンドウ関数

基本的な集計関数

# 各種統計情報
SELECT
    COUNT(*) as total_users,
    COUNT(DISTINCT email) as unique_emails,
    MIN(created_at) as first_user,
    MAX(created_at) as latest_user
FROM users;

# 月別注文集計
SELECT
    DATE_FORMAT(order_date, '%Y-%m') as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as avg_order_value
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

ウィンドウ関数（MySQL 8.0以降）

# 行番号付与
SELECT
    username,
    email,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num
FROM users;

# ランキング
SELECT
    user_id,
    total_amount,
    RANK() OVER (ORDER BY total_amount DESC) as sales_rank
FROM orders;

# 累積合計
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) as running_total
FROM orders;

文字列・日付関数

文字列操作

# 文字列結合
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;

# 文字列置換
SELECT REPLACE(email, '@gmail.com', '@company.com') as work_email FROM users;

# 文字列抽出
SELECT
    email,
    SUBSTRING(email, 1, LOCATE('@', email) - 1) as username_part
FROM users;

# 大文字・小文字変換
SELECT UPPER(username), LOWER(email) FROM users;

日付操作

# 現在日時
SELECT NOW(), CURDATE(), CURTIME();

# 日付計算
SELECT
    created_at,
    DATE_ADD(created_at, INTERVAL 30 DAY) as expire_date,
    DATEDIFF(NOW(), created_at) as days_since_creation
FROM users;

# 日付フォーマット
SELECT
    order_date,
    DATE_FORMAT(order_date, '%Y年%m月%d日') as formatted_date,
    YEAR(order_date) as order_year,
    MONTH(order_date) as order_month
FROM orders;

バックアップとメンテナンス

定期バックアップ戦略

データの安全性を確保するための包括的なバックアップ戦略について説明します。

完全バックアップ

# 全データベースの完全バックアップ
mysqldump -u root -p --all-databases --single-transaction --routines --triggers > full_backup_$(date +%Y%m%d_%H%M%S).sql

# 特定データベースの完全バックアップ
mysqldump -u root -p --single-transaction --routines --triggers my_database > my_database_backup_$(date +%Y%m%d).sql

# 圧縮バックアップ
mysqldump -u root -p --single-transaction my_database | gzip > my_database_backup_$(date +%Y%m%d).sql.gz

増分バックアップ

# バイナリログを有効にする（my.cnfに設定）
# log-bin=mysql-bin
# server-id=1

# バイナリログのバックアップ
mysqlbinlog mysql-bin.000001 > incremental_backup.sql

# 特定時点からのバイナリログ
mysqlbinlog --start-datetime="2025-01-01 00:00:00" mysql-bin.000001 > incremental_from_date.sql

データベースメンテナンス

テーブル最適化

# テーブルの最適化
OPTIMIZE TABLE users;

# すべてのテーブルを最適化
OPTIMIZE TABLE users, orders, order_items;

# テーブル修復
REPAIR TABLE users;

# テーブル分析（統計情報更新）
ANALYZE TABLE users;

ログ管理

# バイナリログの確認
SHOW BINARY LOGS;

# バイナリログの削除（3日前まで）
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

# エラーログの確認
SHOW GLOBAL VARIABLES LIKE 'log_error';

# スロークエリログの有効化
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

パフォーマンス監視

システム状態の確認

# 現在の接続数
SHOW PROCESSLIST;

# サーバー状態確認
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

# データベースサイズ確認
SELECT
    table_schema AS 'database_name',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'size_mb'
FROM information_schema.tables
GROUP BY table_schema;

# テーブルサイズ確認
SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'size_mb'
FROM information_schema.tables
WHERE table_schema = 'my_database'
ORDER BY (data_length + index_length) DESC;

セキュリティ強化

セキュリティ設定

# 匿名ユーザーの削除
DELETE FROM mysql.user WHERE User='';

# testデータベースの削除
DROP DATABASE IF EXISTS test;

# rootユーザーのリモートアクセス禁止
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

# 設定の反映
FLUSH PRIVILEGES;

ベストプラクティス

  • 定期バックアップ：日次の完全バックアップと増分バックアップを組み合わせる
  • バックアップテスト：定期的にリストアテストを実行する
  • 監視：ディスク使用量、接続数、スロークエリを監視する
  • セキュリティ：不要なユーザーや権限を定期的にレビューする
  • ログ保持：適切なログローテーション設定を行う

まとめ

このガイドでは、MySQLの基本的な操作から応用的な管理手法まで、実践的な内容を体系的に解説しました。データベース管理は継続的な学習と実践が重要です。

重要なポイント

  • 基礎の徹底：ユーザー管理と権限設定はセキュリティの基盤
  • 適切な設計：テーブル構造とインデックス設計がパフォーマンスに直結
  • 安全な操作：データ操作時は必ず条件句を使用し、バックアップを取る
  • 継続的メンテナンス：定期的な最適化と監視が安定運用の鍵
  • バックアップ戦略：完全バックアップと増分バックアップの組み合わせ

MySQLは豊富な機能を持つ強力なデータベースシステムです。基本をしっかりと理解し、段階的に高度な機能を習得していくことで、効率的で安全なデータベース運用が可能になります。