MySQL error "one" [ERROR] Missing system table Mysql.proxies_priv

Environment: MySQL One master one from the architecture, the main library is mysql5.1, from the library is the mysql5.6; the system is CentOS6.2

Problem:

SQL statements executed on top of the main library

1. Create a table

CREATE TABLE ' app_versions ' (
' Date ' date is not NULL,
' App ' char (+) is not NULL,
' ver ' char (+) is not NULL,
' Val ' int (one) DEFAULT ' 0 ',
PRIMARY KEY (' Date ', ' app ', ' ver ')
) Engine=myisam DEFAULT charset=latin1;

2. Create a user and give permission

Grant SELECT on databasename.* to ' username ' @ ' IPaddress ' identified by ' password '

3. Refresh Permission Information

Flush Privileges

After executing on the main library, execute show slave status from the top of the library, \g discover that the IO process and SQL process show no, and then execute the start slave io_thread again after the show slave status \g Discover that the IO process is pulled up to show yes, before executing the start slave sql_thread process, show slave status \g discover that both the IO process and the SQL process are displayed no, and can be obtained from the library's error log:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/94/B3/wKiom1kMTOTQMb_CAAAZoFCCPSU491.png "title=" Q1.png "alt=" Wkiom1kmtotqmb_caaazofccpsu491.png "/>

In the error log, it is obvious that the log prompt can be seen:

Missing system table Mysql.proxies_pri;please run Mysql_upgrade to create it

Log prompt system table Mysql.proxies_pri does not exist, need to execute mysql_upgrade, and then I myself Google a bit,

Most of the discovery was caused by not performing mysql_upgrade after upgrading MySQL, but I did not enter it at all on the main library.

Line any upgrade operation, in the same way from the library, and then the online recommendation is Mysql_upgrade upgrade to fix it.

The main function of Mysql_upgrade is to detect all the tables and upgrade all the tables in the system library, which is an online upgrade, so it does not affect the inline operation (PS: not including the operation of MySQL library).

themysql.proxies_privtable contains information about proxy privileges. The table can be queried and although it's possible to directly update it,it 's best-useGRANTFor setting privileges.

You can see the above for the MYSQL.PROXIES_PRIV system table guess, it can be more obvious to see this table is mainly used to manage

Database user rights information for the table, so I guess the database is probably stuck in the Permissions section, and in the From library I have not found the user I created before grant in the Mysql.user table. At this point I'm setting up a skip over a transaction from the Vault:

Set Global Sql_slave_skip_counter = 1 (just skip a transaction, skip to 0)

Then I was restarting start slave. Slave restored to normal, the log can also be normal to write inside. So I guess the question and

permissions, if you need to verify, it is best to open the general log from above the library, and in the Binlog from the library to get the latest things

Information and find the next transaction in the relay log relay log based on the information obtained is not this.

But this solution is also a symptom of the problem, the next time you execute the grant operation, this may still occur, so the last use of Mysql_upgrade

Mysql_upgrade-uroot-p

[Email protected] data]# mysql_upgrade-uroot-p

Enter Password:

Looking for ' MySQL ' As:mysql

Looking for ' Mysqlcheck ' As:mysqlcheck

This installation of MySQL are already upgraded to 5.6.35, use--force if you still need to run Mysql_upgrade

[Email protected] data]# mysql_upgrade-uroot-p--force

Enter Password:

Looking for ' MySQL ' As:mysql

Looking for ' Mysqlcheck ' As:mysqlcheck

Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/data/mysql/mysql.sock '

Warning:using a password on the command line interface can is insecure.

Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/data/mysql/mysql.sock '

Warning:using a password on the command line interface can is insecure.

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.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_bak 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

Running ' Mysql_fix_privilege_tables ' ...

Warning:using a password on the command line interface can is insecure.

Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/data/mysql/mysql.sock '

Warning:using a password on the command line interface can is insecure.

Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/data/mysql/mysql.sock '

Warning:using a password on the command line interface can is insecure.

Core_test.test OK

Data_test.test OK

Gitlabhq_production.abuse_reports OK

Gitlabhq_production.application_settings OK

Gitlabhq_production.audit_events OK

Gitlabhq_production.broadcast_messages OK

Gitlabhq_production.deploy_keys_projects OK

Gitlabhq_production.emails OK

Gitlabhq_production.events OK

Gitlabhq_production.forked_project_links OK

Gitlabhq_production.identities OK

Gitlabhq_production.issues OK

Gitlabhq_production.keys OK

Gitlabhq_production.label_links OK

Gitlabhq_production.labels OK

Gitlabhq_production.members OK

Gitlabhq_production.merge_request_diffs OK

Gitlabhq_production.merge_requests OK

Gitlabhq_production.milestones OK

Gitlabhq_production.namespaces OK

Gitlabhq_production.notes OK

Gitlabhq_production.oauth_access_grants OK

Gitlabhq_production.oauth_access_tokens OK

Gitlabhq_production.oauth_applications OK

Gitlabhq_production.project_import_data OK

Gitlabhq_production.projects OK

Gitlabhq_production.protected_branches OK

Gitlabhq_production.schema_migrations OK

Gitlabhq_production.services OK

Gitlabhq_production.snippets OK

Gitlabhq_production.subscriptions OK

Gitlabhq_production.taggings OK

Gitlabhq_production.tags OK

Gitlabhq_production.users OK

Gitlabhq_production.users_star_projects OK

Gitlabhq_production.web_hooks OK

Ok

MySQL error "one" [ERROR] Missing system table Mysql.proxies_priv