MySQLのデータ移設時の考慮事項

社内の手順書鯖に書きなぐってたやつですがたまに見たりポインタするのでグローバルから見たいとき用に備忘録。
マネージドなPaaS使ってる人はそっちのマニュアルも見たほうがいいんじゃないかと思います。
あと移すんじゃなくてバージョン上げたいなら公式サイトに載っているのでそちらをどうぞ。
5.5に上げる場合に廃止されたオプションの解説

長いけど主にmysqldumpのことしかかいていません。
コールドバックアップ移設やバイナリログでポイントインタイムリカバリで差分移設などは書いてないです。
あとGTID（グローバルトランザクションID）とその制約のこともポインタのみですみません。

###★項目★
・よく使うmysqldumpオプション(InnoDBのみ、バイナリログ出力時)
・mysqldumpオプションのデフォルト値について
・主要なmysqldumpオプションの意味など
・特定のスキーマだけレストア、スキーマやテーブルごとに切り分け、where句をつかう
・文字コードに注意
・データの整合性に注意
・read_onlyを有効にしてもGRANT ALLのアカウントでは更新されるので注意
・明示的にオプションでスキップしない限りデータは上書きされるので注意
・バイナリログを一時的に出さないようにすると速度アップ
・パスワード不明時の再設定の方法について
・replicationが絡む場合の移設方法について
・my.cnfのパラメータが変わる場合のmysqld再起動時の注意点
・MySQL-5.5以降で使える--dump-slave
・slaveを増やしたい場合の方法について
・MySQL-5.6以降のGTIDがらみのオプションなど
・MySQL-5.7以降からつかえるmysqlpump
・データ量とメモリサイズについてとハードウェアなど
・ほかのほうほうのメモ
・作業見積もりなど

###★詳細★

移設時に徹底すべきことは、可能な限り環境を変えないことです。
そうすることで問題が起きた場合の切り分けをしやすくなります。
以下に記載している移行元と移行先は書いた当初はmysqlの5.0または5.1系で、移行時のバージョン変更などは基本ない前提。
バージョンが変わる場合はアプリケーションからのSQLのテストが必要になります。

####▼よく使うmysqldumpオプション(InnoDBのみ、バイナリログを出力している場合)

--all-databases --default-character-set=utf8 --quote-names --opt --single-transaction --master-data=2 --hex-blob --flush-logs

####▼mysqldumpオプションのデフォルト値について
以下がデフォルトで有効なmysqldumpオプション。意識する必要がある場合があるのは--opt。

--opt
　--add-drop-table,--add-locks,--create-options,--quick,--extend-insert,--lock-tables,--set-charset,--disable-keys
　を指定したのと同じ
--create-options
　CREATE TABLE文にENGINEなどのオプションを含める
--set-charset
　'SET NAMES default_character_set'文を出力する
--tz-utc
　"SET TIME_ZONE='+00:00'"を設定する

####▼主要なmysqldumpオプションの意味など

--opt 
　--add-drop-table,--add-locks,--create-options,--quick,--extend-insert,--lock-tables,--set-charset,--disable-keysの指定と同じ
--single-transaction 
　トランザクション内でテーブルをダンプする(バイナリログを出していない環境で実行するとエラーとなる)
--master-data=2 
　1ならCHANGE MASTER文が出力される、2ならコメントとして出力される(バイナリログを出していない環境で実行するとエラーとなる)
　ただしslaveからdumpする場合にはslave自身をmasterとしたポジション等が出力されるため必要ないオプション。
--no-create-info 
　CREATE TABLE文を出力しない
--no-create-db 
　CREATE DATABASE文を出力しない
--default-character-set=utf8 
　デフォルト文字セットの指定(文字化けを回避するにはbinaryオプション指定で変換しないという選択肢もあるが4系など古いと使えない)
--quote-names 
　テーブル名とカラム名をシングルクォート'`'で囲む
--skip-opt 
　--add-drop-table,--add-locks,--create-options,--quick,--extend-insert,--lock-tables,--set-charset,--disable-keysを無効にする
　--skip-add-drop-table等で、--optの中の特定のオプションだけ無効にすることが可能。
--add-locks 
　INSERTの前後にLOCK TABLES,UNLOCK TABLESを追加
-q（--quick） 
　クエリ結果をキャッシュせずに出力する
-e（--extend-insert） 
　複数レコードINSERT文を出力する
-l（--lock-tables） 
　全てのテーブルを共有ロックする
--set-charset 
　SET NAMES文を出力する、デフォルトで有効
-K（--disable-keys） 
　INSERT文の前後に、ALTER TABLE DISABLE KEYSとALTER TABLE ENABLE KEYSを追加
--hex-blob 
　バイナリカラムを16進数で出力する
-R（--routines）
　ストアド関数とストアドプロシジャをダンプする

