mysql innodb table dosen't exist_Error setting up MySQL: Table 'mysql.plugin' doesn't exist

问题

I have a MySQL 5.7 instance running without problems on Ubuntu 16, but when i try to install any other package or try to update existing ones through apt-get, i get the following error:

# apt-get upgrade

Setting up mysql-server-5.7 (5.7.20-0ubuntu0.16.04.1) ...

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

mysql_upgrade: [ERROR] 1146: Table 'mysql.plugin' doesn't exist

mysql_upgrade failed with exit status 5

dpkg: error processing package mysql-server-5.7 (--configure):

subprocess installed post-installation script returned error exit status 1

No apport report written because the error message indicates its a followup error from a previous failure.

dpkg: dependency problems prevent configuration of mysql-server:

mysql-server depends on mysql-server-5.7; however:

Package mysql-server-5.7 is not configured yet.

dpkg: error processing package mysql-server (--configure):

dependency problems - leaving unconfigured

Errors were encountered while processing:

mysql-server-5.7

mysql-server

And if i run a mysqlcheck, it looks like some of the system tables exists but others are missing:

mysql.columns_priv OK

mysql.db OK

mysql.engine_cost

Error : Table 'mysql.engine_cost' doesn't exist

status : Operation failed

mysql.event OK

mysql.func OK

mysql.general_log OK

mysql.gtid_executed

Error : Table 'mysql.gtid_executed' doesn't exist

status : Operation failed

mysql.help_category

Error : Table 'mysql.help_category' doesn't exist

status : Operation failed

mysql.help_keyword

Error : Table 'mysql.help_keyword' doesn't exist

status : Operation failed

mysql.help_relation

Error : Table 'mysql.help_relation' doesn't exist

status : Operation failed

mysql.help_topic

Error : Table 'mysql.help_topic' doesn't exist

status : Operation failed

mysql.innodb_index_stats

Error : Table 'mysql.innodb_index_stats' doesn't exist

status : Operation failed

mysql.innodb_table_stats

Error : Table 'mysql.innodb_table_stats' doesn't exist

status : Operation failed

mysql.ndb_binlog_index OK

mysql.plugin

Error : Table 'mysql.plugin' doesn't exist

status : Operation failed

mysql.proc OK

mysql.procs_priv OK

mysql.proxies_priv OK

mysql.server_cost

Error : Table 'mysql.server_cost' doesn't exist

status : Operation failed

mysql.servers

Error : Table 'mysql.servers' doesn't exist

status : Operation failed

mysql.slave_master_info

Error : Table 'mysql.slave_master_info' doesn't exist

status : Operation failed

mysql.slave_relay_log_info

Error : Table 'mysql.slave_relay_log_info' doesn't exist

status : Operation failed

mysql.slave_worker_info

Error : Table 'mysql.slave_worker_info' doesn't exist

status : Operation failed

mysql.slow_log OK

mysql.tables_priv OK

mysql.time_zone

Error : Table 'mysql.time_zone' doesn't exist

status : Operation failed

mysql.time_zone_leap_second

Error : Table 'mysql.time_zone_leap_second' doesn't exist

status : Operation failed

mysql.time_zone_name

Error : Table 'mysql.time_zone_name' doesn't exist

status : Operation failed

mysql.time_zone_transition

Error : Table 'mysql.time_zone_transition' doesn't exist

status : Operation failed

mysql.time_zone_transition_type

Error : Table 'mysql.time_zone_transition_type' doesn't exist

status : Operation failed

mysql.user OK

sys.sys_config

Error : Table 'sys.sys_config' doesn't exist

status : Operation failed

However, the database is correctly running and if I manually check through an SQL, it looks like the tables exist.

mysql> SHOW TABLES;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| engine_cost |

| event |

| func |

| general_log |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

I use the innodb_file_per_table=1 setting, and the owner of /var/lib/mysql directory is correctly set to mysql user.

I would like to fix this issue without dumping existing data, but i'm running out of ideas.

回答1:

I had the same problem today and I managed to solve it without the backup - reinstall - restore steps. (Though I had backup and everyone should!)

In my case, there was a plugin.ibd file in /var/lib/mysql/mysql/ directory, and the other missing tables also had files there, but maybe they were incomplete for some reason.

I did the following:

Moved the files of mysql.plugin table to another directory, e.g.: mv /var/lib/mysql/mysql/plugin.* /other/place/

Ran apt-get upgrade -y to see what happens. The upgrade script recreated the mysql.plugin table correctly and complained about another table.

I repeated steps above with the table name the upgrade script told me and after recreating 19 tables this way, the upgrade completed successfully.

All steps I made, as root of course:

cd /var/lib/mysql/mysql

mkdir temp

mv engine_cost.* temp/

mv gtid_executed.* temp/

mv help_category.* temp/

mv help_keyword.* temp/

mv help_relation.* temp/

mv help_topic.* temp/

mv innodb_index_stats.* temp/

mv innodb_table_stats.* temp/

mv plugin.* temp/

mv server_cost.* temp/

mv servers.* temp/

mv slave_master_info.* temp/

mv slave_relay_log_info.* temp/

mv slave_worker_info.* temp/

mv time_zone.* temp/

mv time_zone_leap_second.* temp/

mv time_zone_name.* temp/

mv time_zone_transition.* temp/

mv time_zone_transition_type.* temp/

apt-get upgrade -y

I hope it helps someone.

回答2:

Finally the only thing that solved the problem was to purge and reinstall the mysql library making a backup of the database and restoring it after all the process:

# Backup database

time mysqldump -u root -p database > /var/backups/restore_backup.sql

# Backup config and data

mv /var/lib/mysql /tmp/backups/mysql-lib.bak

cp /etc/mysql/my.cnf /tmp/backups/my.cnf.bak

# Purge library

sudo apt-get remove --purge mysql-server mysql-client mysql-common

sudo apt-get autoremove

sudo apt-get autoclean

# Reinstall

sudo apt-get update

sudo apt-get install mysql-server

# Restore config

cp /tmp/backups/my.cnf.bak /etc/mysql/my.cnf

sudo service mysql restart

# Create database

mysql -u root -p

mysql> CREATE DATABASE database;

# Restore backup

time mysql -u root -p database < /var/backups/restore_backup.sql

来源：https://stackoverflow.com/questions/47075429/error-setting-up-mysql-table-mysql-plugin-doesnt-exist