MySQL

MySQLは、世界で最も人気の高いオープンソースリレーショナルデータベース管理システム（RDBMS）です。高性能、信頼性、使いやすさを兼ね備え、Web アプリケーションから大規模エンタープライズシステムまで幅広く利用されています。

主な特徴

高性能とスケーラビリティ

• マルチバージョン並行性制御（MVCC）
• InnoDBストレージエンジンによる高速トランザクション処理
• MySQL Clusterによる分散処理とオートシャーディング
• 読み書き分離レプリケーション

エンタープライズ機能

• マスター・スレーブレプリケーション
• Group Replicationによる自動フェイルオーバー
• 透過的データ暗号化（TDE）
• 行レベルセキュリティ

開発者フレンドリー

• JSON データ型とドキュメント操作
• 地理空間データサポート（GIS）
• パーティショニングとシャーディング
• 豊富なコネクター（PHP、Python、Java等）

インストール

Ubuntu/Debian

# MySQL APTリポジトリの追加
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
sudo apt update

# MySQL 8.0のインストール
sudo apt install mysql-server

# セキュア設定
sudo mysql_secure_installation

CentOS/RHEL

# MySQL YUMリポジトリの追加
sudo dnf install https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm

# MySQL 8.0のインストール
sudo dnf install mysql-community-server

# サービス開始
sudo systemctl enable mysqld
sudo systemctl start mysqld

# 初期rootパスワード確認
sudo grep 'temporary password' /var/log/mysqld.log

Docker

# MySQL 8.0の起動
docker run --name mysql-db \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppass \
  -p 3306:3306 \
  -d mysql:8.0

# データの永続化
docker run --name mysql-db \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -v mysql_data:/var/lib/mysql \
  -p 3306:3306 \
  -d mysql:8.0

基本設定

my.cnf設定

[mysqld]
# 基本設定
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid

# 接続設定
bind-address = 0.0.0.0
max_connections = 200
max_connect_errors = 10000

# メモリ設定
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
key_buffer_size = 256M
table_open_cache = 2000
thread_cache_size = 16

# パフォーマンス設定
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_open_files = 300

# レプリケーション設定
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

# セキュリティ設定
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
local_infile = 0

# 文字セット
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

基本操作

データベース管理

-- データベース作成
CREATE DATABASE myapp 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- ユーザー作成と権限付与
CREATE USER 'appuser'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

-- テーブル作成
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL,
    profile JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

JSON操作

-- JSONデータの挿入
INSERT INTO users (username, email, profile) VALUES
('john_doe', '[email protected]', '{"age": 30, "city": "Tokyo", "skills": ["MySQL", "Python"]}');

-- JSON検索
SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.city') = 'Tokyo';
SELECT * FROM users WHERE JSON_CONTAINS(profile, '"MySQL"', '$.skills');
SELECT * FROM users WHERE profile->'$.age' > 25;

-- JSON更新
UPDATE users 
SET profile = JSON_SET(profile, '$.age', 31, '$.last_login', NOW())
WHERE username = 'john_doe';

-- JSON集約
SELECT 
    profile->>'$.city' as city,
    COUNT(*) as user_count,
    JSON_ARRAYAGG(username) as usernames
FROM users 
GROUP BY profile->>'$.city';

パフォーマンス最適化

インデックス戦略

-- 基本インデックス
CREATE INDEX idx_users_email ON users(email);

-- 複合インデックス
CREATE INDEX idx_users_city_age ON users(
    (CAST(profile->>'$.city' AS CHAR(50))),
    (CAST(profile->>'$.age' AS UNSIGNED))
);

-- プレフィックスインデックス
CREATE INDEX idx_users_email_prefix ON users(email(10));

-- 関数ベースインデックス
CREATE INDEX idx_users_upper_username ON users((UPPER(username)));

-- JSON仮想カラムインデックス
ALTER TABLE users 
ADD COLUMN city VARCHAR(50) AS (profile->>'$.city') VIRTUAL,
ADD INDEX idx_virtual_city (city);

クエリ最適化

-- EXPLAIN による実行計画確認
EXPLAIN FORMAT=JSON 
SELECT * FROM users WHERE profile->>'$.city' = 'Tokyo';

