Analysis of different scenarios: Best practices for MySQL upgrade _ MySQL

MySQL upgrade is necessary. on PerconaSupport, we listed various questions about MySQL upgrade best practices. this article recommends some methods to upgrade MySQL under different circumstances. why is MySQL upgrade required? There are many reasons. for example, it is necessary to upgrade MySQL to use the new features. we listed various questions about MySQL upgrade best practices on Percona Support. this article recommends some methods to upgrade MySQL under different circumstances.

Why is MySQL upgrade required? There are many reasons, such as bugs fixed to use new features based on performance considerations. however, it is very dangerous to apply the application to your application before the test is complete, because the upgrade may make your application unable to operate normally-or cause performance problems. in addition, I suggest you follow MySQL release information and Percona Server-to see if the latest version has changed. you may have fixed a problem in the latest version.

Use SQL Dump to upgrade the main version:

The upgrade mode of Percona Server from 5.1 to 5.5 or from 5.5 to 5.6 is also applicable to Oracle MySQL.

First, the upgrade of the main version does not mean that no preparation is required or there is no risk. before upgrading, you need to read the related "upgrade" documents, such as the upgrade documents from MySQL5.1 to MySQL5.5 and from MySQL5.5 to MySQL5.6. in these documents, you need to pay special attention to"Incompatible ChangeAnd check whether you are affected by these changes. they may be configuration parameter name changes, old parameters are removed, new parameters are introduced, and so on-you need to follow these changes in my. change the cnf file accordingly. percona Server 5.5 requires special attention here. Percona Server 5.6 requires special attention here.

Currently, you have several feasible upgrade methods, one of which is more flexible, and the other is dependent on the topology of your current system and the size of the database data-this method is safer than the previous one. Here is an example of upgrading from Percona Server 5.5 to Percona Server 5.6. let's take a look at each other's upgrade process.

Generally, there are two upgrading methods:

• Upgrade directly: after the new version of the database is installed, use the existing data folder and run the mysql_upgrade script to upgrade the database.

• SQL export: export data from an earlier version of mysql and restore it to a new version of database. (Using mysqldump ).

In contrast, the second method is safer, but it also slows down the upgrade process.

Theoretically, the safest way is:

• Export permissions of all users

• Export all data and restore it to the new database version.

• Restore User permissions to the new database

The following are the basic operation steps (it is best to stop the database operation application before you start ).

1) obtain user and permission information. this operation backs up permissions of all users.

 
 

  
  
___FCKpd___0nbsp;wget percona.com/get/pt-show-grants; 
  
  
___FCKpd___0nbsp;perl pt-show-grants --user=root --ask-pass --flush > /root/grants.sql 
 
 

2) dump 5.5 database instance information (except mysql, information_schema, and cece_schema databases ).

 
 

  
  
___FCKpd___1nbsp;mysql -BNe "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'information_schema')" | tr 'n' ' ' > /root/dbs-to-dump.sql 
  
  
___FCKpd___1nbsp;mysqldump --routines --events --single-transaction --databases $(cat /root/dbs-to-dump.sql) > /root/full-data-dump.sql 
 
 

3) stop the database

 
 

  
  
___FCKpd___2nbsp;service mysql stop 
  
  
or 
  
  
___FCKpd___2nbsp;/etc/init.d/mysql stop 
 
 

4) move the data directory of the old database (version 5.5) (assume it is/var/lib/mysql, and change it to your own data directory here)

 
 

  
  
___FCKpd___3nbsp;mv /var/lib/mysql/ /var/lib/mysql-55 
 
 

5) install version 5.6 (follow the normal installation process). If you are not using the yum/apt-get Method for installation, you need to execute the mysql_install_db and mysql_upgrade commands.

6) import the user information exported in the 1) operation to the new database.

 
 

  
  
mysql -uroot < /root/grants.sql 
 
 

7) import the exported data to the new database.

 
 

  
  
