Replication of customer MySQL databases

How to set up MySQL replication of the Atomia shared hosting MySQL databases for customers.

Overview

The Atomia Platform comes with Atomia Cloud Hosting Pack, which enables you to sell shared hosting services to your customers. On a high level the Atomia Cloud Hosting Pack consists of the following components:

Web server (Apache or IIS)
Email server (Postfix / Dovecot or MS Exchange)
FTP Server (PureFTPd)
Database server (MySQL, PostgreSQL or MS SQL)

Using the Atomia Installer you can deploy multiple MySQL nodes to facilitate more customers. Atomia will use all available and configured MySQL servers with round robin principle to spread your customers and their databases evenly across all available MySQL nodes.

By default the Atomia Installer installs only one MySQL server, without any replication. In this article we describe how to set up replication, and what you need to take in consideration when doing it.

Atomia configuration for MySQL resources

On Atomia Internal Server the Resources.xml file can be found in: C:\Program Files (x86)\Atomia\AutomationServer\Common

Important!

Please follow the instructions Using transformation files before proceeding.

This configuration file stores available resources to Atomia for Atomia Cloud Hosting Pack, such as Apache and MySQL servers. To add additional MySQL resources edit (or create, if it doesn’t exist) the transformation in: C:\Program Files (x86)\Atomia\AutomationServer\Common\Transformation Files\Resources.MySQL.xml

Example

<resourceDescription xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
 <bindings xdt:Locator="XPath(//bindings[moduleList/module/@name='Atomia.Provisioning.Modules.MySql.MySql'])">
  <resourceList>
    <resource name="MySql" xdt:Transform="Insert">
         <property name="DatabaseServer">mysql01-internal.somedomain.com</property>
         <property name="PublicIpAddress">mysql01.somedomain.com</property>
         <property name="User">atomia</property>
         <property name="Password">somepassword</property>
     </resource>
   </resourceList>
 </bindings>
</resourceDescription>

The above statement will add a MySQL server located at mysql01-internal.somedomain.com with the public address mysql01-internal.somedomain.com (if enabled to listen on public address in MySQL configuration) to Atomia Cloud Hosting Pack. On this server Atomia will provision new users that have MySQL service enabled.

Important!

Always use hostname when defining MySQL nodes, and never IP addresses. In case where master and slave need to be switched, Atomia will already have stored hostname for provisioned customers and their databases. In case you are using IP addresses and not hostname, you will need to update the Atomia internal database as well.

For Atomia to be able to create and manage new users and their databases, user atomia identified by somepassword must have grant privileges on the MySQL server.

When installing MySQL servers through the Atomia Installer, grant privileges will be automatically created. In case you are creating a MySQL server to be used with Atomia manually, you must add this user yourself using the following statement:

CREATE USER 'atomia'@'%' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON *.* TO atomia@'%' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON *.* TO 'atomia'@'%' WITH GRANT OPTION;

In case you have multiple MySQL nodes (installed through the Atomia Installer or manually) you need to configure Resources.xml as follows:

<resourceDescription xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
 <bindings xdt:Locator="XPath(//bindings[moduleList/module/@name='Atomia.Provisioning.Modules.MySql.MySql'])">
  <resourceList>
    <!-- First MySQL resource -->
    <resource name="MySql" xdt:Transform="Insert">
         <property name="DatabaseServer">mysql01-internal.somedomain.com</property>
         <property name="PublicIpAddress">mysql01.somedomain.com</property>
         <property name="User">atomia</property>
         <property name="Password">somepassword</property>
     </resource>
    <!-- End of first resource Second MySQL resource -->
     <resource name="MySql" xdt:Transform="Insert">
         <property name="DatabaseServer">mysql02-internal.somedomain.com</property>
         <property name="PublicIpAddress">mysql02.somedomain.com</property>
         <property name="User">atomia</property>
         <property name="Password">somepassword</property>
     </resource>
     <!-- End of second MySQL resource -->
   </resourceList>
 </bindings>
</resourceDescription>

You can add as many resources as you require once they are configured, either through the Atomia Installer or manually. If you add them manually, remember to add grant privileges for the user.

MySQL replication

As you might have noticed, there is no mention of MySQL replication so far. This is due to the fact that Atomia is agnostic to the replication process itself. You have all the freedom to choose any replication process that you prefer. Of course, the replication is optional and not mandatory for correct operational status of the Atomia Cloud Hosting Pack and the Atomia Platform.

For all available MySQL replication types and how they work, please see the official MySQL documentation.

Additional Atomia notes for replication

Once the customer starts using MySQL services, Atomia will create a user and a database based on the customer input. The information about the MySQL server location (hostname) as well as the username and the password is stored in the Atomia Database. When creating a replication it is very important to make sure that you will not need to update the Atomia Database in case of an emergency, ensuring that you will have very fast recovery time.

Therefore we recommend following approach:

Scenario

MySQL master on IP address: 10.1.1.1
MySQL slave on IP address: 10.1.1.2

MySQL hostname: mysql01.somedomain.com in DNS pointing to 10.1.1.1

Keep in mind

Keep TTL of this DNS record as low as possible (5 minutes or less).

In Atomia Resources.xml MySQL node hostname is mysql01.somedomain.com.

Make sure that all MySQL server have the exact same user with the exact same grant privileges. Make sure the following statement is executed on all servers:

CREATE USER 'atomia'@'%' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON *.* TO 'atomia'@'%' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON *.* TO 'atomia'@'%' WITH GRANT OPTION;

Important!

The above GRANT privileges are required for Atomia to be able to communicate with the MySQL server.

Now you will have a Master Slave configuration for your customers. In an emergency case whereby master server is not usable any more, you will only need to update your DNS and assign slave IP address to the hostname mysql01.somedomain.com, and all of your customer services will be restored and continue working.

General configuration