mysql

MySQL / MariaDB

開発環境および本番環境で、MySQL と MariaDB のデータベースを管理、最適化し、安全を確保します。

使用する場面

• 成熟しており、幅広くサポートされているリレーショナルデータベースが必要な場合。
• スタックが MySQL 固有の機能や互換性に依存している場合（WordPress、Magento、多くの PHP フレームワークなど）。
• 読み取りスケーリングのために、ソースレプリカレプリケーションを設定する場合。
• 高スループットのトランザクション処理を行うワークロード向けに InnoDB をチューニングする場合。

前提条件

• Linux サーバー（Debian／Ubuntu または RHEL 系）または Docker。
• パッケージのインストールに必要な root または sudo アクセス。
• SQL の基礎知識。

インストールとセットアップ

# Debian / Ubuntu — MySQL 8
sudo apt update
sudo apt install -y mysql-server

# RHEL / Amazon Linux
sudo dnf install -y mysql-server
sudo systemctl enable --now mysqld

# Run the secure installation wizard
sudo mysql_secure_installation
# Prompts: set root password, remove anonymous users, disable remote root, remove test db

# Verify
mysql --version
sudo systemctl status mysql

初期ユーザーとデータベースのセットアップ

sudo mysql -u root -p

-- Create a database
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create an application user with strong auth
CREATE USER 'myapp'@'%' IDENTIFIED BY 'strong_password_here';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myapp'@'%';
FLUSH PRIVILEGES;

-- Verify
SHOW GRANTS FOR 'myapp'@'%';

mysql CLI リファレンス

# Connect
mysql -u myapp -p -h 127.0.0.1 mydb

# Execute a single statement
mysql -u myapp -p -e "SELECT COUNT(*) FROM orders;" mydb

# Import a SQL file
mysql -u myapp -p mydb < schema.sql

# Export query results to CSV
mysql -u myapp -p -e "SELECT * FROM users" mydb \
  | tr '\t' ',' > users.csv

-- Inside the mysql shell
SHOW DATABASES;
USE mydb;
SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users\G
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

設定のチューニング

/etc/mysql/mysql.conf.d/mysqld.cnf を編集します（RHEL では /etc/my.cnf）。

[mysqld]
# -- Networking --
bind-address           = 0.0.0.0
max_connections        = 300
wait_timeout           = 600
interactive_timeout    = 600

# -- InnoDB (most impactful settings) --
innodb_buffer_pool_size   = 4G          # ~70% of RAM on a dedicated server
innodb_buffer_pool_instances = 4        # 1 per GB of pool (up to 64)
innodb_log_file_size      = 1G
innodb_flush_log_at_trx_commit = 1      # 1 = ACID; 2 = faster, slight risk
innodb_flush_method       = O_DIRECT    # avoids double buffering on Linux
innodb_io_capacity        = 2000        # raise for SSD
innodb_io_capacity_max    = 4000

# -- Query cache (disabled in MySQL 8, use ProxySQL or app cache) --
# query_cache_type = 0

# -- Logging --
slow_query_log         = 1
slow_query_log_file    = /var/log/mysql/slow.log
long_query_time        = 1
log_error              = /var/log/mysql/error.log

# -- Binary log (required for replication) --
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800     # 7 days
sync_binlog            = 1

# -- Character set --
character-set-server   = utf8mb4
collation-server       = utf8mb4_unicode_ci

# Apply changes
sudo systemctl restart mysql

# Verify a setting at runtime
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

バックアップと復元

mysqldump による論理バックアップ

# Single database
mysqldump -u root -p --single-transaction --routines --triggers \
  mydb > /backups/mydb_$(date +%F).sql

# All databases
mysqldump -u root -p --all-databases --single-transaction \
  > /backups/all_$(date +%F).sql

# Compressed backup
mysqldump -u root -p --single-transaction mydb \
  | gzip > /backups/mydb_$(date +%F).sql.gz

# Restore
mysql -u root -p mydb < /backups/mydb_2025-01-15.sql

# Restore compressed
gunzip < /backups/mydb_2025-01-15.sql.gz | mysql -u root -p mydb

Percona XtraBackup による物理バックアップ

# Install
sudo apt install -y percona-xtrabackup-80

# Full backup
xtrabackup --backup --user=root --password=secret \
  --target-dir=/backups/full_$(date +%F)

# Prepare the backup (apply redo logs)
xtrabackup --prepare --target-dir=/backups/full_2025-01-15

