MariaDB

第1章: MariaDBの基礎

MariaDBとは

MariaDBは、オープンソースのリレーショナルデータベース管理システム（RDBMS）です。MySQL ABの創設者であるMichael "Monty" Wideniusによって開発され、MySQLのフォークとして2009年に誕生しました。名称は開発者の末娘Mariaにちなんで命名されました。

MariaDBはMySQLとの互換性を維持しながら、独自の発展を遂げています。特に、パフォーマンスの向上、新機能の追加、そしてコミュニティドリブンな開発モデルの採用により、多くの企業や組織で採用されています。

MariaDBの主な特徴として、以下が挙げられます。

MySQLとの高い互換性を保ちながら、独自のストレージエンジンや機能拡張を提供しています。特にAria（MyISAMの代替）やXtraDB（InnoDBの代替）といったストレージエンジンは、高いパフォーマンスと信頼性を実現しています。また、Galera Clusterによる同期レプリケーション機能や、カラムナーストレージエンジンのColumnStoreなど、エンタープライズ向けの機能も充実しています。

インストールと初期設定

MariaDBのインストールは、各オペレーティングシステムの特性に応じて適切な方法を選択します。以下に、主要なOSでのインストール手順を示します。

Debian/Ubuntuの場合：

# パッケージリポジトリの更新
sudo apt-get update

# MariaDBサーバーのインストール
sudo apt-get install mariadb-server

# セキュリティ設定の実行
sudo mysql_secure_installation

設定ファイルは通常、/etc/mysql/mariadb.conf.d/ディレクトリに配置されます。主要な設定ファイルとその役割は以下の通りです。

50-server.cnf の主要な設定例

[mysqld]
# 基本設定
bind-address = 0.0.0.0
port = 3306

# キャラクターセット
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

# パフォーマンス設定
innodb_buffer_pool_size = 1G
max_connections = 100

インストール後の初期設定として、以下のセキュリティ対策を実施することが推奨されます。

-- rootパスワードの設定
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_secure_password';

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

-- リモートrootログインの無効化
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- テストデータベースの削除
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- 権限の再読み込み
FLUSH PRIVILEGES;

基本的な管理作業

データベースの作成と管理は、MariaDBの基本的な管理タスクの一つです。以下に、代表的な管理コマンドとその使用例を示します。

-- データベースの作成
CREATE DATABASE example_db
  CHARACTER SET = 'utf8mb4'
  COLLATE = 'utf8mb4_general_ci';

-- ユーザーの作成と権限付与
CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON example_db.* TO 'example_user'@'localhost';

バックアップと復元は、データ保護の観点から特に重要です。以下に、基本的なバックアップコマンドを示します。

# データベース全体のバックアップ
mysqldump -u root -p --all-databases > full_backup.sql

# 特定のデータベースのバックアップ
mysqldump -u root -p example_db > example_backup.sql

# バックアップの復元
mysql -u root -p example_db < example_backup.sql

システム管理者は、定期的にデータベースの状態を監視する必要があります。以下のクエリを使用して、基本的なシステム情報を確認できます。

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

-- プロセスリストの確認
SHOW PROCESSLIST;

-- システム変数の確認
SHOW VARIABLES LIKE '%max_connections%';

この章では、MariaDBの基礎的な概念と、インストールから基本的な管理作業までを解説しました。次章では、より実践的なSQL文の使用方法と、データベース設計の原則について説明します。

第2章: SQL基礎と実践

基本的なSQL構文

データベースの操作において、正確なSQL構文の理解は不可欠です。本節では、データの取得、操作、定義に関する基本的なSQL文について解説します。

データ定義言語（DDL）

テーブルの作成は、適切なデータ型と制約の選択から始まります。以下に、一般的なテーブル定義の例を示します。

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    birth_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    CONSTRAINT chk_birth_date CHECK (birth_date > '1900-01-01')
);

テーブル定義における重要な考慮点として、以下が挙げられます。データ型の選択は、格納するデータの性質と将来的な拡張性を考慮して行います。また、制約は、データの整合性を保つために適切に設定する必要があります。

データ操作言語（DML）

データの取得には、SELECT文を使用します。以下に、様々な検索パターンを示します。

-- 基本的な検索
SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent
FROM 
    customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE 
    c.status = 'active'
    AND c.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
GROUP BY 
    c.customer_id
HAVING 
    total_orders > 0
ORDER BY 
    total_spent DESC
LIMIT 10;

データの更新と削除は、以下のように行います：

-- トランザクション内でのデータ更新
START TRANSACTION;

UPDATE customers 
SET 
    status = 'suspended',
    updated_at = CURRENT_TIMESTAMP
