Now that I’m the nominal MySQL DBA for PSU, it became my job to jimmy up the MySQL user privileges so that the new web server could connect. I’m not sure if this is the fastest, most efficient way to do it, but it worked quickly enough:

CREATE TABLE mysql.user_copy SELECT * FROM mysql.user;
DELETE FROM mysql.user_copy WHERE Host NOT LIKE 'OLD_HOST_NAME';
UPDATE mysql.user_copy SET Host = 'NEW_HOST_NAME';
INSERT INTO mysql.user SELECT * FROM mysql.user_copy;
DROP TABLE mysql.user_copy;
 
CREATE TABLE mysql.db_copy SELECT * FROM mysql.db;
DELETE FROM mysql.db_copy WHERE Host NOT LIKE 'OLD_HOST_NAME';
UPDATE mysql.db_copy SET Host = 'NEW_HOST_NAME';
INSERT INTO mysql.db SELECT * FROM mysql.db_copy;
DROP TABLE mysql.db_copy;
 
FLUSH PRIVILEGES;

Simply replace the OLD_HOST_NAME and NEW_HOST_NAME with the appropriate values. Most importantly, I didn’t have to know the passwords for each user to do this. This script simply copied the user info and gave them access from the new server.

Posted August 14, 2008 by Casey Bisson
Categories: Technology. Tags: copy users, duplicate users, mysql, mysql users, privileges, users.

1 Comment(s)

Comments RSS TrackBack Identifier URI

User contributed tags for this post: