Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Online environment MySQL5.5 upgrade to 5.6

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This paper mainly records the process of upgrading the online environment percona5.5.35 to 5.6.32-78.0.

Welcome to reprint, please indicate the author and source. Author: Zhang Zheng blog: http://space.itpub.net/26355921 QQ:176036317 if you have any questions, please feel free to contact us.

Due to the need to migrate a group of online servers, and the previous version is 5.5.35, and the migrated version is 5.6.32-78.0, use the way of building cascading slave libraries, and then switch. Restore it using xtrabackup backup, and frequently report errors in version 5.6 error log:

7fc52806d700 InnoDB: Error: Fetch of persistent statistics requested for table "xxx". "xxx"

But the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or

Have unexpected structure. Using transient stats instead.

Because the data file is percona5.5, directly reverting to 5. 6, some system tables are inconsistent, so you need to upgrade. After consulting the official documents, the upgrade conditions are given as follows:

Upgrade 5.5 files to version 5.6 (in place upgrade)

1. The instance after crash should not be upgraded directly. You should do crash recovery first, and then upgrade it.

2. The previous percona-server data page is not 16K and upgrade is not recommended.

3. If innodb_fast_checksum is enabled in version 5.5, in place upgrade cannot be used.

Once the conditions are met, you can upgrade. The steps given in the official document are as follows:

1. Install percona5.6 version of server, client, shared

2. Start using-- skip-grant-tables

3. Upgrade using mysql_upgrade

4. Restart the instance (start normally without adding-- skip-grant-tables)

If it can't find the pid file, kill the server and start it normally:

$killall / usr/sbin/mysqld

$/ sbin/service mysql start

The actual steps are:

1. Percona5.6 related software has been installed on the server.

2. Close the instance, and then start mysqld with mysqld plus-- skip-grant-tables

3. Use mysql_upgrade-socket=/data/mysqlxxxx/mysqldxxxx.sock

An error was reported as a result:

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.host 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 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'...

ERROR 1010 (HY000) at line 150: Error dropping database (can't rmdir'. / performance_schema/', errno: 17)

ERROR 1007 (HY000) at line 1007: Can't create database 'performance_schema'; database exists

FATAL ERROR: Upgrade failed

After careful inspection, it is found that there are other non-database files in the performance_schema database directory. Therefore, mysql_upgrade deletes the Times error, manually deletes the non-database files in the performance_schema directory, and then performs the upgrade again. This step passed, but another error occurred:

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.host 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 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'...

Running 'mysqlcheck' with connection arguments:'-- socket=/data/mysql3307/data/mysqld3307.sock'

Mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

FATAL ERROR: Error during call to mysql_check for fixing the db/tables names on all db (s) except mysql

Because of the root permission problem, mysqld is enabled-- skip-grant-tables, which should not require a password. However, when executing mysql_upgrade, such an error will always be reported at a certain step, so when it is recommended to use mysql_upgrade, it is generally recommended to add the user name and password of root. Execute again:

Mysql_upgrade-uroot-pxxxxx-socket=/data/mysqlxxxx/mysqldxxxx.sock

...

Xxxxxxx. Ok

OK

4. Restart mysqld

At this point, the upgrade from percona5.5 to percona5.6 is complete.

Check the error log and find that there is no error.

Note: if the master library is version 5.5 and the slave library is version 5.6, you will find an error in the error log of the slave library:

[Warning] Slave Unknown system variable O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.35-33.0-log), even though it is on the slave (version: 5.6.32-78.0-log), Error_code: 1193

Check the relevant articles and say don't worry about it: http://bugs.mysql.com/bug.php?id=68164

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report