正しいMySQLのバックアップの取り方

事象

MySQLのDBのバックアップを作成する場合、よく紹介されている書き方が下記の通り

mysqldump -u root -p[PASSWORD] -h [DBのIP] [DB名] > [出力ファイル名]

実際に現場でこのようにバックアップを作成していたところ問題が発生
調べてみると、あるはずのストアドがバックアップに含まれていない。。。

原因

MySQLコマンドオプションを調べてみたところ下記の通り
https://dev.mysql.com/doc/refman/5.6/ja/mysqldump-stored-programs.html

MySQL ver5.5～8.0はデフォルトでストアドとイベントスケジュールは出力されない！！！

対応

ということで、MySQLのバックアップに
「ストアドプロシージャ」「ストアドファンクション」「イベント」は、
デフォルトで含まれないので、オプション「--routines」「--events」を明示的に付加する必要がある。

mysqldump -u root -p[PASSWORD] -h [DBのIP] --routines --events [DB名] > [出力ファイル名]

正しいバックアップ手順は下記の通り

mysqladmin -u root -p[PASSWORD] -h [DBのIP] create [バックアップDB名]
mysqldump -u root -p[PASSWORD] -h [DBのIP] --routines --events [DB名] > [出力ファイル名]
mysql -u root -p[PASSWORD] -h [DBのIP] [バックアップDB名] < [出力ファイル名]

おまけ

MySQLのDBバックアップ作成ワンライナー

mysqladmin -u root -p[PASSWORD] -h [DBのIP] create [バックアップDB名] && mysqldump -u root -p[PASSWORD] -h [DBのIP] --routines --events [DB名] | mysql -u root -p[PASSWORD] -h [DBのIP] [バックアップDB名]

追記 2019/12/11

pvコマンドを使うと進捗状況を表示できるゾ

シェルのパイプの速度(進捗バー) は pv で

エクスポート（〇〇の部分は想定されるGBを入れよう）

mysqldump -u root -p[PASSWORD] -h [DBのIP] --routines --events [DB名] | pv -s ○○g > [出力ファイル名]

インポート

pv [入力ファイル名] | mysql -u root -p[PASSWORD] -h [DBのIP] [バックアップDB名]

参考

【MySQL】 データのエクスポート / バックアップを行うには... ～ mysqldump ～ ( ソフトウェア ) - プログラム の個人的なメモ - Yahoo!ブログ

mysqldumpリカバリの進捗を表示する