Copying MySQL Usernames and Database Priveleges

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.

2 thoughts on “Copying MySQL Usernames and Database Priveleges

  1. Excellent post – I was wondering if there was an easier way to do this compared to my previous methods- thanks !

  2. Thank you for this little gem! I just wrote this into a little sql script. I just synced a database dump.
    For enabling all my client accounts in a new network, nothing could be easier than:

    mysql -u root -p < UpgradeUserHosts.sql

    You saved me a lot of time.

    Thanks again!

Comments are closed.