|
Plesk - Multiple mysql databases with single user |
|
|
Following info is for webmasters running virtual dedicated servers with Plesk.
PLESK INTERFACE (browser)
First you have to create database in Plesk and assign to it user.
This is business as usual, no problems there. Now create second
database in Plesk. Still good. No problem. Now, how the heck do
you add the same user you used for the first database to the second database. Ok, here the trouble starts.
Illogical thing from Plesk makers is that you can not do it from Plesk interface from browser. You'll have
to get dirty, use shell and do it manually. Once you get through it first time it's easy. But getting through
it first time could be a little pain. Been through it so I hope following will help at least a little.
SHELL
Now you have to make changes from shell directly to mysql tables.
 Using shell access log remotely into account where you want to make the changes.
$ ssh user@domain.com
 To access mysql, you will have to use your Plesk 'admin' username and it's password.
It is the same username and password you are using when you are loging to Plesk from your browser.
Plesk installation removes root user from mysql. You can not log in as root in mysql.
$ mysql -u admin -p
 After you log in, you can see current access to databases by typing:
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','name_of_second_db','same_username_you_used_for_first_db','Y','Y','Y','Y','Y','Y','N','Y','Y','Y');
 But that's not all. Although now 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 and therefor you will be not able to
work with it through phpadmin. 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. This is how to do it:
mysql> use plesk;
6. First you must find out database id Plesk internally assigned to your second database:
mysql> select * from data_bases;
+----+-------------+-------+--------+
| id | name | type | dom_id |
+----+-------------+-------+--------+
| 1 | Mambo | mysql | 1 |
| 2 | Mambo_O | mysql | 5 |
| 3 | peach_MA | mysql | 10 |
| 4 | peach_db | mysql | 10 |
+----+-------------+-------+--------+
In this case database id for peach_db is 4.
7. Second you must find out what account_id is Plesk internally assigning to your database user:
mysql> select * from db_users;
+----+-------------+---------------+-------+
| id | login | account_id | db_id |
+----+-------------+---------------+-------+
| 1 | simplesteps
|
7 |
1 |
| 2 |
oljet
|
20 |
2 |
| 3 |
peach
|
33 |
3 |
+----+-------------+---------------+-------+
So account_id for user peach is in this case 33.
8. Now you have to link this user to second database. You do it by inserting:
mysql> INSERT into db_users VALUES('','peach','33','4');
where 'peach' is username you want to use for second database
'33' is internal plesk id for 'peach' user
and '4' is internal plesk id for your second database
After the changes to db_users table, it should look something like this:
+----+-------------+---------------+-------+
| id | login | account_id | db_id |
+----+-------------+---------------+-------+
| 1 | simplesteps
|
7 |
1 |
| 2 |
oljet
|
20 |
2 |
| 3 |
peach
|
33 |
3 |
| 4 |
peach
|
33 | 4 |
+----+-------------+---------------+-------+
9. Restart mysql.
|
Health