WHERE 
    customer_id IN (
        SELECT customer_id 
        FROM orders 
        GROUP BY customer_id 
        HAVING SUM(total_amount) < 0
    );

-- 条件に合致する場合のみコミット
SELECT ROW_COUNT() INTO @affected_rows;
IF @affected_rows > 100 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

高度なクエリ技法

JOINの活用

複数のテーブルを結合する際の適切なJOINの選択は、クエリのパフォーマンスと結果の正確性に大きく影響します。以下に、代表的なJOINパターンとその使用例を示します。

-- 売上レポートの生成
WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(o.order_date, '%Y-%m') AS sale_month,
        p.category_id,
        SUM(oi.quantity * oi.unit_price) AS total_sales
    FROM 
        orders o
        INNER JOIN order_items oi ON o.order_id = oi.order_id
        INNER JOIN products p ON oi.product_id = p.product_id
    WHERE 
        o.status = 'completed'
    GROUP BY 
        sale_month,
        p.category_id
)
SELECT 
    ms.sale_month,
    c.category_name,
    ms.total_sales,
    ms.total_sales / SUM(ms.total_sales) OVER (
        PARTITION BY ms.sale_month
    ) * 100 AS sales_percentage
FROM 
    monthly_sales ms
    INNER JOIN categories c ON ms.category_id = c.category_id
ORDER BY 
    ms.sale_month DESC,
    ms.total_sales DESC;

ストアドプロシージャとトリガー

複雑な業務ロジックは、ストアドプロシージャとして実装することで、再利用性と保守性が向上します。

DELIMITER //

CREATE PROCEDURE process_order(
    IN p_customer_id INT,
    IN p_order_items JSON,
    OUT p_order_id INT
)
BEGIN
    DECLARE v_total_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_item_count INT DEFAULT 0;
    
    -- トランザクション開始
    START TRANSACTION;
    
    -- 注文ヘッダーの作成
    INSERT INTO orders (
        customer_id,
        order_date,
        status
    ) VALUES (
        p_customer_id,
        CURRENT_TIMESTAMP,
        'pending'
    );
    
    SET p_order_id = LAST_INSERT_ID();
    
    -- 注文明細の処理
    INSERT INTO order_items (
        order_id,
        product_id,
        quantity,
        unit_price
    )
    SELECT 
        p_order_id,
        JSON_EXTRACT(item, '$.product_id'),
        JSON_EXTRACT(item, '$.quantity'),
        (SELECT price FROM products WHERE product_id = JSON_EXTRACT(item, '$.product_id'))
    FROM JSON_TABLE(
        p_order_items,
        '$[*]' COLUMNS (
            item JSON PATH '$'
        )
    ) AS items;
    
    -- 合計金額の計算と更新
    SELECT 
        COUNT(*),
        SUM(quantity * unit_price)
    INTO 
        v_item_count,
        v_total_amount
    FROM 
        order_items
    WHERE 
        order_id = p_order_id;
    
    IF v_item_count > 0 THEN
        UPDATE orders 
        SET 
            total_amount = v_total_amount,
            status = 'confirmed'
        WHERE 
            order_id = p_order_id;
        COMMIT;
    ELSE
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'No valid items in order';
    END IF;
END //

DELIMITER ;

このストアドプロシージャの呼び出し例：

SET @order_items = '[
    {"product_id": 1, "quantity": 2},
    {"product_id": 3, "quantity": 1}
]';

CALL process_order(1, @order_items, @new_order_id);
SELECT @new_order_id;

インデックスと最適化

クエリのパフォーマンスを向上させるため、適切なインデックスの設計が重要です。以下に、インデックス作成の基本パターンを示します。

-- 複合インデックスの作成
CREATE INDEX idx_customer_status_date 
ON customers (status, created_at);

-- EXPLAIN文によるクエリ実行計画の確認
EXPLAIN FORMAT=JSON
SELECT 
    customer_id,
    first_name,
    last_name
FROM 
    customers 
WHERE 
    status = 'active'
    AND created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH);

インデックスの効果を最大限に活用するためには、以下の点に注意が必要です：

1. カーディナリティ（値の種類の多さ）の高い列を優先してインデックスに含める
2. インデックスのサイズと更新コストを考慮する
3. 実際のクエリパターンに基づいてインデックスを設計する

本章では、基本的なSQL構文から高度なクエリ技法まで、実践的な例を交えて解説しました。次章では、これらの知識を基にしたパフォーマンスチューニングについて説明します。

第3章: パフォーマンスチューニング

パフォーマンス監視

データベースのパフォーマンスを最適化するためには、まず現状を正確に把握する必要があります。MariaDBでは、様々な監視メトリクスやツールを活用してパフォーマンスを評価できます。

