TL;DR

MySQL 8.0.17 でついに俺待望の Cloneプラグインが追加された
- CLONE LOCAL DATA DIRECTORY .. で、ローカルファイルシステムにほぼノンブロッキングで物理バックアップを吐き出す
- CLONE INSTANCE FROM USER@HOST:PORT IDENTIFIED BY 'password' .. で~~グループレプリケーションの経路を使って~~フツーのレプリケーションと同じ3306の経路を使ってインスタンスの丸コピーができるらしい
このへんを読んでおくのが良さげ
- MySQL :: MySQL 8.0 Reference Manual :: 5.6.7 The Clone Plugin
- MySQL :: MySQL 8.0 Reference Manual :: 5.6.7.13 Clone Plugin Limitations

取り敢えず CLONE LOCAL DATA の方。
準備は INSTALL PLUGIN で一発。

mysql80 35> INSTALL PLUGIN CLONE SONAME "mysql_clone.so";
Query OK, 0 rows affected (0.01 sec)

mysql80 35> SHOW PLUGINS;
+---------------------------------+----------+--------------------+-----------------+---------+
| Name                            | Status   | Type               | Library         | License |
+---------------------------------+----------+--------------------+-----------------+---------+
..
| clone                           | ACTIVE   | CLONE              | mysql_clone.so  | GPL     |
+---------------------------------+----------+--------------------+-----------------+---------+
46 rows in set (0.00 sec)

使い方も簡単。 CLONE LOCAL DATA DIRECTORY /tmp/clone とかこれだけ。
ディレクトリーは書き込み権限があれば大丈夫。 CLONE LOCAL DATA ステートメントを実行するアカウントには BACKUP_ADMIN 権限が要るが、それ以外(たとえば、グローバルSELECTとかFLUSHとか)は要らない。

mysql80 35> create user yoku0825;
Query OK, 0 rows affected (0.01 sec)

mysql80 35> grant backup_admin on *.* TO yoku0825;
Query OK, 0 rows affected (0.00 sec)

