YIKES! :) - Thanks Shawn >>> <[EMAIL PROTECTED]> 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: CREATE TABLE oldUsers 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: FLUSH PRIVELEGES; 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 FLUSH PRIVILEGES; 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" <[EMAIL PROTECTED]> wrote on 02/14/2006 12:11:05 PM: > Does anyone have any idea on this one? > > - Thanks > > >>> "Ed Reed" <[EMAIL PROTECTED]> 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 > > >
