mysql错误【一】[ERROR] Missing system table mysql.proxies_priv

环境：mysql一主一从架构，主库是mysql5.1，从库是mysql5.6；系统均为CentOS6.2 问题： 在主库上面执行的SQL语句 1.创建表 CREATETABLE`app_versions`(
`date`dateNOTNULL,
`app`char(16)NOTNULL,
`ver`char(16)NOTNULL,
`val`int(11)DEFAULT’0′,
PRIMARYKEY(`date`,`app`,`ver`)
)ENGINE=MyISAMDEFAULTCHARSET=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库的操作)。 Themysql.proxies_privtable contains information about proxy privileges. The table can be queried and although it is possible to directly update it, it is best to useGRANTfor 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 -pEnter password:Looking for ‘mysql’ as: mysqlLooking for ‘mysqlcheck’ as: mysqlcheckThis 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 –forceEnter password:Looking for ‘mysql’ as: mysqlLooking for ‘mysqlcheck’ as: mysqlcheckRunning ‘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 OKmysql.db OKmysql.event OKmysql.func OKmysql.general_log OKmysql.help_category OKmysql.help_keyword OKmysql.help_relation OKmysql.help_topic OKmysql.innodb_index_stats OKmysql.innodb_table_stats OKmysql.ndb_binlog_index OKmysql.plugin OKmysql.proc OKmysql.procs_priv OKmysql.proxies_priv_bak OKmysql.servers OKmysql.slave_master_info OKmysql.slave_relay_log_info OKmysql.slave_worker_info OKmysql.slow_log OKmysql.tables_priv OKmysql.time_zone OKmysql.time_zone_leap_second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKRunning ‘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 OKdata_test.test OKgitlabhq_production.abuse_reports OKgitlabhq_production.application_settings OKgitlabhq_production.audit_events OKgitlabhq_production.broadcast_messages OKgitlabhq_production.deploy_keys_projects OKgitlabhq_production.emails OKgitlabhq_production.events OKgitlabhq_production.forked_project_links OKgitlabhq_production.identities OKgitlabhq_production.issues OKgitlabhq_production.keys OKgitlabhq_production.label_links OKgitlabhq_production.labels OKgitlabhq_production.members OKgitlabhq_production.merge_request_diffs OKgitlabhq_production.merge_requests OKgitlabhq_production.milestones OKgitlabhq_production.namespaces OKgitlabhq_production.notes OKgitlabhq_production.oauth_access_grants OKgitlabhq_production.oauth_access_tokens OKgitlabhq_production.oauth_applications OKgitlabhq_production.project_import_data OKgitlabhq_production.projects OKgitlabhq_production.protected_branches OKgitlabhq_production.schema_migrations OKgitlabhq_production.services OKgitlabhq_production.snippets OKgitlabhq_production.subscriptions OKgitlabhq_production.taggings OKgitlabhq_production.tags OKgitlabhq_production.users OKgitlabh开发云主机域名q_production.users_star_projects OKgitlabhq_production.web_hooks OKOK

相关推荐: prompt 提示符

欢迎关注MySQL 8.0必知必会系列课程。重新配置默认mysql>的提示符•常用选项•用户 (u)•主机 (h) •数据库 (d)•计数器 (c)•session ID(C)•完整账号名称user_name@host_name（U）•完整的当前日期（D…