#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    How to add MySQL User to over 500 Databases


    Hi.

    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..
  2. #2
  3. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,754
    Rep Power
    9397
    Do you mean the user should have access to every database, or just that there are 500 of them?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    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
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    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?
  8. #5
  9. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,754
    Rep Power
    9397
    If you were to create a user that had access to all the databases, not explicitly those 500, then you could
    Code:
    GRANT whatever ON *.* TO user
    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:
    Code:
    INSERT INTO mysql.db (Host, Db, User, Select_Priv, Insert_Priv, Update_Priv, Delete_priv)
    SELECT "%", SCHEMA_NAME, "the user", "Y", "Y", "Y", "Y"
    FROM information_schema.SCHEMATA
    WHERE conditions to pick the databases
    followed by
    Code:
    FLUSH PRIVILEGES

IMN logo majestic logo threadwatch logo seochat tools logo