Database Optimization in Laravel: Master/Slave Configuration and Implementation with Docker

Efficient database management is essential to ensure performance and scalability in application development. One popular solution is the Master-Slave model, where write and read operations are separated to distribute the load across multiple servers. In this article, we’ll explore how to implement this strategy in Laravel 11 and configure Docker containers to set up a Master-Slave replication with MySQL 8.

What is the Master-Slave Model and Why Use It?

The Master-Slave model is a database architecture where:
• Master: Handles all write operations (INSERT, UPDATE, DELETE).
• Slave(s): Handle read operations (SELECT).

Benefits of the Master-Slave Model

1.  Improved Performance:
•   Read queries are distributed across multiple Slave servers, reducing the load on the Master server.
2.  High Availability:
•   If a Slave fails, read operations can be redirected to other servers.
3.  Scalability:
•   More Slave servers can be added as the demand for read queries grows.
4.  Resource Optimization:
•   Write and read operations do not compete for resources, improving efficiency.

Implementing the Master-Slave Model in Laravel 11

Laravel makes it easy to manage multiple database connections with its built-in read/write splitting configuration. Follow these steps to set up a Master-Slave environment:

Configuration in config/database.php

Define the Master and Slave connections in the database configuration file:

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,

    // Master-Slave Configuration
    'read' => [
        'host' => [
            env('DB_SLAVE1_HOST', '127.0.0.1'),
            env('DB_SLAVE2_HOST', '127.0.0.1'),
        ],
    ],
    'write' => [
        'host' => [
            env('DB_MASTER_HOST', '127.0.0.1'),
        ],
    ],

    'sticky' => true, // Keeps the connection to Master after a write
],

Environment Variables

Set up the server addresses in the .env file:

DB_MASTER_HOST=master-db-host
DB_SLAVE1_HOST=slave1-db-host
DB_SLAVE2_HOST=slave2-db-host
DB_PORT=3306
DB_DATABASE=mydatabase
DB_USERNAME=myuser
DB_PASSWORD=mypassword

Laravel Automatically Handles Queries

Laravel separates write and read operations:
• Operations like INSERT, UPDATE, and DELETE are routed to the Master server.
• SELECT queries are routed to the Slave servers.

For example:

// Read operation (Slave)
$users = User::all();

// Write operation (Master)
$user = User::find(1);
$user->name = 'New Name';
$user->save();

Setting Up MySQL Master-Slave Replication with Docker

docker-compose.yml

Create a Docker Compose file to define the Master and Slave services:

version: '3.8'

services:
  mysql-master:
    image: mysql:8.0
    container_name: mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: master_root_password
      MYSQL_REPLICATION_USER: replicator
      MYSQL_REPLICATION_PASSWORD: replication_password
      MYSQL_DATABASE: mydatabase
    ports:
      - "3306:3306"
    volumes:
      - master_data:/var/lib/mysql
      - ./master.cnf:/etc/mysql/conf.d/master.cnf
    networks:
      - mysql-network

  mysql-slave:
    image: mysql:8.0
    container_name: mysql-slave
    environment:
      MYSQL_ROOT_PASSWORD: slave_root_password
      MYSQL_REPLICATION_USER: replicator
      MYSQL_REPLICATION_PASSWORD: replication_password
    ports:
      - "3307:3306"
    volumes:
      - slave_data:/var/lib/mysql
      - ./slave.cnf:/etc/mysql/conf.d/slave.cnf
    networks:
      - mysql-network
    depends_on:
      - mysql-master

volumes:
  master_data:
  slave_data:

networks:
  mysql-network:

MySQL Configuration

Create configuration files for the Master and Slave:

master.cnf

[mysqld]
server-id=1
log_bin=/var/log/mysql/mysql-bin.log
binlog_do_db=mydatabase

slave.cnf

[mysqld]
server-id=2
relay-log=/var/log/mysql/mysql-relay-bin.log
log_bin=/var/log/mysql/mysql-bin.log
read_only=1

Start the Containers

Run the containers:

docker-compose up -d

Configure Replication

•	On the Master:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

	•	On the Slave:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='mysql-master',
    SOURCE_USER='replicator',
    SOURCE_PASSWORD='replication_password',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=4;
START REPLICA;
SHOW REPLICA STATUS\G;

Conclusion

Combining Laravel 11 with a Master-Slave database configuration provides a scalable and high-performance solution for modern applications. By leveraging Docker to manage containers, you can quickly set up replicated environments to ensure availability and efficiency.

Adopting this strategy is crucial for large-scale projects, allowing applications to handle more users and operate more efficiently. If you haven’t tried it yet, now is the perfect time to implement the Master-Slave replication strategy in your Laravel workflow.