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.
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.
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 |+—-+—————————+——-+————-+——————+—————————–+
mysql>SELECT * FROM db_users;
+—-+—————–+—————-+———+| id | login | account_id | db_id |+—-+—————–+—————-+———+| 1 | first_db_user | 3 | 1 || 2 | drupalTest | 4 | 4 |+—-+—————–+—————-+———+
INSERT INTO db_users VALUES(”,’first_db_user’,’3′,’2′);