The upcoming release has a new feature regarding databases (clustering or not). You can now set up multiple MySQL servers and shard out your clients/resellers to multiple MySQL instances.
Hi
Could we have a little more informations about this upcoming feature ?
Will it be possible to create a database cluster, managed by interworx ?
Could you give me some informations by the technologies used (some are very good, others are very bad) ?
is it a real multi databases, clustered databases ?
With read and write ?
We don’t have to change app code ?
Thanks to give me more detailled (technical/functionnal) informations
pfo,
The MySQL multi-server feature in InterWorx 4 allows you to assign external MySQL servers to Resellers and SiteWorx accounts. This way you can have one InterWorx instalation running on a number of other servers which only run the databases (these servers don’t even need to have InterWorx installed). Also, backups/restores will still work the same way.
The idea is that just because you have a high database load from a client, doesn’t mean you would have to setup a new InterWorx instance. You can simple add another database server to the pool and start moving accounts over (hopefully lowering load on the InterWorx install).
One of the other things you could do is build a MySQL cluster and point your InterWorx install to use that cluster for databases, but the cluster itself would not be managed by InterWorx.
The only thing that has changed is that when you create a SiteWorx account, you can assign which database server it uses (this will be the server that SiteWorx creates dbs and users). All you need on the other servers is a properly configuration instalation of MySQL.
If you move a SiteWorx account from a local database to a remote one, it will break any application code on the user’s account. So be aware of that, there really isn’t anything we can do to automatically update the account’s database connection information.
Does this answer your questions? Is there anything else I can clear up?
There was a problem validating the form. Please see details below.
? DB Error: connect failed mysql://root:********@(LANIP)/mysql
? ERROR 2003 (HY000): Can’t connect to MySQL server on ‘(LANIP)’ (113)
We have new dedicated database server connected to our private switch (Server1).
Local IPs are setup for all the eth1 interface. Once can ssh between the servers.
Mysql is setup on server1 and 3306 port is open.
I’ve tried to setup new connection pool on server2 pointing to server1 using the local ip.
I’ve tried using the WAN Ip as well but I get the above error.
What else do I need to do to establish this connection?
See if you can make it connect from the command line of the interworx server, like
mysql -h LANIP -u root -p
if you can connect, I’m not sure. If you can’t, there’s either a firewall preventing the connection to port 3306 of the remote server, or the root user doesn’t have permission to connect from that host.
I got it working - learned some interesting things. I recommend never try setting up root user for the remote conn - i ended up killing the user in process and took awhile to get root user resetup right. Best to create separate user for these remote connections.
Anyway, connection pool working, but found that I cannot give exiting accounts access to the remote database server.
What if we have customer already setup and then down the road we find their db usage is extremely heavy and need to move the database to the dedicated mysql server. How can this be handled? Will we need to ‘recreate’ the account?
It would be nice if we can provide customers with both, access to local or remote mysql - as remote as extra service option.
It is possible to move one account from using an existing mysql server to another, but not via the web interface, since this is not a trivial operation that should be taken lightly. We do have the following bin script that can be used however:
[root@server interworx]# ~iworx/bin/iworxdb-transfer.pex
--domain is required
--new-server-nickname is required
Usage: iworxdb-transfer.php [ options ]
--domain <string> Domain of the SiteWorx account you wish to transfer.
--new-server-nickname <string> Nickname of the MySQL Server for where you want to transfer the SiteWorx databases.
--force Transfer all databases reguardless of collisions. WARNING: If a collision is found the database on the new server WILL BE DROPPED.
--dont-transfer-data Only directs the current SiteWorx account to use the new server.Does NOT transfer users or databases.
Another question - Is there a way to set the mysql pool based on package? How can I direct whether account setup will use localhost or one of the remote mysql servers in the pool?
Is there a way to set the mysql pool based on package?
No, not at this time.
How can I direct whether account setup will use localhost or one of the remote mysql servers in the pool?
What do you mean by “direct”? I mean, if you’re manually creating the siteworx account, you just choose the database server when filling out the form - so I assume you’re imagining a different scenario, and I’m just not quite following.
I’d imagine not. The API that the billing system is using supports choosing a database server - if the billing system doesn’t provide a database server option (which I’m sure it doesn’t, yet), it defaults to localhost. It probably would make the most sense to be able to attach options like database server to the Packages feature somehow, to remove extra work for the billing system authors. We’ll have to think about how that could be done…
