mysqlで日付パーティションをバックアップするバッチの作成

よっしー

こんにちは。よっしーです(^^)

今日は、mysqlで日付パーティションをバックアップする方法についてご紹介します。

背景
作成ファイル一覧
1. aws/work/bak_partition.sh
動作確認
解説
1. aws/work/bak_partition.sh
おわりに

背景

mysqlの日付パーティションを調査した際に、検証環境の構築したときの内容を備忘として残しました。

この記事のソースは下記のリポジトリにあります。

GitHub - Gate-Yossi/Musica: 読み方：ムジカ

読み方：ムジカ. Contribute to Gate-Yossi/Musica development by creating an account on GitHub.

作成ファイル一覧

下記のファイルを作成しました。ファイルの内容は下記のセクションに記載しています。

aws/work/bak_partition.sh

aws/work/bak_partition.sh

#!/bin/bash

host='mysql'
user='root'
pass='P@ssw0rd'

db='sample'
tbl='test'

mysql_cmd="mysql -h${host} -u${user} -p${pass} -D${db}"

partition_info=$(${mysql_cmd} -sN <<EOL
SELECT TABLE_SCHEMA
     , PARTITION_NAME
     , PARTITION_ORDINAL_POSITION
     , PARTITION_EXPRESSION
     , PARTITION_DESCRIPTION
     , TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME =  '${tbl}'
 ORDER BY PARTITION_DESCRIPTION ASC
 LIMIT 1
;
EOL
);

column=$(echo "${partition_info}" | awk '{print $4}')

min_p_name=$(echo "${partition_info}" | awk '{print $2}')

min_cond_date=$(echo "${partition_info}" | awk '{print $5}')
min_cond_date=${min_cond_date/\'/}

bak=${tbl}_${min_p_name}.sql

mysqldump -h${host} -u${user} -p${pass} ${db} ${tbl} \
  --default-character-set=utf8 \
  --complete-insert \
  --no-create-info \
  --where="${column}<'${min_cond_date} 00:00:00'" \
> "${bak}"

cat <<EOL
success !!
$column
BAK : ${min_p_name} ( LESS THAN ${min_cond_date} )
${bak}
EOL

動作確認

下記のコマンドを実行します。

make build
make up
make login_aws
cd /work

下記のコマンドで日付パーティションが追加されます。

bash bak_partition.sh

bash-5.2# bash bak_partition.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
success !!
`date_at`
BAK : p20231102 ( LESS THAN 2023-11-03 )
test_p20231102.sql

解説

aws/work/bak_partition.sh

このBashスクリプトは、MySQLデータベース内の特定のパーティションをバックアップするための手順を含んでいます。以下はスクリプトの各部分の説明です：

スクリプトの冒頭では、MySQLの接続情報が設定されています。host、user、pass、db、tbl変数にそれぞれホスト、ユーザー、パスワード、データベース、テーブルの情報が設定されています。
mysql_cmd変数には、MySQLクライアントのコマンドが構築されています。これにより、MySQLデータベースへの接続が確立されます。
partition_info変数には、mysql_cmdを使用してMySQLデータベースにクエリを送信し、テーブルの最も古いパーティションの情報を取得するためのSQLクエリが実行されています。取得された情報は、テーブルのスキーマ、パーティション名、パーティション順序、およびパーティション条件の日付などを含みます。awkコマンドを使用して、特定の列の値を変数に格納しています。
min_p_name変数は、最も古いパーティションの名前を取得します。
min_cond_date変数は、最も古いパーティションの条件日付を取得し、シングルクォートを削除して格納します。
バックアップファイル名 bak を生成します。この名前はテーブル名と最も古いパーティション名に基づいています。
mysqldumpコマンドを使用して、特定のパーティションのデータをエクスポートします。--whereオプションを使用して、特定の条件を満たすデータのみをエクスポートします。この条件は、column（パーティション条件の列）とmin_cond_date（最も古いパーティションの条件日付）を使用しています。エクスポートされたデータは、bak 変数で指定されたファイルに保存されます。
最後に、スクリプトは成功メッセージを表示し、バックアップされたパーティションとその条件を示します。また、バックアップファイルの名前も表示されます。