たまごかける日報

ここにAA貼りたい

Transportable Tablespace@InnoDBを使ってみたよ

最近飲み歩きたい欲が凄い。
そろそろ3大欲求に食い込んでくるんじゃないかと思ってる。
いや流石に嘘だけど。

というわけで、 Transportable Tablespaces@InnoDBを使ってみたって話。

TL;DR

  • MySQL5.6.6から使えるようになったTransportable Tablespaces@InnoDBつかってみた
  • file-per-tableのテーブルスペースとメタデータの転送でデータ移行ができる
  • ので以下の特徴がある
    • データを直接ポンと置くのでmysqldumpとかよりも早い
    • import時にテーブルスペースの破損check、index再構築などをしてくれる
    • 種サーバからメタデータを作るときは、対象テーブルに共有ロックがかかっちゃうよ

経緯

とあるStorageからMySQLへの移行的なモノを行なっていた。

作業用MySQLサーバで種データを作って本番サーバにデータを配布したいんだけど結構全体容量がデカイのでどうするか悩んでいた。出来れば簡単かつチョッパやでオシャレな感じでデータ配布したい。

そんな折に、MyISAMみたいにfileをcopyしてきてデータ移行が出来るTransportable Tablespaceなる機能がInnoDBにあると知り、試してみることに。

みたいな、ざっくりってるから正確にはチョト違うんだけど、こんな感じのが経緯で触らせて頂くことに。

前提

すべてのサーバで

  • MySQL 5.6.6 以降である
  • innodb_file_per_tableがONになってる
  • ページサイズが一緒

対象サーバ
種鯖:10.10.10.10
本番master:10.10.10.11
本番slave:10.10.10.1[2-5]

aho DBにある tmp_?? みたいな正規表現で引っかかるtable * 256個をよそDBにimportしてみよう。

作業

流れ的には

  • 移行先にテーブルつくる
  • 種サーバで一瞬メタデータ作る
  • メタデータibd fileを転送して、移行先サーバのデータディレクトリに展開
  • importする

簡単ンゴ

なんで実際にやったcommand的なアレをメモしておく。

配布先に空のテーブルだけ作って待ち構えるで

### 種サーバ作業
# tmp_??のテーブル定義抜き出して、転送するよ
$ ssh 10.10.10.10
$ mysql -uroot -N -D aho -e "show tables like 'tmp___';" | cat | xargs mysqldump --no-data -uroot aho > /tmp/aho_tmp___.dump
$ scp /tmp/aho_tmp___.dump 10.10.10.11:/tmp

### masterサーバ作業
## tmp_??のテーブルを本番サーバで作るで
$ ssh 10.10.10.11 
$ mysql -uroot -D test -vvv < /tmp/aho_tmp___.dump > /tmp/aho_tmp___.dump.out

## 対象テーブルに排他ロックかけてテーブルスペースを切り離すよ
## つまり、ALTER TABLE {対象テーブル} DISCARD TABLESPACE; のSQLを作って流すよ
$ mysql -uroot -N -D aho -e "show tables like 'tmp___'" | cat | xargs -i echo "ALTER TABLE {} DISCARD TABLESPACE;" > /tmp/aho_DISCARD_TABLESPACE_tmp___.ddl
# 流すよ
$ mysql -uroot -D aho -vvv < /tmp/aho_DISCARD_TABLESPACE_tmp___.ddl > /tmp/aho_DISCARD_TABLESPACE_tmp___.ddl.out

データを種サーバから配るで

### 種サーバ作業
## 共有ロックかける代わりにメタデータ吐き出すよ
## 複数テーブルのメタデータを一気に吐き出して転送したいから、1個のセッション内で作業してね
## FLUSH TABLES {対象テーブル} FOR EXPORT; のSQLを作って実行するよ
$ ssh 10.10.10.10
$ echo -e "ALTER TABLE `mysql -uroot -N -D aho -e \"show tables like 'tmp____'\" | cat | xargs -i echo -n {},` FOR EXPORT;" | sed "s/, FOR/ FOR/g" > /tmp/aho_ALTER_TABLE_tmp___FOR_EXPORT.ddl
$ mysql -uroot -D aho
mysql> さっき作ったSQLをぺろって貼ってね
mysql> まだexitしないでね!メタデータが消えちゃうよ!

# 別セッションで作業するよ
$ ssh 10.10.10.10
$ sudo ls /var/lib/mysql/aho/tmp_??.{ibd,cfg} | wc -l
512
# 確認してみると、tmp_??テーブル256個分のメタデータtmp_??.cfgがちゃんと256個作られてるよ。この2つを転送先サーバに送るから固めよう
$ cd /var/lib/mysql/aho/
$ tar cfv ../aho_tmp_??.tar tmp_??.{ibd,cfg}

## メタデータ込みのデータを固められたから、もうロック解除していいよ。
# さっきのmysqlのセッションに戻ってね
mysql> UNLOCK TABLES;
mysql> exit
# ちなみに、ロックを解除したらメタデータは消えるよ
$ sudo ls /var/lib/mysql/aho/tmp_??.cfg
ls: cannot access /var/lib/mysql/aho/tmp_??.cfg: そのようなファイルやディレクトリはありません

データをimportするよ

### 本番サーバ全台
## importするデータを引っ張ってくるよ
$ ssh 10.10.10.1{1-5}
$ cd /var/lib/mysql/
$ scp 10.10.10.10:/var/lib/mysql/aho_tmp_??.tar .
$ cd aho
$ tar xfv ../aho_tmp_??.tar
# ahoディレクトリ配下に.ibdと.cfgを設置したよ

### 本番masterサーバ作業
## ALTER TABLE {対象table} IMPORT TABLESPACE; のSQLつくって流すよ
$ ssh 10.10.10.11
$ mysql -uroot -N -D aho -e "show tables like 'tmp___';" | cat | xargs -i echo -e "ALTER TABLE {} IMPORT TABLESPACE;" > /tmp/aho_ALTER_TABLE_tmp___IMPORT_TABLESPACE.ddl
$ mysql -uroot -D aho -vvv < /tmp/aho_ALTER_TABLE_tmp___IMPORT_TABLESPACE.ddl > /tmp/aho_ALTER_TABLE_tmp___IMPORT_TABLESPACE.ddl.out
# メタデータはもういらないから、移行が終わったら消しておこうね
$ rm /var/lib/mysql/aho/*.cfg

まとめ的なアレ

悪くないんだけど、ちょっと制限が多いし手順がちょいちょいあって面倒くさい、、、

でも今回、データ移行時にテーブルのカテゴリ別にちまちま分割してimportしてたんだけど、70GB (table30個、各5000万ちょっとレコード) ぐらいの奴がSAS Raid10でimportに40分ちょっとぐらいだったから、遅くない、、、よね??( •̀ㅁ•́;)

メンテ打ってこのテーブルだけ他のSSDの鯖に移すぜ!とかいうシーンだったら重宝するかな?master分割的な?

あとは今回のケースみたいに、 Archives的な基本的に更新のないデータのStorage移行時とかには、移行用テーブルデータの用意が出来たらポンポン本番に追加していくとかには使えるのかな。

的な。
おわり🍺

参考ページ
14.4.6 Copying File-Per-Table Tablespaces to Another Server