[BlueOnyx:25976] Re: Adding existing mysql database/users

[Michael Stauber]

Hi Darren,

> In mysql I already have many users and databases, entirely outside the scope of the gui, none of which were created using the gui.
> I now want to bring these existing users and databases into the gui, but I want to avoid the painful task of enabling mysql on each vsite, noting the created mysql username/db/password, moving the database from its old name to its new name, and updating configuration files for the websites.
> 
> If I enable mysql in the vsite, It won't let me enter an existing db/username/password as it says they already exist, so I don't know how to achieve what I want.
> 
> So what I want is a way to force the gui to accept the existing mysql db/username/password that I give it without trying to create the user or database itself.


Due to the many variables (which DB belongs to which Vsite?) this can't 
be scripted easily without knowing more details.

But you can use "cceclient" to accomplish this from the command line.

Let me walk you through an example and for that we establish some 
parameters:

The Vsite in question is "site1". It has MariaDB/MySQL enabled in the 
GUI. The GUI created DB is called 'vsite_M82fXCH_db' and we allow more 
than 1 DB.

Now you do have additional DBs created for this Vsite *outside* of the 
GUI and want to add them to the GUI. These databases are named 
'site1_text_db' and 'site1_images_db' im my example below.

As "root" and on the shell you fire up cceclient:

[root at 5211r ~]# /usr/sausalito/bin/cceclient
100 CSCP/0.99
200 READY

In there you then type:

FIND Vsite name = "site1"

Example:

[root at 5211r ~]# /usr/sausalito/bin/cceclient
100 CSCP/0.99
200 READY
FIND Vsite name = "site1"
104 OBJECT 34
201 OK

Note the line that starts with 104? That is the result. So you know know 
that Vsite "site1" is the CODB Object 34.

We now want to know what NameSpaces that Object has:

[root at 5211r ~]# /usr/sausalito/bin/cceclient
100 CSCP/0.99
200 READY
FIND Vsite name = "site1"
104 OBJECT 34
201 OK
NAMES 34
105 NAMESPACE UserDefaults
105 NAMESPACE SSL
105 NAMESPACE SiteStats
105 NAMESPACE Shell
105 NAMESPACE FTPNONADMIN
105 NAMESPACE subdomains
105 NAMESPACE DNS
105 NAMESPACE CGI
105 NAMESPACE LOGS
105 NAMESPACE Compass_webapps
105 NAMESPACE wordpress
105 NAMESPACE REDIRECT
105 NAMESPACE roundcubemail
105 NAMESPACE PHP
105 NAMESPACE PHPVsite
105 NAMESPACE USERWEBS
105 NAMESPACE Disk
105 NAMESPACE Nginx
105 NAMESPACE MYSQL_Vsite
105 NAMESPACE OpenDKIM
105 NAMESPACE SSI
201 OK

These are all the NameSpaces with configs for that Vsite. The one that 
interests us is the NameSpace 'MYSQL_Vsite'. So let us take a look at that:

[root at 5211r ~]# /usr/sausalito/bin/cceclient
100 CSCP/0.99
200 READY
GET 34 . MYSQL_Vsite
102 DATA pass = "XXXXX"
102 DATA CREATE = "1"
102 DATA destroy = ""
102 DATA GRANT = "0"
102 DATA hidden = "1665385676"
102 DATA fileTrigger = ""
102 DATA DROP = "1"
102 DATA LOCK_TABLES = "1"
102 DATA ALTER_ROUTINE = "0"
102 DATA REFERENCES = "0"
102 DATA MAX_CONNECTIONS_PER_HOUR = "0"
102 DATA SELECT = "1"
102 DATA userPermsReset = ""
102 DATA fileSource = ""
102 DATA host = "127.0.0.1"
102 DATA enabled = "1"
102 DATA doBackupDBname = ""
102 DATA DBdel = ""
102 DATA doBackupDB = ""
102 DATA CREATE_VIEW = "0"
102 DATA doRestoreDBname = ""
102 DATA CLASSVER = "1.0"
102 DATA DELETE = "1"
102 DATA EVENT = "0"
102 DATA username = "vsite_M82fXCH"
102 DATA userPermChange = ""
102 DATA MAX_UPDATES_PER_HOUR = "0"
102 DATA fileTarget = ""
102 DATA TEMPORARY = "1"
102 DATA create = "1665385676"
102 DATA SHOW_VIEW = "0"
102 DATA DB = "vsite_M82fXCH_db"
102 DATA NAMESPACE = "MYSQL_Vsite"
102 DATA DBmultiDel = "1666334631"
102 DATA doRestoreDB = ""
102 DATA port = "3306"
102 DATA TRIGGER = "0"
102 DATA DBmultiAdd = "1666334640"
102 DATA ALTER = "1"
102 DATA DBmulti = ""
102 DATA INSERT = "1"
102 DATA userPermsUpdate = "1676598814"
102 DATA EXECUTE = "0"
102 DATA UPDATE = "1"
102 DATA CREATE_ROUTINE = "0"
102 DATA FILE = "0"
102 DATA MAX_QUERIES_PER_HOUR = "0"
102 DATA maxDBs = "6"
102 DATA INDEX = "1"
201 OK

There are only two values that are of interest for your usage case:

102 DATA DB = "vsite_M82fXCH_db"
102 DATA DBmulti = ""

The key "DB" holds the name of the GUI created database. You can leave 
it as is. That field only takes ONE database name. No more, no less.

The field "DBmulti" contains names of any *additional* databases beyond 
the primary DB and that takes an Array of one or more values. However, 
an Array in CODB has individual values encapsulated in & symbols.

So if you wanted to add just ONE database to it? Then you would run this 
command:

SET 34 . MYSQL_Vsite DBmulti = "&site1_text_db&"

Note the leading and trailing & in this SET command. And 34 is the 
Object ID for Vsite "site1" in my example. Your Object ID might be 
different.

If you want to add both our example DBs ('site1_text_db' and 
'site1_images_db') to it? Then you would use this SET transaction instead:

SET 34 . MYSQL_Vsite DBmulti = "&site1_text_db&site1_images_db&"

As you can see: There is still a leading and training & and also one & 
as separator between the two DB names.

When you run that SET transaction, the DBs should show up in the GUI as 
being associated to Vsite "site1". The will show at the bottom of the 
MariaDB page of that Vsite.

HOWEVER: There is more to it. You need to be mindful of the MariaDB 
ownership of these DBs. Make sure to use phpMyAdmin and grant the 
MySQL-User of that Vsite ownership and sufficient rights to manage these 
DBs, so that the GUI can properly access them, too.

Because if the siteAdmin uses the GUI to manage these DBs, then the GUI 
uses the configured MySQL-User for the Vsite for the management instead 
of using MySQL user "root". So if the DBs aren't owned by the MySQL user 
of said Vsite, then the GUI (for siteAdmin users!) won't allow 
management of them.

So it's not complicated to manually assign the DBs via the GUI. It's 
just that it's not easy to script it when you don't know who owns what 
and how the actual DBs are called. Or what the overall MySQL ownership 
situation is or what it actually should be.

-- 
With best regards

Michael Stauber