MYSQLのバックアップとローカル利用の編集

[ トップ ] [ 編集 | 差分 | バックアップ | 添付 | リロード ] [ 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ ]

#contents

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

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

MYSQLのバックアップ

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

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

*** .env [#fd78edfa]
環境変数の設定

*** MySQL Configuration [#me0ff17c]
 
以下を.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 [#ie5f8de0]

日本語が使えるように設定しておく
 # 必要なパッケージのインスト # 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 [#a97cf4d5]

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 [#b879560b]
 [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 [#c32e1339]
 #!/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 [#g0238a46]
 #!/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

spamではない場合はチェックをいれてください。タイムスタンプを変更しない