システムステータスの確認

MariaDBの現在の状態を確認するための主要なクエリを以下に示します。

-- グローバルステータスの確認
SELECT * FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN (
    'Threads_connected',
    'Threads_running',
    'Queries',
    'Slow_queries',
    'Created_tmp_disk_tables',
    'Created_tmp_tables',
    'Handler_read_first',
    'Handler_read_key',
    'Handler_read_next',
    'Handler_read_rnd',
    'Handler_read_rnd_next'
);

-- InnoDBステータスの確認
SHOW ENGINE INNODB STATUS;

-- プロセスリストと実行中のクエリ
SELECT 
    p.ID,
    p.USER,
    p.HOST,
    p.DB,
    p.COMMAND,
    p.TIME,
    p.STATE,
    p.INFO,
    FORMAT(p.TIME_MS/1000.0, 3) as 'Seconds',
    FORMAT(p.ROWS_SENT, 0) as 'Rows Sent',
    FORMAT(p.ROWS_EXAMINED, 0) as 'Rows Examined'
FROM information_schema.PROCESSLIST p
WHERE p.COMMAND != 'Sleep'
ORDER BY p.TIME_MS DESC;

パフォーマンススキーマの活用

パフォーマンススキーマ（Performance Schema）を使用すると、より詳細なパフォーマンス情報を収集できます。

-- パフォーマンススキーマの有効化
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_statements%';

-- クエリダイジェストの確認
SELECT 
    DIGEST_TEXT as query_pattern,
    COUNT_STAR as exec_count,
    FORMAT_PICO_TIME(AVG_TIMER_WAIT) as avg_latency,
    FORMAT_PICO_TIME(MAX_TIMER_WAIT) as max_latency,
    FORMAT_PICO_TIME(MIN_TIMER_WAIT) as min_latency,
    ROUND(AVG_ROWS_SENT) as avg_rows_sent,
    ROUND(AVG_ROWS_EXAMINED) as avg_rows_examined,
    ROUND(SUM_ROWS_SENT/COUNT_STAR*100) as rows_sent_per_query
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

システム最適化

メモリ設定の最適化

MariaDBのメモリ関連パラメータは、システムの総メモリ量に応じて適切に設定する必要があります。以下に、主要なメモリパラメータの設定例を示します。

[mysqld]
# InnoDBバッファプール
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8

# クエリキャッシュ（注：MariaDB 10.1.7以降は非推奨）
query_cache_size = 0
query_cache_type = 0

# テーブルオープンキャッシュ
table_open_cache = 4000
table_definition_cache = 2000

# ソートバッファ
sort_buffer_size = 4M
join_buffer_size = 2M
read_rnd_buffer_size = 2M

メモリ設定の推奨値は以下の計算式を参考に決定します：

-- 現在のメモリ使用状況の確認
SELECT 
    @@innodb_buffer_pool_size/1024/1024/1024 as buffer_pool_size_gb,
    @@innodb_buffer_pool_instances as buffer_pool_instances,
    @@table_open_cache as table_open_cache,
    @@sort_buffer_size/1024/1024 as sort_buffer_size_mb;

-- InnoDBバッファプールの使用状況
SELECT 
    pool_id,
    pool_size/1024/1024 as pool_size_mb,
    free_buffers/1024/1024 as free_buffers_mb,
    database_pages/1024/1024 as database_pages_mb
FROM information_schema.INNODB_BUFFER_POOL_STATS;

ディスクI/O最適化

ディスクI/Oの最適化は、以下のパラメータを調整することで実現できます。

[mysqld]
# InnoDBログファイル設定
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

# ダブルライト無効化（注意：データ整合性に影響）
innodb_doublewrite = 1

# フラッシュ方法の設定
innodb_flush_method = O_DIRECT

# バッファプールダンプの設定
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

トラブルシューティング

スロークエリの分析

スロークエリログを活用して、パフォーマンス問題を特定し改善します。

-- スロークエリログの設定
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log';
SET GLOBAL long_query_time = 1.0;

-- スロークエリの分析クエリ
SELECT 
    sql_text,
    exec_count,
    total_exec_time,
    avg_exec_time,
    rows_examined,
    rows_sent
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY total_exec_time DESC
LIMIT 10;

デッドロック対策

デッドロックの検出と解決は、以下のような方法で行います。

-- デッドロック情報の確認
SHOW ENGINE INNODB STATUS;

