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 for each server in the database cluster. connection pools MariaDB MaxScale is an advanced database proxy that can be used as a read/write splitter that routes statements to replica nodes and / / 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. SELECT INSERT UPDATE DELETE 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 . The best part: you’ll learn all this without leaving your web browser! topology The Play With Docker Website (PWD) is a website that allows you to create virtual machines with preinstalled and interact with them directly in your browser. Log in and start a new session. Play With Docker Docker You will use a total of 5 nodes: : Primary server node1 : Replica server A node2 : Replica server B node3 : MaxScale database proxy node4 : Test machine (equivalent to a web server, for example) node5 : Even though 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). Note databases on Docker containers Running the Primary Server Add a new instance using the corresponding button: On , run a MariaDB primary server as follows: node1 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 ( ). Replicas will use this user to connect to the primary. replication_user Running the Replica Servers Create two new instances ( and ) and run the following command on both of them: node2 node3 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 with the IP address of . You can find the IP address in the instances list. <PRIMARY_IP_ADDRESS> node1 Now you have a cluster formed by one primary node and two replicas. All the writes you perform on the primary node ( ) are automatically replicated to all replica nodes ( and ). node1 node1 node2 Running MaxScale MaxScale is a that understands . 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. database proxy SQL Create a new instance ( ) and run as follows: node4 MaxScale 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 and . Docker Swarm Kubernetes Launch a new shell in : node4 docker exec -it maxscale bash You need to create objects in MaxScale. These are the MariaDB databases to which MaxScale routes reads and writes. Replace , , and with the IP addresses of the corresponding nodes ( , , and ) and execute the following: server <NODE_1_IP_ADDRESS> <NODE_2_IP_ADDRESS> <NODE_3_IP_ADDRESS> node1 node2 node3 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 to check the state of the cluster. Run the following command: monitor maxctrl create monitor mdb_monitor mariadbmon \
    --monitor-user root --monitor-password 'password' \
    --servers node1 node2 node3 Don’t use the user in production environments! It’s okay in this ephemeral lab environment, but in other cases and give it the appropriate grants. Note: root create a new database user for MaxScale Now that MaxScale is monitoring the servers and making this information available to other modules, you can create a MaxScale . 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: service maxctrl create service query_router_service readwritesplit \
    user=root \
    password=password \
    --servers node1 node2 node3 Finally, you need to create a MaxScale . 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: listener maxctrl create listener \
    query_router_service query_router_listener 4000 \
    --protocol=MariaDBClient Notice how the listener is configured to use port . This is the same port you published when you run the Docker container. 4000 Check that the servers are up and running: maxctrl list servers You should see something like the following: Testing the Setup To test the cluster, create a new instance ( ) and start an Ubuntu container: node5 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 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 SELECT *, CONCAT("Read from server ", @@server_id) FROM demo.message; Run the previous query several times. You should get a result like this: In my cluster, all the writes were performed by server ID which is the primary node. Reads were executed by server IDs and which are the replica nodes. You can confirm the ID values by running the following on the primary and replica nodes: 367 908 308 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 to your backend database topology, perform , perform load balancing, import and export data from and into , and even convert NoSQL/MongoDB API commands to SQL. MaxScale also includes a and for operations. Check the to learn more about MaxScale. security automated failover connection-based Kafka REST API web-based GUI documentation Also Published Here

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

9 Useful Interactive CLI Tools for Linux

Are MySQL replications as smooth as you think they are?

Data Replication in Distributed Systems - Part 1: Understanding the Need for Database Distribution

Data Replication in Distributed Systems – Part 2: Conflict Resolution and Multi-Leader Configuration

Data Reliability in an Unreliable World

Dealing With Replication, High-Performance Queries And Other Data Platforms Challenges

9 Useful Interactive CLI Tools for Linux

Are MySQL replications as smooth as you think they are?

Data Replication in Distributed Systems - Part 1: Understanding the Need for Database Distribution

Data Replication in Distributed Systems – Part 2: Conflict Resolution and Multi-Leader Configuration

Data Reliability in an Unreliable World

Dealing With Replication, High-Performance Queries And Other Data Platforms Challenges

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps