Native table performance_schema has the wrong structure

При обновлении MySQL иногда случается в логе

160805 07:42:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160805 7:42:04 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
160805 7:42:04 [Note] /usr/sbin/mysqld (mysqld 5.5.50-0+deb7u2-log) starting as process 19697 ...
160805 7:42:04 [Note] Plugin 'FEDERATED' is disabled.
160805 7:42:04 InnoDB: The InnoDB memory heap is disabled
160805 7:42:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160805 7:42:04 InnoDB: Compressed tables use zlib 1.2.7
160805 7:42:04 InnoDB: Using Linux native AIO
160805 7:42:04 InnoDB: Initializing buffer pool, size = 128.0M
160805 7:42:04 InnoDB: Completed initialization of buffer pool
160805 7:42:04 InnoDB: highest supported file format is Barracuda.
160805 7:42:04 InnoDB: Waiting for the background threads to start
160805 7:42:05 InnoDB: 5.5.50 started; log sequence number 5788453054
160805 7:42:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160805 7:42:05 [Note] - '0.0.0.0' resolves to '0.0.0.0';
160805 7:42:05 [Note] Server socket created on IP: '0.0.0.0'.
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/events_waits_current.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/events_waits_history.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/events_waits_history_long.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/setup_consumers.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/setup_instruments.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/setup_timers.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/performance_timers.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/threads.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/events_waits_summary_by_thread_by_event_name.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/events_waits_summary_by_instance.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/events_waits_summary_global_by_event_name.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/file_summary_by_event_name.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/file_summary_by_instance.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/mutex_instances.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/rwlock_instances.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/cond_instances.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
160805 7:42:05 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/file_instances.frm' (errno: 13)
160805 7:42:05 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
160805 7:42:05 [Note] Event Scheduler: Loaded 0 events
160805 7:42:05 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.50-0+deb7u2-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)

т.е. ломается структура базы performance_schema

Лечение:

Останавливаем MySQL сервер

service mysql stop

Перемещаем директорию с поврежденной базой performance_schema куда-нибудь из рабочей директории MySQL (/var/lib/mysql для Debian), к примеру в /tmp

mv /var/lib/mysql/performance_schema /tmp/performance_schema

Запускаем MySQL руками в фоновом режиме (& в конце), указав ключик --skip-performance-schema для игнорирования базы performance_schema и ключик --skip-grant-tables чтобы выполнить mysql_upgrade без авторизации

mysqld_safe --skip-performance-schema --skip-grant-tables &

Запускаем штатное обновление структуры системных таблиц. Буду проверены все существующие таблицы, не только системные. Несуществующие системные таблицы будут пересозданы, в том числе и удаленная нами performance_schema.

mysql_upgrade --force

 Ключ --force нужен для принудительной проверки. Иначе может сказать что обновление уже выполнено и проигнорирует проверку:

This installation of MySQL is already upgraded to 5.5.50, use --force if you still need to run mysql_upgrade

Останавливаем сервер (помним что он запущен у нас с небезопасными ключами --skip-performance-schema и --skip-grant-tables)

service mysql stop

Запускаем штатным способом

service mysql start

Лог MySQL здорового человека

160812 12:59:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160812 12:59:29 [Note] /usr/sbin/mysqld (mysqld 5.5.50-0+deb7u2) starting as process 31737 ...
160812 12:59:29 [Note] Plugin 'FEDERATED' is disabled.
160812 12:59:29 InnoDB: The InnoDB memory heap is disabled
160812 12:59:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160812 12:59:29 InnoDB: Compressed tables use zlib 1.2.7
160812 12:59:29 InnoDB: Using Linux native AIO
160812 12:59:29 InnoDB: Initializing buffer pool, size = 128.0M
160812 12:59:29 InnoDB: Completed initialization of buffer pool
160812 12:59:29 InnoDB: highest supported file format is Barracuda.
160812 12:59:30 InnoDB: Waiting for the background threads to start
160812 12:59:31 InnoDB: 5.5.50 started; log sequence number 923906293
160812 12:59:31 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160812 12:59:31 [Note] - '0.0.0.0' resolves to '0.0.0.0';
160812 12:59:31 [Note] Server socket created on IP: '0.0.0.0'.
160812 12:59:31 [Note] Event Scheduler: Loaded 0 events
160812 12:59:31 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.50-0+deb7u2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)

 

2006-2019 © ООО "Флайнет"
Лицензии 170013, 170014