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

[Darren Wolfe]

That's super useful, thanks - I do know all the databases, users, and to which vsite they should belong, so scripting something won't be too difficult.

I was hoping just shoving the data I needed into CODB directly would work, and that the various hooks wouldn't automatically try to create the databases/users and fail - presumably that creation or checking process is done within the logic of the gui, and not the interface to codb?




> -----Original Message-----
> From: Blueonyx <blueonyx-bounces at mail.blueonyx.it> On Behalf Of
> Michael Stauber
> Sent: 17 February 2023 03:01
> To: blueonyx at mail.blueonyx.it
> Subject: [BlueOnyx:25976] Re: Adding existing mysql database/users
> 
> 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
> _______________________________________________
> Blueonyx mailing list
> Blueonyx at mail.blueonyx.it
> http://mail.blueonyx.it/mailman/listinfo/blueonyx