おそらく MySQL5.1 から 5.6 へ上げた影響だと思われるが、
ユーザの権限を設定することができなくなっていた。
mysql> grant all privileges on testdb.* to testuser@localhost identified by 'testpass';
ERROR 2013 (HY000): Lost connection to MySQL server during query
エラーログには以下のような文言がでていた。
バージョン違いの SQL をぶち込んだため、5.6 で必要なテーブルがなくなったのかな。
InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
これ以外にもエラーが出ているテーブルがたくさんあったが、
たぶんすべての原因はこいつ。
- MySQL :: Re: InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
- MySQL Bugs: #67179: mysql system tables innodb_table_stats,slave_master_info not accessible on clean
ググるとどうにも MySQL にバグ？っぽいので、
対応 SQL ファイルを取り込んでみる。
# mysql -u root -p -t mysql < five-tables.sql
# mysql -u root -p
mysql> grant all privileges on testdb.* to testuser@localhost identified by 'testpass';
ERROR 2013 (HY000): Lost connection to MySQL server during query
はい、ざんねーん。変わりません。
でもエラーログの内容は変わってました。
...
[ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure
[ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
[ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
[ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
[ERROR] Native table 'performance_schema'.'users' has the wrong structure
[ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
[ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
[ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
[ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
[ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
[ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
[ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
...
データベースが壊れているっぽいですね。
上記 URL を参考にすべての DB を upgrade してみる。
# mysql_upgrade -u root
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'
mroonga.diaries OK
mroonga.memos OK
mroonga.shops OK
mroonga.snippet_test OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
test01.attachment OK
test01.auth_cookie OK
test01.cache OK
test01.component OK
test01.enum OK
test01.milestone OK
test01.node_change OK
test01.permission OK
test01.report OK
test01.repository OK
test01.revision OK
test01.session OK
test01.session_attribute OK
test01.system OK
test01.ticket OK
test01.ticket_change OK
test01.ticket_custom OK
test01.version OK
test01.wiki OK
wordpress.wp_commentmeta OK
wordpress.wp_comments OK
wordpress.wp_links OK
wordpress.wp_options OK
wordpress.wp_popularpostsdata OK
wordpress.wp_popularpostsdatacache OK
wordpress.wp_postmeta OK
wordpress.wp_posts OK
wordpress.wp_term_relationships OK
wordpress.wp_term_taxonomy OK
wordpress.wp_terms OK
wordpress.wp_usermeta OK
wordpress.wp_users OK
Running 'mysql_fix_privilege_tables'...
OK
再度挑戦！
mysql> grant all privileges on testdb.* to testuser@localhost identified by 'testpass';
Query OK, 0 rows affected (0.00 sec)
できたーー ♪