MySQL Plesk

PLESK: How to create multiple Mysql DB’s with a Single User

Login with ssh

Login to mysql

# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

use the mysql db.

mysql> use mysql;
mysql> SELECT* FROM db;

To add same user to another database, you have to insert that user into db table and give him same privileges he already has for his existing database.

mysql> INSERT INTO db VALUES(‘localhost’,’second_db’,’same_username_you_used_for_first_db’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);

The second database is accessible to the same user as the first database, from command line but it will not appear yet in your Plesk admin panel. In order to have access to second database from Plesk interface you will have to link your database user to this second database in plesk table:


mysql> use psa;

First you must find out the database id Plesk internally assigned to your second database which we do with the following:


mysql> SELECT * FROM data_bases;

    +—-+—————————+——-+————-+————————+———————-+
    | id | name                             | type  | dom_id              | db_server_id    | default_user_id    |
    +—-+—————————+——-+————-+——————-+—————————+
    |  1 | first_db                         | mysql |      1              |            1 |               1       |
    |  2 | second_db                        | mysql |      1              |            1 |               0       |
    |  3 | test                             | mysql |      1              |            1 |               0       |
    |  4 | wordpress                        | mysql |      1              |            1 |               3       |
    +—-+—————————+——-+————-+——————+—————————–+

Note: We are going to get second_db to use first_db’s user so we are going to need the id “2”. Now we have to find out what id plesk has signed internally to the Database user:


mysql> SELECT * FROM db_users;

    +—-+—————–+—————-+———+
    | id | login          | account_id    | db_id     |
    +—-+—————–+—————-+———+
    |  1 | first_db_user  |       3       |    1      |
    |  2 | worpress_user  |       4       |    4      |
    +—-+—————–+—————-+———+

Note: We are going to use first_db’s account ID above which is “3”. Now you have to link the user to the second database using the command below


mysql> INSERT INTO db_users VALUES(”,’first_db_user’,’3′,’2′);

Note: For people who do not like to copy an paste those are single quotes at the start.
You should run the select db_users command again to check for your changes then exit mysql and restart MySQL:


# service mysqld restart