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

1. Upgrade method

In-Place Upgrade: (local upgrade) Involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.Logical Upgrade: (logical upgrade) Involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version, and running mysql_upgrade.

two。 Upgrade path

Only General Availability (GA) versions are supported from 5.6to 5.7.It is recommended to upgrade to the latest version before the next version of the upgrade. For example, the last version of 5.6 is upgraded first, and then the skip version is not supported. For example, upgrading from 5.5x to 5.7.y is supported.

Upgrade within a release series is supported

3. Before we begin

Backup, including system library, system table view incompatible changes, see Features Removed in MySQL 5.7. See Section 2.11.1.2, "Changes Affecting Upgrades to MySQL 5.7". This section describes changes that may require action before or after upgrading. This section describes the implementation changes that may be required before and after the upgrade. If you use replication replication, check out Section 16.4.3, "Upgrading a Replication Setup". If you use InnoDB's XA transactions, run "XA RECOVER" before upgrading to check for uncommitted Xa transactions. If any results are returned, you need to execute the XA COMMIT or XA ROLLBACK statement to commit or roll back Xa transactions.

In-Place Upgrade

To perform an in-place upgrade:

Review the changes described in Section 2.11.1.2, "Changes Affecting Upgrades to MySQL 5.7" for steps to be performed before upgrading.Configure MySQL to perform a slow shutdown by setting innodb_fast_shutdown to 0. For example:

Mysql-u root-p-- execute= "SET GLOBAL innodb_fast_shutdown=0"

With a slow shutdown, InnoDB performs a full purge and change buffer merge before shutting down, which ensures that data files are fully prepared in case of file format differences between releases.

Shut down the old MySQL server. For example:

Mysqladmin-u root-p shutdownUpgrade the MySQL binary installation or packages. If upgrading a binary installation, unpack the new MySQL binary distribution package. See Obtain and Unpack the Distribution. For package-based installations, replace the old packages with the new ones.

Note

For supported Linux distributions, the preferred method for replacing the MySQL packages is to use the MySQL software repositories; see Section 2.11.1.3, "Upgrading MySQL with the MySQL Yum Repository", Section 2.11.1.4, "Upgrading MySQL with the MySQL APT Repository", or Upgrading MySQL with the MySQL SLES Repository for instructions.Start the MySQL 5.7 server, using the existing data directory. For example:

Mysqld_safe-user=mysql-datadir=/path/to/existing-datadir

Run mysql_upgrade. For example:

Mysql_upgrade-u root-p

Mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL. Mysql_upgrade also upgrades the mysql system database so that you can take advantage of new privileges or capabilities.

Note

Mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.11, "Server-Side Help".

Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect. For example:

Mysqladmin-u root-p shutdown

Mysqld_safe-user=mysql-datadir=/path/to/existing-datadir

Logical Upgrade

Review the changes described in Section 2.11.1.2, "Changes Affecting Upgrades to MySQL 5.7" for steps to be performed before upgrading.Export your existing data from the previous MySQL version:

Mysqldump-u root-p

-add-drop-table-routines-events

-- all-databases-- force > data-for-upgrade.sql

Note

Use the-routines and-events options with mysqldump (as shown above) if your databases include stored programs. The-all-databases option includes all databases in the dump, including the mysql database that holds the system tables.

Important

If you have tables that contain generated columns, use the mysqldump utility provided with MySQL 5.7.9 or higher to create your dump files. The mysqldump utility provided in earlier releases uses incorrect syntax for generated column definitions (Bug # 20769542). You can use the INFORMATION_SCHEMA.COLUMNS table to identify tables with generated columns.

Shut down the old MySQL server. For example:

Mysqladmin-u root-p shutdownInstall MySQL 5.7. For installation instructions, see Chapter 2, Installing and Upgrading MySQL.Initialize a new data directory, as described at Section 2.10.1, "Initializing the Data Directory". For example:

Mysqld-initialize-datadir=/path/to/5.7-datadir

Copy the temporary 'root'@'localhost' password displayed to your screen or written to your error log for later use.

Start the MySQL 5.7 server, using the new data directory. For example:

Mysqld_safe-user=mysql-datadir=/path/to/5.7-datadirReset the root password:

Shell > mysql-u root-p

Enter password: * ALTER USER USER () IDENTIFIED BY 'your new password'

Load the previously created dump file into the new MySQL server. For example:

Mysql-u root-p-- force < data-for-upgrade.sql

Note

It is not recommended to load a dump file when GTIDs are enabled on the server (gtid_mode=ON), if your dump file includes system tables. Mysqldump issues DML instructions for the system tables which use the non-transactional MyISAM storage engine, and this combination is not permitted when GTIDs are enabled. Also be aware that loading a dump file from a server with GTIDs enabled, into another server with GTIDs enabled, causes different transaction identifiers to be generated.

Run mysql_upgrade. For example:

Mysql_upgrade-u root-p

Mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL. Mysql_upgrade also upgrades the mysql system database so that you can take advantage of new privileges or capabilities.

Note

Mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.11, "Server-Side Help".

Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect. For example:

Mysqladmin-u root-p shutdown

Mysqld_safe-user=mysql-datadir=/path/to/5.7-datadir

Upgrade Troubleshooting

If problems occur, such as that the new mysqld server does not start, verify that you do not have an old my.cnf file from your previous installation. You can check this with the-print-defaults option (for example, mysqld-- print-defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation.If, after an upgrade, you experience problems with compiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, check the date for your mysql.h file and libmysqlclient.a library to verify that they are from the new MySQL distribution. If not, recompile your programs with the new headers and libraries. Recompilation might also be necessary for programs compiled against the shared client library if the library major version number has changed (for example, from libmysqlclient.so.15 to libmysqlclient.so.16). If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different nonconflicting name. The same is true if the new version of MySQL implements a built-in function with the same name as an existing stored function. See Section 9.2.4, "Function Name Parsing and Resolution", for the rules describing how the server interprets references to different kinds of functions.

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.