Plesk and Mult-DB database creation questions

I was wondering if I used the Plesk control panel 'Add New Database' tool to create the 4096 databases needed for our blogging community project will using this tool equivalent to executing the following line from the PhpMyAdmin - SQL tab:

CREATE DATABASE comdb001db_000 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

?

Reason I am asking is that through this tool I can't select the Default Character set or anything else, I am just allowed to enter the database and that is it...

Also I was wondering will it make any difference if I assigned the same username and password to all the 4096 databases or is it a must that I create a new username / password for each one of the 4096 databases?

Thanks in advance for your time and efforts

  • Barry
    • DEV MAN’s Mascot

    Not used Plesk so I can't answer that one (though I would suspect the two options are equivalent).

    Also I was wondering will it make any difference if I assigned the same username and password to all the 4096 databases or is it a must that I create a new username / password for each one of the 4096 databases?

    I'd use the same username and password if you can, it will save you a lot of headaches later on.

  • bpilot
    • Site Builder, Child of Zeus

    Hi Barry,

    Thanks for your advice, highly appreciated. I will make sure I have same username and password for all databases.

    On a minor note, even though I've read many times in the forum here that using PhpMyAdmin wasn't a good idea to create the 4096 database, I managed to login to phpMyAdmin as the server admin and create all the 4096 database but not as one shot....I created 200 then another 200 and so on....and it worked :slight_smile:

    I am trying to figure out now a way to grant the same user name and password for all 4096 databases...all in one command

    So do you happen to know how I can do this?

    Thanks in advance for your time

  • Barry
    • DEV MAN’s Mascot

    Assuming your database prefix is "freddy_" and the user you have created is myuser you could try:
    GRANT ALL PRIVILEGES ON freddy\_%.* TO myuser@localhost;

    But wrap the freddy\_% in backward quotes (which i can't put in here because it changes them to code elements) -
    Note, I got this from here (which has the required quotes in):
    http://stackoverflow.com/questions/2469119/grant-permissions-to-a-set-of-databases-matching-a-pattern-in-mysql-5-0/2546870#2546870

  • bpilot
    • Site Builder, Child of Zeus

    Hi Barry,

    Thanks again for your time, I've tried executing the GRANT command but I found that another user was created with the same name but no password

    When I looked at the users table, I found that the Host column next to my user name shows %, while the new one had localhost under the host column...pretty confusing for me :slight_frown:

    I am not sure what I am doing wrong here, I am sure my user name was on localhost as this is what I use in the wp-config.php file of the community!

    Also I noticed when I clicked on the new user name to see what databases are assigned to it, I found only the wildcard table assigned (Ex. freddy\_ ) so is this normal?

    Thanks in advance for your time

  • bpilot
    • Site Builder, Child of Zeus

    I've tried the following from PHPMyAdmin:

    1. Selected Privileges >> Selected the user name >> Clicked on 'Edit privileges' of this selected user

    2. In the (Database-Specific privileges) I added ( comdb001db\_% ) in the box and clicked Go, Selected all privileges and clicked Go again

    3. A new page was generated with the following syntax:

    GRANT ALL PRIVILEGES ON comdb001db\_% . * TO 'my-user-name'@'%';

    I am not sure if this will assign my user name to the 4096 or not....so any ideas if what I did above is correct ?

    Thanks in advance for your time

  • bpilot
    • Site Builder, Child of Zeus

    Another weird issue here, even though I've created the 4096 databases through PhpMyAdmin and I can see them there, the Plesk control panel >> Database server tab show only 1 database!!

    So is this normal?

    On a minor note, in PhpMyAdmin I can see all the 4096 databases as sub-nodes / branches from the main database

  • drmike
    • DEV MAN’s Mascot

    I hate to suggest that but you may want to check with your host about that. I've never used Plesk and I don;t think many folks here have.

    As I read the above, you;ve set it correctly when you assigned access to the mysql user. (If I read that right. Again, I;ve got screaming kids around me.) That's how it;s done in both direct admin and cpanel I believe.

  • fiddyp
    • Site Builder, Child of Zeus

    I have plesk on my server and I'm using multi-db with 256 databases.
    You should be able to use a master user password combo to access all databases on your account.
    it's the same user/pass that you use to log in to plesk.

    I can't see my databases in phpmyadmin if I go via plesk so have to use a desktop app called sqlyog which is actually much easier to use!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.