2012-07-04

Amazon RDSから取得したdumpデータを通常のMySQLサーバへリストア

Amazon RDSをmysqldumpでバックアップで作成したダンプファイルを復元しようと、単純に
zcat hoge.zip | mysql -uxxxx -pxxxx
で良いのかと思ったのですがシステムテーブル関連でエラーが出て、1つ1つ対処していこうかとも思ったのですが量が多かったので-fオプションつけちゃいました(ノ゚⊿゚)ノ
zcat hoge.gz | mysql -f
ERROR 1465 (HY000) at line 8860: Triggers can not be created on system tables
ERROR 1465 (HY000) at line 8884: Triggers can not be created on system tables
ERROR 1465 (HY000) at line 9274: Triggers can not be created on system tables
ERROR 1465 (HY000) at line 9309: Triggers can not be created on system tables
ERROR 1465 (HY000) at line 9344: Triggers can not be created on system tables
ERROR 1142 (42000) at line 9395: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances'
ERROR 1044 (42000) at line 9396: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9397: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9431: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'events_waits_current'
ERROR 1044 (42000) at line 9432: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9433: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9467: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'events_waits_history'
ERROR 1044 (42000) at line 9468: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9469: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9503: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'events_waits_history_long'
ERROR 1044 (42000) at line 9504: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9505: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9530: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'events_waits_summary_by_instance'
ERROR 1044 (42000) at line 9531: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9532: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9557: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'events_waits_summary_by_thread_by_event_name'
ERROR 1044 (42000) at line 9558: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9559: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9583: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'events_waits_summary_global_by_event_name'
ERROR 1044 (42000) at line 9584: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9585: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9606: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'file_instances'
ERROR 1044 (42000) at line 9607: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9608: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9631: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'file_summary_by_event_name'
ERROR 1044 (42000) at line 9632: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9633: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9657: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'file_summary_by_instance'
ERROR 1044 (42000) at line 9658: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9659: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9680: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'mutex_instances'
ERROR 1044 (42000) at line 9681: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9682: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9704: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'performance_timers'
ERROR 1044 (42000) at line 9705: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9706: INSERT command denied to user 'root'@'localhost' for table 'performance_timers'
ERROR 1044 (42000) at line 9707: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9729: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'rwlock_instances'
ERROR 1044 (42000) at line 9730: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9731: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9752: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9753: INSERT command denied to user 'root'@'localhost' for table 'setup_consumers'
ERROR 1044 (42000) at line 9754: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9776: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9777: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9798: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9799: INSERT command denied to user 'root'@'localhost' for table 'setup_timers'
ERROR 1044 (42000) at line 9800: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1142 (42000) at line 9821: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'threads'
ERROR 1044 (42000) at line 9822: Access denied for user 'root'@'localhost' to database 'performance_schema'
ERROR 1044 (42000) at line 9823: Access denied for user 'root'@'localhost' to database 'performance_schema'
と、大量にエラーが発生したものの自分で作成したデータなどはリストアされているみたいですヽ(´ー`)/

1 件のコメント:

  1. とても助かりました。ありがとうございます!

    返信削除