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

Environment: mysql one master one slave architecture, master library is mysql5.1, slave library is mysql5.6;. All systems are CentOS6.2.

Question:

SQL statements executed on top of the main library

1. Create a tabl

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

two。 Create a user and grant permissions

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

3. Refresh permission information

Flush privileges

After executing on the master library, execute show slave status\ G on the slave library to find that both the IO process and the SQL process display NO, and then execute show slave status\ G again after executing start slave IO_THREAD to find that the IO process is pulled up to show YES, and then execute the start slave SQL_THREAD process. Show slave status\ G finds that both the IO process and the SQL process are displayed NO, which can be obtained from the error log of the slave library:

The log prompt can be clearly seen in the error log:

Missing system table mysql.proxies_pri;please run mysql_upgrade to create it

The log indicates that the system table mysql.proxies_pri does not exist and needs to execute mysql_upgrade, and then I google myself.

I found that most of it was caused by not executing mysql_upgrade after upgrading mysql, but I didn't enter the main library at all.

Line any upgrade operation, the same is true in the slave library, and then the online suggestion is to fix the mysql_upgrade upgrade.

The main function of mysql_upgrade is to detect all tables and upgrade all tables in the mysql system library, which is upgraded online, so it does not affect online operations (PS: does not include operations on the mysql library, of course).

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.

You can see the above guesses about the mysql.proxies_priv system table, and it is obvious that this table is mainly used to manage

A table of database user rights information, so I guess the database is probably stuck in the permissions section, and I didn't find the users I created by grant in the mysql.user table from the library. At this time, I set up a skip transaction from the library:

Set global sql_slave_skip_counter = 1 (just skip a transaction and return to 0 after skipping)

Then I was rebooting start slave. Slave has returned to normal, and the log can be written into it normally. So I guess this question has something to do with

Permission is related. If verification is needed, it is best to open general log from the library and get the latest information from the library's binlog.

And find out if this is the next transaction in the relay log relay log based on the information obtained.

But this solution is also a palliative rather than a permanent cure, and this problem may still occur next time when performing the grant operation, so we should use mysql_upgrade in the end.

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

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.