mysql_upgrade
mysql_upgrade is a tool that checks and updates your tables to the latest version.
Usage
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-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
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 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
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
- 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 ---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.
