Best practices for MySQL upgrade

Best practices for MySQL upgrade

MySQL upgrade is very necessary. We listed a variety of questions about MySQL upgrade best practices on Percona Support. This article recommends some methods to upgrade MySQL in different situations.

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.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

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, pay special attention to the "Incompatible Change" section and 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.

$ Wget percona.com/get/pt-show-grants;
$ 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 ).

$ Mysql-BNe "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME not in ('mysql', 'performance _ scheme', 'information _ scheme ') "| tr 'n'''>/root/dbs-to-dump. SQL
$ Mysqldump -- routines -- events -- single-transaction -- databases $ (cat/root/dbs-to-dump. SQL)>/root/full-data-dump. SQL

3) Stop the database

$ Service mysql stop
Or
$/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)

$ 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.

$ Mysql-e "set global max_allowed_packet = 1024*1024*1024 ";
$ 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.

For more details, please continue to read the highlights on the next page:

1 2 Next Page