Learn Database Read/Write Splitting Using Your Browser

Read/write splitting is a technique to route reads and writes to multiple database servers, allowing you to perform query-based load balancing. Implementing this at the application level is hard because it couples code or configuration parameters to the underlying database topology. For example, you might have to define different connection pools for each server in the database cluster.

MariaDB MaxScale is an advanced database proxy that can be used as a read/write splitter that routes SELECT statements to replica nodes and INSERT/UPDATE/DELETE statements to primary nodes. This happens automatically without having to change your application code or even configuration—with MaxScale, the database looks like a single-node database to your application.

In this hands-on tutorial, you’ll learn how to configure MariaDB database replication with one primary and two replica nodes, as well as how to set up MaxScale to hide the complexity of the underlying topology. The best part: you’ll learn all this without leaving your web browser!

The Play With Docker Website

Play With Docker (PWD) is a website that allows you to create virtual machines with Docker preinstalled and interact with them directly in your browser. Log in and start a new session.

The Play With Docker website

You will use a total of 5 nodes:

• node1: Primary server

• node2: Replica server A

• node3: Replica server B

• node4: MaxScale database proxy

• node5: Test machine (equivalent to a web server, for example)

Note: Even though databases on Docker containers are a good fit for the most simple scenarios and for development environments, it might not be the best option for production environments. MariaDB Corporation does not currently offer support for Docker deployments in production environments. For production environments, it is recommended to use MariaDB Enterprise (on the cloud or on-premise) or MariaDB SkySQL (currently available on AWS and GCP).

Running the Primary Server

Add a new instance using the corresponding button:

A Docker-ready instance

On node1, run a MariaDB primary server as follows:

docker run --name mariadb-primary \
  -d \
  --net=host \
  -e MARIADB_ROOT_PASSWORD=password \
  -e MARIADB_DATABASE=demo \
  -e MARIADB_USER=user \
  -e MARIADB_PASSWORD=password \
  -e MARIADB_REPLICATION_MODE=master \
  -e MARIADB_REPLICATION_USER=replication_user \
  -e MARIADB_REPLICATION_PASSWORD=password \
  bitnami/mariadb:latest

This configures a container running MariaDB Community Server with a database user for replication (replication_user). Replicas will use this user to connect to the primary.

Running the Replica Servers

Create two new instances (node2 and node3) and run the following command on both of them:

docker run --name mariadb-replica \
  -d \
  --net=host \
  -e MARIADB_MASTER_ROOT_PASSWORD=password \
  -e MARIADB_REPLICATION_MODE=slave \
  -e MARIADB_REPLICATION_USER=replication_user \
  -e MARIADB_REPLICATION_PASSWORD=password \
  -e MARIADB_MASTER_HOST=<PRIMARY_IP_ADDRESS> \
  bitnami/mariadb:latest

Replace <PRIMARY_IP_ADDRESS> with the IP address of node1. You can find the IP address in the instances list.

Now you have a cluster formed by one primary node and two replicas. All the writes you perform on the primary node (node1) are automatically replicated to all replica nodes (node1 and node2).

Running MaxScale

MaxScale is a database proxy that understands SQL. This allows it to route write operations to the master node and read operations to the replicas in a load-balanced fashion. Your application can connect to MaxScale using a single endpoint as if it was a one-node database.

Create a new instance (node4) and run MaxScale as follows:

docker run --name maxscale \
  -d  \
  --publish 4000:4000 \
  mariadb/maxscale:latest

You can configure MaxScale through config files, but in this tutorial, we’ll use the command line to make sure you understand each step. In less ephemeral environments you should use config files, especially in orchestrated deployments such as Docker Swarm and Kubernetes.

Launch a new shell in node4:

docker exec -it maxscale bash

You need to create server objects in MaxScale. These are the MariaDB databases to which MaxScale routes reads and writes. Replace <NODE_1_IP_ADDRESS>, <NODE_2_IP_ADDRESS>, and <NODE_3_IP_ADDRESS> with the IP addresses of the corresponding nodes (node1, node2, and node3) and execute the following:

maxctrl create server node1 <NODE_1_IP_ADDRESS>
maxctrl create server node2 <NODE_2_IP_ADDRESS>
maxctrl create server node3 <NODE_3_IP_ADDRESS>

Next, you need to create a MaxScale monitor to check the state of the cluster. Run the following command:

maxctrl create monitor mdb_monitor mariadbmon \
    --monitor-user root --monitor-password 'password' \
    --servers node1 node2 node3

Note: Don’t use the root user in production environments! It’s okay in this ephemeral lab environment, but in other cases create a new database user for MaxScale and give it the appropriate grants.

Now that MaxScale is monitoring the servers and making this information available to other modules, you can create a MaxScale service. In this case, the service uses a MaxScale router to make reads and writes go to the correct type of server in the cluster (primary or replica). Run the following to create a new service:

maxctrl create service query_router_service readwritesplit \
    user=root \
    password=password \
    --servers node1 node2 node3

Finally, you need to create a MaxScale listener. This kind of object defines a port that MaxScale uses to receive requests. You have to associate the listener with the router. Run the following to create a new listener:

maxctrl create listener \
    query_router_service query_router_listener 4000 \
    --protocol=MariaDBClient

Notice how the listener is configured to use port 4000. This is the same port you published when you run the Docker container.

Check that the servers are up and running:

maxctrl list servers

You should see something like the following:

A cluster of 3 MariaDB nodes configured in MaxScale for read/write splitting

Testing the Setup

To test the cluster, create a new instance (node5) and start an Ubuntu container:

docker run --name ubuntu -itd ubuntu

This container is equivalent to, for example, a machine that hosts a web application that connects to the database. Run a new Bash session in the machine:

docker exec -it ubuntu bash

Update the package catalog:

apt update

Install the MariaDB SQL client so you can run SQL code:

apt install mariadb-client -y

Connect to the database, or more precisely, to the MaxScale database proxy:

mariadb -h 192.168.0.15 --port 4000 -u user -p

As you can see, it’s as if MaxScale was a single database. Create the following table:

CREATE TABLE demo.message(content TEXT);

We want to insert rows that contain the unique server ID of the MariaDB instance that actually performs the insert operation. Here’s how:

INSERT INTO demo.message VALUES \
    (CONCAT("Write from server ", @@server_id)), \
    (CONCAT("Write from server ", @@server_id)), \
    (CONCAT("Write from server ", @@server_id));

Now let’s see which MariaDB server performed the write and read operations:

SELECT *, CONCAT("Read from server ", @@server_id) FROM demo.message;

Run the previous query several times. You should get a result like this:

Testing read/write splitting

In my cluster, all the writes were performed by server ID 367 which is the primary node. Reads were executed by server IDs 908 and 308 which are the replica nodes. You can confirm the ID values by running the following on the primary and replica nodes:

docker exec -it mariadb-primary mariadb -u root -p \
    --execute="SELECT @@server_id"
 
docker exec -it mariadb-replica mariadb -u root -p \
    --execute="SELECT @@server_id"

What’s Next?

We focused on basic read/write splitting in this tutorial, but MaxScale can do much more than this. For example, enforce security to your backend database topology, perform automated failover, perform connection-based load balancing, import and export data from and into Kafka, and even convert NoSQL/MongoDB API commands to SQL. MaxScale also includes a REST API and web-based GUI for operations. Check the documentation to learn more about MaxScale.