首页 > 代码库 > mysql错误【一】[ERROR] Missing system table mysql.proxies_priv
mysql错误【一】[ERROR] Missing system table mysql.proxies_priv
环境：mysql一主一从架构，主库是mysql5.1，从库是mysql5.6；系统均为CentOS6.2
问题：
在主库上面执行的SQL语句
1.创建表
CREATE TABLE `app_versions` (
`date` date NOT NULL,
`app` char(16) NOT NULL,
`ver` char(16) NOT NULL,
`val` int(11) DEFAULT ‘0‘,
PRIMARY KEY (`date`,`app`,`ver`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2.创建用户并且给予权限
grant select on databasename.* to ‘username‘@‘IPaddress‘ identified by ‘password‘
3.刷新权限信息
flush privileges
在主库上面执行完之后，在从库上面执行show slave status \G发现IO进程和SQL进程显示的都是NO，然后执行start slave IO_THREAD之后再次执行show slave status \G 发现IO进程是拉起来了的显示的是YES，之后再执行start slave SQL_THREAD进程，show slave status \G发现IO进程和SQL进程都是显示的NO，并且在从库的错误日志中可以获取得到:
在错误日志中可以很明显的看得到日志提示：
Missing system table mysql.proxies_pri;please run mysql_upgrade to create it
日志提示系统表mysql.proxies_pri不存在，需要执行mysql_upgrade，然后我自己google了一下，
发现大部分都是因为升级mysql之后没有执行mysql_upgrade导致的，但是我在主库上面根本就没有进
行任何的升级操作，在从库也是这个样子，然后网上的建议是mysql_upgrade升级修复一下。
mysql_upgrade主要作用是检测所有的表并且升级mysql这个系统库内所有的表，是进行在线升级的，所以并不会影响线上操作(PS:当然不包括有关mysql库的操作)。
The mysql.proxies_priv table contains information about proxy privileges. The table can be queried and although it is possible to directly update it, it is best to use GRANT for setting privileges.
可以看到上述对于mysql.proxies_priv系统表的猜测，可以比较明显的看到这个表主要是用来管理
数据库用户权限信息的表，所以我猜测数据库很有可能卡在权限这块了，并且在从库中我在mysql.user这个表中并没有发现我之前grant创建的用户。这个时候我在从库上面设置了跳过一个事务：
set global sql_slave_skip_counter = 1(只是跳过一个事务，跳过之后归0)
之后我在重启start slave。slave恢复了正常，日志也能够正常的往里面写了。所以我猜想这个问题和
权限有关，假如需要验证的话，最好是在从库上面开启general log，并且在从库的binlog获取最新的事
务的信息并且根据获取的信息在relay log中继日志找到下一个事务是不是这个。
但是这个方案也是属于治标不治本，下次在执行grant操作的时候，可能还是会出现这个问题，所以还是最后使用mysql_upgrade
mysql_upgrade -uroot -p
[root@gitlab-test data]# mysql_upgrade -uroot -p
Enter password:
Looking for ‘mysql‘ as: mysql
Looking for ‘mysqlcheck‘ as: mysqlcheck
This installation of MySQL is already upgraded to 5.6.35, use --force if you still need to run mysql_upgrade
[root@gitlab-test 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 be insecure.
Running ‘mysqlcheck‘ with connection arguments: ‘--port=3306‘ ‘--socket=/data/mysql/mysql.sock‘
Warning: Using a password on the command line interface can be 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 be insecure.
Running ‘mysqlcheck‘ with connection arguments: ‘--port=3306‘ ‘--socket=/data/mysql/mysql.sock‘
Warning: Using a password on the command line interface can be insecure.
Running ‘mysqlcheck‘ with connection arguments: ‘--port=3306‘ ‘--socket=/data/mysql/mysql.sock‘
Warning: Using a password on the command line interface can be 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] Missing system table mysql.proxies_priv