\
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](https://dzone.com/articles/what-is-connection-pooling-and-why-should-you-care) 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](https://mariadb.com/docs/deploy/topologies/primary-replica/). The best part: you’ll learn all this without leaving your web browser!

## The Play With Docker Website

[Play With Docker](https://labs.play-with-docker.com) (PWD) is a website that allows you to create virtual machines with [Docker](https://dzone.com/refcardz/getting-started-with-docker-1) preinstalled and interact with them directly in your browser. Log in and start a new session.

\
 ![The Play With Docker website](https://cdn.hackernoon.com/images/RLmwLcqcgCQhlcbhuqLdzf7ASis1-mjc3qty.png)

\
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](https://dzone.com/articles/database-delivery-with-docker-and-sql-change-autom) 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:

\
 ![](https://cdn.hackernoon.com/images/RLmwLcqcgCQhlcbhuqLdzf7ASis1-lzd3qhk.png)

\
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](https://dzone.com/articles/what-is-a-smart-database-proxy) that understands [SQL](https://dzone.com/refcardz/getting-started-with-distributed-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](https://hub.docker.com/r/mariadb/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](https://docs.docker.com/engine/swarm/) and [Kubernetes](https://kubernetes.io/).

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](https://mariadb.com/docs/deploy/topologies/primary-replica/enterprise-server-10-6/config-primary/) 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:

\
 ![](https://cdn.hackernoon.com/images/RLmwLcqcgCQhlcbhuqLdzf7ASis1-o5e3q2n.png)

\
## 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:

MariaDB SQL

\
```javascript
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:

MariaDB SQL

\
```
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:

MariaDB SQL

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

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

\
 ![](https://cdn.hackernoon.com/images/RLmwLcqcgCQhlcbhuqLdzf7ASis1-03f3qj6.png)

\
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](https://mariadb.com/kb/en/mariadb-maxscale-2208-nosql-protocol-module/) to your backend database topology, perform [automated failover](https://mariadb.com/kb/en/mariadb-maxscale-6-automatic-failover-with-mariadb-monitor/), perform [connection-based](https://mariadb.com/kb/en/mariadb-maxscale-25-readconnroute/) load balancing, import and export data from and into [Kafka](https://mariadb.com/docs/architecture/components/maxscale/routers/kafkacdc/), and even convert NoSQL/MongoDB API commands to SQL. MaxScale also includes a [REST API](https://mariadb.com/kb/en/mariadb-maxscale-2208-rest-api-tutorial/) and [web-based GUI](https://mariadb.com/kb/en/mariadb-maxscale-2208-using-maxgui-tutorial/) for operations. Check the [documentation](https://mariadb.com/docs/products/mariadb-maxscale/) to learn more about MaxScale.


---

Also Published [Here](https://dzone.com/articles/learn-database-read-write-splitting-using-your-bro)

The code in this story is for educational purposes. The readers are solely responsible for whatever they build with it.

The writer has or has previously had a business relationship with companies mentioned in this article.

How to do Database Read/Write Splitting With Your Browser

Too Long; Didn't Read

Companies Mentioned

@alejandroduarte

Credibility

RELATED STORIES