___FCKpd___5nbsp;mysql -e "SET GLOBAL max_allowed_packet=1024*1024*1024"; 
  
  
___FCKpd___5nbsp;mysql -uroot -p --max-allowed-packet=1G < /root/full-data-dump.sql; 
 
 

At this time, all the tables are rebuilt and reloaded in MySQL 5.6, so all the binary files are available for MySQL 5.6. at the same time, you have completed the most clean and stable upgrade process, and you can restore your application-this upgrade process is the same as the upgrade process of valina MySQL and Percona Server. you can even upgrade Oracle MySQL to Percona Server. for example, upgrade Oracle MySQL 5.5 to Percona Server 5.6. emphasize that the upgrade process of MySQL is the same as that of Percona Server. you only need to replace Percona Server with Oracle MySQL.

"SQL dump" can also be called logical backup. using the upgraded MySQL binary file to recreate all tables is basically not compatible. in this respect, "SQL dump" is safer. however, for TB and GB-level big data, this method is very time-consuming. on the other hand, backing up/reloading such a large dataset, such as recreating an InnoDB table, you need to restore a lot of idle disks, which will cause disk fragmentation optimization. this is helpful for data that is often updated or deleted.

Use In-Place to upgrade the minor version:

This method is to upgrade a minor version in a major version, such as upgrading MySQL 5.5.35 to MySQL 5.5.38 or upgrading Percona Server 5.6.14 to the latest Percona Server 5.6.20.

This is called an on-site upgrade. you only install a new binary package and run the mysql_upgrade script to check and update the system table if necessary. Still, we strongly recommend that you check the release notes and bug fixes for new features during local upgrade. The release notes for Percona Server 5.5 and Percona Server 5.6 can be found here and there respectively.

When we need to upgrade the Percona Server, we have additional documents to describe some details about its Percona-specific features, which can be found here and there. This also includes the yum/apt package manager that completes the local upgrade process.

At the same time, in terms of security, you can upgrade the logic dump programs that use earlier descriptions, mysqldump or mydumper programs-the former is parallel backup and recovery and logical backup, and the upgrade is the safest method.

Upgrade to the latest version:

Skip MySQL 5.1 to upgrade MySQL from MySQL 5.0 to MySQL 5.5, or skip MySQL 5.5 to MySQL 5.1. 6. you can even upgrade from MySQL 5.0 to MySQL 5.6, although only a few people are still using MySQL 5.0. this is also used by Percona Server.

Here we assume that version 5.5 is skipped and Oracle MySQL or Percona Server is upgraded directly from version 5.1 to version 5.6.

Before you start, you must realize that this is a very careful operation and will step through an important MySQL version. That is to say, this is risky. Binary file upgrade is not recommended, and it is not safe to directly cross an important version, so you must not such 5.0-> 5.5, 5.1-> 5.6, or 5.0-> 5.6. One problem is that not all changes to MySQL are backward compatible. Some changes introduced in the new version may not only affect how data is processed, but also affect server behaviors, including SQL statements, MySQL servers, and internal storage engines (no standard here ). Another problem is the change between MySQL 5.6 and MySQL. some default setting variables are changed, which may lead to completely different results or completely different behaviors. For example, the default storage engine of MySQL5.5 is InnoDB. the storage engine of MySQL5.6 InnoDB can create separate tablespace (separate tablespace) for each database table, and GTID replication (GTID replication) is also introduced. Too many details are not listed here. all these changes are described in the "upgrade document" of MySQL. for details, see the upgrade document.

It is not recommended to skip an important version. Upgrading from MySQL5.1 to 5.6 should not be completed in one step. I suggest upgrading from 5.1 to 5.5 first, and then from 5.5 to 5.6. MySQL automatic upgrade (mysql_upgrade) should be run in each step ). This will effectively handle the upgrade changes mentioned in the manual.

MySQL upgrade prevention measures:

