レプリケーション遅延を発生させないMySQLデータ更新

目的

レプリケーションを行っているMySQL環境にて
大量のデータ更新、削除をサービスを停止させずノーメンテで行うことを目的としています。

背景

大量のデータ更新、削除を行う際、ある程度の塊を対象とした作業を行うことが多いと思います。
(例：ある期間に登録されたデータ, あるタイプを持つデータ)

DELETE FROM hoge WHERE ins_date BETWEEN '2017-01-01 00:00:00' AND '2017-06-30 23:59:59';

その際、単純なWhere句を使用し1クエリで実行をしてしまうと実行に時間がかかり、
同等の実行時間がスレーブでも発生するためレプリケーション遅延の原因となります。

ですので理想としては各UPDATE,DELETEクエリはPrimaryKeyを指定して実行ができると好ましいです。

delete_old_hoge.sql

DELETE FROM hoge WHERE id=1;
DELETE FROM hoge WHERE id=2;
DELETE FROM hoge WHERE id=3;
...
DELETE FROM hoge WHERE id=999999;
DELETE FROM hoge WHERE id=1000000;

mysql -u user -ppass fuga < delete_old_hoge.sql

ですが、各クエリを1SQLファイルにまとめてインポートをしてしまうと、
そのインポートが1トランザクションとして扱われ、この1SQLファイルの処理にかかった時間が
同様にスレーブでもかかり、こちらもレプリケーション遅延の原因となります。

ではどうすればよいのでしょうか？

実行用スクリプトの作成

オススメとしては用意したSQLファイルの各クエリを
1クエリ1トランザクションで反映するスクリプトを作成します。

execute_query.sh

#!/bin/sh

l_num=1

BUFIFS=$IFS
IFS=

exec 3< $1
while read FL 0<&3
do
        if test `expr $l_num % 1000` -eq 0 ; then
                sleep 1s
        fi
        echo "$FL"
        mysql -h [DBHost] -u [User] -p[Password] -A [TableName] -e $FL
        l_num=`expr $l_num + 1`
done
exec 3<&-

IFS=$BUFIFS

実行方法

sh execute_query.sh [SQL File Path]

このような1クエリ1トランザクションでクエリを実行する方法を取ることで
時間こそかかりますが安全にかつ作業も少なくクエリを反映することができます。

1回スクリプトを用意しておくと何かと便利なのでオススメです。