MYSQL Migration - Best Method

If the version of the new server is bigger than the version from which you try to move
then copyiing the data directory will not work.

Have you tried to use MySQL Administrator?
It has backup/restore functionality via GUI.
http://www.mysql.com/products/tools/administrator/

Cheers,
za-k/

I have tried, but it doesn't move users and permissions, only schemas. It's a slightly newer version, but I can downgrade it if that's a problem. Is there not a tool out there that moves the whole thing?

Be sure you connect with a user that has ALL permision to ALL databases when you backup!

Ok, here are some pictures....
Also, pay attention if you move from an older MySQL version to a new one:
http://dev.mysql.com/doc/refman/5.0/en/upgrade.html
>>>>Some releases of MySQL introduce incompatible changes to tables. (Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.) Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.  To avoid problems due to such changes, after you upgrade to a new version of MySQL, you should run mysql_upgrade to check your tables (and repair them if necessary), and to update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 4.4.8, mysql_upgrade  Check Tables for MySQL Upgrade. <<<<
Check mysql_upgrade here: http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html

Cheers,
za-k/

MySQLAdministrator-Backup1.JPG
MySQLAdministrator-Backup2.JPG
MySQLAdministrator-Restore.JPG

I just downgraded the server so that it matches the version on the old server exactly. I've backed up and restored almost like you indicate. I can't do flush read locks while we are live, it will have to be before the actual transfer. I still get this error:

Error while executing this query:CREATE DATABASE /*!32312 IF NOT EXISTS*/ information_schema;
The server has returned this error message:Access denied for user 'root'@'localhost' to database 'information_schema'
MySQL Error.

I am using the original root account, with no modifications. Will this method transfer users and permissions?

When I selecte Complete Backup, like you recommended, it automatically backs it up. I can do a restore successfully without it, but what about users/permissions?

Well, when I tried to do everything but the information_schema, the restore failed on part of the mysql.users table. I then tried to restore only the pride (our primary) schema, which failed stating something about a missing user entry. I then tried to restore only the mysql schema, which worked fine. Then tried to pride schema and now I get this:

Error while executing this query:/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
The server has returned this error message:MySQL server has gone away
Restore Thread cannot connect to MySQL

Why is this so darn difficult?

Ok, I quit the Administrator for a while, and tried again, and this time I got this message:

Error while executing this query:CREATE ALGORITHM=UNDEFINED DEFINER=`keithdavis`@`%` SQL SECURITY DEFINER VIEW `pride`.`no_diag_codes` AS (select `c`.`skey` AS `skey`,`c`.`case_number` AS `case_number`,`c`.`episode` AS `episode`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name` from (`pride`.`rm5_case5` `c` join `pride`.`diagnosis_codes` `d`) where ((concat(ifnull(`d`.`diag_1`,_latin1''),ifnull(`d`.`diag_2`,_latin1''),ifnull(`d`.`diag_3`,_latin1''),ifnull(`d`.`diag_4`,_latin1''),ifnull(`d`.`diag_5`,_latin1''),ifnull(`d`.`diag_6`,_latin1''),ifnull(`d`.`diag_7`,_latin1''),ifnull(`d`.`diag_8`,_latin1'')) = _latin1'') and (`d`.`case5_skey` = `c`.`skey`) and (`c`.`cls_date` > _latin1'2003-01-01') and (left(`c`.`sprog`,1) in (_latin1'C',_latin1'4',_latin1'5',_latin1'6')) and (`c`.`episode` <> _latin1'008')));
The server has returned this error message:Table 'pride.rm5_case5' doesn't exist
MySQL Error.

I know what the problem is. This code is trying to create a view for a table that doesn't yet exist. Why doesn't it do the views last? Is that a bug or what?

Ok, I got it to work. The problem is this is a restore to a new empty database. I had to remove the views from the restore, and do the rest first. I also had to increase the max packet size:

max_allowed_packet=128M

Cool that you got it working!
I was also playing with MySQL Administrator trying to do backups and restores and
I couldn't belive how fragile the entire process is. I thought they had more mature tools :)

Cheers,
za-k/