From: Shawn Green (MySQL) (shawn.l.greenoracle.com)
Date: Fri Dec 03 2010 - 06:48:53 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

On 12/3/2010 03:40, Machiel Richards wrote:
> I checked now and saw that they have already attempted a restore
> previously and hence the original table was dropped and recreated.
>
> I found some links on the internet stating that after restoring the
> dump file to the new version, the proc table should be dumped using the
> new version and reloaded.
> ... snip ...
> Any ideas?

The easiest way to migrate between major versions is to dump logical
contents of the system data tables (the entire MySQL database)
separately from the rest of the data. It changes very slowly so there is
no risk of being out of sync with the rest of the data.

For example, instead of dumping the user tables (user, db, privs-table,
etc...) and restoring them as raw data on the new system, you should get
the SHOW GRANTS reports for each of your users.

http://dev.mysql.com/doc/refman/5.1/en/show-grants.html

Instead of dumping the raw data in the `proc` table, use the --routines
option of mysqldump instead to write out the stored procedures as SQL
statements.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_routines

Did you remember to dump your triggers (which are database-specific)
using the --events option so that they were recreated with your
production data tables?

The tables in the `mysql` database can and usually do change sizes and
definitions between major versions. The utility mysql_upgrade will
modify the table definitions to match the current version after you
restore your old-version tables but if you want to try to avoid that
step, you can use my techniques.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]