mysql_upgrade - Check and Upgrade MySQL Tables

MySQLMySQLi Database

Let us understand mysql_upgrade program −


  • Every time MySQL is upgraded, the user should execute mysql_upgrade, that looks for incompatibilities with the upgraded MySQL server.

  • It upgrades the system tables in the mysql schema so that the user can take advantage of new privileges or capabilities that could have been added after the upgrade.

  • It basically upgrades the Performance Schema and sys schema.

  • It also examines user schemas.

  • If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if some problems are found, attempts a table repair operation

The mysql_upgrade can be used as shown below −

  • Ensure that the server is currently running.

  • Invoke mysql_upgrade to upgrade the system tables in the mysql schema.

Check and repair tables in other schemas using the below command −

shell> mysql_upgrade [options]

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

If there are multiple MySQL server instances that need to be upgraded, then mysql_upgrade can be invoked with connection parameters that are appropriate to connect to each of the desired servers. Let us take an example −

With servers running on the local host on ports 3306 through 3308, upgrade each of the servers by connecting to the appropriate port. This can be done using the below commands −

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 will force a connection using TCP/IP instead of the Unix socket file.

Updated on 10-Mar-2021 12:43:31