add username to db

I use hostgator and have unlimited sql dbs... on my first project I ended up manually adding my username to each of the 250 or so databases... that sucked lol

My other project im planning I'll be adding multi db again... so my question is, is there a command when adding the databases or after to add my username to all of the dbs as opposed to adding it 250+ times.

  • dapadoo

    @LCDist

    A few weeks back I was trying to do what your wanting to do. I have a VPS server with hostgator and access to the root. Aaron, a lead developer here on WPMU DEV posted the following information for me and I played around with it and finally gave up. It's a bit more complicated then I had time to learn some thing new. The process to do what your wanting to do goes beyond support for the for the plugin. It goes into detail on root for servers and working with mysql not the plugin its self. If you have any success with learning how to setting up user name to the databases using a script please let me know. I'll add this thread to my favorite and check it daily.

    Good Luck!

    Do you have a root user for mysql, one with permissions for all databases? If so you can use sql:
    http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

    Something like
    GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost';

    Of course if you run other dbs than wpmu on that db server you should limit the user to just wpmu tables for security, like:

    GRANT ALL PRIVILEGES ON wpmudb1.* TO 'monty'@'localhost';
    GRANT ALL PRIVILEGES ON wpmudb2.* TO 'monty'@'localhost';
    GRANT ALL PRIVILEGES ON wpmudb3.* TO 'monty'@'localhost';

    etc.

  • eddieburroughs

    I can't seem to figure this out myself.

    I have created these two scripts to plug into phpmyadmin under the SQL tab...

    CREATE USER 'user'@'localhost' IDENTIFIED BY 'mypass';
    GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost'
    GRANT ALL ON *.* TO 'user'@'localhost';
    GRANT SELECT, INSERT ON *.* TO 'user'@'localhost';

    and this one

    CREATE USER 'user'@'localhost' IDENTIFIED BY 'mypass';
    GRANT ALL ON mydb_.* TO 'user'@'localhost';
    GRANT SELECT, INSERT ON mydb_.* TO 'user'@'localhost';

    neither of these seems to be working I get

    #1227 - Access denied; you need the CREATE USER privilege for this operation

    I am not keen enough on sql to figure this out.

    Seem like there should be a simple script you can run in phpmyadmin that would allow you to add one "master" user to all the databases we just created.

    I know you all are busy so when you get a chance please shed some light my way.

    Thanks
    Eddie

  • embc

    Hi LCDist,

    If you have root access, it should be easy to do. Bear in mind that I'm explaining how to deal with the creation of the databases, so be sure to follow the instructions for the plugin very accurately as explained at https://premium.wpmudev.org/project/multi-db/installation/

    First, go to https://premium.wpmudev.org/db-tools/db_sql.php and select 256 as well as enter your table_prefix as defined in wp_config.php. Select the code and paste it into a new plain text file (if you're using Windows, Notepad++ comes very handy here)

    Do not forget to add the global database and, if needed, the VIP database.

    Save that plain text file onto the VPS and give it a name that you can identify (i.e. multi_db.sql)

    Now comes the easy part. I have marked in bold the commands that you enter. The rest is the output from the console, for your guidance (which is no output, really). It may change depending on your operating system and the version of MySQL that you use.

    root@yourvpsserver:~# mysql -uroot -pyourrootpassword < multi_db.sql
    root@yourvpsserver:~#

    That's all you need to do to create the databases.

  • Barry

    What control panel do you have with hostgator? If you can't add a user using phpmyadmin then there will be a control panel method available. Have you tried searching the hostgator help system?

    If you are running with a control panel then you'll have to either do the operation 256 times, or put in a support ticket to your host to do it for you. If you aren't and are running a complete vps then the suggestions above will work.

  • LCDist

    ok that didnt work as hoped either I k ept adding databases but no users.... so I found a solution... not as nice a solution as I woulda hoped but oh well... lifes not perfect

    on the tool in multidb I went to dblist and after I had went through the steps in the instructions I used it one more time... this time in the field for name I went:

    grant all on !NAME\

    That for some reason made all the dbnames look like this:

    grant all on !NAME\\ so I went and did a find a replace of \\ with \ and just had it fix em all so that was easy enough but then I had to add this to the end of every line:

    !.* to!USERNAME!@!localhost!; so that unfortunatly meant hitting ctrl v enter 256 times but ya its the best I could come up with there lol

    anyway when that was done I had lines that looked like this for every database I had previously created:

    grant all on !NAME\_00!.* to !USERNAME!@!localhost!;
    grant all on !NAME\_02!.* to !USERNAME!@!localhost!;

    etc etc etc

    anyway that did it for me... so the maker of that nifty tool we all use with multi db may wanan consider making another tab on it that does the above... would be a nice lil resource to have assuming of course the person can grant privlages and whatnot I did try it with an account that had root access so next tiem I try it Ill try it with an account that has just normal access

    NOTE: every time I had an apostrophe this system thought I was typing in code so where theres a ! assume its an apostrophe

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.