MYSQLのバックアップとローカル利用 の変更点

• 追加された行はこの色です。
• 削除された行はこの色です。
• MYSQLのバックアップとローカル利用 へ行く。

<!-- markdown -->

[TOC]

# 想定する利用環境というか状況
開発用のDBを個人に用意されていないプロジェクトで、ローカル開発ができるようにしたい状況

BashシェルとDockerを使って以下を行う

MYSQLのバックアップ

Dockerを使ってバックアップしたファイルをローカルで動作させる

## ファイル構成
    .
    ├── .env                  # 環境変数設定
    ├── docker-compose.yml    # Docker環境設定
    ├── functions.sh          # 共通関数定義
    ├── export.sh            # エクスポート実行スクリプト
    └── import.sh            # インポート実行スクリプト
    └── docker
      ├── Dockerfile.txt
      └── my.cnf

### .env
環境変数の設定

### MySQL Configuration
    
以下を.bash_profileなどに保管する

    # .env
    # MySQLの設定
    MYSQL_HOST=localhost
    MYSQL_PORT=3306
    MYSQL_USER=your_username
    MYSQL_PASSWORD=your_password
    MYSQL_DATABASE=your_database
    MYSQL_ROOT_PASSWORD=your_root_password
    
    # エクスポート設定
    EXPORT_DIR=./mysql_backup
    
    # Docker設定
    DOCKER_CONTAINER_NAME=mysql_container
    DOCKER_MYSQL_PORT=3307

## docker/Dockerfile.txt

日本語が使えるように設定しておく
    # 必要なパッケージのインスト # syntax=docker/dockerfile:1
    FROM mysql:8.4
    
    # 必要なパッケージのインストール
    RUN microdnf update -y \
        && microdnf install -y glibc-locale-source glibc-langpack-ja \
        && rm -rf /var/cache/dnf \
        && localedef -f UTF-8 -i ja_JP ja_JP.UTF-8

## docker-compose.yml

    version: '3.8'
    
    services:
      mysql:
        build: 
          context: ./docker
          dockerfile: Dockerfile.txt
        container_name: ${DOCKER_CONTAINER_NAME}
        restart: always
        environment:
          MYSQL_DATABASE: ${MYSQL_DATABASE}
          MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
          MYSQL_USER: ${MYSQL_USER}
          MYSQL_PASSWORD: ${MYSQL_PASSWORD}
          LANG: ja_JP.UTF-8
          LANGUAGE: ja_JP:ja
          LC_ALL: ja_JP.UTF-8
        ports:
          - '${DOCKER_MYSQL_PORT}:3306'
        volumes:
          - mysql-data:/var/lib/mysql
          - mysql-logs:/var/log/mysql
          - ./docker/my.cnf:/etc/mysql/conf.d/my.cnf
    
    volumes:
      mysql-data:
      mysql-logs:
      
## my.cnf
    [mysqld]
    # 文字コードの設定
    character-set-server=utf8mb4
    collation-server=utf8mb4_unicode_ci
    
    # 日本語対応
    init_connect='SET NAMES utf8mb4'
    skip-character-set-client-handshake
    
    # SQLモード設定
    sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    
    # InnoDBの設定
    innodb_buffer_pool_size=256M
    innodb_log_file_size=64M
    innodb_flush_log_at_trx_commit=2
    innodb_flush_method=O_DIRECT
    
    # パフォーマンス設定
    innodb_file_per_table=1
    innodb_buffer_pool_instances=2
    
    # ログ設定
    log_error=/var/log/mysql/mysql-error.log
    slow_query_log=1
    slow_query_log_file=/var/log/mysql/mysql-slow.log
    long_query_time=2
    
    # クエリログ（開発環境のみ有効にすることを推奨）
    # general_log=1
    # general_log_file=/var/log/mysql/mysql-general.log
    
    # 最大接続数設定
    max_connections=100
    max_connect_errors=10000
    
    # タイムアウト設定
    wait_timeout=60
    interactive_timeout=60
    
    # その他の設定
    max_allowed_packet=64M
    explicit_defaults_for_timestamp=1
    
    # バイナリログ設定（必要な場合のみ有効化）
    # log-bin=/var/log/mysql/mysql-bin.log
    # binlog_expire_logs_seconds=604800
    # max_binlog_size=100M

