お仕事で、CentOS上で定期的にMySQLのデータを削除するためcronを使うことになり、下記のサイト様を参考にさせていただきました。
参考サイト様 forward ShellScriptでsqlを定期実行
その中で、躓いたところ調べた内容などをまとめました。
最近本当忘れるの速くて…調べて参考になりそうなサイトは全てPocketに入れておくのですが、 内容がなかなか記憶に定着しないので、もっとアウトプットしていかなくてはと反省しております。
存在チェックを行うため「EXISTS」を使用
参考サイト様 forward 【MySQL入門】exists句の使い方を理解しよう！データが存在するかをチェック
今回、SQL文の中で存在チェックを行う処理があるので、「EXISTS」を使用しました。(T_SAMPLE2の中にT_SAMPLE1のSEQと同じSEQを持つデータがあるかどうか)
該当データが存在する場合「true」で返却してくれるので簡単でした。
DELETE FROM T_SAMPLE1 WHERE EXISTS ( SELECT * FROM T_SAMPLE2 WHERE T_SAMPLE2.SEQ = T_SAMPLE1.SEQ )
逆に存在しないかどうかをチェックする「NO EXISTS」もあるので、機会があれば使ってみようと思います。
日数を加算するため「DATE_ADD」を使用
参考サイト様 forward 日付関数(比較, 加算, 差分, 抽出)の使い方
SQLで「1週間前のデータを削除する」という条件を指定したかったので、「DATE_ADD」を使用しました。
今回は、日数の加算を行いましたが、時間や日数と時間を組み合わせた加算なども行えるので、便利ですね。
DELETE FROM T_SAMPLE2 WHERE DATE_ADD ( CREATE_DATETIME , INTERVAL 7 DAY ) <= NOW();
MySQLのユーザー名とパスワードは別ファイルを使用
参考サイト様 forward パスワードを入れてmysqlコマンドを実行すると「Warning: Using a password on the command line interface can be insecure」が表示される
当初、MySQLのユーザー名もパスワードもすべて、ShellScriptの中に記載していたのですが、実際に動かしたところ「警告」が表示されてしまいました。
mysqldump:Warning: Using a password on the command line interface can be insecure
これは、パスワードをShellScriptに記述するのはよろしくないですよとのことなので、 別ファイルを作成し(mysql-dbaccess.cnf)、ユーザーとパスワードを記述しました。
[client]
user = username
password = password
で、このファイルを他から利用出来ないように最小限の権限設定を行いました。
chmod 400 mysql-dbaccess.cnf
準備は出来たので、SQL文実行ファイルを変更して完了です。 これで、警告が表示されなくなりました。
mysql -h $MYSQL_HOST -u $MYSQL_USER -p $MYSQL_DATABASE --password='$MYSQL_PASSWORD' -e '$MYSQL_SQL'
↓
mysql --defaults-extra-file=mysql-dbaccess.cnf -h $MYSQL_HOST $MYSQL_DATABASE -e '$MYSQL_SQL'
参考にさせていただいたサイト
- ShellScriptでsqlを定期実行
- 【MySQL入門】exists句の使い方を理解しよう！データが存在するかをチェック
- 日付関数(比較, 加算, 差分, 抽出)の使い方
- パスワードを入れてmysqlコマンドを実行すると「Warning: Using a password on the command line interface can be insecure」が表示される
編集後記
当初SQL文の中で、テーブルに別名をつけて使用するつもりだったのですが、上手くいきませんでした。原因はまだ分かっていないのですが、別名を付けずに記述すればエラーが解消したので、現在別名を付けていないのですが、長いSQL文などの場合は出来ないと不便なので、原因が分かり次第こちらに追記していきたいと思います。
久しぶりのコーディング楽しかったです。今年は、個人的にWebサービス構築したり色々挑戦していきたいですね。