Detailed explanation of security upgrade Mysql method _mysql

MySQL upgrades are very necessary. We have listed a variety of questions about MySQL upgrade best practices on Percona support. This article recommends several ways to upgrade MySQL in different situations.

Why is MySQL upgrade necessary? There are many reasons, such as: To use the new features, based on performance considerations, fix bugs. But it's very risky to apply to your application without adequate testing, because upgrades can make your application not work-and can cause performance problems. In addition, I recommend that you focus on MySQL's release information and Percona Server-see what changes have been made to the latest version. Perhaps you have fixed a problem that you are worrying about in the latest release.

To upgrade a major version via SQL dump:

Upgrading the Percona server from 5.1 to version 5.5 or upgrading from 5.5 to 5.6 is also true for Oracle MySQL.

First, the major version upgrade is not to say that you can do no preparation, or that there is no risk. Before you upgrade, you will need to read the relevant "upgrade" documents, such as MySQL5.1 to MySQL5.5 upgrades, MySQL5.5 to MySQL5.6 upgrades. In these documents you need to pay special attention to the "Incompatible change" section, and check to see if you are affected by these changes. They may be configuration parameter name changes, remove old parameters, introduce new parameters, etc.-you need to make the corresponding changes in the my.cnf file according to these changes. Percona Server 5.5 requires special attention to the documentation here, Percona Server 5.6 requires special attention to the documentation here.

Now you have several possible ways to upgrade, one of which is more flexible, and the other relies on the topology of your current system and the size of your database data-a more secure approach than the previous one. Let's take a look at an example from Percona Server 5.5 to Percona server 5.6 to look at each other's upgrade process.

In general, there are two ways to upgrade:

1. Direct upgrade: Install the new version of the database, using the existing Data folder, while running Mysql_upgrade to upgrade the script.
2. SQL Export: Export data from an older version of MySQL and revert to a new version of the database. (using the Mysqldump tool).

In contrast, the second approach is safer, but it also makes the upgrade process slower.

In theory, the safest way to do this is to:

• Export Permissions for all users
• Export all data and revert to the new version database
• Restore user rights to the new database

Here is the basic procedure (it is best to stop the application of database operations before you start).

1 Get user and permission information. This operation backs up the 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 instances of all information (except MySQL, information_schema and Performance_schema databases).

$ 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
$ 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 old database (5.5 version) of the data directory (assuming it is/var/lib/mysql, this should be changed to your own data directory)

$ mv/var/lib/mysql//var/lib/mysql-55

5 Install version 5.6 (Follow normal installation procedure). If you are not installing in a yum/apt-get way, you need to perform mysql_install_db and mysql_upgrade two commands.

6 The user information exported in the 1 operation is imported into the new database.

 
Mysql-uroot </root/grants.sql

7) The data exported by 2 is imported into 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 point, all the tables are rebuilt and reloaded in MySQL 5.6, so all binaries are available for MySQL 5.6. At the same time you have completed the cleanest/most stable upgrade process, you can restore your application-This upgrade process is the same as Valina MySQL and Percona server upgrade process. You can even upgrade Oracle MySQL to Percona server. For example, upgrade Oracle MySQL 5.5 to Percona Server 5.6. Again: The MySQL upgrade process is the same as the Percona server upgrade process, just replace the Percona server with Oracle MySQL.

SQL dump can also be called a logical backup. Using the upgraded MySQL binaries to rebuild all of the tables there is basically no compatibility problem, and "SQL Dump" is more secure in this regard. But for terabytes, gigabytes of large data, this is a time-consuming process. On the other hand, backup/reload such a large dataset, such as rebuilding the InnoDB table, you need to restore a lot of free disk, which will cause disk defragmentation optimization. This is useful for data that is frequently updated or deleted.

To upgrade a small version with In-place:

This upgrade is a minor version upgrade 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 in-place upgrade, you install only a new binary package, then run the Mysql_upgrade script and, if necessary, check and update the system tables. Still, in-place upgrades we strongly recommend checking the release notes for new features, bug fixes, and more. Percona Server 5.5 and Percona Server 5.6 Release notes can be found here and there respectively.

When we need to upgrade the Percona server, some of the features of its percona-specific, for the Percona server we have additional documentation to describe some of the details that can be found here and there. This also includes the YUM/APT Package Manager that completes the in-place upgrade process.

At the same time, in terms of security, you can upgrade those logical dumps that use the earlier descriptions, mysqldump or Mydumper programs-the former in parallel backup and recovery and logical backup, the safest way to upgrade.