mysql80 70> SHOW GRANTS;
+---------------------------------------------+
| Grants for yoku0825@%                       |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%`        |
| GRANT BACKUP_ADMIN ON *.* TO `yoku0825`@`%` |
+---------------------------------------------+
2 rows in set (0.00 sec)

mysql80 70> CLONE LOCAL DATA DIRECTORY '/tmp/clone';
Query OK, 0 rows affected (3.66 sec)

tpcc_start -u tpcc -w 10 -S /usr/mysql/8.0.17/data/mysql.sock -d tpcc -l 1200 -r 0 しながらローカルクローンしてみる。

mysql80 35> CLONE LOCAL DATA DIRECTORY '/tmp/clone';
Query OK, 0 rows affected (7.78 sec)

$ ll /tmp/clone
total 161804
drwxr-x--- 2 yoku0825 yoku0825       85 Jul 24 18:31 #clone
drwxr-x--- 2 yoku0825 yoku0825       19 Jul 24 18:31 d1
-rw-r----- 1 yoku0825 yoku0825 12582912 Jul 24 18:31 ibdata1
-rw-r----- 1 yoku0825 yoku0825 50331648 Jul 24 18:31 ib_logfile0
-rw-r----- 1 yoku0825 yoku0825 50331648 Jul 24 18:31 ib_logfile1
drwxr-x--- 2 yoku0825 yoku0825        6 Jul 24 18:31 mysql
-rw-r----- 1 yoku0825 yoku0825 24117248 Jul 24 18:31 mysql.ibd
drwxr-x--- 2 yoku0825 yoku0825       27 Jul 24 18:31 sys
drwxr-x--- 2 yoku0825 yoku0825     4096 Jul 24 18:31 tpcc
-rw-r----- 1 yoku0825 yoku0825 14680064 Jul 24 18:31 undo_001
-rw-r----- 1 yoku0825 yoku0825 13631488 Jul 24 18:31 undo_002

$ du -sh /tmp/clone
1.1G    /tmp/clone

その間のCommit per Secはこんな感じになった。

2019-07-24 18:30:33     value:0/1s      variable_name:Handler_commit
2019-07-24 18:30:34     value:3365/1s   variable_name:Handler_commit
2019-07-24 18:30:35     value:4616/1s   variable_name:Handler_commit
2019-07-24 18:30:36     value:5040/1s   variable_name:Handler_commit
2019-07-24 18:30:37     value:5043/1s   variable_name:Handler_commit
2019-07-24 18:30:38     value:4081/1s   variable_name:Handler_commit
2019-07-24 18:30:39     value:5448/1s   variable_name:Handler_commit
2019-07-24 18:30:40     value:4167/1s   variable_name:Handler_commit
2019-07-24 18:30:41     value:5259/1s   variable_name:Handler_commit
2019-07-24 18:30:42     value:4897/1s   variable_name:Handler_commit
2019-07-24 18:30:43     value:4807/1s   variable_name:Handler_commit
2019-07-24 18:30:44     value:5556/1s   variable_name:Handler_commit
2019-07-24 18:30:45     value:5892/1s   variable_name:Handler_commit
2019-07-24 18:30:46     value:5263/1s   variable_name:Handler_commit
2019-07-24 18:30:47     value:5093/1s   variable_name:Handler_commit
2019-07-24 18:30:48     value:5727/1s   variable_name:Handler_commit
2019-07-24 18:30:49     value:5506/1s   variable_name:Handler_commit
2019-07-24 18:30:50     value:5612/1s   variable_name:Handler_commit
2019-07-24 18:30:51     value:6066/1s   variable_name:Handler_commit
2019-07-24 18:30:52     value:4491/1s   variable_name:Handler_commit
2019-07-24 18:30:53     value:4460/1s   variable_name:Handler_commit
2019-07-24 18:30:54     value:4901/1s   variable_name:Handler_commit
2019-07-24 18:30:56     value:4807/1s   variable_name:Handler_commit
2019-07-24 18:30:57     value:5649/1s   variable_name:Handler_commit
2019-07-24 18:30:58     value:4307/1s   variable_name:Handler_commit
2019-07-24 18:30:59     value:4661/1s   variable_name:Handler_commit
2019-07-24 18:31:00     value:4864/1s   variable_name:Handler_commit
2019-07-24 18:31:01     value:5460/1s   variable_name:Handler_commit
2019-07-24 18:31:02     value:4245/1s   variable_name:Handler_commit
2019-07-24 18:31:03     value:4109/1s   variable_name:Handler_commit
2019-07-24 18:31:04     value:1661/1s   variable_name:Handler_commit <-- CLONE LOCAL DATA開始
2019-07-24 18:31:05     value:1950/1s   variable_name:Handler_commit
2019-07-24 18:31:06     value:2399/1s   variable_name:Handler_commit
2019-07-24 18:31:07     value:2168/1s   variable_name:Handler_commit
2019-07-24 18:31:08     value:2121/1s   variable_name:Handler_commit <-- CLONE LOCAL DATA終了
2019-07-24 18:31:09     value:4773/1s   variable_name:Handler_commit
2019-07-24 18:31:10     value:4563/1s   variable_name:Handler_commit
2019-07-24 18:31:11     value:4592/1s   variable_name:Handler_commit
2019-07-24 18:31:12     value:5245/1s   variable_name:Handler_commit
2019-07-24 18:31:13     value:4650/1s   variable_name:Handler_commit
2019-07-24 18:31:14     value:5380/1s   variable_name:Handler_commit
2019-07-24 18:31:15     value:4874/1s   variable_name:Handler_commit
2019-07-24 18:31:16     value:4629/1s   variable_name:Handler_commit
2019-07-24 18:31:17     value:4493/1s   variable_name:Handler_commit

優秀なのでは！？

エラーログはこんな感じに出てた。Noteなので log_error_verbosity= 3 が必要。

2019-07-24T18:31:03.279399+09:00 35 [Note] [MY-013457] [InnoDB] Clone Begin Master Task by root@localhost
2019-07-24T18:31:03.279482+09:00 35 [Note] [MY-013457] [InnoDB] Clone Apply Master Loop Back
2019-07-24T18:31:03.279494+09:00 35 [Note] [MY-013457] [InnoDB] Clone Apply Begin Master Task
2019-07-24T18:31:03.279727+09:00 35 [Note] [MY-013458] [InnoDB] Clone set state change ACK: 1
2019-07-24T18:31:03.279749+09:00 35 [Note] [MY-013458] [InnoDB] Clone Master received state change ACK
2019-07-24T18:31:03.279779+09:00 35 [Note] [MY-013458] [InnoDB] Clone State Change : Number of tasks = 1
2019-07-24T18:31:03.279793+09:00 35 [Note] [MY-013458] [InnoDB] Clone State BEGIN FILE COPY
2019-07-24T18:31:03.295060+09:00 35 [Note] [MY-011845] [InnoDB] Clone Start PAGE ARCH : start LSN : 1354569844, checkpoint LSN : 13458
13923
2019-07-24T18:31:03.295198+09:00 35 [Note] [MY-013458] [InnoDB] Clone State FILE COPY : 25 chunks,  chunk size : 64 M
2019-07-24T18:31:03.295584+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State Change : Number of tasks = 1
2019-07-24T18:31:03.295609+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State FILE COPY:
2019-07-24T18:31:03.295674+09:00 35 [Note] [MY-011978] [InnoDB] Clone estimated size: 1.00 GiB Available space: 3.06 GiB
2019-07-24T18:31:03.775468+09:00 35 [Note] [MY-013458] [InnoDB] Stage progress: 20% completed.
2019-07-24T18:31:04.884874+09:00 35 [Note] [MY-013458] [InnoDB] Stage progress: 40% completed.
2019-07-24T18:31:06.387871+09:00 35 [Note] [MY-013458] [InnoDB] Stage progress: 60% completed.
2019-07-24T18:31:07.846118+09:00 35 [Note] [MY-013458] [InnoDB] Stage progress: 80% completed.
2019-07-24T18:31:08.347343+09:00 35 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Tune Threads from: 1 to: 2 prev: 1 target: 2.'
2019-07-24T18:31:08.348284+09:00 0 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 1
2019-07-24T18:31:08.348325+09:00 0 [Note] [MY-013457] [InnoDB] Clone Apply Begin Task ID: 1
2019-07-24T18:31:08.521355+09:00 35 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Total Data: 928 MiB @ 177 MiB/sec, Network: 0 MiB @ 0 MiB/sec.'
2019-07-24T18:31:08.521409+09:00 35 [Note] [MY-013458] [InnoDB] Clone set state change ACK: 2
2019-07-24T18:31:08.521428+09:00 35 [Note] [MY-013458] [InnoDB] Clone Master received state change ACK
2019-07-24T18:31:08.521444+09:00 35 [Note] [MY-013458] [InnoDB] Clone State Change : Number of tasks = 2
2019-07-24T18:31:08.521455+09:00 35 [Note] [MY-013458] [InnoDB] Clone State BEGIN PAGE COPY
2019-07-24T18:31:08.544317+09:00 35 [Note] [MY-011840] [InnoDB] Clone Start LOG ARCH : start LSN : 1347785138
2019-07-24T18:31:08.544373+09:00 35 [Note] [MY-011846] [InnoDB] Clone Stop  PAGE ARCH : end   LSN : 1347785138, log sys LSN : 1355428039
2019-07-24T18:31:08.544804+09:00 35 [Note] [MY-013458] [InnoDB] Clone State PAGE COPY : 1551 pages, 78 duplicate pages, 1 chunks,  chunk size : 64 M
2019-07-24T18:31:08.646061+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State Change : Number of tasks = 2
2019-07-24T18:31:08.646130+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State PAGE COPY:
2019-07-24T18:31:10.222406+09:00 35 [Note] [MY-013458] [InnoDB] Stage progress: 100% completed.
2019-07-24T18:31:10.222447+09:00 35 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Total Data: 952 MiB @ 137 MiB/sec, Network: 0 MiB @ 0 MiB/sec.'
2019-07-24T18:31:10.222461+09:00 35 [Note] [MY-013458] [InnoDB] Clone set state change ACK: 3
2019-07-24T18:31:10.222470+09:00 35 [Note] [MY-013458] [InnoDB] Clone Master received state change ACK
2019-07-24T18:31:10.222480+09:00 35 [Note] [MY-013458] [InnoDB] Clone State Change : Number of tasks = 2
2019-07-24T18:31:10.222487+09:00 35 [Note] [MY-013458] [InnoDB] Clone State BEGIN REDO COPY
2019-07-24T18:31:10.340523+09:00 35 [Note] [MY-011841] [InnoDB] Clone Stop  LOG ARCH : end LSN : 1356084650
2019-07-24T18:31:10.340576+09:00 35 [Note] [MY-013458] [InnoDB] Clone State REDO COPY : 3 chunks,  chunk size : 64 M
2019-07-24T18:31:10.442258+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State Change : Number of tasks = 2
2019-07-24T18:31:10.442296+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State REDO COPY:
2019-07-24T18:31:10.450725+09:00 35 [Note] [MY-013458] [InnoDB] Stage progress: 33% completed.
2019-07-24T18:31:10.450778+09:00 35 [Note] [MY-013458] [InnoDB] Stage progress: 66% completed.
2019-07-24T18:31:10.450791+09:00 35 [Note] [MY-013458] [InnoDB] Stage progress: 100% completed.
2019-07-24T18:31:10.550946+09:00 35 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Total Data: 960 MiB @ 132 MiB/sec, Network: 0 MiB @ 0 MiB/sec.'
2019-07-24T18:31:10.550995+09:00 35 [Note] [MY-013458] [InnoDB] Clone set state change ACK: 4
2019-07-24T18:31:10.551009+09:00 35 [Note] [MY-013458] [InnoDB] Clone Master received state change ACK
2019-07-24T18:31:10.551019+09:00 35 [Note] [MY-013458] [InnoDB] Clone State Change : Number of tasks = 2
2019-07-24T18:31:10.551026+09:00 35 [Note] [MY-013458] [InnoDB] Clone State DONE
2019-07-24T18:31:10.654535+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State Change : Number of tasks = 2
2019-07-24T18:31:10.654584+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State FLUSH DATA:
2019-07-24T18:31:10.697683+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State FLUSH REDO:
2019-07-24T18:31:11.015694+09:00 35 [Note] [MY-013458] [InnoDB] Clone Apply State DONE
2019-07-24T18:31:11.059224+09:00 0 [Note] [MY-013457] [InnoDB] Clone Apply End Task ID: 1 Passed, code: 0:
2019-07-24T18:31:11.059277+09:00 0 [Note] [MY-013457] [InnoDB] Clone End Task ID: 1 Passed, code: 0:
2019-07-24T18:31:11.059677+09:00 35 [Note] [MY-013457] [InnoDB] Clone Apply End Master Task ID: 0 Passed, code: 0:
2019-07-24T18:31:11.059910+09:00 35 [Note] [MY-013457] [InnoDB] Clone End Master Task ID: 0 Passed, code: 0:

取り敢えず感動をお伝えしたかった。

日々の覚書

GA

2019/07/24

MySQL 8.0.17でついにCloneプラグインが入った

TL;DR

0 件のコメント :

コメントを投稿