MySQL upgrade prevention measures are a basic part of the upgrade. Before you upgrade, make sure that you have thoroughly tested all application functions in the new MySQL version. This is especially important for upgrades between major versions and for major versions (for example, upgrading from MySQL5.1 to MySQL5.6 ).

Make sure you carefully read the release notes and understand all the changes. You can obtain the release notes for Oracle MySQL 5.5 and 5.6 from the following links:

Http://dev.mysql.com/doc/relnotes/mysql/5.5/en/

Http://dev.mysql.com/doc/relnotes/mysql/5.6/en/

Percona Server has a separate release description. For more information, see the following link:

Http://www.percona.com/doc/percona-server/5.5/release-notes/release-notes_index.html
Http://www.percona.com/doc/percona-server/5.6/release-notes/release-notes_index.html

If you plan to upgrade to Oracle MySQL 5.6 or Percona Server 5.6, we recommend that you first check the following key buckets. The following are some of the bugs you need to pay attention:

Http://bugs.mysql.com/bug.php? Id = 66546
Http://bugs.mysql.com/bug.php? Id = 68953
Http://bugs.mysql.com/bug.php? Id = 69444
Http://bugs.mysql.com/bug.php? Id = 70922
Http://bugs.mysql.com/bug.php? Id = 72794
Http://bugs.mysql.com/bug.php? Id = 73820

Upgrade level:

This is also the key to every MySQL upgrade. You need to perform each upgrade based on an upgrade level. We recommend that you upgrade dev/QA servers, staging server, and production servers. In fact, you can narrow down the upgrade process and upgrade to the desired version step by step. each step can fully test the original application.

Once you are satisfied with the upgrade on test servers and staging servers, you can upgrade on your production servers. In the copy environment, we recommend that you upgrade MySQL server load balancer (one by one) and then the MySQL master. In fact, you can first upgrade a server load balancer instance so that it can run for several days in a secure environment and observe its running status carefully. If no replica environment is set on your server, it is worth creating a replica environment to test the new version of MySQL. Once you are satisfied with the upgrade results, you can upgrade other slaves and finally upgrade the master.

The Percona package helps you upgrade MySQL:

Percona Toolkit can help you with the MySQL upgrade. Percona Toolkit tools will play a major role.

Pt-upgrade is one of the tools. It allows you to test whether the new MySQL instance can process certain queries as quickly as the old version. The new version may have some fundamental changes, because MySQL's query optimizer has changed significantly from 5.1 to 5.6, and data statistics may be refreshed, so the query plan will change. You can see more Optimizer changes in the manual.

Pt-query-digest is another tool that can be of great help to you. You can run your slow query log multiple times to compare the performance differences between the current version and the new version.

You can also get help from MySQL Percona Cloud Tools, a managed service that provides query performance analysis for all MySQL usage. Now you can register this service for free because it is currently being publicly tested. Percona Cloud Tools allows you to visually observe the query performance after each MySQL upgrade in many similar products.

Again, we strongly recommend that you back up data before each MySQL update. Percona XtraBackup is a free and open source (just like all Percona software ). This is a hot backup tool that allows you to back up data online without affecting your read/write operations on the database, and it has a slight impact on data backup.

Finally, you will think this post is also very useful "Upgrading MySQL." Although it is already an old post, it still makes sense. Let's look at a meaningful article webinar, "Upgrading to MySQL 5.6: Best Practices.". Both articles are from PerconaCEO Peter Zaitsev.

Conclusion:

A MySQL upgrade seems to be a simple task, but it is actually not that simple. In this article, I try to overwrite all possible MySQL updates. Again, I suggest you test the effect of your application in the new version before updating the application version. Otherwise, your application may crash, or the query efficiency may be reduced instead of increasing the query efficiency. Finally, I suggest you prepare a downgrade plan or measures to prevent unexpected errors after the upgrade. When a problem occurs, a planned downgrade program will greatly reduce the downtime of your application. We look forward to your comments and questions below.

MySQL upgrade best practices