## functions.sh
    #!/bin/bash
    
    # functions.sh
    # MySQLデータベースの移行に関する共通関数
    
    # 環境変数の読み込みと検証
    load_env() {
        if [ ! -f .env ]; then
            echo "Error: .env file not found"
            exit 1
        fi
        source .env
    
        # 必須環境変数の検証
        local required_vars=(
            "MYSQL_HOST"
            "MYSQL_PORT"
            "MYSQL_USER"
            "MYSQL_PASSWORD"
            "MYSQL_DATABASE"
            "MYSQL_ROOT_PASSWORD"
            "EXPORT_DIR"
            "DOCKER_CONTAINER_NAME"
            "DOCKER_MYSQL_PORT"
        )
    
        for var in "${required_vars[@]}"; do
            if [ -z "${!var}" ]; then
                echo "Error: Required environment variable $var is not set"
                exit 1
            fi
        done
    }
    
    # 初期化
    init() {
        load_env
        mkdir -p "${EXPORT_DIR}"
        mkdir -p "logs"
    }
    
    # Dockerコマンドのエイリアス関数
    docker_mysql_exec() {
        docker exec -i "${DOCKER_CONTAINER_NAME}" mysql \
            -u"${MYSQL_USER}" \
            -p"${MYSQL_PASSWORD}" \
            "$@"
    }
    
    # エクスポートファイル名の生成
    generate_export_filenames() {
        local timestamp=$(date +%Y%m%d_%H%M%S)
        local schema_file="${EXPORT_DIR}/${MYSQL_DATABASE}_schema_${timestamp}.sql"
        local data_file="${EXPORT_DIR}/${MYSQL_DATABASE}_data_${timestamp}.sql"
        echo "${schema_file}|${data_file}"
    }
    
    # 最新のエクスポートファイルを取得
    get_latest_export_files() {
        if [ ! -d "${EXPORT_DIR}" ]; then
            echo "Error: Export directory not found"
            return 1
        }
    
        local latest_schema=$(ls -t "${EXPORT_DIR}"/*_schema_*.sql 2>/dev/null | head -n1)
        local latest_data=$(ls -t "${EXPORT_DIR}"/*_data_*.sql 2>/dev/null | head -n1)
    
        if [ -z "${latest_schema}" ] || [ -z "${latest_data}" ]; then
            echo "Error: Export files not found"
            return 1
        fi
    
        echo "${latest_schema}|${latest_data}"
    }
    
    # ログ出力関数
    log_message() {
        local level=$1
        local message=$2
        local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
        echo "[${timestamp}] [${level}] ${message}"
        echo "[${timestamp}] [${level}] ${message}" >> "logs/migration_$(date +%Y%m%d).log"
    }
    
    # MySQLデータのエクスポート
    export_mysql_data() {
        local schema_file=$1
        local data_file=$2
    
        if [ -z "${schema_file}" ] || [ -z "${data_file}" ]; then
            log_message "ERROR" "Export filenames not provided"
            return 1
        }
    
        log_message "INFO" "Starting database export..."
        log_message "INFO" "Exporting schema to: ${schema_file}"
    
        # スキーマ(DDL)のエクスポート
        mysqldump --host="${MYSQL_HOST}" \
                  --port="${MYSQL_PORT}" \
                  --user="${MYSQL_USER}" \
                  --password="${MYSQL_PASSWORD}" \
                  --no-data \
                  --set-gtid-purged=OFF \
                  --skip-add-locks \
                  --skip-comments \
                  --routines \
                  --triggers \
                  "${MYSQL_DATABASE}" > "${schema_file}" 2>> "logs/migration_$(date +%Y%m%d).log"
    
        if [ $? -ne 0 ]; then
            log_message "ERROR" "Failed to export schema"
            return 1
        fi
    
        log_message "INFO" "Exporting data to: ${data_file}"
    
        # データ(DML)のエクスポート
        mysqldump --host="${MYSQL_HOST}" \
                  --port="${MYSQL_PORT}" \
                  --user="${MYSQL_USER}" \
                  --password="${MYSQL_PASSWORD}" \
                  --no-create-info \
                  --set-gtid-purged=OFF \
                  --skip-add-locks \
                  --skip-comments \
                  --complete-insert \
                  --extended-insert=FALSE \
                  "${MYSQL_DATABASE}" > "${data_file}" 2>> "logs/migration_$(date +%Y%m%d).log"
    
        if [ $? -ne 0 ]; then
            log_message "ERROR" "Failed to export data"
            return 1
        fi
    
        log_message "INFO" "Export completed successfully"
        return 0
    }
    
    # DockerコンテナへのMySQLデータのインポート
    import_mysql_to_docker() {
        local schema_file=$1
        local data_file=$2
    
        if [ -z "${schema_file}" ] || [ -z "${data_file}" ]; then
            local latest_files=$(get_latest_export_files)
            if [ $? -ne 0 ]; then
                log_message "ERROR" "No export files found"
                return 1
            fi
            schema_file=$(echo "${latest_files}" | cut -d'|' -f1)
            data_file=$(echo "${latest_files}" | cut -d'|' -f2)
            log_message "INFO" "Using latest export files:"
            log_message "INFO" "Schema: ${schema_file}"
            log_message "INFO" "Data: ${data_file}"
        fi
    
        # Dockerコンテナが実行中か確認
        if ! docker ps | grep -q "${DOCKER_CONTAINER_NAME}"; then
            log_message "INFO" "Starting Docker containers..."
            docker-compose up -d
            
            log_message "INFO" "Waiting for MySQL to start..."
            local max_attempts=30
            local attempt=1
            
            while [ $attempt -le $max_attempts ]; do
                if docker_mysql_exec -e "SELECT 1" &>/dev/null; then
                    break
                fi
                log_message "INFO" "Attempt $attempt of $max_attempts: MySQL not ready yet..."
                sleep 2
                ((attempt++))
            done
    
            if [ $attempt -gt $max_attempts ]; then
                log_message "ERROR" "MySQL failed to start within the expected time"
                return 1
            fi
        fi
    
        # データベースが存在しない場合は作成
        log_message "INFO" "Creating database if not exists"
        docker_mysql_exec -e "CREATE DATABASE IF NOT EXISTS ${MYSQL_DATABASE};"
    
        if [ $? -ne 0 ]; then
            log_message "ERROR" "Failed to create database"
            return 1
        fi
    
        # スキーマ(DDL)のインポート
        log_message "INFO" "Importing schema from ${schema_file}"
        docker_mysql_exec "${MYSQL_DATABASE}" < "${schema_file}"
    
        if [ $? -ne 0 ]; then
            log_message "ERROR" "Failed to import schema"
            return 1
        fi
    
        # データ(DML)のインポート
        log_message "INFO" "Importing data from ${data_file}"
        docker_mysql_exec "${MYSQL_DATABASE}" < "${data_file}"
    
        if [ $? -ne 0 ]; then
            log_message "ERROR" "Failed to import data"
            return 1
        fi
    
        log_message "INFO" "Import completed successfully"
        return 0
    }
    
    # データベースの状態確認
    check_database_status() {
        log_message "INFO" "Checking database status..."
        
        # テーブル一覧の取得
        log_message "INFO" "Getting table list..."
        docker_mysql_exec "${MYSQL_DATABASE}" -e "SHOW TABLES;"
        
        # テーブル数の取得
        log_message "INFO" "Getting table count..."
        docker_mysql_exec "${MYSQL_DATABASE}" -e "
            SELECT COUNT(*) as total_tables 
            FROM information_schema.tables 
            WHERE table_schema='${MYSQL_DATABASE}';"
        
        # データベースのサイズ情報
        log_message "INFO" "Getting database size information..."
        docker_mysql_exec "${MYSQL_DATABASE}" -e "
            SELECT table_schema as database_name,
                   ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb
            FROM information_schema.tables
            WHERE table_schema='${MYSQL_DATABASE}'
            GROUP BY table_schema;"
    }
    
    # 初期化の実行
    init
## export.sh
    #!/bin/bash
    
    # export.sh
    # MySQLデータベースのエクスポートスクリプト
    
    # スクリプトのディレクトリを取得
    SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
    
    # 共通関数の読み込み
    source "${SCRIPT_DIR}/functions.sh"
    
    # ヘルプメッセージ
    show_help() {
        echo "Usage: $0 [options]"
        echo
        echo "Options:"
        echo "  -h, --help     Show this help message"
        echo "  -v, --verbose  Enable verbose output"
        echo
        echo "Environment variables (from .env file):"
        echo "  MYSQL_HOST            MySQL host"
        echo "  MYSQL_PORT            MySQL port"
        echo "  MYSQL_DATABASE        Database name"
        echo "  EXPORT_DIR            Export directory"
        echo
        echo "Example:"
        echo "  $0 --verbose"
    }
    
    # コマンドライン引数の解析
    VERBOSE=0
    
    while [[ $# -gt 0 ]]; do
        case $1 in
            -h|--help)
                show_help
                exit 0
                ;;
            -v|--verbose)
                VERBOSE=1
                shift
                ;;
            *)
                echo "Unknown option: $1"
                show_help
                exit 1
                ;;
        esac
    done
    
    # エクスポート処理の開始
    log_message "INFO" "Starting database export process"
    
    if [ $VERBOSE -eq 1 ]; then
        log_message "INFO" "Using configuration:"
        log_message "INFO" "  Host: ${MYSQL_HOST}"
        log_message "INFO" "  Port: ${MYSQL_PORT}"
        log_message "INFO" "  Database: ${MYSQL_DATABASE}"
        log_message "INFO" "  Export Directory: ${EXPORT_DIR}"
    fi
    
    # エクスポートファイル名の生成
    files=$(generate_export_filenames)
    schema_file=$(echo "${files}" | cut -d'|' -f1)
    data_file=$(echo "${files}" | cut -d'|' -f2)
    
    # エクスポートの実行
    if export_mysql_data "${schema_file}" "${data_file}"; then
        log_message "INFO" "Export completed successfully"
        log_message "INFO" "Schema file: ${schema_file}"
        log_message "INFO" "Data file: ${data_file}"
        exit 0
    else
        log_message "ERROR" "Export failed"
        exit 1
    fi