mysql_upgrade is a tool that checks and updates your tables to the latest version.


mysql_upgrade [--force] [--user=# --password --host=hostname --port=# --socket=#
--protocol=tcp|socket|pipe|memory --verbose] OTHER_OPTIONS]

You should run mysql_upgrade after upgrading from one major MySQL/MariaDB release to another, such as from MySQL 5.0 to MariaDB 5.1 or MariaDB 5.5 to MariaDB 10.0. It is also recommended that you run mysql_upgrade after upgrading from a minor version, like MariaDB 5.5.40 to MariaDB 5.5.41, or even after a direct "horizontal" migration from MySQL 5.5.40 to MariaDB 5.5.40. If calling mysql_upgrade was not necessary, it does nothing.

On Windows Server 2008 or newer, mysql_upgrade needs to be run with administrator privileges.

It is recommended to make a backup of all the databases before running mysql_upgrade.

The following groups are read from the my.cnf files: [mysql_upgrade] and [client].

The following options to handle option files may be given as the first argument:

--print-defaultsPrint the program argument list and exit.
--no-defaultsDon't read default options from any option file.
--defaults-file=#Only read default options from the given file #.
--defaults-extra-file=#Read this file after the global files are read.

Main arguments are:

-?, --helpDisplay this help message and exit.
-#, --debug[=name]Output debug log.
--debug-checkCheck memory and open file usage at exit.
-T, --debug-infoPrint some debug info at exit.
-f, --forceForce execution of mysqlcheck even if mysql_upgrade has already been executed for the current version of MySQL.
-h, --host=nameConnect to host.
-p, --password[=name]Password to use when connecting to server. If password is not given, it's solicited on the tty.
-P, --port=namePort number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
--protocol=nameThe protocol to use for connection (tcp, socket, pipe, memory).
--silentPrint less information.
-S, --socket=nameThe socket file to use for connection.
-t, --tmpdir=nameDirectory for temporary files.
-s, --upgrade-system-tablesOnly upgrade the system tables in the mysql database. Tables in other databases are not checked or touched.
-u, --user=nameUser for login if not current user.
-v, --verboseDisplay more output about the process, using it twice will print connection arguments; using it 3 times will print out all CHECK, RENAME and ALTER TABLE commands used during the check phase; using it 4 times will also write out all mysqlcheck commands used (added in MariaDB 10.0.14).
-k, --version-checkRun this program only if its 'server version' matches the version of the server to which it's connecting check. Note: the 'server version' of the program is the version of the MariaDB server with which it was built/distributed. (Defaults to on; use --skip-version-check to disable.)
--write-binlogAll commands including mysqlcheck are written to the binary log. Enabled by default ; Use --skip-write-binlog when commands should not be sent to replication slaves.

"mysql_upgrade" is mainly a framework to call mysqlcheck. mysql_upgrade works by doing the following operations:

# Find out path to datadir
echo "show show variables like 'datadir'" | mysql
mysqlcheck --no-defaults --check-upgrade --auto-repair --databases mysql
mysqlcheck --no-defaults --all-databases --fix-db-names --fix-table-names --write-binlog
mysqlcheck --no-defaults --check-upgrade --all-databases --auto-repair --write-binlog

The connect options given to mysql_upgrade are passed along to mysqlcheck and mysql.

The mysql_fix_privilege_tables script is not actually called; it's included as part of mysql_upgrade.

If you have a problem with mysql_upgrade, try run it in very verbose mode:

mysql_upgrade --verbose --verbose other-options

Differences between mysql_upgrade in MariaDB and MySQL

This is as of MariaDB 5.1.50:

  • MariaDB will convert long table names properly.
  • MariaDB will convert InnoDB tables (no need to do a dump/restore or ALTER TABLE).
  • MariaDB will convert old archive tables to the new 5.1 format.
  • "mysql_upgrade --verbose" will run "mysqlcheck --verbose" so that you get more information of what is happening. Running with 3 times --verbose will in MariaDB 10.0 print out all CHECK, RENAME and ALTER TABLE commands executed.
  • More descriptive output.

Speeding up mysql_upgrade

- If you are sure that all your tables are up to date with the current version, then you can run mysql_upgrade ---upgrade-system-tables, which will only fix your system tables in the mysql database to be compatible with the latest version.

The main reason to run mysql_upgrade on all your tables is to allow it to check that:

  • There has not been any change in table formats between versions
  • If some of the tables are using an index for which we have changed sort order.

If you are 100% sure this applies to you, you can just run mysql_upgrade with the ---upgrade-system-tables option.

Symptoms of not having run mysql_upgrade when it was needed

  • Errors in the error log that some system tables doesn't have all needed columns.
  • Updates or searches on may not find the record.
  • CHECKSUM TABLE may report the wrong checksum for MyISAM or Aria tables.

To fix issues like this, run mysql_upgrade, mysqlcheck, CHECK TABLE and if needed REPAIR TABLE on the wrong table.

See also


Comments loading...