August 22nd, 2013, 01:17 PM
How to add MySQL User to over 500 Databases
I am moving a client wordpresses from one server to another. Over 500 wordpress installs. I have the files moved over and imported all the databases which took for ever. Now I need to add the MySQL User to all the databases. I am using the same user for every database. Is there a query I can run for this? I have the user created I just need to get that user added to every database with full permissions.
Thanks for any help..
August 22nd, 2013, 01:52 PM
Do you mean the user should have access to every database, or just that there are 500 of them?
August 22nd, 2013, 02:36 PM
I have over 500 database (one for each wordpress) I need to add the MySQL user and password with full privileges to each of the 500 databases.
In phpmyadmin I have to select the database then add user to that database and set privilege to full one by one, its taking forever. I am hoping for a script or query I can run?
The old server would not let me do a mysqldump so I had to do a full site backup through the cpanel to get all my databases. I have all the databases loaded on the new server I just need to add the user and set permissions.
Thanks for the reply
August 22nd, 2013, 03:16 PM
I think another way to say it is: "Allow multiple MySQL databases to be accessed by only one user". That is what I am trying to do. Is there some way I can do this with a query?
August 22nd, 2013, 04:12 PM
If you were to create a user that had access to all the databases, not explicitly those 500, then you could
For 500 you'll want something automated. There's no way I can think of to do multiple grants in one statement, but you could create the user privileges manually via the mysql.db and mysql.tables_priv tables. Combining that with information_schema you could do an INSERT...SELECT:
GRANT whatever ON *.* TO user
INSERT INTO mysql.db (Host, Db, User, Select_Priv, Insert_Priv, Update_Priv, Delete_priv)
SELECT "%", SCHEMA_NAME, "the user", "Y", "Y", "Y", "Y"
WHERE conditions to pick the databases