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_priv
table 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