Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
MYSQL_UPGRADE(1)	     MySQL Database System	      MYSQL_UPGRADE(1)

       mysql_upgrade - check and upgrade MySQL tables

       mysql_upgrade [options]

       mysql_upgrade examines all tables in all	databases for
       incompatibilities with the current version of MySQL Server.
       mysql_upgrade also upgrades the system tables so	that you can take
       advantage of new	privileges or capabilities that	might have been	added.

       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.11.4,
       "Rebuilding or Repairing	Tables or Indexes" for manual table repair

       You should execute mysql_upgrade	each time you upgrade MySQL.

       As of MySQL 5.7.5, mysql_upgrade	communicates directly with the MySQL
       server, sending it the SQL statements required to perform an upgrade.
       Before 5.7.5, mysql_upgrade invokes the mysql and mysqlcheck client
       programs	to perform the required	operations. For	the older
       implementation, if you install MySQL from RPM packages on Linux,	you
       must install the	server and client RPMs.	 mysql_upgrade is included in
       the server RPM but requires the client RPM because the latter includes
       mysqlcheck. (See	Section	2.5.5, "Installing MySQL on Linux Using	RPM
       Packages	from Oracle".)

	   As of MySQL 5.7.12, the default --early-plugin-load value is	empty.
	   To load the keyring_file plugin, you	must use an explicit
	   --early-plugin-load option with a nonempty value.

	   In MySQL 5.7.11, the	default	--early-plugin-load value was the name
	   of the keyring_file plugin library file, so that plugin was loaded
	   by default.	InnoDB tablespace encryption requires the keyring_file
	   plugin to be	loaded prior to	InnoDB initialization, so this change
	   of default 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_file plugin: Start the server	with an
	   --early-plugin-load option that names the plugin library file. For
	   additional information, see Section 7.5.3, "The MySQL Keyring".

	   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, "Changes Affecting Upgrades to	MySQL 5.7".

	   On Windows Server 2008, Vista, and newer, 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

	   You should always back up your current MySQL	installation before
	   performing an upgrade. See Section 8.2, "Database Backup Methods".

	   Some	upgrade	incompatibilities may require special handling before
	   you upgrade your MySQL installation and run mysql_upgrade. See
	   Section 2.11.1, "Upgrading MySQL", for instructions on determining
	   whether any such incompatibilities apply to your installation and
	   how to handle them.

       To use mysql_upgrade, make sure that the	server is running. Then	invoke
       it like this to check and repair	tables and to upgrade the system

	   shell> mysql_upgrade	[options]

       After running mysql_upgrade, stop the server and	restart	it so that any
       changes made to the system tables take effect.

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

	   shell> mysql_upgrade	--protocol=tcp -P 3306 [other_options]
	   shell> mysql_upgrade	--protocol=tcp -P 3307 [other_options]
	   shell> 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.

       mysql_upgrade processes all tables in all databases, which 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.

       For details about what table-checking operations	entail,	see the
       description of the FOR UPGRADE option of	the CHECK TABLE	statement (see
       Section, "CHECK	TABLE Syntax").

       All checked and repaired	tables are marked with the current MySQL
       version number. This ensures that next time you run mysql_upgrade with
       the same	version	of the server, it can tell whether there is any	need
       to check	or repair the table again.

       mysql_upgrade also 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.

       As of MySQL 5.7.2, mysql_upgrade	checks user 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. As of MySQL	5.7.5, support for
       pre-4.1 password	hashing	and mysql_old_password is 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, "Migrating Away from Pre-4.1
       Password	Hashing	and the	mysql_old_password Plugin".

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

       As of MySQL 5.7.7, 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.  mysql_upgrade returns an
       error if	a sys schema exists but	has no version view, on	the assumption
       that its	absence	indicates a user-created schema:

	   Error occurred: 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

       In MySQL	5.7.9 and later, mysql_upgrade checks for partitioned InnoDB
       tables that were	created	using the generic partitioning handler and
       attempts	to upgrade them	to InnoDB native partitioning (used in MySQL
       5.7.6 and later). (Bug #76734, Bug #20727344) Also beginning with MySQL
       5.7.9, you can upgrade such tables individually in the mysql client
       using the ALTER TABLE ... UPGRADE PARTITIONING SQL statement.

       By default, mysql_upgrade runs as the MySQL root	user. If the root
       password	is expired when	you run	mysql_upgrade, you will	see a message
       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:

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

       Reset the password using	the appropriate	SQL statement. As of MySQL
       5.7.6, use ALTER	USER:

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

       Before 5.7.6, use SET PASSWORD:

	   mysql> SET PASSWORD = PASSWORD('root-password');

       Then exit mysql and run mysql_upgrade again:

	   shell> mysql_upgrade	[options]

       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 5.2.6, "Using Option	Files".

       o   --help

	   Display a short help	message	and exit.

       o   --basedir=dir_name

	   The path to the MySQL installation directory. This option was
	   removed in MySQL 5.7.2.

       o   --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. This	option was added in MySQL 5.7.5.

       o   --character-sets-dir=dir_name

	   The directory where character sets are installed. See Section 11.5,
	   "Character Set Configuration".

       o   --compress, -C

	   Compress all	information sent between the client and	the server if
	   both	support	compression. The -C form of this option	was added in
	   MySQL 5.7.5.

       o   --datadir=dir_name

	   The path to the data	directory. This	option was removed in MySQL

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

       o   --debug-check

	   Print some debugging	information when the program exits.

       o   --debug-info, -T

	   Print debugging information and memory and CPU usage	statistics
	   when	the program exits.

       o   --default-auth=plugin

	   A hint about	the client-side	authentication plugin to use. See
	   Section 7.3.8, "Pluggable Authentication".

       o   --default-character-set=charset_name

	   Use charset_name as the default character set. See Section 11.5,
	   "Character Set Configuration".

       o   --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.  file_name is interpreted
	   relative to the current directory if	given as a relative path name
	   rather than a full path name.

       o   --defaults-file=file_name

	   Use only the	given option file. If the file does not	exist or is
	   otherwise inaccessible, an error occurs.  file_name is interpreted
	   relative to the current directory if	given as a relative path name
	   rather than a full path name.

       o   --defaults-group-suffix=str

	   Read	not only the usual option groups, but also groups with the
	   usual names and a suffix of str. For	example, mysql_upgrade
	   normally reads the [client] and [mysql_upgrade] groups. If the
	   --defaults-group-suffix=_other option is given, mysql_upgrade also
	   reads the [client_other] and	[mysql_upgrade_other] groups.

       o   --force

	   Ignore the mysql_upgrade_info file and force	execution even if
	   mysql_upgrade has already been executed for the current version of

       o   --host=host_name, -h	host_name

	   Connect to the MySQL	server on the given host.

       o   --login-path=name

	   Read	options	from the named login path in the .mylogin.cnf login
	   path	file. A	"login path" is	an option group	containing options
	   that	specify	which MySQL server to connect to and which account to
	   authenticate	as. To create or modify	a login	path file, use the
	   mysql_config_editor utility.	See mysql_config_editor(1).

       o   --max-allowed-packet=value

	   The maximum size of the buffer for client/server communication. The
	   default value is 24MB. The minimum and maximum values are 4KB and
	   2GB.	This option was	added in MySQL 5.7.5.

       o   --net-buffer-length=value

	   The initial size of the buffer for client/server communication. The
	   default value is 1MB	a 1KB. The minimum and maximum values are 4KB
	   and 16MB. This option was added in MySQL 5.7.5.

       o   --no-defaults

	   Do not read any option files. If program startup fails due to
	   reading unknown options from	an option file,	--no-defaults can be
	   used	to prevent them	from being read.

	   The exception is that the .mylogin.cnf file,	if it exists, is read
	   in all cases. This permits passwords	to be specified	in a safer way
	   than	on the command line even when --no-defaults is used.
	   (.mylogin.cnf is created by the mysql_config_editor utility.	See

       o   --password[=password], -p[password]

	   The password	to use when connecting to the server. If you use the
	   short option	form (-p), you cannot have a space between the option
	   and the password. If	you omit the password value following the
	   --password or -p option on the command line,	mysql_upgrade prompts
	   for one.

	   Specifying a	password on the	command	line should be considered
	   insecure. See Section, "End-User Guidelines for Password
	   Security". You can use an option file to avoid giving the password
	   on the command line.

       o   --pipe, -W

	   On Windows, connect to the server using a named pipe. This option
	   applies only	if the server supports named-pipe connections.

       o   --plugin-dir=dir_name

	   The directory in which to look for plugins. Specify this option if
	   the --default-auth option is	used to	specify	an authentication
	   plugin but mysql_upgrade does not find it. See Section 7.3.8,
	   "Pluggable Authentication".

       o   --port=port_num, -P port_num

	   The TCP/IP port number to use for the connection.

       o   --print-defaults

	   Print the program name and all options that it gets from option

       o   --protocol={TCP|SOCKET|PIPE|MEMORY}

	   The connection protocol to use for connecting to the	server.	It is
	   useful when the other connection parameters normally	would cause a
	   protocol to be used other than the one you want. For	details	on the
	   permissible values, see Section 5.2.2, "Connecting to the MySQL

       o   --shared-memory-base-name=name

	   On Windows, the shared-memory name to use, for connections made
	   using shared	memory to a local server. The default value is MYSQL.
	   The shared-memory name is case sensitive.

	   The server must be started with the --shared-memory option to
	   enable shared-memory	connections.

       o   --skip-sys-schema

	   As of MySQL 5.7.7, mysql_upgrade installs the sys schema if it is
	   not installed, and upgrades it to the current version otherwise.
	   The --skip-sys-schema option	suppresses this	behavior. This option
	   was added in	MySQL 5.7.7.

       o   --socket=path, -S path

	   For connections to localhost, the Unix socket file to use, or, on
	   Windows, the	name of	the named pipe to use.

       o   --ssl*

	   Options that	begin with --ssl specify whether to connect to the
	   server using	SSL and	indicate where to find SSL keys	and
	   certificates. See Section 7.4.5, "Command Options for Secure

       o   --tls-version=protocol_list

	   The protocols permitted by the client for encrypted connections.
	   The value is	a comma-separated list containing one or more protocol
	   names. The protocols	that can be named for this option depend on
	   the SSL library used	to compile MySQL. For details, see
	   Section 7.4.3, "Secure Connection Protocols and Ciphers".

	   This	option was added in MySQL 5.7.10.

       o   --tmpdir=dir_name, -t dir_name

	   The path name of the	directory to use for creating temporary	files.
	   This	option was removed in MySQL 5.7.5 due to a reimplementation
	   that	no longer uses temporary files.

       o   --upgrade-system-tables, -s

	   Upgrade only	the system tables, do not upgrade data.

       o   --user=user_name, -u	user_name

	   The MySQL user name to use when connecting to the server. The
	   default user	name is	root.

       o   --verbose

	   Verbose mode. Print more information	about what the program does.

       o   --version-check, -k

	   Check the version of	the server to which mysql_upgrade is
	   connecting to verify	that it	is the same as the version for which
	   mysql_upgrade was built. If not, mysql_upgrade exits. This option
	   is enabled by default; to disable the check,	use
	   --skip-version-check. This option was added in MySQL	5.7.2.

       o   --write-binlog

	   By default, binary logging by mysql_upgrade is disabled. Invoke the
	   program with	--write-binlog if you want its actions to be written
	   to the binary log.

	   Running mysql_upgrade is not	recommended with a MySQL Server	that
	   is running with global transaction identifiers enabled (Bug
	   #13833710). This is because enabling	GTIDs means that any updates
	   which mysql_upgrade might need to perform on	system tables using a
	   nontransactional storage engine such	as MyISAM to fail. See
	   Section, "Restrictions on Replication with GTIDs", for
	   more	information.

       Copyright (C) 1997, 2016, Oracle	and/or its affiliates. All rights

       This documentation is free software; you	can redistribute it and/or
       modify it only under the	terms of the GNU General Public	License	as
       published by the	Free Software Foundation; version 2 of the License.

       This documentation is distributed in the	hope that it will be useful,
       but WITHOUT ANY WARRANTY; without even the implied warranty of
       General Public License for more details.

       You should have received	a copy of the GNU General Public License along
       with the	program; if not, write to the Free Software Foundation,	Inc.,
       51 Franklin Street, Fifth Floor,	Boston,	MA 02110-1301 USA or see

       For more	information, please refer to the MySQL Reference Manual, which
       may already be installed	locally	and which is also available online at

       Oracle Corporation (

MySQL 5.7			  09/28/2016		      MYSQL_UPGRADE(1)


Want to link to this manual page? Use this URL:

home | help