MySQL8.0 でごにょごにょしてみた（仮） つづき
さて、前回の続きでっす。
run-around.hatenablog.com
バイナリログについてちょっとだけ勉強してみた。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.2.4 バイナリログ
リンクはバージョンが5.6なのだけど、リファレンスマニュアルです。
バイナリログには、テーブル作成操作やテーブルデータへの変更などのデータベース変更を記述する「イベント」が格納されます。
ふーむ。
バイナリロギングを有効にしてサーバーを実行すると、パフォーマンスがいくらか低下します。ただし、レプリケーションをセットアップでき、リストア操作に対応できるというバイナリログの利点は、一般的にこのパフォーマンスの減少よりも重要です。
確かに、書き込みが増えるんだからパフォーマンスには影響するでしょうなぁ。
このバイナリログ、MySQL 8.0からデフォルトで出力されているらしいです。
あ、ってことは8.0以前は自分で設定変えないと出力出来なかったってことかしら。
前回書いたバックアップしたダンプデータだけではバックアップを取得した時点のデータしか戻せない。
でもこのバイナリログを利用することで戻したい時点を選んでデータを戻せるってことですかね。
で、バイナリログってどんな感じってことで触ってみることにします。
全てのバイナリログファイル名を表示してみます。
mysql> show master logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000002 | 199 | | binlog.000003 | 1782 | +---------------+-----------+ 2 rows in set (0.02 sec)
１番のファイルがないのは、ちょっと試しに消してみたので無くなってますw
気にしないでー。
３番のファイルがデカイっすね。テーブル作成やデータ登録したからここに詰まってるんですかね。
で、今使ってるログファイルはどれなの？を調べるのはこれかな。
mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000003 | 1782 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
あ、やっぱり３番のファイルがナウなんですな。
今３番なのですが次のファイルに切り替えたいなーっていう時はこれ。
mysql> flush binary logs; Query OK, 0 rows affected (0.06 sec)
本当に切り替わったかしら、と確認してみる。
mysql> show master logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000002 | 199 | | binlog.000003 | 1826 | | binlog.000004 | 155 | +---------------+-----------+ 3 rows in set (0.02 sec)
おー。４番のファイルが出来たー。
不要なバイナリログの削除も出来ちゃう。２番消しちゃおうかな。
ちょっと注意なのは、２番を消したい場合は「３番以前のファイルをパージ」って設定するんですね。
mysql> purge master logs to 'binlog.000003'; Query OK, 0 rows affected (0.02 sec) mysql> show master logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000003 | 1826 | | binlog.000004 | 155 | +---------------+-----------+ 2 rows in set (0.00 sec)
全てのバイナリログ削除も出来るんですが、今回はやんないw
reset master;
バイナリログってテーブル変更時などに記録されるんですよね。selectとかじゃ記録されないのかな？
mysql> show master logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000003 | 1826 | | binlog.000004 | 155 | +---------------+-----------+ 2 rows in set (0.00 sec) mysql> select * from stray_cat_tbl; +-----+----------+---------------+ | seq | cat_name | cat_type | +-----+----------+---------------+ | 1 | Black | NULL | | 2 | Mike | Tortoiseshell | +-----+----------+---------------+ 2 rows in set (0.00 sec) mysql> show master logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000003 | 1826 | | binlog.000004 | 155 | +---------------+-----------+ 2 rows in set (0.00 sec)
うん、やっぱりバイナリログは動いてないなー。
で、データ登録をすると・・・。
mysql> show master logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000003 | 1826 |
| binlog.000004 | 155 |
+---------------+-----------+
2 rows in set (0.00 sec)
mysql> INSERT INTO house_cat_tbl (cat_name, cat_type) VALUES ('Anko', 'Mix');
Query OK, 1 row affected (0.18 sec)
mysql> show master logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000003 | 1826 |
| binlog.000004 | 457 |
+---------------+-----------+
2 rows in set (0.00 sec)おー。バイナリログが４番に書き込まれたー。
バイナリログからロールフォワード用SQL文を生成っていうのをやってみようかと。
まず実際のバイナリログってどこに保存されてるんですかね？
ほほぅ。ここのパスなのか。
mysql> show variables like 'datadir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | datadir | /usr/local/var/mysql/ | +---------------+-----------------------+ 1 row in set (0.02 sec)
んじゃ、本当にファイルがあるか確認。ありますねー。ちゃんとある。
ls -la /usr/local/var/mysql/ total 336000 drwxr-xr-x 28 run admin 952 10 21 14:07 . drwxrwxr-x 4 run admin 136 10 16 23:47 .. -rw-r----- 1 run admin 2175 10 20 23:58 USERnoMacBook-Pro.local.err -rw-r----- 1 run admin 6 10 17 20:35 USERnoMacBook-Pro.local.pid -rw-r----- 1 run admin 56 10 16 23:47 auto.cnf -rw-r----- 1 run admin 1826 10 21 13:59 binlog.000003 -rw-r----- 1 run admin 457 10 21 14:22 binlog.000004 -rw-r----- 1 run admin 32 10 21 14:07 binlog.index (略)
ロールフォワード用SQLを作ってみる。指定するバイナリログは３番にします！
mysqlbinlog --disable-log-bin --start-position=0 /usr/local/var/mysql/binlog.000003 > /Users/run/Downloads/recover.sql cat /Users/run/Downloads/recover.sql /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!32316 SET @OLD_SQL_LOG_BIN=@@SQL_LOG_BIN, SQL_LOG_BIN=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #181017 20:47:53 server id 1 end_log_pos 124 CRC32 0xdbb81c39 Start: binlog v 4, server v 8.0.12 created 181017 20:47:53 BINLOG ' aSHHWw8BAAAAeAAAAHwAAAAAAAQAOC4wLjEyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgE5HLjb '/*!*/; # at 124 #181017 20:47:53 server id 1 end_log_pos 155 CRC32 0x3c0d7a6a Previous-GTIDs # [empty] # at 155 #181021 0:07:40 server id 1 end_log_pos 228 CRC32 0x34c60bf0 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1540048060324006 immediate_commit_timestamp=1540048060324006 transaction_length=182 # original_commit_timestamp=1540048060324006 (2018-10-21 00:07:40.324006 JST) # immediate_commit_timestamp=1540048060324006 (2018-10-21 00:07:40.324006 JST) /*!80001 SET @@session.original_commit_timestamp=1540048060324006*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 228 #181021 0:07:40 server id 1 end_log_pos 337 CRC32 0x6b672ae7 Query thread_id=9 exec_time=0error_code=0 Xid = 15 SET TIMESTAMP=1540048060/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/; create database miaow /*!*/; # at 337 #181021 0:12:52 server id 1 end_log_pos 412 CRC32 0x3a6d06c7 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1540048373074347 immediate_commit_timestamp=1540048373074347 transaction_length=379 # original_commit_timestamp=1540048373074347 (2018-10-21 00:12:53.074347 JST) # immediate_commit_timestamp=1540048373074347 (2018-10-21 00:12:53.074347 JST) /*!80001 SET @@session.original_commit_timestamp=1540048373074347*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 412 #181021 0:12:52 server id 1 end_log_pos 716 CRC32 0x900b0bd2 Query thread_id=9 exec_time=1error_code=0 Xid = 23 use `miaow`/*!*/; SET TIMESTAMP=1540048372/*!*/; CREATE TABLE `house_cat_tbl` ( `seq` int(10) unsigned NOT NULL auto_increment, `cat_name` varchar(255) NOT NULL, `cat_type` varchar(255) default NULL, PRIMARY KEY (`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!*/; # at 716 #181021 0:12:54 server id 1 end_log_pos 791 CRC32 0xa8495111 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no original_committed_timestamp=1540048375056693 immediate_commit_timestamp=1540048375056693 transaction_length=379 # original_commit_timestamp=1540048375056693 (2018-10-21 00:12:55.056693 JST) # immediate_commit_timestamp=1540048375056693 (2018-10-21 00:12:55.056693 JST) /*!80001 SET @@session.original_commit_timestamp=1540048375056693*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 791 #181021 0:12:54 server id 1 end_log_pos 1095 CRC32 0x31c4cf25 Query thread_id=9 exec_time=1 error_code=0 Xid = 24 SET TIMESTAMP=1540048374/*!*/; CREATE TABLE `stray_cat_tbl` ( `seq` int(10) unsigned NOT NULL auto_increment, `cat_name` varchar(255) NOT NULL, `cat_type` varchar(255) default NULL, PRIMARY KEY (`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!*/; # at 1095 #181021 0:20:11 server id 1 end_log_pos 1170 CRC32 0x42108fb4 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes original_committed_timestamp=1540048811250826 immediate_commit_timestamp=1540048811250826 transaction_length=687 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1540048811250826 (2018-10-21 00:20:11.250826 JST) # immediate_commit_timestamp=1540048811250826 (2018-10-21 00:20:11.250826 JST) /*!80001 SET @@session.original_commit_timestamp=1540048811250826*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1170 #181021 0:18:53 server id 1 end_log_pos 1246 CRC32 0x27a08215 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1540048733/*!*/; BEGIN /*!*/; # at 1246 #181021 0:18:53 server id 1 end_log_pos 1315 CRC32 0x0151adba Table_map: `miaow`.`house_cat_tbl` mapped to number 67 # at 1315 #181021 0:18:53 server id 1 end_log_pos 1371 CRC32 0x87f099de Write_rows: table id 67 flags: STMT_END_F BINLOG ' XUfLWxMBAAAARQAAACMFAAAAAEMAAAAAAAEABW1pYW93AA1ob3VzZV9jYXRfdGJsAAMDDw8E/QL9 AgQBAYACASG6rVEB XUfLWx4BAAAAOAAAAFsFAAAAAEMAAAAAAAEAAgAD/wABAAAABABUYW1hCABKYXBhbmVzZd6Z8Ic= '/*!*/; # at 1371 #181021 0:18:53 server id 1 end_log_pos 1440 CRC32 0x33a35344 Table_map: `miaow`.`house_cat_tbl` mapped to number 67 # at 1440 #181021 0:18:53 server id 1 end_log_pos 1505 CRC32 0x8727ce76 Write_rows: table id 67 flags: STMT_END_F BINLOG ' XUfLWxMBAAAARQAAAKAFAAAAAEMAAAAAAAEABW1pYW93AA1ob3VzZV9jYXRfdGJsAAMDDw8E/QL9 AgQBAYACASFEU6Mz XUfLWx4BAAAAQQAAAOEFAAAAAEMAAAAAAAEAAgAD/wACAAAAAwBab2USAEFtZXJpY2FuIFNob3J0 aGFpcnbOJ4c= '/*!*/; # at 1505 #181021 0:18:53 server id 1 end_log_pos 1574 CRC32 0xe64ddbc0 Table_map: `miaow`.`stray_cat_tbl` mapped to number 68 # at 1574 #181021 0:18:53 server id 1 end_log_pos 1621 CRC32 0x06e417a5 Write_rows: table id 68 flags: STMT_END_F BINLOG ' XUfLWxMBAAAARQAAACYGAAAAAEQAAAAAAAEABW1pYW93AA1zdHJheV9jYXRfdGJsAAMDDw8E/QL9 AgQBAYACASHA203m XUfLWx4BAAAALwAAAFUGAAAAAEQAAAAAAAEAAgAD/wQBAAAABQBCbGFja6UX5AY= '/*!*/; # at 1621 #181021 0:18:53 server id 1 end_log_pos 1690 CRC32 0x94a25b09 Table_map: `miaow`.`stray_cat_tbl` mapped to number 68 # at 1690 #181021 0:18:53 server id 1 end_log_pos 1751 CRC32 0x5b723527 Write_rows: table id 68 flags: STMT_END_F BINLOG ' XUfLWxMBAAAARQAAAJoGAAAAAEQAAAAAAAEABW1pYW93AA1zdHJheV9jYXRfdGJsAAMDDw8E/QL9 AgQBAYACASEJW6KU XUfLWx4BAAAAPQAAANcGAAAAAEQAAAAAAAEAAgAD/wACAAAABABNaWtlDQBUb3J0b2lzZXNoZWxs JzVyWw== '/*!*/; # at 1751 #181021 0:20:11 server id 1 end_log_pos 1782 CRC32 0x5783d3c3 Xid = 27 COMMIT/*!*/; # at 1782 #181021 13:59:19 server id 1 end_log_pos 1826 CRC32 0xd8f503e3 Rotate to binlog.000004 pos: 4 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!32316 SET SQL_LOG_BIN=@OLD_SQL_LOG_BIN*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
なんか凄いの出たw
次はバイナリログからデータベースを復元する！をやります。
じゃ、まずはデータベースを削除しますかー。
mysql> drop database miaow; Query OK, 2 rows affected (0.65 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.02 sec)
ほいほい、消えましたね。ばっさり。
で、コマンドを実行するわけですが・・・「mysql.sock」のありかが分からなかったんで、調べてみた。
え、こんなとこにあるの？w
mysql_config --socket /tmp/mysql.sock
でもって、さっき作ったロールフォワード用SQLを作ってようやく実行。
mysql --user=root --password=(パスワード) --socket=/tmp/mysql.sock --default-character-set=utf8mb4 < /Users/run/Downloads/recover.sql
さて、データベースは復活したかな？
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | miaow | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
わーい！みぁおぅ！w
テーブルも確認してみます。
mysql> select * from house_cat_tbl; +-----+----------+--------------------+ | seq | cat_name | cat_type | +-----+----------+--------------------+ | 1 | Tama | Japanese | | 2 | Zoe | American Shorthair | +-----+----------+--------------------+ 2 rows in set (0.00 sec) mysql> select * from stray_cat_tbl; +-----+----------+---------------+ | seq | cat_name | cat_type | +-----+----------+---------------+ | 1 | Black | NULL | | 2 | Mike | Tortoiseshell | +-----+----------+---------------+ 2 rows in set (0.00 sec)
そう、ロールフォワード用SQLを作る時、３番のログを指定しているので、以下のデータは含まれないんですよね。
mysql> INSERT INTO house_cat_tbl (cat_name, cat_type) VALUES ('Anko', 'Mix');まー、これでなんとか指定時点のデータを戻すっていうのは検証出来たかな。
お疲れ様ー。
