MySQL Server Pooling

Overview

This feature is in no way MySQL Cluster.

MySQL Server Pooling is the ability to manage multiple physical MySQL servers from a single installation of InterWorx. MySQL Servers can be added and then assigned to new SiteWorx accounts, so their database storage is being done by another server.

Requirements

  • A working InterWorx installation.
  • MySQL Server Pooling enabled in Settings.
  • At least one external MySQL server.

The external MySQL server must be setup in a way to allow remote connections from the InterWorx server. For the initial setup you will also need to provide a root-level MySQL username and password.

Setup

  1. Click the Icon System Services menu item if it is not already open.
  2. Click the Icon MySQL Server menu item if it is not already open.
  3. Click the Icon Overview menu item.
  4. You will see an [ Add ] link at the top of the page, click that, enter a nickname for the server, it's hostname, root-level username, and password.
  5. Click the button.
  6. If the above information is correct then you should be taken back to the overview page, and the new server should be listed.
  7. You can now assign future SiteWorx accounts to use this MySQL server instead of the local server.

Restrictions

  • Once a MySQL server is in use by a SiteWorx account, it cannot be deleted until that SiteWorx account has its databases transferred to another MySQL server.
  • You cannot manage settings or restart remote MySQL servers directly through InterWorx.

Transfer of SiteWorx Accounts

Changing the database server that a SiteWorx account uses can only be done via the command line at this time. 

/home/interworx/bin/iworxdb-transfer.pex

iworxdb-transfer.pex has several command line parameters:

Required Parameters
Parameter Description
--domain=Domain Domain of the SiteWorx account you wish to transfer.
--new-server-nickname=Server Name Nickname of the MySQL Server for where you want to transfer the SiteWorx databases.
--force Transfer all databases regardless 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.

Transfer Usage

  • Before attempting a database server transfer, it is highly recommended that you backup the SiteWorx account first.
  • Any databases, users, permissions will be left on the old server after the transfer. Those need to be removed manually.
  • The transfer script DOES NOT update any application connection strings, that will have to be done manually.

The following command copies the databases from the existing database server to "Server2".

/home/interworx/bin/iworxdb-transfer.pex --domain=yourdomain.com --new-server-nickname=Server2

If there are collisions then you can force the iworxdb-transfer script to drop the colliding tables using the --force flag.

/home/interworx/bin/iworxdb-transfer.pex --domain=yourdomain.com --new-server-nickname=Server2 --force

If the database is quite large and you only want the table structure to be transferred to the new server then specify the --dont-transfer-data flag.

/home/interworx/bin/iworxdb-transfer.pex --domain=yourdomain.com --new-server-nickname=server2 --dont-transfer-data