There are 2 kind of upgrade is possible in MySQL.
- Major version upgrade (i.e MySQL 4.1 to MySQL 5.0)
- Minor version upgrade (i.e MySQL 5.0.45 to MySQL 5.0.47)
In the Major version upgrade, people are generally using very common technique which is, taking backup by mysqldump from MySQL 4.1 and reload into MySQL 5.0. This is also recommended by MySQL Guru’s specially when data is sensitive and contains different character-sets (Here, its recommended to convert data from normal text to binary and vice versa) but we can also use mysql_upgrade script when data is not that much important or possible to re-generate easily by the row data.
For the minor version upgrade, mysql_upgrade script is the best way to upgrade the mysql dbs.
mysql_upgrade is generally checking all tables of all databases for incompatibilities with the current version of MySQL Server. It also upgrades the system tables / privilege tables so that you can use new privileges or capabilities which are added in current MySQL server version by running following commands.
mysql_upgrade does not upgrade the contents of the help tables so it might be possible that in help of mysql commands you will not get those options which are already supported by current version.
mysqlcheck –all-databases –check-upgrade –auto-repair
mysql < fix_priv_tables
mysqlcheck –all-databases –check-upgrade –auto-repair –fix-db-names –fix-table-names
fix_priv_tables is the script which will generated internally by mysql_upgrade. This script will contain sql command for upgrading mysql DB.
From the MySQL 5.1, last command has been added in mysql_upgrade with “-fix-db-names –fix-table-names” options. It will useful for re-encode database or table names that contain non-alphanumeric characters.
We can use mysql_upgrade [options] on the shell prompt like
shell> mysql_upgrade -u <mysqluser> -p <pass> –socket=/var/lib/mysql/mysql.sock -v
You can get the all [options] by running mysql_upgrade –help command.
After completing upgrade process, all checked and repaired tables will be marked with current mysql version number. We can also get the number in mysql_upgrade_info file which will be created in data dir by default.
Due to mysql_upgrade_info file, we can face one issue while doing minor version upgrade. Sometime mysql_upgrade can give msg “The installation of MySQL is already upgraded” while running it but actually DB is not upgraded. In this situation, we can use –force option with mysql_upgrade which will ignore the above statement and will do check/repair things.