色々なオプションがありますが-A(--all-databases)だけしか使わない場合もあります。(--optがデフォルトなので)
ただしその場合には文字コードはlatin1になります。（my.cnfの[mysqldump]セクションで指定してなければ）

####▼特定のスキーマだけレストア、スキーマやテーブルごとに切り分け、where句をつかう

dumpデータから1つのdbスキーマを取り出すawkスクリプト
http://nippondanji.blogspot.com/2009/12/mysqldumpawk.html

全体バックアップから特定のDBだけレストアしたい場合には、
-o, --one-databaseというオプションを利用するとよい模様

$ mysql -u root -p --one-database DB名 < バックアップしたSQLデータ

dumpデータからテーブルをcsplitで分ける方法↓

$ csplit dumpfile '/DROP TABLE IF EXISTS/' {*}

実はwhere句も指定できる模様

$ mysqldump -u <user> -p<password> <database> hoge --where 'id>=2 ORDER BY id DESC LIMIT 1'

####▼文字コードに注意
データ移設時は、mysqldumpでデータを出力し、mysqlでデータを投入します。

mysqldump -u root -p > dumpdata
mysql -u root -p < dumpdata

その際、
mysqldump,mysql双方のコマンドに文字コードを指定するオプションをつけることで文字化けを防ぐことができます。
多くの場合、my.cnfで指定されている文字コードを指定すれば良いのですが、
--default-character-set=utf8
もし、アプリケーション側の都合でデータの中身が違う文字コードになっているかもしれない場合や、
開発会社が別またはお客様に確認が難しい場合でかつmysqldのバージョンが5.0以上な時には、
--default-character-set=binary
というように文字コードにbinaryを指定することで、変換がおこなわれずそのまま出力・入力される為、
移設前と後で全く同じアプリケーション(かつ全く同じ設定のサーバ)からの接続であれば文字化け防ぐことが可能になります。

####▼データの整合性に注意
データベーススキーマやテーブル間でデータの関連性がある場合、テーブル毎にデータを出力してしまうと
時系列でのデータの整合性が崩れてしまいますので共有ロックをかけるかreplicationを止めるなどして、
一括でデータを出力する必要があります。
--single-transaction
　トランザクション内でテーブルをダンプする(バイナリログを出していない環境で実行するとエラーとなる)
このオプションでは、トランザクションが使えないMyISAMなどが混在している場合、整合性が崩れるため、
replicaitonを止めるなどしてデータを出力する必要があります。

MyISAMが混在する環境の場合、トランザクションは使えないので、
replicaion停止および共有ロックで更新停止とmysqldumpオプションに--lock-all-tablesが必要になります。

　ex)
　FLUSH TABLES WITH READ LOCK;
　sleepやsyncでロックが終わる(確実に更新停止される)のを待つ
　ポジションとファイル名をとりログに記録する
　dumpする
　UNLOCK TABLES;

http://d.hatena.ne.jp/jitsu102/20110423/1303553133
https://blog.isao.co.jp/mysqldump_myisam/

####▼read_onlyを有効にしてもGRANT ALLのアカウントでは更新されるので注意
移設開始後、データが更新されないようにメンテナンス画面を表示し、更新を無効にします。

set global read_only=1;
show global variables like 'read_only';

上記のように更新を無効にしますが、DBアカウントの権限がALL(SUPERを含む)だと、更新されてしまいます。

show master status;
netstat -anptu

コマンドで更新されていないかと通信が来なくなってるか等を良く確認し問題があれば報告する必要があります。
事前にアプリケーション側から使用する適切な権限のDBアカウントを作成するとこの事態を防ぐことができます。

一括でDBアカウントを確認したい場合には、PerconaToolKit （その日本語マニュアル）に含まれる"pt-show-grants"を使うと便利です。
例）

pt-show-grants -u root -p*****|egrep -v -a 'localhost|--'
pt-show-grants -u root -p*****|egrep -v -a 'localhost|--'|sed -e 's/移設元セグメント/移設先セグメント/g'

