MYSQL Migration - Best Method

What is the best way to move a MYSQL database to a new server? I've tried several methods, but none without issues. Is there a way to move everything (schemas, users, and permissions)?

Windows Server 2003 R2 SP2
MYSQL 5.051a-community-nt
LVL 2
laurin1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hernst42Commented:
shutdown original server, copy all files from the data-Directory to the new servers data-directory and start the new server.

or make a dump with mysqldump -A > backup.sql
and recreate the new database with
mysql
\. backup.sql

restart new mysql server
0
adrpoCommented:

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/
0
laurin1Author Commented:
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?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

adrpoCommented:
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
0
laurin1Author Commented:
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?

0
adrpoCommented:

DO NOT BACKUP/RESTORE THE information_schema database. Is not needed!
It will be created automatically when you restore the other databases.

Cheers,
za-k/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
laurin1Author Commented:
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?
0
adrpoCommented:

The user and their permissions are in table: mysql.user in the mysql database.
If that gets restored ok you should have EVERYTHING with regards to users and permissions.
The information schema is just there for compatibility with SQL standard.

Cheers,
za-k/
0
laurin1Author Commented:
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?
0
laurin1Author Commented:
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?
0
adrpoCommented:

I shouldn't be this difficult. And the error you're getting is really BAD:
"MySQL server has gone away". This means the SQL server crashed.

Let's try again.

On the old server:
1. Run MySQL Administrator
2. Backup mysql database in mysql.sql
3. Backup pride database in pride.sql

ON THE NEW SERVER:
0. Something fishy has happened with your server that's why you get
    that nasty "MySQL server has gone away"
1. Uninstall MySQL server (this will not delete the "data" directory)
2. Delete the data directory (C:\Program Files\MySQL\data)
3. Install MySQL
4. Connect to the new server with MySQL Administrator
5. Restore mysql database from mysql.sql
6. Restore pride database from pride.sql

Hopefully like this will work.
It should really work also with backup everything besides information_schema.
I read more and you don't need the information_schema as it only contains views
into the mysql.* tables.

Cheers,
za-k/
0
adrpoCommented:
Hi again,

When you restore you might try to check "Create database(s) if they don't exist".
http://dev.mysql.com/doc/administrator/en/mysql-administrator-restore-general.html

Cheers,
za-k/
0
laurin1Author Commented:
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
0
adrpoCommented:

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/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.