Laravel Master Slave Database

Laravel Master/Slave Database

Laravel provides an efficient way to configure and use a master/slave database setup. This setup allows you to distribute the read load across multiple database replicas (slaves) while maintaining a single database (master) for write operations.

Configuration

To configure the master/slave database setup in Laravel, you need to make changes to the config/database.php file.

Step 1: Define Connection Settings

Within the connections array, you can define your master and slave database connections. Typically, you use the same driver (e.g., MySQL) for both connections. Here’s an example:

{
    'connections' => [
        'mysql' => [
            'read' => [
                'database' => 'database_name',
                'username' => 'slave_username',
                'password' => 'slave_password',
                'host' => 'slave_hostname',
            ],
            'write' => [
                'database' => 'database_name',
                'username' => 'master_username',
                'password' => 'master_password',
                'host' => 'master_hostname',
            ],
            'driver' => 'mysql',
        ],
    ],
}

Step 2: Configure Database Connection

You need to specify the connection settings for your application. In this example, we’ll configure the database key defined within the config/database.php file:

{
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'read' => [
                'host' => [
                    'slave_hostname1',
                    'slave_hostname2',
                ],
            ],
            'write' => [
                'host' => 'master_hostname',
            ],
            'sticky' => true, // Optional: Keep using the same slave for subsequent requests
        ],
    ],
}

Usage

Laravel provides various methods to execute read and write queries using the master/slave database connection.

Executing Read Queries

To execute read queries using the slave connection, you can use the read method on the database facade. Here’s an example:

\DB::connection('mysql')->read()->select('SELECT * FROM users');

Executing Write Queries

To execute write queries using the master connection, you can use the standard query builder methods without any special configuration.

\DB::table('users')->insert(['name' => 'John Doe']);

Transaction Handling

When executing transactions, you might want to ensure they are always performed on the master connection. You can use the onWriteConnection method for this purpose. Here’s an example:

\DB::transaction(function () {
    \DB::onWriteConnection()->table('users')->insert(['name' => 'John Doe']);
});

Additional Considerations

It’s important to note that the master/slave setup should be used with caution. There might be cases where read replicas are not perfectly in sync with the master due to replication delays. Additionally, some queries might need to be executed on the master for data consistency purposes.

You should carefully analyze your application requirements and choose the approprate database architecture.

Laravel Master Slave Database