####▼明示的にオプションでスキップしない限りデータは上書きされるので注意

mysqldumpのデフォルトオプションである--optには、--add-drop-tableが含まれるため、
dumpデータ内を見てみると、CREATE TABLEの前にDROP TABLEが出力されています。
これを無効にしたい場合(データ統合・追記したい場合など)は、--skip-add-drop-tableと指定すると無効にできます。
初期状態意外でデータを投入する場合は、データが不要な場合を除きバックアップを取っておく必要があります。
　⇒システムテーブルデータが上書きされたあとアカウントの状態も上書きされることに注意
　⇒データ投入後にシステムテーブルを上書きした場合はmysqldの再起動等が必要
mysqlスキーマというシステムテーブル内には、DBのアカウント他のシステム情報が含まれます。
--all-databasesを指定すると、mysqlスキーマも同じように出力されるため、そのデータを上書きすることで、
作っておいたアカウント情報が上書きされてしまいます。
そうするとどうなるかというと、DBアカウントのパスワードが旧環境のものになってしまう、
また、DBアカウントのホストの指定が旧環境のネットワーク用に戻ってしまうためにアプリケーションから接続できない、
rootユーザのアカウントが戻ってしまったために各種処理が行えなくなる、等々の自体が起こりえます。
対策として、移設開始直後にバックアップを取ってから旧環境のDBに新環境用のDBアカウントを作成してからデータをdumpする、
また、自ホスト名の認識やmy.cnfの設定を確実に反映させるために、データ投入後にmysqldの再起動を行う必要があります。

####▼バイナリログを一時的に出さないようにすると速度アップ

SET sql_log_bin=OFF;
show global variables like 'sql_log_bin';
SOURCE 'dump.sql'

※※→使用時はこちらの記事をよくご確認ください

上記のコマンドでバイナリログをデータ投入時のみ一時的に出力しないようにできます。
これにより余計なI/Oを減らすことができるため、データ投入の速度が向上します。
データ投入後は、
SET sql_log_bin=ON;
とするか、mysqldを再起動すると戻すことができます。

・その他の速度アップの方法
「InnoDBのデータローディングが快適になるライフハック」
http://nippondanji.blogspot.jp/2010/03/innodb.html

「mysqldump の結果をそのまま圧縮したり転送したりするコマンド例」
http://c-brains.jp/blog/wsg/10/09/30-183733.php
※そのまま転送した挙句パイプでmysqlに渡して投入する荒業も可能な模様。
mysqldump -udb_user db_name -pdb_pass | gzip | ssh example.com 'zcat | mysql -udb_user db_name -pdb_pass'