-- トランザクション分離レベルの確認と設定
SELECT @@tx_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- デッドロック対策例：行のロック順序の統一
DELIMITER //

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE ordered_account1 INT;
    DECLARE ordered_account2 INT;
    
    -- アカウントIDを順序付け
    SET ordered_account1 = LEAST(from_account, to_account);
    SET ordered_account2 = GREATEST(from_account, to_account);
    
    START TRANSACTION;
    
    -- 小さいIDから順にロック
    SELECT balance INTO @balance1 
    FROM accounts 
    WHERE account_id = ordered_account1 
    FOR UPDATE;
    
    SELECT balance INTO @balance2 
    FROM accounts 
    WHERE account_id = ordered_account2 
    FOR UPDATE;
    
    -- 残りの処理
    IF ordered_account1 = from_account THEN
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
    ELSE
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    END IF;
    
    COMMIT;
END //

DELIMITER ;

本章では、MariaDBのパフォーマンスチューニングについて、監視から最適化、トラブルシューティングまでを解説しました。次章では、高可用性と冗長化について説明します。

第4章: 高可用性と冗長化

レプリケーション構成

MariaDBにおける高可用性は、主にレプリケーションによって実現されます。本節では、一般的なレプリケーション構成とその実装方法について解説します。

マスター・スレーブレプリケーション

最も基本的なレプリケーション構成であるマスター・スレーブ構成の設定手順を説明します。

マスターサーバーの設定：

my.cnf（マスターサーバー）

[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

# レプリケーション対象のデータベースを指定
binlog-do-db = production_db

マスターサーバーでのレプリケーションユーザーの作成：

-- レプリケーション用ユーザーの作成
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

-- 現在のバイナリログ位置の確認
SHOW MASTER STATUS;

スレーブサーバーの設定：

my.cnf（スレーブサーバー）

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin
read_only = 1

スレーブサーバーでのレプリケーション開始：

CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='secure_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1234;

START SLAVE;

-- レプリケーションステータスの確認
SHOW SLAVE STATUS\G

Galera Cluster

Galera Clusterは、同期マルチマスターレプリケーションを提供します。以下に、3ノードクラスターの設定例を示します。

galera.cnf（各ノード共通）

[mysqld]
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 8G

# Galera Provider Configuration
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_provider_options = "gcache.size=1G"

# Galera Cluster Configuration
wsrep_cluster_name = "production_cluster"
wsrep_cluster_address = "gcomm://node1,node2,node3"

# Galera Synchronization Configuration
wsrep_sst_method = mariabackup
wsrep_sst_auth = sst_user:secure_password

# Node Configuration
wsrep_node_address = "THIS_NODE_IP"
wsrep_node_name = "THIS_NODE_NAME"

クラスターの初期化と管理：

-- SST（State Snapshot Transfer）ユーザーの作成
CREATE USER 'sst_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst_user'@'localhost';

-- クラスターステータスの確認
SHOW STATUS LIKE 'wsrep_%';

バックアップ戦略

論理バックアップ

mysqldumpを使用した論理バックアップの実装例：

backup_script.sh

#!/bin/bash

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
MYSQL_USER="backup_user"
MYSQL_PASSWORD="secure_password"

# バックアップディレクトリの作成
mkdir -p ${BACKUP_DIR}/${DATE}

# 完全バックアップの実行
mysqldump --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} \
    --all-databases \
    --single-transaction \
    --quick \
    --lock-tables=false \
    --set-gtid-purged=OFF \
    --triggers \
    --routines \
    --events \
 | xz -9e > ${BACKUP_DIR}/${DATE}/full_backup.sql.xz

# バックアップの検証
unxz -t ${BACKUP_DIR}/${DATE}/full_backup.sql.xz

# 古いバックアップの削除（30日以上前）
find ${BACKUP_DIR} -type d -mtime +30 -exec rm -rf {} \;

物理バックアップ

MariaBackupを使用した物理バックアップの実装：

# 完全バックアップの取得
mariabackup --backup \
    --target-dir=/backup/mysql/full \
    --user=backup_user \
    --password=secure_password

# バックアップの準備
mariabackup --prepare \
    --target-dir=/backup/mysql/full

# 増分バックアップの取得
mariabackup --backup \
    --target-dir=/backup/mysql/incremental \
    --incremental-basedir=/backup/mysql/full \
    --user=backup_user \
    --password=secure_password

災害対策

フェイルオーバー設定

自動フェイルオーバーを実現するためのスクリプト例：

failover_check.sh

#!/bin/bash

MASTER_HOST="master_host"
SLAVE_HOST="slave_host"
MYSQL_USER="monitor_user"
MYSQL_PASSWORD="secure_password"

# マスターの状態確認
check_master() {
    mysqladmin ping -h ${MASTER_HOST} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} > /dev/null 2>&1
    return $?
}

