List:General Discussion« Previous MessageNext Message »
From:Ed Reed Date:February 15 2006 7:03pm
Subject:Re: Migration toolkit
View as plain text  
- Thanks Shawn

>>> <SGreen@stripped> 2/14/06 10:08:14 AM >>>

 I haven't tried the migration toolkit so I don't know what it can or cannot do. I am
assuming that you have hand-transferred a few users from your old system to the new one. 

 What I can suggest is that you generate two sets of data. The first is a list of your
users, their hostnames, and their password hashes from your old server: 

SELECT user, host, password 
FROM mysql.user; 

You can use the utility mysqldump to convert oldUsers table to a SQL script. Save this
dump into a text file (oldusers.sql) for later. 

Next you need to run a bunch of SHOW GRANTS for statements.  A script (pick your favorite
scripting language for this) can crawl through oldUsers (just created) and capture the
results of 

SHOW GRANTS FOR 'user'@'host' 

for every user in the oldUsers table. Save the results into another text file
(oldgrants.sql). These will be the SQL statements you will need to restore permissions to
your current users in your new database. 

Now comes the fun part: I prefer to enter the CLI and navigate to the correct database by
hand before executing scripts like these (I have seen many people just do this from the
command line but I would rather be sure). On your new server, start your mysql CLI and
navigate to the mysql database. Once there, execute the script that generates the oldUsers
table. The sequence should look something like this: 

mysql -u yourlogin -p mysql 
<provide your password> 
mysql> source full_path_to_oldusers.sql 

That should create a table of all of your user accounts in the table oldUsers in the mysql
database of your new server. Bulk insert them into your users table like this 

INSERT IGNORE user (user, host, password) SELECT user, host, password FROM oldUsers; 

And refresh the permissions cache: 


Now you are ready to re-apply privileges. Assuming that you correctly captured the GRANT
statements from your "SHOW GRANTS for" script, you should be able to say. 

mysql> source full_path_to_oldgrants.sql 

and do one last 


Your old accounts should now exist on your new server with their old permissions restored.
Sorry but you asked for "any" ideas... ;-) 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

"Ed Reed" <EReed@stripped> wrote on 02/14/2006 12:11:05 PM:

> Does anyone have any idea on this one?
> - Thanks
> >>> "Ed Reed" <EReed@stripped> 2/10/06 3:09 PM >>>
> I'm trying to use the Migration Toolkit to migrate a 4.1 system to a
> 5.0 system. It doesn't appear that it can successfully migrate my 
> usernames and privileges. Is it supposed to be able to and is there 
> anything special I need to do to make it happen?
> - Thanks

Migration toolkitEd Reed11 Feb
Re: Migration toolkitEd Reed14 Feb
  • Re: Migration toolkitSGreen14 Feb
Re: Migration toolkitEd Reed15 Feb