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 when you upgrade from another major MySQL/MariaDB release, like from MySQL 5.0 to MariaDB 5.1 or MariaDB 5.5 to MariaDB 10.0. It may also be necessary when upgrading a minor version, if something changes in the tables format. 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).
-s, --silentPrint less information.
-S, --socket=nameThe socket file to use for connection.
-t, --tmpdir=nameDirectory for temporary files.
-u, --user=nameUser for login if not current user.
-v, --verboseDisplay more output about the process.
--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 --all-databases --fix-db-names --fix-table-names --write-binlog
mysqlcheck --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

The following differences exists between mysql_upgrade in MariaDB and MySQL (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.
  • More descriptive output.


Comments loading...