# スレーブの状態確認
check_slave() {
    local slave_status=$(mysql -h ${SLAVE_HOST} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} \
        -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master")
    
    if [[ $slave_status =~ [0-9]+ ]]; then
        return 0
    else
        return 1
    fi
}

# フェイルオーバーの実行
perform_failover() {
    # スレーブの昇格
    mysql -h ${SLAVE_HOST} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} << EOF
    STOP SLAVE;
    RESET SLAVE ALL;
    SET GLOBAL read_only = 0;
EOF

    # DNS/VIPの切り替え
    # この部分は環境に応じて実装
}

# メイン処理
while true; do
    if ! check_master; then
        if check_slave; then
            perform_failover
            logger "Failover completed: ${SLAVE_HOST} promoted to master"
            break
        else
            logger "Failover failed: Slave is not in a consistent state"
            exit 1
        fi
    fi
    sleep 10
done

このスクリプトは、以下のような監視設定と組み合わせて使用します：

/etc/supervisor/conf.d/failover_monitor.conf

[program:failover_monitor]
command=/usr/local/bin/failover_check.sh
autostart=true
autorestart=true
stderr_logfile=/var/log/failover_monitor.err.log
stdout_logfile=/var/log/failover_monitor.out.log

本章では、MariaDBの高可用性と冗長化について、実装方法から運用管理まで解説しました。次章では、セキュリティについて説明します。

第5章: セキュリティ

基本的なセキュリティ対策

MariaDBのセキュリティは、多層的な防御アプローチで実装する必要があります。本節では、基本的なセキュリティ対策からより高度な設定まで解説します。

アクセス制御の実装

ユーザー認証とアクセス権限の適切な設定は、セキュリティの基礎となります。

-- セキュアなユーザー作成
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password' 
    PASSWORD EXPIRE INTERVAL 90 DAY 
    FAILED_LOGIN_ATTEMPTS 3 
    PASSWORD HISTORY 5;

-- 最小権限の原則に基づく権限付与
GRANT SELECT, INSERT, UPDATE 
    ON production_db.* 
    TO 'app_user'@'%';

-- 特定のテーブルへのアクセス制限
GRANT SELECT ON production_db.sensitive_data 
    TO 'app_user'@'%' 
    WITH GRANT OPTION;

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

パスワードポリシーの設定：

my.cnf

[mysqld]
validate_password_policy = STRONG
validate_password_length = 12
validate_password_mixed_case_count = 1
validate_password_number_count = 1
validate_password_special_char_count = 1

ネットワークセキュリティ

SSL/TLS通信の設定：

my.cnf

