MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
This section describes how to upgrade MySQL binary and package-based installations on Unix/Linux. In-place and logical upgrade methods are described.
An in-place upgrade involves shutting down the old MySQL server, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and upgrading any remaining parts of the existing installation that require upgrading.
Only upgrade a MySQL server instance that was properly shut
down. If the instance unexpectedly shutdown, then restart the
instance and shut it down with
innodb_fast_shutdown=0
before
upgrade.
If you upgrade an installation originally produced by installing multiple RPM packages, upgrade all the packages, not just some. For example, if you previously installed the server and client RPMs, do not upgrade just the server RPM.
For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysqld_safe is not installed. In such cases, use systemd for server startup and shutdown instead of the methods used in the following instructions. See Section 2.5.10, “Managing MySQL Server with systemd”.
To perform an in-place upgrade:
If you use XA transactions with InnoDB
,
run XA
RECOVER
before upgrading to check for uncommitted
XA transactions. If results are returned, either commit or
rollback the XA transactions by issuing an
XA
COMMIT
or
XA
ROLLBACK
statement.
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 shutdown
Upgrade 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, install the new packages.
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.
mysql_upgrade does not upgrade the contents of the time zone tables or help tables. For upgrade instructions, see Section 5.1.13, “MySQL Server Time Zone Support”, and Section 5.1.14, “Server-Side Help Support”.
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
&
A logical upgrade involves exporting SQL from the old MySQL instance using a backup or export utility such as mysqldump or mysqlpump, installing the new MySQL server, and applying the SQL to your new MySQL instance.
For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysqld_safe is not installed. In such cases, use systemd for server startup and shutdown instead of the methods used in the following instructions. See Section 2.5.10, “Managing MySQL Server with systemd”.
To perform a logical upgrade:
Review the information in Section 2.10.1, “Before You Begin”.
Export your existing data from the previous MySQL installation:
mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > data-for-upgrade.sql
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.
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 shutdown
Install MySQL 5.7. For installation instructions, see Chapter 2, Installing and Upgrading MySQL.
Initialize a new data directory, as described at Section 2.9.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-datadir
&
Reset the root
password:
$>mysql -u root -p
Enter password:****
<- enter temporary root password
mysql> 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
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.
mysql_upgrade does not upgrade the contents of the time zone tables or help tables. For upgrade instructions, see Section 5.1.13, “MySQL Server Time Zone Support”, and Section 5.1.14, “Server-Side Help Support”.
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
&