How to Create a Clone of Your MySQL or PostgreSQL Database Cluster

If you are managing a production database, chances are high that you’ve had to clone your database to a different server other than the production server. The basic method of creating a clone is to restore a database from a recent backup onto another database server. Another method is by replicating from a source database while it is still running, in which case it is important that the original database be unaffected by any cloning procedure.

Why Would You Need to Clone a Database?

A cloned database cluster is useful in a number of scenarios:

• Troubleshoot your cloned production cluster in the safety of your test environment while performing destructive operations on the database.
• Patch/upgrade test of a cloned database to validate the upgrade process before applying it to the production cluster.
• Validate backup & recovery of a production cluster using a cloned cluster.
• Validate or test new applications on a cloned production cluster before deploying it on the live production cluster.
• Quickly clone the database for audit or information compliance requirements for example by quarter or year end where the content of the database must not be changed.
• A reporting database can be created at intervals in order to avoid data changes during the report generations.
• Migrate a database to new servers, new deployment environment or a new data center.

When running your database infrastructure on the cloud, the cost of owning a host (shared or dedicated virtual machine) is significantly lower compared to the traditional way of renting space in a datacenter or owning a physical server. Furthermore, most of the cloud deployment can be automated easily via provider APIs, client software and scripting. Therefore, cloning a cluster can be a common way to duplicate your deployment environment for example, from dev to staging to production or vice versa.

We haven’t seen this feature being offered by anyone in the market thus it is our privilege to showcase how it works with ClusterControl.

Cloning a MySQL Galera Cluster

One of the cool features in ClusterControl is it allows you to quickly clone, an existing MySQL Galera Cluster so you have an exact copy of the dataset on the other cluster. ClusterControl performs the cloning operation online, without any locking or bringing downtime to the existing cluster. It’s like a cluster scale out operation except both clusters are independent to each other after the syncing completes. The cloned cluster does not necessarily need to be as the same cluster size as the existing one. We could start with one-node cluster, and scale it out with more database nodes at a later stage.

In this example, we are having a cluster called “Staging” that we would want to clone as another cluster called “Production”. The premise is the staging cluster already stored the necessary data that is going to be in production soon. The production cluster consists of another 3 nodes, with production specs.

The following diagram summarizes final architecture of what we want to achieve:

The first thing to do is to set up a passwordless SSH from ClusterControl server to the production servers. On ClusterControl server run the following:

$ whoami

root

$ ssh-copy-id [email protected]

$ ssh-copy-id [email protected]

$ ssh-copy-id [email protected]

Enter the root password of the target server if prompted.

From ClusterControl database cluster list, click on the Cluster Action button and choose Clone Cluster. The following wizard will appear:

Specify the IP addresses or hostnames of the new cluster and make sure you get all the green tick icon next to the specified host. The green icon means ClusterControl is able to connect to the host via passwordless SSH. Click on the “Clone Cluster” button to start the deployment.

The deployment steps are:

1. Create a new cluster consists of one node.
2. Sync the new one-node cluster via SST. The donor is one of the source servers.
3. The remaining new nodes will be joining the cluster after the donor of the cloned cluster is synced with the cluster.

Once done, a new MySQL Galera Cluster will be listed under ClusterControl cluster dashboard once the deployment job completes.

Note that the cluster cloning only clones the database servers and not the whole stack of the cluster. This means, other supporting components related to the cluster like load balancers, virtual IP address, Galera arbitrator or asynchronous slave are not going to be cloned by ClusterControl. Nevertheless, if you would like to clone as an exact copy of your existing database infrastructure, you can achieve that with ClusterControl by deploying those components separately after the database cloning operation completes.

Creating a Database Cluster from a Backup

Another similar feature offered by ClusterControl is “Create Cluster from Backup”. This feature is introduced in ClusterControl 1.7.1, specifically for Galera Cluster and PostgreSQL clusters where one can create a new cluster from the existing backup. Contratory to cluster cloning, this operation does not bring additional load to the source cluster with a tradeoff of the cloned cluster will not be at the current state as the source cluster.

In order to create cluster from a backup, you must have a working backup created. For Galera Cluster, all backup methods are supported while for PostgreSQL, only pgbackrest is not supported for new cluster deployment. From ClusterControl, a backup can be created or scheduled easily under ClusterControl -> Backups -> Create Backup. From the list of the created backup, click on Restore backup, choose the backup from the list and choose to “Create Cluster from Backup” from the restoration option:

In this example, we are going to deploy a new PostgreSQL Streaming Replication cluster for staging environment, based on the existing backup we have in the production cluster. The following diagram illustrates the final architecture:

The first thing to do is to set up a passwordless SSH from ClusterControl server to the production servers. On ClusterControl server run the following:

$ whoami

root

$ ssh-copy-id [email protected]

$ ssh-copy-id [email protected]

$ ssh-copy-id [email protected]

When you choose Create Cluster From Backup, ClusterControl will open a deployment wizard dialog to assist you on setting up the new cluster:

A new PostgreSQL Streaming Replication instance will be created from the selected backup, which will be used as the base dataset for the new cluster. The selected backup must be accessible from the nodes in the new cluster, or stored in the ClusterControl host. 

Clicking on “Continue” will open the standard database cluster deployment wizard:

Note that the root/admin user password for this cluster must the same as the PostgreSQL admin/root password as included in the backup. Follow the configuration wizard accordingly and ClusterControl then perform the deployment on the following order:

1. Install necessary softwares and dependencies on all PostgreSQL nodes.
2. Start the first node.
3. Stream and restore backup on the first node.
4. Configure and add the rest of the nodes.

Once done, a new PostgreSQL Replication Cluster will be listed under ClusterControl cluster dashboard once the deployment job completes.

Conclusion

ClusterControl allows you to clone or copy a database cluster to multiple environments with just a number of clicks. You can download it for free today. Happy cloning!