Ubuntu Manpage: mysql_upgrade - check and upgrade MySQL tables

Provided by: mysql-server-core-5.7_5.7.42-0ubuntu0.18.04.1_amd64

NAME

       mysql_upgrade - check and upgrade MySQL tables

SYNOPSIS

       mysql_upgrade [options]

DESCRIPTION

Each time you upgrade MySQL, you should execute mysql_upgrade, which looks for
incompatibilities with the upgraded MySQL server:

• It upgrades the system tables in the mysql schema so that you can take advantage of
new privileges or capabilities that might have been added.

• It upgrades the Performance Schema and sys schema.

• It examines user schemas.

If mysql_upgrade finds that a table has a possible incompatibility, it performs a table
check and, if problems are found, attempts a table repair. If the table cannot be
repaired, see Section 2.10.12, “Rebuilding or Repairing Tables or Indexes” for manual
table repair strategies.

mysql_upgrade communicates directly with the MySQL server, sending it the SQL statements
required to perform an upgrade.

Important
In MySQL 5.7.11, the default --early-plugin-load value is the name of the keyring_file
plugin library file, causing that plugin to be loaded by default. In MySQL 5.7.12 and
higher, the default --early-plugin-load value is empty; to load the keyring_file
plugin, you must explicitly specify the option with a value naming the keyring_file
plugin library file.

InnoDB tablespace encryption requires that the keyring plugin to be used be loaded
prior to InnoDB initialization, so this change of default --early-plugin-load value
introduces an incompatibility for upgrades from 5.7.11 to 5.7.12 or higher.
Administrators who have encrypted InnoDB tablespaces must take explicit action to
ensure continued loading of the keyring plugin: Start the server with an
--early-plugin-load option that names the plugin library file. For additional
information, see Section 6.4.4.1, “Keyring Plugin Installation”.

Important
If you upgrade to MySQL 5.7.2 or later from a version older than 5.7.2, a change to
the mysql.user table requires a special sequence of steps to perform an upgrade using
mysql_upgrade. For details, see Section 2.10.3, “Changes in MySQL 5.7”.

Note
On Windows, you must run mysql_upgrade with administrator privileges. You can do this
by running a Command Prompt as Administrator and running the command. Failure to do so
may result in the upgrade failing to execute correctly.

Caution
You should always back up your current MySQL installation before performing an
upgrade. See Section 7.2, “Database Backup Methods”.

Some upgrade incompatibilities may require special handling before upgrading your
MySQL installation and running mysql_upgrade. See Section 2.10, “Upgrading MySQL”, for
instructions on determining whether any such incompatibilities apply to your
installation and how to handle them.

Use mysql_upgrade like this:

1. Ensure that the server is running.

2. Invoke mysql_upgrade to upgrade the system tables in the mysql schema and check and
repair tables in other schemas:

mysql_upgrade [options]

3. Stop the server and restart it so that any system table changes take effect.

If you have multiple MySQL server instances to upgrade, invoke mysql_upgrade with
connection parameters appropriate for connecting to each of the desired servers. For
example, with servers running on the local host on parts 3306 through 3308, upgrade each
of them by connecting to the appropriate port:

mysql_upgrade --protocol=tcp -P 3306 [other_options]
mysql_upgrade --protocol=tcp -P 3307 [other_options]
mysql_upgrade --protocol=tcp -P 3308 [other_options]

For local host connections on Unix, the --protocol=tcp option forces a connection using
TCP/IP rather than the Unix socket file.

By default, mysql_upgrade runs as the MySQL root user. If the root password is expired
when you run mysql_upgrade, it displays a message telling you that your password is
expired and that mysql_upgrade failed as a result. To correct this, reset the root
password to unexpire it and run mysql_upgrade again. First, connect to the server as root:

$> mysql -u root -p
Enter password: **** <- enter root password here

Reset the password using ALTER USER:

mysql> ALTER USER USER() IDENTIFIED BY 'root-password';

Then exit mysql and run mysql_upgrade again:

$> mysql_upgrade [options]

