How do I upgrade mysql?

Question

When upgrading MySQL, I first create a backup of the database. Then I will uninstall the current version installed, and delete all the files that were left by the installer. Then I install the latest GA version, and restore the created back-up, using the MySQL Administrator.

Is there a better way of doing an upgrade of the MySQL. Because I have to create again all the users that are allowed to connect to the database.

The installation of the MySQL is used in a college enrollment system, a client server system I have developed using VB.Net and MySQL. I can only do the update at night because i know no one is connected to the database.

Answer 1

you can dump the mysql.user and mysql.db tables, which contains all the user info, and reimport that as well, to avoid having to recreate all that. i'd also suggest running repairs on the table after you re-import.

alternatively, you could create a listing of grants:

select concat('show grants for ',quote(user),'@',quote(host),';') from mysql.user

this will output a list of sql statements that you can then run to get specific grant statements.

ultimately though, you'll want to check out your specific version information regarding upgrades, to make sure you've considered any version specific compatibility issues.

here is some information for a 4.1 - 5.x upgrade, for example.

Answer 2

Why is the mysql upgrade so braindead? The fact that you need to back up all your data, install MySQL to a different directory, reimport your data -- just to upgrade is ridiculous. There has to be an easier way.