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

Top / MYSQLのバックアップとローカル利用

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

最新の30件

pukiwiki記事一覧

想定する利用環境というか状況 †

開発用の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

       

Last-modified: 2024-11-11 (月) 00:48:58 (127d)

Web Master: hayashi_kuniyuki

PukiWiki 1.4.7 Copyright © 2001-2006 PukiWiki Developers Team. License is GPL.
Based on "PukiWiki" 1.3 by yu-ji. Powered by PHP 5.3.29. HTML convert time: 0.010 sec. skin apricot by hirokasa