MySQLでバイナリログを利用
ローカルに、HomebrewでMySQLをインストールした。
ちなmac使用。
my.cnfにlog-binという一行を追加してやると、MySQLサーバーの起動時・停止時にバイナリログを出力してくれるようになる。
% pwd
/usr/local/etc
% cat my.cnf
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
log-bin
もちろん、設定変更後はMySQLサーバーの再起動を忘れないでね。
バイナリログファイルは /usr/local/var/mysql ディレクトリ配下に格納される。
バイナリログファイルのみならず、デフォルトのエラーログファイルとかMySQLサーバー起動時に必要なPIDファイルとかなんかも、ここにあった。
% pwd
/usr/local/var/mysql
% ls
#ib_16384_0.dblwr
ib_logfile0
performance_schema
#ib_16384_1.dblwr
ib_logfile1
private_key.pem
#innodb_temp
ibdata1
public_key.pem
auto.cnf ibtmp1
server-cert.pem
binlog.000001
username-bin.000001
server-key.pem
binlog.index
username-bin.000002
sys
ca-key.pem
username-bin.index
test
ca.pem
username.local.err
undo_001
client-cert.pem
username.local.pid
undo_002
client-key.pem
ib_buffer_pool
...ん？でも、このmy.cnfで設定したやつ起因のバイナリログファイルって、
binlog.000001
と
username-bin.000001
username-bin.000002
のうち、どっちなんだろうな。
どっちかが、デフォルトで出力されているバイナリファイルなんではないか。
試しに、my.cnfの設定を変更してみる（インストールした直後のデフォルトの状態へ戻す）。
% cat /usr/local/etc/my.cnf
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
MySQLサーバーを再起動させて、再度/usr/local/var/mysqlのバイナリログファイルらしきものを確認してみる。
% sudo mysql.server restart
% cd /usr/local/var/mysql
% ls
余計なファイルがありすぎるので、バイナリログファイルっぽいものだけ抜粋すると...。
binlog.000001
binlog.000002
username-bin.000001
username-bin.000002
binlogの方が1ファイル増えておる。
再度、MySQLサーバーを再起動してみる。
% sudo mysql.server restart
Shutting down MySQL
. SUCCESS!
Starting MySQL
.. SUCCESS!
再度バイナリログファイルを確認してみる。
binlog.000001
binlog.000002
binlog.000003
username-bin.000001
username-bin.000002
やはり、binlogの方が1ファイル増えている。
てことは、今はもうデフォルトでbinlog.{連番} という形式でバイナリログファイルを出力してくれているということだな。
この状態で、mysqlにテーブルとレコードをセット。
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 DATETIME);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES(1, NOW());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+----+---------------------+
| c1 | c2 |
+----+---------------------+
| 1 | 2021-10-28 08:54:12 |
+----+---------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
最新のバイナリログファイルをrecover.sqlという名前のsql文ファイルへ変換。
% sudo mysqlbinlog binlog.000003 > ~/recover.sql
確認してみると、SETほにゃららみたいな文言があったので、このバイナリファイルにテーブル＆レコードをインサートしたSQL文実行履歴が確かに記録されていたとわかる。
% less ~/recover.sql
t1テーブルのレコードを削除してみる。
mysql> SHOW tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t1;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> quit
Bye
さっき作ったrecover.sqlをmysqlコマンドで実行。
MySQLに再度接続して確認。
mysql> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM t1;
+----+---------------------+
| c1 | c2 |
+----+---------------------+
| 1 | 2021-10-28 08:54:12 |
+----+---------------------+
1 row in set (0.00 sec)
mysql> quit;
Bye
おっ！無事レコードが復旧してますね！！
ちなみに、ずっとバックアップ取ってなくてかつその間に何度もサーバーが落ちていたら、その間に発生したバイナリログファイルを複数sql文へ書き換えては実行して、というのをやらないといけない。