[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport = ON

SSL証明書の生成と設定：

# SSL証明書の生成
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# 証明書の配置
sudo cp ca.pem server-cert.pem server-key.pem /etc/mysql/ssl/
sudo chown mysql:mysql /etc/mysql/ssl/*
sudo chmod 600 /etc/mysql/ssl/*

SSL接続の確認：

-- SSL状態の確認
SHOW VARIABLES LIKE '%ssl%';

-- 現在の接続がSSLを使用しているか確認
\s

-- SSL必須ユーザーの作成
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

監査とコンプライアンス

監査ログの設定

MariaDB Audit Pluginの設定と使用：

-- 監査プラグインのインストール
INSTALL SONAME 'server_audit';

-- 監査設定の確認
SHOW VARIABLES LIKE 'server_audit%';

-- 監査ログの設定
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_file_rotate_size = 1000000;
SET GLOBAL server_audit_file_rotations = 9;

監査ログの分析用クエリ：

-- 監査ログテーブルの作成
CREATE TABLE audit_log (
    timestamp DATETIME,
    serverhost VARCHAR(60),
    username VARCHAR(32),
    host VARCHAR(60),
    connectionid INT,
    queryid INT,
    operation VARCHAR(20),
    database VARCHAR(30),
    object VARCHAR(30),
    retcode INT
);

-- 監査ログの分析
SELECT 
    DATE(timestamp) as date,
    username,
    operation,
    COUNT(*) as operation_count
FROM audit_log
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY date, username, operation
ORDER BY date DESC, operation_count DESC;

データ暗号化

データベースレベルの暗号化設定：

my.cnf

[mysqld]
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
innodb_encryption_rotate_key_age = 1

テーブルレベルの暗号化：

-- 暗号化テーブルの作成
CREATE TABLE secure_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sensitive_data TEXT
) ENCRYPTION='Y';

-- 既存テーブルの暗号化
ALTER TABLE existing_table ENCRYPTION='Y';

-- 暗号化状態の確認
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ENCRYPTION
FROM information_schema.TABLES
WHERE ENCRYPTION = 'Y';

アプリケーションレベルでの暗号化実装：

-- 暗号化関数の使用例
CREATE TABLE customer_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    credit_card VARBINARY(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE PROCEDURE insert_encrypted_data(
    IN p_name VARCHAR(255),
    IN p_credit_card VARCHAR(255)
)
BEGIN
    INSERT INTO customer_data (name, credit_card)
    VALUES (
        p_name,
        AES_ENCRYPT(p_credit_card, SHA2(@@global.server_uuid, 512))
    );
END //

CREATE PROCEDURE get_decrypted_data(
    IN p_id INT
)
BEGIN
    SELECT 
        id,
        name,
        AES_DECRYPT(credit_card, SHA2(@@global.server_uuid, 512)) as decrypted_card
    FROM customer_data
    WHERE id = p_id;
END //

DELIMITER ;

セキュリティベストプラクティス

セキュリティチェックリスト

以下のSQLクエリで、セキュリティ設定を確認できます：

-- セキュリティ関連の設定確認
SELECT VARIABLE_NAME, VARIABLE_VALUE 
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME IN (
    'have_ssl',
    'ssl_cipher',
    'require_secure_transport',
    'validate_password_policy',
    'log_error',
    'general_log',
    'slow_query_log',
    'log_bin',
    'skip_name_resolve'
);

-- 匿名ユーザーの確認
SELECT User, Host FROM mysql.user WHERE User = '';

-- 特権ユーザーの確認
SELECT User, Host 
FROM mysql.user 
WHERE Super_priv = 'Y' 
    OR Grant_priv = 'Y';

-- パスワード有効期限の確認
SELECT User, Host, password_expired, password_lifetime
FROM mysql.user;

本章では、MariaDBのセキュリティについて、基本的な設定から高度な暗号化まで解説しました。次章では、開発者向けのガイドラインについて説明します。

第6章: 開発者ガイド

アプリケーション連携

データベース接続管理

効率的なデータベース接続管理は、アプリケーションのパフォーマンスと安定性に直接影響します。以下に、主要な言語での実装例を示します。

Python（SQLAlchemy使用）での実装例：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.pool import QueuePool
from contextlib import contextmanager

# データベース接続設定
DATABASE_URL = "mysql+pymysql://user:password@localhost/dbname"

# エンジン設定
engine = create_engine(
    DATABASE_URL,
    pool_size=5,  # 常時接続数
    max_overflow=10,  # 追加で許可する接続数
    pool_timeout=30,  # 接続待ちタイムアウト
    pool_recycle=3600,  # 接続再利用の制限時間
    echo=False  # SQLログ出力の制御
)

# セッション作成
SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)
Base = declarative_base()

@contextmanager
def get_db():
    db = SessionLocal()
    try:
        yield db
        db.commit()
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()

Node.js（mysql2使用）での実装例：

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'dbname',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

async function executeQuery(query, params) {
  let connection;
  try {
    connection = await pool.getConnection();
    const [rows] = await connection.execute(query, params);
    return rows;
  } catch (error) {
    console.error('Database error:', error);
    throw error;
  } finally {
    if (connection) connection.release();
  }
}

// トランザクション処理の実装
async function executeTransaction(callback) {
  let connection;
  try {
    connection = await pool.getConnection();
    await connection.beginTransaction();
    
    const result = await callback(connection);
    
    await connection.commit();
    return result;
  } catch (error) {
    if (connection) await connection.rollback();
    throw error;
  } finally {
    if (connection) connection.release();
  }
}

クエリビルダーとORM

効率的なSQLクエリの構築と実行のために、クエリビルダーやORMを活用します。

TypeORM（TypeScript）での実装例：

import { Entity, Column, PrimaryGeneratedColumn, OneToMany, Repository } from 'typeorm';
import { IsNotEmpty, Length, IsEmail } from 'class-validator';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ length: 100 })
  @IsNotEmpty()
  @Length(2, 100)
  name: string;

  @Column({ unique: true })
  @IsEmail()
  email: string;

  @Column({ select: false })
  password: string;

  @OneToMany(() => Order, order => order.user)
  orders: Order[];
}

// リポジトリクラスの実装
export class UserRepository {
  constructor(private repository: Repository<User>) {}

  async findWithOrders(id: number): Promise<User || null> {
    return this.repository.findOne({
      where: { id },
      relations: ['orders'],
      cache: true
    });
  }

  async searchUsers(criteria: {
    name?: string;
    email?: string;
    page?: number;
    limit?: number;
  }): Promise<[User[], number]> {
    const query = this.repository.createQueryBuilder('user');
    
    if (criteria.name) {
      query.andWhere('user.name LIKE :name', { name: <code>%${criteria.name}%</code> });
    }
    
    if (criteria.email) {
      query.andWhere('user.email = :email', { email: criteria.email });
    }
    
    return query
      .skip((criteria.page || 0) * (criteria.limit || 10))
      .take(criteria.limit || 10)
      .getManyAndCount();
  }
}

トランザクション管理

トランザクション分離レベル

アプリケーションの要件に応じた適切なトランザクション分離レベルの選択が重要です。

-- トランザクション分離レベルの設定
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 分離レベルの確認
SELECT @@transaction_isolation;

分散トランザクションの実装例：

interface TransactionManager {
  begin(): Promise<void>;
  commit(): Promise<void>;
  rollback(): Promise<void>;
}

class DistributedTransaction implements TransactionManager {
  private connections: Connection[] = [];
  
  async begin(): Promise<void> {
    for (const conn of this.connections) {
      await conn.beginTransaction();
    }
  }
  
  async commit(): Promise<void> {
    try {
      // プリペアフェーズ
      for (const conn of this.connections) {
        await conn.query('XA PREPARE');
      }
      
      // コミットフェーズ
      for (const conn of this.connections) {
        await conn.query('XA COMMIT');
      }
    } catch (error) {
      await this.rollback();
      throw error;
    }
  }
  
  async rollback(): Promise<void> {
    for (const conn of this.connections) {
      await conn.query('XA ROLLBACK');
    }
  }
}

パフォーマンス最適化

N+1問題の解決

N+1問題を回避するためのクエリ最適化例：

// 問題のあるクエリパターン
async function getOrdersWithItems_problematic() {
  const orders = await Order.findMany();
  for (const order of orders) {
    order.items = await OrderItem.findMany({ 
      where: { orderId: order.id } 
    });
  }
  return orders;
}

// 最適化されたクエリ
async function getOrdersWithItems_optimized() {
  return Order.findMany({
    include: {
      items: true
    }
  });
}

// さらに最適化：必要なフィールドのみ取得
async function getOrdersWithItems_optimized_select() {
  return Order.findMany({
    select: {
      id: true,
      orderDate: true,
      items: {
        select: {
          id: true,
          quantity: true,
          price: true
        }
      }
    }
  });
}

キャッシュ戦略

効率的なキャッシュ実装例：

import { createClient } from 'redis';
import { promisify } from 'util';

class CacheManager {
  private client: ReturnType<typeof createClient>;
  private readonly defaultTTL: number = 3600; // 1時間

  constructor() {
    this.client = createClient({
      url: process.env.REDIS_URL
    });
    
    this.client.on('error', (err) => console.error('Redis Client Error', err));
  }

  async get<T>(key: string): Promise<T || null> {
    const data = await this.client.get(key);
    return data ? JSON.parse(data) : null;
  }

  async set(key: string, value: any, ttl: number = this.defaultTTL): Promise<void> {
    await this.client.set(key, JSON.stringify(value), {
      EX: ttl
    });
  }

  async getOrSet<T>(
    key: string,
    factory: () => Promise<T>,
    ttl: number = this.defaultTTL
  ): Promise<T> {
    const cached = await this.get<T>(key);
    if (cached) return cached;

    const value = await factory();
    await this.set(key, value, ttl);
    return value;
  }
}

// 使用例
const cacheManager = new CacheManager();

async function getUserWithCache(id: number) {
  return cacheManager.getOrSet(
    <code>user:${id}</code>,
    async () => {
      return await userRepository.findOne(id);
    },
    1800 // 30分
  );
}

附録

A. コマンドリファレンス

システム管理コマンド

データベース管理者が日常的に使用する主要なコマンドを以下に示します。

-- システム情報の確認
SHOW VARIABLES;              -- システム変数の表示
SHOW STATUS;                -- システムステータスの表示
SHOW PROCESSLIST;           -- 実行中のプロセス一覧
SHOW ENGINES;               -- 利用可能なストレージエンジン
SHOW PLUGINS;               -- インストール済みプラグイン

-- データベース管理
CREATE DATABASE dbname;      -- データベース作成
DROP DATABASE dbname;        -- データベース削除
SHOW DATABASES;             -- データベース一覧
USE dbname;                 -- データベース選択

-- ユーザー管理
CREATE USER 'username'@'host' IDENTIFIED BY 'password';  -- ユーザー作成
DROP USER 'username'@'host';                            -- ユーザー削除
SHOW GRANTS FOR 'username'@'host';                      -- 権限確認
FLUSH PRIVILEGES;                                       -- 権限変更の反映

-- テーブル管理
SHOW TABLES;                -- テーブル一覧
DESCRIBE tablename;         -- テーブル構造の表示
SHOW CREATE TABLE tablename; -- テーブル作成文の表示

メンテナンスコマンド

システムの保守管理に使用する重要なコマンドです。

-- テーブルメンテナンス
ANALYZE TABLE tablename;     -- テーブル分析
OPTIMIZE TABLE tablename;    -- テーブル最適化
REPAIR TABLE tablename;      -- テーブル修復
CHECK TABLE tablename;       -- テーブルチェック

-- インデックス管理
SHOW INDEX FROM tablename;   -- インデックス情報の表示
CREATE INDEX idx_name ON tablename (column);  -- インデックス作成
DROP INDEX idx_name ON tablename;            -- インデックス削除

-- ストレージエンジン管理
ALTER TABLE tablename ENGINE = InnoDB;  -- ストレージエンジンの変更

B. 設定パラメータ一覧

主要な設定パラメータとその推奨値を以下の表で示します。

システム設定

パラメータ	説明	推奨値	備考
innodb_buffer_pool_size	InnoDBバッファプールサイズ	総メモリの50-75%	サーバー専用機の場合
innodb_log_file_size	InnoDBログファイルサイズ	256M-1G	トランザクション量による
max_connections	最大接続数	100-500	アプリケーション要件による
thread_cache_size	スレッドキャッシュサイズ	8-16	接続数に応じて調整
query_cache_type	クエリキャッシュタイプ	0	10.1.7以降は無効化推奨

パフォーマンス設定

パラメータ	説明	推奨値	備考
innodb_flush_log_at_trx_commit	ログ書き込みタイミング	1	耐障害性重視の場合
innodb_flush_method	フラッシュメソッド	O_DIRECT	Linuxシステムの場合
innodb_file_per_table	テーブル単位のファイル	ON	管理容易性向上
tmp_table_size	一時テーブルサイズ	16M-64M	メモリ使用量に注意
max_heap_table_size	MEMORYテーブルサイズ	16M-64M	一時テーブルと同値推奨

C. トラブルシューティングガイド

一般的な問題とその解決方法を示します。

接続関連の問題

1. 最大接続数の超過

   -- 現在の接続数確認
   SHOW STATUS LIKE 'Threads_connected';
   
   -- 最大接続数の変更
   SET GLOBAL max_connections = 200;
   
   -- 接続待ちの監視
   SHOW STATUS LIKE 'Connection_errors%';
   

2. 認証エラー

   -- ユーザーの認証方式確認
   SELECT user, host, plugin 
   FROM mysql.user 
   WHERE user = 'problem_user';
   
   -- 認証方式の変更
   ALTER USER 'username'@'host' 
   IDENTIFIED WITH mysql_native_password 
   BY 'new_password';
   

パフォーマンス関連の問題

1. スロークエリの特定

   -- スロークエリログの有効化
   SET GLOBAL slow_query_log = 1;
   SET GLOBAL long_query_time = 2;
   
   -- スロークエリの分析
   SHOW GLOBAL STATUS LIKE '%slow%';
   

2. メモリ不足

   -- メモリ使用状況の確認
   SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
   
   -- テンポラリテーブルの使用状況
   SHOW GLOBAL STATUS LIKE 'Created_tmp%';
   

D. アップグレード手順

MariaDBのメジャーバージョンアップグレード手順を示します。

アップグレード前の準備

# 1. 現在の設定のバックアップ
sudo cp -p /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

# 2. データベースのバックアップ
mysqldump --all-databases --single-transaction \
    --triggers --routines --events > full_backup.sql

# 3. システム変数の確認
mysql -e "SHOW VARIABLES" > variables_before_upgrade.txt

アップグレード実行

# 1. 既存パッケージの削除（データは保持）
sudo systemctl stop mariadb
sudo apt-get remove mariadb-server

# 2. 新バージョンのリポジトリ追加
sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
sudo add-apt-repository 'deb [arch=amd64] http://mirror.netcologne.de/mariadb/repo/10.6/ubuntu focal main'

# 3. 新バージョンのインストール
sudo apt-get update
sudo apt-get install mariadb-server

# 4. アップグレードスクリプトの実行
sudo mysql_upgrade -u root -p

E. 用語集

主要なデータベース用語の解説です。

用語	説明
ACID	トランザクションの4つの特性（Atomicity, Consistency, Isolation, Durability）
B-tree	データベースで一般的に使用されるインデックス構造
MVCC	Multi-Version Concurrency Control（同時実行制御機構）
InnoDB	MariaDBのデフォルトストレージエンジン
WAL	Write-Ahead Logging（先行書き込みログ）
LSN	Log Sequence Number（ログシーケンス番号）
DDL	Data Definition Language（データ定義言語）
DML	Data Manipulation Language（データ操作言語）
DCL	Data Control Language（データ制御言語）