「--order-by-primary」をmysqldump時につける(主キーソートする)と、innodb_buffer_pool_sizeが
テーブルサイズに比べて小さい場合は、リストア時間に大差が出る(つけたほうが早くなる）
http://d.hatena.ne.jp/sh2/20120122

またはログファイルへの書き出しをCOMMIT毎、データファイルへの書き出しを毎秒にします
innodb_flush_log_at_trx_commit = 2
レプリケーションが追いつかないとき等にも。
http://blog.hatak.net/2011/12/07/9407
http://blog.layer8.sh/ja/2011/12/16/mysql%E3%81%AB%E3%81%8A%E3%81%91%E3%82%8Binnodb_flush_log_at_trx_commit%E3%81%AE%E8%A8%AD%E5%AE%9A%E5%80%A4%E3%81%AE%E3%81%BE%E3%81%A8%E3%82%81/

####▼パスワード不明時の再設定の方法について
MySQL の root ユーザのパスワードを忘れてしまった！というときは、
--skip-grant-tables オプションを起動引数につけるか、 my.cnf に

[mysqld]
skip-grant-tables

という記述をして起動すれば、 -p オプションなしに root ユーザで入れます。
その後

mysql> UPDATE user SET Password=PASSWORD('rootpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;

とすれば変更できます。
変更した後は起動引数あるいは my.cnf から skip-grant-tables を消してから再起動すれば元通りになります。

####▼replicationが絡む場合の移設方法について
もし移設元と移設先がインターネットを介さない同じデータセンタ内でラックが異なる程度の隣接するNWであれば、
slaveからオンラインでデータを出力し(このときmasterのポジションを取る)、
それを投入して移設元masterから移設先masterをslaveとするようにreplication設定を行っておけば、
当日にデータを出力・転送・入力する必要はなく、停止と切替と確認と開始のみとなります。
異なるDC間などでの移設にreplicationを使うのは、回線圧迫するし圧縮・暗号化されていないデータを送ることになります。
セキュリティ確保の手間の問題と回線の事情からDC間replicationは物理的に無理なケースが多いかと思われます。

replicationを組んだ複数台のDBサーバに移設する場合、replicationを止めてそれぞれ投入するか、masterにだけ投入して同期を待つか、
どちらが速いかというのはデータ量にもよるので、事前に検証し速度を測るのが良いかと思われます。
(恐らく同じスペックの物理環境であれば、replicationが長い間遅延するようなことはなくすぐ追いつくことが殆どかと考えられます)
クラウド(仮想)環境の場合、I/Oがボトルネックになるため、圧縮コマンドを挟むとそうでない場合よりも高速になることが多いようです。

速）mysqldump |gzip> dumpdata.gz
遅）mysqldump > dumpdata; gzip dumpdata

もしマルチマスタreplication構成となっている場合は、
replicationを止めて両方のノードにデータ投入するなら、
片方だけreplicationを止め忘れることは避けなければなりません。
dropしてからcreateするようにdumpファイルには書かれてるはずなので、
dropされてreplication反映されてからもう片方でinsertされた場合などに
不整合が出てreplicationは停止しますが、データ投入に失敗します。

replicationの同期確認については以下の通りです。

mysql> show slave status\G	
Slave_IO_Running: Yes	
Slave_SQL_Running: Yes	

Yesが2つあればreplication同期は成功しています。
Master_Log_File:が、同期しているmasterのバイナリログファイルです。
Read_Master_Log_Pos:が、masterのバイナリログファイルを読んでいる位置です。
Exec_Master_Log_Pos:が、masterのバイナリログファイルを実行している位置です。
Seconds_Behind_Master:が0であれば、遅延秒数は0秒です。

ちなみにALTER TABLEをスレーブ側で行うとSQLスレッドの同期が止まる、つまりreplicationは途切れますので
スレーブ側をALTERで更新してreplicationを維持しながらローリングアップデートというようなことは、、最近のInnoDBでは可能なケースもあるようですがMyISAMからのALTER＋バージョンアップ移設などは無理かと思われます。
https://yoku0825.blogspot.jp/2015/12/pt-online-schema-change56-innodbalter.html

GTIDを使ったreplicationのセットアップはこちら

####▼my.cnfのパラメータが変わる場合のmysqld再起動時の注意点
あまり移設は関係ない、良くあるエラーの話です。
innodb_log_buffer_sizeを変える場合、mysqld停止時にib_logfile*を消すかデータディレクトリから
移動しておいてからmysqldを起動する必要があります。
そうしないとmysqldが起動しないかエラーログに大量にエラーが出力されることになります。
作業後のログの確認は必須となります。

あまりないとは思いますが、画像など、入れてるデータが大きすぎる場合(1GB超のテーブル)、
max_allowed_packetの上限値である1GBを超えるためにmysqlコマンドでレストアできない場合があります。
そんな時は、個別に--tabオプションでdumpし、mysqlimportコマンドで投入する必要があります。

####▼MySQL-5.5以降で使える--dump-slave
slaveからmysqldumpする場合、自前でFLUSH TABLES WITH READ LOCKおよびSHOW SLAVE STATUSコマンドを使い情報を取得、
またはreplicationを停止してmysqldumpしエラーログに出る情報を見る等の対応が必要でした。
MySQL-5.5から、mysqldumpのオプションとして、以下のオプションを使うことが可能になりました。
--dump-slave: スレーブからダンプをとった場合、スレーブが参照しているマスターの情報をCHANGE MASTERとしてダンプに含める。
--apply-slave-statements: STOP SLAVEおよびSTART SLAVEコマンドを、CHANGE MASTERの前後に追加する。
--include-master-host-port: CHANGE MASTERコマンドにマスターのホスト名とポートを含める。
http://nippondanji.blogspot.com/2010/12/mysql-55.html

####▼slaveを増やしたい場合の方法について
既存のslaveがあれば、オンラインで増やすことが可能です。
無ければmasterからデータをdumpする必要がある為、dumpしている間はmasterへのアクセスは制限され、データ量に応じて時間がかかります。
AWSなどの考慮された特殊な仮想環境の場合、mysqldを停止せずに既存のslaveのDBデータの載ったEBSvolumeのsnapshotを取り、
とったsnapshotからEBSvolumeを新たに作成し新たに作成した別のDBインスタンスにアタッチし、重複クエリを
pt-slave-restart -hXXXXX -uXXXX -pXXXXX--error-numbers 1062 --verbose
などとして一括スキップすることで、他の方法に比べかなり高速にslaveを増やすことが可能です。
（本当は、mysqldを停止するかreplicationを停止するとクエリが重複するようなことは防げるのですが、
　高負荷で止めたくないが増やしたい時等もあるためです。）
国内クラウドの場合も仮想OSをコピーするなどで同様のことが可能です。
物理環境の場合は、アプリケーションからのslaveへのアクセスを一時取りやめ、slaveからmysqldumpする。
このとき、FLUSH TABLES WITH READ LOCKおよびSHOW SLAVE STATUSコマンドを使うか、
replicationを停止するなどして、データdump時のmasterの情報を得る必要があります。
(CHANGE MASTERでそのポジションとバイナリログを指定する必要があるからです)
（--dump-slaveが使える5.5以降はダンプデータに位置が書かれるためshowコマンドによる確認はいらない）

GTIDのスレーブをふやす方法

####▼MySQL-5.6以降のGTIDがらみのオプションなど
基本的にはおなじ方法で取れますが、以下の点が異なるようです。
・5.6以前のバージョンのMySQLにimportする場合、また、リモートの5.5サーバから5.6のmysqldumpを使用してダンプする場合は、、mysqldumpに--set-gtid-purged=OFFを付けないとダメ
　GTID有効な場合、mysqldumpに--triggers --routines --eventsも付けないとエラーがでる
MySQL5.6のちょっとした話 - まめ畑
・バッファプールのダンプとリストアが可能になった
・遅延レプリケーションで古いデータが取れるようになった
・GTIDが有効な場合その確認をする必要があるようになった。(上記slaveを増やす方法参照のこと)
・.ibdがコピーできるようになった。(単純にコピーするだけではなくてALTERでDISCARDとIMPORT的なオプション指定必須)

####▼MySQL-5.7以降からつかえるmysqlpump
InnoDB限定で並列dumpらしい。詳しくは以下リンク先をどうぞ。
日々の覚書: mysqldumpじゃないよ、mysqlpumpだよ in MySQL 5.7.8 （新機能編）
日々の覚書: mysqldumpじゃないよ、mysqlpumpだよ in MySQL 5.7.8 （mysqldumpとの違い編）

ほかにxtrabackupなどとの比較してる人もいたのでのせておきます。

####▼データ量とメモリサイズについてとハードウェアなど
そもそもデータベースの負荷の種類はディスクI/Oです。
データの入出力に時間がかかってボトルネックにならないようにデータをメモリにキャッシュさせて使うのが普通です。
その為にmysqldのサーバパラメータの設定を調整することをオンメモリにするという言い方をするようです。
移行時、移行元のデータサイズより移行先のメモリサイズが小さかったりした場合、
ディスクアクセスがボトルネックとなりピーク時などに劇的な遅延が起こる可能性があります。
ハードウェアの種類が変わることによっても影響があるかもしれません。
移設先でRaidコントローラのWriteBackキャッシュ、SSDかSASのディスク(SATAは大容量だけどSASより遅い)などハードの恩恵がなくなることは避けたほうが良いです。
基本的すぎる話ですみませんが知らないと詰むので一応書きます、OSとソフトウェアのCPUのアーキテクチャが32bitだとメモリ2GBまでしか使えないので64bitのをお使いください。

####▼ほかのほうほうのメモ

・冒頭にかいたコールドとバイナリログつかう方法
・SQLでselectしてスキーマとテーブルは準備しといてinsertをするかcsvにだしてload data infile
・コンバートツールはpostgresqlにするやつならmysql-postgresql-converterやpgloaderなどが。

####▼作業見積もりなど

なにからなにに移すかってのによって色々考慮すべきことが異なるのでポインタ↓で失礼。
http://nippondanji.blogspot.jp/2009/02/db7.html
データ量に応じて時間がかかる対象は、dump・転送・レストア・ALTERなど。環境つくって試験する必要があります。