Problem of unable to create user after recovering to different version of MySQL library with Xtrabackup

1. prompt when creating users with Grant and Create User:-

cannotload from MySQL . Proc. thetable is probably corrupted

2. But you can use insert INTO mysql.user values(); to add,

The password section may need to use Selectpassword () The password encryption value is calculated and then inserted.

3. the mysql.proc_priv table and the mysql.procs table need to be repaired.

4. There are three ways to fix this:

1. Create a new empty instance directly, and then overwrite the non-corrupted table file with the problematic strength. Because the MySQL library table is the MyISAM table on the 5.6-5.7 version. You can directly copy the table file for modification.

2. useful mysql_upgrade program to repair table structure

[Centos:]mysql_upgrade-ulocalhost-u root-p123

The output is as follows:

Enterpassword:

Checkingif update is needed.

Checkingserver version.

Runningqueries to upgrade MySQL server.

Checkingsystem database.

Mysql.columns_priv OK

Mysql.db OK

Mysql.engine_cost OK

Mysql.event OK

Mysql.func OK

Mysql.general_log OK

Mysql.gtid_executed OK

Mysql.help_category OK

Mysql.help_keyword OK

Mysql.help_relation OK

Mysql.help_topic OK

Mysql.innodb_index_stats OK

Mysql.innodb_table_stats OK

Mysql.mysql_recover OK

Mysql.ndb_binlog_index OK

Mysql.plugin OK

Mysql.proc OK

Mysql.procs_priv OK

Mysql.proxies_priv OK

Mysql.server_cost 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

Thesys schema is already up to date (version 1.5.1).

3. Use the dwarfish but modify the copy-friendly DML statement

   UseShow CREATE TABLEstatement to compare a new empty instance to an old corrupt instance, you can find: On some fields, the old and new versions,Perconaand theMySQLThe official version does differ in the length definition of certain fields. Older versions, the official version of the field length may be shorter relative to the new version and branch version. Short Although in theory can be stored under the inserted data, but the server is not allowed and do not know the other, resulting in the inability to insert a new user problem arises. This will require manual creationDMLstatement to synchronize the table structure of the old and new versions. This allows you to synchronize the changes to the cluster or from the library, which is more secure.

It is worth noting that, although shorter than the set value, the system considers the table to be corrupt. However, longer than the set value, or the field name case difference, although the system will be checked and displayed in error log , but will ignore the error itself.

This article is from "Long SQL Road ..." Blog, be sure to keep this source http://l0vesql.blog.51cto.com/4159433/1958927

Problem of unable to create user after recovering to different version of MySQL library with Xtrabackup