# Restore (stop MySQL first)
sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql/*
xtrabackup --move-back --target-dir=/backups/full_2025-01-15
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

XtraBackup による増分バックアップ

# Incremental based on the full backup
xtrabackup --backup --user=root --password=secret \
  --target-dir=/backups/inc_$(date +%F) \
  --incremental-basedir=/backups/full_2025-01-15

# Prepare: apply full, then incremental
xtrabackup --prepare --apply-log-only --target-dir=/backups/full_2025-01-15
xtrabackup --prepare --target-dir=/backups/full_2025-01-15 \
  --incremental-dir=/backups/inc_2025-01-16

ソースレプリカレプリケーション

ソース（プライマリ）

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id       = 1
log_bin         = /var/log/mysql/mysql-bin
binlog_format   = ROW

-- Create replication user
CREATE USER 'replicator'@'10.0.0.%' IDENTIFIED BY 'repl_secret';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.0.%';
FLUSH PRIVILEGES;

-- Get current binary log position
SHOW MASTER STATUS\G

レプリカ

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id  = 2
relay_log  = /var/log/mysql/relay-bin
read_only  = ON

-- Point replica to source (use SHOW MASTER STATUS values)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST     = '10.0.0.1',
  SOURCE_USER     = 'replicator',
  SOURCE_PASSWORD = 'repl_secret',
  SOURCE_LOG_FILE = 'mysql-bin.000003',
  SOURCE_LOG_POS  = 154;

START REPLICA;

-- Verify
SHOW REPLICA STATUS\G
-- Check: Replica_IO_Running = Yes, Replica_SQL_Running = Yes, Seconds_Behind_Source = 0

監視クエリ

-- Connection statistics
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- InnoDB buffer pool hit ratio (should be > 99%)
SELECT
  ROUND(100 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  ) * 100, 2) AS buffer_pool_hit_pct;

-- Top 10 slow queries (requires performance_schema)
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Table sizes
SELECT table_name,
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC;

-- Check replication lag
SHOW REPLICA STATUS\G
-- Look at Seconds_Behind_Source

Docker Compose のセットアップ

# docker-compose.yml
version: "3.9"

services:
  mysql:
    image: mysql:8.0
    restart: unless-stopped
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: mydb
      MYSQL_USER: myapp
      MYSQL_PASSWORD: secret
    volumes:
      - mysql_data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command: >
      --innodb-buffer-pool-size=512M
      --max-connections=200
      --slow-query-log=ON
      --long-query-time=1
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-prootpass"]
      interval: 10s
      timeout: 5s
      retries: 5

  phpmyadmin:
    image: phpmyadmin:latest
    restart: unless-stopped
    ports:
      - "8080:80"
    environment:
      PMA_HOST: mysql
      PMA_USER: root
      PMA_PASSWORD: rootpass
    depends_on:
      mysql:
        condition: service_healthy

volumes:
  mysql_data:

docker compose up -d
mysql -h 127.0.0.1 -u myapp -psecret mydb

メンテナンスタスク

# Optimize a fragmented table (locks the table briefly)
mysql -u root -p -e "OPTIMIZE TABLE mydb.orders;"

# Analyze tables to update statistics
mysql -u root -p -e "ANALYZE TABLE mydb.orders;"

# Check and repair a table
mysql -u root -p -e "CHECK TABLE mydb.orders;"
mysql -u root -p -e "REPAIR TABLE mydb.orders;"

# Rotate slow query log
sudo mv /var/log/mysql/slow.log /var/log/mysql/slow.log.old
mysqladmin -u root -p flush-logs

トラブルシューティング

症状	考えられる原因	対処方法
Too many connections	接続上限を超過している	max_connections を増やします。接続プーリング（ProxySQL）を使用します
クエリ全般が遅い	innodb_buffer_pool_size が小さすぎる	使用可能な RAM の約 70% に設定して再起動します
レプリケーションが停止した（SQL_Running: No）	レプリカでキーが重複している、またはスキーマが一致しない	SHOW REPLICA STATUS\G のエラーを確認し、該当の行をスキップするか修正します
Table is full	ディスク容量を使い切った、またはテーブルの上限に達した	ディスク容量を空けます。innodb_data_file_path の autoextend を確認します
Lock wait timeout exceeded	実行時間の長いトランザクションが行ロックを保持している	SHOW ENGINE INNODB STATUS で特定し、ブロックしているクエリを終了します
IOPS／ディスク使用量が多い	Redo ログが小さすぎてフラッシュが頻繁に発生している	innodb_log_file_size を増やします（再起動が必要です）

関連スキル

• postgresql - 代替のリレーショナルデータベース
• database-backups - 自動バックアップ戦略
• redis - データベースの負荷を軽減するキャッシュレイヤー
• planetscale - ブランチ機能を備えたマネージド MySQL 互換サービス