First you have to create your database in Plesk and assign to it user. This is simple enough. Now create second database in Plesk. No problem. Now, how to add the same user you used for the first database to the second database. Here is where the trouble starts.

You’ll have to get dirty with ssh and do it manually. Once you get through it the first time it gets easier. I have done this a lot with Drupal migrations. Lets get started.

Using shell access log remotely into account where you want to make the changes.

ssh user@host.com

To access mysql, you will have to use your Plesk ‘admin’ username and it’s password. Plesk installation removes root user from mysql, so you can not log in as root in mysql.

mysql -u admin -p

Once you log into the mysql server you should be able to access the DB’s, so lets use the mysql db.

use 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.

INSERT INTO db VALUES(‘localhost’,’name_of_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’);

There is the first part finished, although now the second database is accessible to the same user as the first database, from command line, 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 | civicrm                          | mysql |      1              |            1 |               0       |
|  3 | g2gallery                      | mysql |      1              |            1 |               0        |
|  4 | drupal                            | mysql |      1              |            1 |               3      |
+—-+—————————+——-+————-+——————+—————————–+
Note: We are going to get civicrm 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 | drupalTest      |       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
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:
/etc/init.d/mysqld restart

Sorry, comments are closed.