I just RTFM and searched the web, but I still don't know
the correct way to upgrade from 5.0 to 5.5.

For one, the documentation has a lot about incompatibilities
and things to watch out for, but *the actual process* of
upgrading is not described. The only information I got was
that I need to upgrade to 5.1 first.

My questions:
- Is upgrade done by just replacing the binaries and then
running mysql_upgrade, or by mysqldump --all-databases
in the old instance and loading it into a new server?

- If upgrade should be performed by dump/restore, is it
still necessary to run mysql_upgrade?

- Would upgrade by dump/restore work from 5.0 to 5.5?

Thanks,
Laurenz Albe

"Laurenz Albe" <invite (AT) spam (DOT) to.invalid> wrote:

This is the procedure known as "binary upgrade". It is guaranteed
to work from one major release to the next only (i.e. 5.0 -> 5.1
or 5.1 -> 5.5, but not 5.0 -> 5.5). In such a case you stop the MySQL
server, replace the binaries and restart it. Then immediately run
mysql_upgrade. Backup is recommended. There are some edge cases where
this procedure will not work. I.e. ARCHIVE tables.

This is an alternative with the advantage that you can upgrade to
any version and also downgrade (except you use a new feature, i.e.
downgrading from a version with triggers to one without).
The drawback is, that dumping and loading typically takes much
longer time.

Hint: do not use mysqldump --all-databases because this includes
the `mysql` schema which is likely to have changed in the newer
version. Instead you should:

1. dump the MySQL accounts, i.e. with mysqldumpgrants (3rd party)
2. dump your databases with
mysqldump --triggers --routines --events --databases db1 db2 ...
preferrably use mysqldump from the new version for that
3. install clean new MySQL version
4. import MySQL accounts
5. import the dump

Only if you dumped/reloaded the `mysql` schema


XL

Axel Schwenke wrote:
Thanks for your detailed answer.

I think I'd prefer the dump/restore way.

I don't know if I should trust mysqldumpgrants - does it work well
in your experience?

Will dump/restore of the "mysql" schema with subsequent mysql_upgrade
work for upgrading from 5.0 to 5.5 or would I have to upgrade to
5.1 first?

Yours,
Laurenz Albe

"Laurenz Albe" <invite (AT) spam (DOT) to.invalid> wrote:
It uses SHOW GRANTS which has some advantages:

- it's much better human readable than the SQL dump

- it can cope with extra fields that might be introduced
in the newer MySQL version. I.e. "ALL" will mean ALL
and not just "all privileges known by the old version"

- applying saved GRANTs does not mess up existing users

In my DBA days I used such a script to put MySQL accounts into
a version control system. This was much better than a SQL dump.

The tables in the `mysql` schema are normally not a problem and
will only be extended by adding new columns. There was a separate
mysql_fix_privilege_tables.sql script in older versions which is
now integrated in mysql_upgrade. This will work for any version
of the `mysql` schema.


XL

Axel Schwenke wrote:
[helpful replies]

Thanks a lot!

Yours,
Laurenz Albe