2012年10月15日月曜日

MySQLアップデート(mysql_upgrade)

そういえば、MySQLのエラーログを見ていたら、
InnoDB: Progress in percents: 1121014 17:46:25  InnoDB: Waiting for the background threads to start
121014 17:46:26 InnoDB: 1.1.8 started; log sequence number 123738696720
121014 17:46:26 [Warning] 'user' entry 'root@db01' ignored in --skip-name-resolve mode.
121014 17:46:26 [Warning] 'user' entry '@db01' ignored in --skip-name-resolve mode.
121014 17:46:26 [Warning] 'proxies_priv' entry '@ root@db01' ignored in --skip-name-resolve mode.
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/events_waits_current.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/events_waits_history.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/events_waits_history_long.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/setup_consumers.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/setup_instruments.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/setup_timers.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/performance_timers.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/threads.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/events_waits_summary_by_thread_by_event_name.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/events_waits_summary_by_instance.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/events_waits_summary_global_by_event_name.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/file_summary_by_event_name.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/file_summary_by_instance.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/mutex_instances.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/rwlock_instances.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/cond_instances.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
121014 17:46:26 [ERROR] /usr/libexec/mysqld: Can't find file: './performance_schema/file_instances.frm' (errno: 13)
121014 17:46:26 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
という、エラーが出ていてなんだろう?って思ってたら、
MySQLのアップデートしろという事らしいです。
それで、mysql_upgradeを行ってみました
mysql_upgrade -u root -p
そうしたら、次のようなエラーになってしまってうまく行きませんでした。
Running 'mysql_fix_privilege_tables'...
ERROR 1007 (HY000) at line 160: Can't create database 'performance_schema'; database exists
ERROR 1005 (HY000) at line 183: Can't create table 'cond_instances' (errno: 13)
ERROR 1005 (HY000) at line 213: Can't create table 'events_waits_current' (errno: 13)
ERROR 1005 (HY000) at line 227: Can't create table 'events_waits_history' (errno: 13)
ERROR 1005 (HY000) at line 241: Can't create table 'events_waits_history_long' (errno: 13)
ERROR 1005 (HY000) at line 262: Can't create table 'events_waits_summary_by_instance' (errno: 13)
ERROR 1005 (HY000) at line 283: Can't create table 'events_waits_summary_by_thread_by_event_name' (errno: 13)
ERROR 1005 (HY000) at line 303: Can't create table 'events_waits_summary_global_by_event_name' (errno: 13)
ERROR 1005 (HY000) at line 320: Can't create table 'file_instances' (errno: 13)
ERROR 1005 (HY000) at line 339: Can't create table 'file_summary_by_event_name' (errno: 13)
ERROR 1005 (HY000) at line 359: Can't create table 'file_summary_by_instance' (errno: 13)
ERROR 1005 (HY000) at line 376: Can't create table 'mutex_instances' (errno: 13)
ERROR 1005 (HY000) at line 394: Can't create table 'performance_timers' (errno: 13)
ERROR 1005 (HY000) at line 412: Can't create table 'rwlock_instances' (errno: 13)
ERROR 1005 (HY000) at line 428: Can't create table 'setup_consumers' (errno: 13)
ERROR 1005 (HY000) at line 445: Can't create table 'setup_instruments' (errno: 13)
ERROR 1005 (HY000) at line 461: Can't create table 'setup_timers' (errno: 13)
ERROR 1005 (HY000) at line 478: Can't create table 'threads' (errno: 13)
調べるのが面倒だったのでアップデートせずに放置プレイ!(゚∀゚)
そうしたら見事に天罰がガ━━(;゚Д゚)━━ン!!
いつの間にかサイトの表示が正しくされなくなってしまっていました:(;゙゚'ω゚'):
スレーブのステータスを見てみると次のエラーが発生していました
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.255.7.2
                  Master_User: repl
                  Master_Port: 3306
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   Last_Errno: 1580
                   Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE general_log
   MODIFY event_time TIMESTAMP NOT NULL,
   MODIFY user_host MEDIUMTEXT NOT NULL,
   MODIFY thread_id INTEGER NOT NULL,
   MODIFY server_id INTEGER UNSIGNED NOT NULL,
   MODIFY command_type VARCHAR(64) NOT NULL,
   MODIFY argument MEDIUMTEXT NOT NULL'

調べてみると、こちらのサイトに書かれているような現象に近かったと思います。
マスターで行ったmysql_upgradeがスレーブ側で失敗しているせい?

