テーブルの1日前のデータを自動で削除する方法【MySQL】

■はじめに

テーブルに登録しているデータで「TIMESTAMP」を使用することがあります。
この「TIMESTAMP」のデータを使用して、1日前のデータは削除するなど、一定期間で削除する方法を記載します。
もちろん手動でSQLを流してもいいですが、手間なので、今回は自動で削除する方法を記載します。

■手順

まずは、以下で、24時間前以前のデータを取得することができます。

SELECT * FROM [DB名].[テーブル名] WHERE TIMESTAMPDIFF(HOUR, [TIMESTAMPのカラム名], CURRENT_TIMESTAMP()) > 24

上記をちょっと解説すると、「TIMESTAMPDIFF」というSQLの関数を利用して時間の差分を出しています。
第一引数に「HOUR」とすることで、時間の差分になります。
※ちなみに、「MINUTE」なら分、「SECOND」なら秒です。

では、上記を元に、今度は削除のSQLを以下に記載します。

DELETE FROM [DB名].[テーブル名] WHERE TIMESTAMPDIFF(HOUR, [TIMESTAMPのカラム名], CURRENT_TIMESTAMP()) > 24

これで、削除のSQLは完成です。
ただ、今回は自動で削除したいので、cronを使用します。

以下のコマンドでcronの編集モードにします。

crontab -e

編集モードになったら、以下を記載します。

0 0 * * * mysql --user=[ユーザー名] --password=[パスワード] --database=[DB名] --execute="DELETE FROM [DB名].[テーブル名] WHERE TIMESTAMPDIFF(HOUR, [TIMESTAMPのカラム名], CURRENT_TIMESTAMP()) > 24" > /var/log/mysql-[DB名]-[カラム名]-delete.log 2>&1

上記は、毎日0時にDELETE文を実行するcronです。
実行されたら、以下にログが出力されるので、確認してみてください。

/var/log/mysql-[DB名]-[カラム名]-delete.log

MySQLのアカウント作成方法【MySQL】

はじめに

プロジェクト毎、環境毎などDBを分けることはよくあると思います。
当然、全てrootで操作するとセキュリティ上よくないです。
ここでは、アカウントを作成し、DBの操作権限を付与する部分を記載します。

手順

rootユーザーでMySQLに入ってください。

mysql -u root -p

以下のSQL文を実行し、ユーザーを作成する。

CREATE USER '[ユーザー名]'@'localhost' IDENTIFIED BY '[パスワード]';

以下のSQL文を実行し、ユーザーが操作できるテーブルを追加する。

GRANT ALL PRIVILEGES ON [db名].* TO '[ユーザー名]'@'localhost';

以下のSQL文を実行し、変更を反映させる。

FLUSH PRIVILEGES;

これで、アカウント作成とDBの設定ができました。

ユーザーのパスワードを変更する方法

以下のSQL文を実行し、パスワードを変更し、変更を反映する。

ALTER USER '[ユーザー名]'@'localhost' IDENTIFIED WITH mysql_native_password BY '[パスワード]';
FLUSH PRIVILEGES;