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
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-defaults||Print the program argument list and exit.|
|--no-defaults||Don'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:
|-?, --help||Display this help message and exit.|
|-#, --debug[=name]||Output debug log.|
|--debug-check||Check memory and open file usage at exit.|
|-T, --debug-info||Print some debug info at exit.|
|-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. If password is not given, it's solicited on the tty.|
|-P, --port=name||Port 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=name||The protocol to use for connection (tcp, socket, pipe, memory).|
|--silent||Print less information.|
|-S, --socket=name||The socket file to use for connection.|
|-t, --tmpdir=name||Directory for temporary files.|
|-s, --upgrade-system-tables||Only upgrade the system tables in the mysql database. Tables in other databases are not checked or touched.|
|-u, --user=name||User for login if not current user.|
|-v, --verbose||Display 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-check||Run 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-binlog||All 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
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 mysql_fix_privilege_tables 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
mysql_fix_privilege_tables script is not actually called; it's included as part of
If you have a problem with mysql_upgrade, try run it in very verbose mode:
mysql_upgrade --verbose --verbose other-options
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
- 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
- Has not happened after MariaDB 5.1
- If some of the tables are using an index for which we have changed sort order.
- Has not happened after MariaDB 5.5
If you are 100% sure this applies to you, you can just run
mysql_upgrade with the
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.