とりあえず、放置していたアップデートを続けることにしました。
まずはスレーブ側でアップデートを行います。
その前にslaveのレプリケーションを停止しておきます
# mysql -u root -p
mysql> slave stop;
その後にアップデート(`・ω・´)シャキーン
# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/lib/mysql/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/lib/mysql/mysql.sock'
:
:
Running 'mysql_fix_privilege_tables'...
OK
スレーブ側は無事に成功しました( ̄ー ̄)bグッ!
ただ、スレーブ側のレプリケーションのエラーはまだ残っていますので、それを解消します
エラーが出ているSQLをスキップさせます。
#スレーブ停止
mysql> slave stop;
#1つだけスキップする
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
#スレーブ起動
mysql> slave start;
#スレーブのステータス確認
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   Last_Errno: 1580
                   Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log
   MODIFY start_time TIMESTAMP NOT NULL,
   MODIFY user_host MEDIUMTEXT NOT NULL,
   MODIFY query_time TIME NOT NULL,
   MODIFY lock_time TIME NOT NULL,
   MODIFY rows_sent INTEGER NOT NULL,
   MODIFY rows_examined INTEGER NOT NULL,
   MODIFY db VARCHAR(512) NOT NULL,
   MODIFY last_insert_id INTEGER NOT NULL,
   MODIFY insert_id INTEGER NOT NULL,
   MODIFY server_id INTEGER UNSIGNED NOT NULL,
   MODIFY sql_text MEDIUMTEXT NOT NULL'
あれ・・・まだ解消されていない(´;ω;`)ウッ…
もう一度スキップしてみる
#スレーブ停止
mysql> slave stop;
#1つだけスキップする
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
#スレーブ起動
mysql> slave start;
#スレーブのステータス確認
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error:
これで無事に解消されました!( ̄ー ̄)bグッ!

そして次はエラーが起きていたマスター側を行います
# mysql_upgrade -u root -p
Running 'mysql_fix_privilege_tables'...
ERROR 1007 (HY000) at line 160: Can't create database 'performance_schema'; database exists
ERROR 1005 (HY000) at line 183: Can't create table 'cond_instances' (errno: 13)
ERROR 1005 (HY000) at line 213: Can't create table 'events_waits_current' (errno: 13)
ERROR 1005 (HY000) at line 227: Can't create table 'events_waits_history' (errno: 13)
ERROR 1005 (HY000) at line 241: Can't create table 'events_waits_history_long' (errno: 13)
ERROR 1005 (HY000) at line 262: Can't create table 'events_waits_summary_by_instance' (errno: 13)
ERROR 1005 (HY000) at line 283: Can't create table 'events_waits_summary_by_thread_by_event_name' (errno: 13)
ERROR 1005 (HY000) at line 303: Can't create table 'events_waits_summary_global_by_event_name' (errno: 13)
ERROR 1005 (HY000) at line 320: Can't create table 'file_instances' (errno: 13)
ERROR 1005 (HY000) at line 339: Can't create table 'file_summary_by_event_name' (errno: 13)
ERROR 1005 (HY000) at line 359: Can't create table 'file_summary_by_instance' (errno: 13)
ERROR 1005 (HY000) at line 376: Can't create table 'mutex_instances' (errno: 13)
ERROR 1005 (HY000) at line 394: Can't create table 'performance_timers' (errno: 13)
ERROR 1005 (HY000) at line 412: Can't create table 'rwlock_instances' (errno: 13)
ERROR 1005 (HY000) at line 428: Can't create table 'setup_consumers' (errno: 13)
ERROR 1005 (HY000) at line 445: Can't create table 'setup_instruments' (errno: 13)
ERROR 1005 (HY000) at line 461: Can't create table 'setup_timers' (errno: 13)
ERROR 1005 (HY000) at line 478: Can't create table 'threads' (errno: 13)
やっぱり、エラーになってしまいますね・・・。
エラー内容を見てみると、なにやらテーブルが作れないとのこと。
errno: 13はどこかで見たようなエラーだと思いながら、
パーミッションを確認してみると、所有者がrootになっていましたΣ(゚Д゚ υ) アリャ
# ls -alt /var/lib/mysql/|grep performance_schema
drwx------  2 mysql mysql        4096 Oct 14 17:07 performance_schema
なので、所有者をmysqlに変更し、再度実行すると無事に成功します!
# chown -R mysql:mysql /var/lib/mysql/performance_schema
# mysql_upgrade -u root -p
:
:
Running 'mysql_fix_privilege_tables'...
OK
今度は、スレーブ側のレプリケーションを再開します。
# mysql -u root -p
mysql> slave start;
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
Slave_IO_Running、Slave_SQL_RunningがYesになっていることを事を確認する。(`・ω・´)ゞビシッ!!

参考URL

0 件のコメント:

コメントを投稿