Direct upgrade to latest version:

Skip version 5.1 directly to upgrade MySQL from version 5.0 to 5.5, or skip the 5.5 version directly from MySQL 5.1 upgrade to 5.6. Even, you can upgrade directly from MySQL 5.0 to MySQL 5.6 Although only a few people are still using the MySQL 5.0 version. This is also used in Percona Server.

Here we assume skip version 5.5 to upgrade Oracle MySQL or Percona server directly from 5.1 to version 5.6.

Before you begin, realize that this is a very discreet operation that will step across an important version of MySQL. In other words, there is a risk. Upgrading with binary files is not recommended, and it is not safe to cross an important version directly, so you must not 5.0->5.5,5.1->5.6, or 5.0->5.6. One problem is that the MySQL version is not all of the changes are forward compatible. Some of the changes described in the new version may not only affect how the data is handled, but also affect server behavior including SQL statements and MySQL servers and internal storage engines (no standards are known here). Another problem is the change between MySQL5.0 and version 5.6, and some default settings variables are changed, which may result in a completely different or completely distinct behavior. For example, the default storage engine for MySQL5.5 is the innodb,mysql5.6 storage engine InnoDB will be able to create a separate tablespace (separate tablespace) for each database table, as well as Gtid replication (Gtid Replication) was also introduced. There are so many details that are not listed here, all of which are described in MySQL's "Upgrade document," Where you can view the upgrade documentation.

Here it is important to mention that skipping a major version is highly recommended. Upgrade from MySQL5.1 to 5.6 should not be completed in one step, I suggest that the first upgrade from 5.1 to 5.5, and then upgrade from 5.5 to 5.6, every step to run MySQL automatic upgrade (mysql_upgrade). This will compare effectively with the upgrade changes described in the manual.

MySQL Upgrade prevention measures:

MySQL's upgrade precautions are an essential part of the upgrade. Before you upgrade, make sure you thoroughly test all of the application features in the new MySQL version. This is particularly important for upgrades between important versions, and it is also important to cross upgrades between important versions (for example, from MySQL5.1 to MySQL5.6).

Make sure you read the release notes carefully and that you know all the changes. You can get 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 separate release notes, which are listed in the order described below:

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, I recommend that you first check the following key bugs that already exist. Here are some bugs you need to be aware of:

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

Upgraded hierarchy:

This is also the key to the MySQL upgrade every time. You need to do every upgrade based on an upgrade hierarchy. The recommended order structure is to upgrade DEV/QA servers first, then upgrade staging server, and finally upgrade to production servers. In fact, you can reduce the pace of upgrades, step-by-Step upgrade to the version you want, each step can fully test the original application.

Once you are satisfied with the upgrades that are running on test servers, staging servers, you can upgrade on your production servers. In the replica environment, I recommend that you upgrade MySQL slaves (one after the other) and finally upgrade MySQL master. In fact, you can upgrade a slaves to allow it to run in a safe environment for a few days, while observing its operation. If your server does not have a replica environment set up, then it is worthwhile to create a replica environment to test the new version of MySQL. Once you are satisfied with the upgrade results, you can upgrade the other slaves and finally upgrade master.

Percona package helps you to update MySQL:

In the MySQL upgrade, Percona Toolkit can help you. Some of the tools of Percona Toolkit will play a big part.

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

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

You can also get help from MySQL's Percona Cloud tools, a managed service that provides query performance analysis for all MySQL uses. Now you can sign up for this service for free, because the service is now being tested publicly. Percona Cloud Tools in many of the same products, allows you to visually observe each MySQL upgrade after the query performance.

Again, it is strongly recommended that you back up your data every time you update MySQL. Percona Xtrabackup is a free, open source (just like all Percona software). This is a hot backup tool that allows you to back up data online without affecting the read and write operations of your database, and it has a small impact when it backs up data.
Conclusion:

A MySQL upgrade seems like a simple task, but it's not really that simple. I'm trying to maximize coverage of all the things you might encounter with MySQL upgrades in this article. Again, I suggest that you test the effect of your application in the new version before applying the updated version. Otherwise, your application may crash, or you will not be able to improve query efficiency but reduce query efficiency. Finally, I suggest that you prepare a downgrade plan or measure to prevent unexpected errors after you upgrade. When problems arise, having a planned downgrade program will greatly reduce your application downtime. I expect you to post comments and questions below.