How to Check MySQL Tables for Upgrade

When you upgrade MySQL, you should check all tables from all databases for incompatibilities. To do this, you can use the mysql_upgrade program. You can use mysql_upgrade to upgrade the system tables so that you can take advantage of new privileges or capabilities that might have been added.

When you run the utility, mysql_upgrade will mark all checked and repaired tables with the current MySQL version and when you run again the utility with the same version of server, it will know if there is any need to check or repair the table again. If you want to check and repair the tables, use the following commands:

mysqlcheck --check-upgrade --all-databases --auto-repair

Before version 5.1.10, for upgrade was the command mysql_fix_privilege_tables. In version 5.1.7 this command was replaced with mysql_upgrade only for Unix systems, but in version 5.1.10 was introduced mysql_upgrade for all systems.

To use it, start the server and type:
shell> mysql_upgrade .

mysql_upgrade reads options from the command line and from the [mysql_upgrade] group in option files. After you use this command restart your MySQL server.

The options for this command are:

- -?, --help
Display help message.

-b, --basedir=path
The path to the MySQL installation directory

-d, --datadir=path
The path to the data directory.

--default-character-set=name
Set the default character set.

--character-sets-dir=path
Directory where character sets are.

--compress
Use compression in server/client protocol.

-f, --force
Force execution of mysqlcheck even if mysql_upgrade has already been executed for the current version of MySQL.

-h, --host=name
Connect to host.

-p, --password[=name]
Password to use when connecting to server.

-W, --pipe
Use named pipes to connect to server.

-P, --port=name
Port number to use for connection.

--protocol=name
The protocol of connection (tcp,socket,pipe,memory).

--shared-memory-base-name=name
Base name of shared memory.

-S, --socket=name
Socket file to use for connection.

-u, --user=name
User for login if not current user.

--ssl
Enable SSL for connection (automatically enabled with other flags). Disable with --skip-ssl.

--ssl-ca=name
CA file in PEM format (check OpenSSL docs, implies --ssl).

--ssl-capath=name
CA directory (check OpenSSL docs, implies --ssl).

--ssl-cert=name
X509 cert in PEM format (implies --ssl).

--ssl-cipher=name
SSL cipher to use (implies --ssl).

--ssl-key=name
X509 key in PEM format (implies --ssl).

--ssl-verify-server-cert
Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default.

-v, --verbose
Display more output about the process