Note
If you run the server with the disabled_storage_engines system variable set to disable
certain storage engines (for example, MyISAM), mysql_upgrade might fail with an error
like this:

mysql_upgrade: [ERROR] 3161: Storage engine MyISAM is disabled
(Table creation is disallowed).

To handle this, restart the server with disabled_storage_engines disabled. Then you
should be able to run mysql_upgrade successfully. After that, restart the server with
disabled_storage_engines set to its original value.

Unless invoked with the --upgrade-system-tables option, mysql_upgrade processes all tables
in all user schemas as necessary. Table checking might take a long time to complete. Each
table is locked and therefore unavailable to other sessions while it is being processed.
Check and repair operations can be time-consuming, particularly for large tables. Table
checking uses the FOR UPGRADE option of the CHECK TABLE statement. For details about what
this option entails, see Section 13.7.2.2, “CHECK TABLE Statement”.

mysql_upgrade marks all checked and repaired tables with the current MySQL version number.
This ensures that the next time you run mysql_upgrade with the same version of the server,
it can be determined whether there is any need to check or repair a given table again.

mysql_upgrade saves the MySQL version number in a file named mysql_upgrade_info in the
data directory. This is used to quickly check whether all tables have been checked for
this release so that table-checking can be skipped. To ignore this file and perform the
check regardless, use the --force option.

mysql_upgrade checks mysql.user system table rows and, for any row with an empty plugin
column, sets that column to 'mysql_native_password' or 'mysql_old_password' depending on
the hash format of the Password column value.

Support for pre-4.1 password hashing and mysql_old_password has been removed, so
mysql_upgrade sets empty plugin values to 'mysql_native_password' if the credentials use a
hash format compatible with that plugin. Rows with a pre-4.1 password hash must be
upgraded manually. For account upgrade instructions, see Section 6.4.1.3, “Migrating Away
from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

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

Unless invoked with the --skip-sys-schema option, mysql_upgrade installs the sys schema if
it is not installed, and upgrades it to the current version otherwise. An error occurs if
a sys schema exists but has no version view, on the assumption that its absence indicates
a user-created schema:

A sys schema exists with no sys.version view. If
you have a user created sys schema, this must be renamed for the
upgrade to succeed.

To upgrade in this case, remove or rename the existing sys schema first.

mysql_upgrade checks for partitioned InnoDB tables that were created using the generic
partitioning handler and attempts to upgrade them to InnoDB native partitioning. (Bug
#76734, Bug #20727344) You can upgrade such tables individually in the mysql client using
the ALTER TABLE ... UPGRADE PARTITIONING SQL statement.

mysql_upgrade supports the following options, which can be specified on the command line
or in the [mysql_upgrade] and [client] groups of an option file. For information about
option files used by MySQL programs, see Section 4.2.2.2, “Using Option Files”.

• --help Display a short help message and exit.

• --bind-address=ip_address On a computer having multiple network interfaces, use this
option to select which interface to use for connecting to the MySQL server.

• --character-sets-dir=dir_name The directory where character sets are installed. See
Section 10.15, “Character Set Configuration”.

• --compress, -C Compress all information sent between the client and the server if
possible. See Section 4.2.6, “Connection Compression Control”.

• --debug[=debug_options], -# [debug_options] Write a debugging log. A typical
debug_options string is d:t:o,file_name. The default is
d:t:O,/tmp/mysql_upgrade.trace.

• --debug-check Print some debugging information when the program exits.

• --debug-info, -T Print debugging information and memory and CPU usage statistics when
the program exits.

• --default-auth=plugin A hint about which client-side authentication plugin to use. See
Section 6.2.13, “Pluggable Authentication”.

• --default-character-set=charset_name Use charset_name as the default character set.
See Section 10.15, “Character Set Configuration”.

• --defaults-extra-file=file_name Read this option file after the global option file but
(on Unix) before the user option file. If the file does not exist or is otherwise
inaccessible, an error occurs. If file_name is not an absolute path name, it is
interpreted relative to the current directory.