-- 統計情報の更新
ANALYZE TABLE users;

-- クエリキャッシュ（MySQL 5.7まで）
-- MySQL 8.0ではQuery Cacheは削除されています

-- パフォーマンススキーマ活用
SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000000 as SUM_TIMER_WAIT_SEC
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC 
LIMIT 10;

レプリケーション設定

マスター設定

# my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
binlog_do_db = myapp
expire_logs_days = 7

-- レプリケーションユーザー作成
CREATE USER 'replicator'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

-- マスターステータス確認
SHOW MASTER STATUS;

スレーブ設定

# my.cnf
[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1

-- スレーブ設定
CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'replica_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- レプリケーション開始
START SLAVE;

-- レプリケーション状況確認
SHOW SLAVE STATUS\G

Group Replication設定

-- Group Replication設定
SET GLOBAL group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
SET GLOBAL group_replication_start_on_boot = OFF;
SET GLOBAL group_replication_local_address = '192.168.1.10:33061';
SET GLOBAL group_replication_group_seeds = '192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061';

-- Group Replication開始
START GROUP_REPLICATION;

-- 状態確認
SELECT * FROM performance_schema.replication_group_members;

バックアップ戦略

論理バックアップ

# mysqldump（単一データベース）
mysqldump -u root -p --single-transaction --routines --triggers myapp > myapp_backup.sql

# 全データベース
mysqldump -u root -p --all-databases --single-transaction > all_databases_backup.sql

# 圧縮バックアップ
mysqldump -u root -p --single-transaction myapp | gzip > myapp_backup.sql.gz

# 復元
mysql -u root -p myapp < myapp_backup.sql

物理バックアップ

# MySQL Enterprise Backup（商用版）
mysqlbackup --user=root --password=password \
  --backup-dir=/backup/full \
  backup-and-apply-log

# Percona XtraBackup（オープンソース）
xtrabackup --backup --user=root --password=password \
  --target-dir=/backup/full

# 復元
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

Point-in-Time Recovery

# バイナリログから特定時点まで復元
mysqlbinlog --start-datetime="2024-01-15 09:00:00" \
           --stop-datetime="2024-01-15 10:00:00" \
           mysql-bin.000001 | mysql -u root -p myapp

監視とメンテナンス

基本監視クエリ

-- 接続状況確認
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE COMMAND != 'Sleep';

-- テーブルサイズ確認
SELECT 
    table_schema as 'Database',
    table_name as 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) as 'Size (MB)'
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

-- インデックス使用状況
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write,
    count_fetch,
    count_insert,
    count_update,
    count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY count_read DESC;

パフォーマンス統計

-- スロークエリ確認
SELECT 
    query_sample_text,
    exec_count,
    total_latency,
    avg_latency,
    rows_examined_avg
FROM sys.statements_with_full_table_scans 
ORDER BY total_latency DESC 
LIMIT 10;

-- ロック状況確認
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

高度な機能

パーティショニング

-- レンジパーティション
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) 
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- ハッシュパーティション
CREATE TABLE user_data (
    user_id INT NOT NULL,
    data TEXT,
    PRIMARY KEY (user_id)
)
PARTITION BY HASH(user_id)
PARTITIONS 4;

フルテキスト検索

-- フルテキストインデックス作成
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT KEY ft_title_content (title, content)
) ENGINE=InnoDB;

-- フルテキスト検索実行
SELECT *, MATCH(title, content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE) as score
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- ブール検索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

地理空間データ

-- 地理空間テーブル作成
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL,
    SPATIAL INDEX (location)
);

-- 地理空間データ挿入
INSERT INTO locations (name, location) VALUES
('Tokyo', ST_PointFromText('POINT(139.7670 35.6814)')),
('Osaka', ST_PointFromText('POINT(135.5023 34.6937)'));

-- 距離計算
SELECT 
    name,
    ST_Distance_Sphere(location, ST_PointFromText('POINT(139.7670 35.6814)')) / 1000 as distance_km
FROM locations
ORDER BY distance_km;

MySQLは、その使いやすさ、高性能、豊富な機能により、世界中の多くのWebアプリケーションやエンタープライズシステムで利用されています。継続的な開発により、最新のアプリケーション要件に対応し続けています。