Shulou(Shulou.com)06/01 Report--

The following is mainly to solve the problem that xtrabackup can not create a user after restoring the MySQL library. I hope these words can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

1. Prompt when creating a user with grant and create user:-

Cannot load from MySQL.proc. The table is probably corrupted

two。 But you can add it with insert into mysql.user values ();

The password section may need to use select password () to find the password encryption value and then insert it.

3. The mysql.proc_ private table and the mysql.procs table need to be repaired.

4. There are three ways to fix it:

1. Directly create a new empty instance, and then overwrite the uncorrupted table file to the problematic strength. Because the tables in the mysql library are myisam tables in version 5. 6-5. 7. Table files can be copied directly for modification.

two。 Practical mysql_upgrade program to repair table structure

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

The output is as follows:

Enter password:

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system 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

The sys schema is already up to date (version 1.5.1).

3. Use the most elegant but copy-friendly DML statements to modify

Using the show create table statement to compare the new empty instance with the old corrupted instance, we can find that in some fields, the old and new versions, the official version of percona and mysql do differ in the definition of the length of some fields. In the old version, the field length set by the official version may be shorter than the new version and branch version. Although it is short in theory, it can store the inserted data, but the server is not allowed and not recognized, resulting in the problem that new users cannot be inserted. This requires the manual creation of DML statements to synchronize the new and old versions of the table structure. This allows you to synchronize changes to the cluster or from the library, which is more secure.

It is worth noting that although it is shorter than the set value, the system will think that the table is damaged. But longer than the set value, or field name case difference, although the system will check and display in the error log, but will ignore this error.

For the above about solving xtrabackup recovery MySQL library can not create user method brief analysis, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.