Master table

The master/slave configuration uses a master table that allows SELECT, UPDATE, and DELETE statements. These statements modify the table or write to it. There may also be multiple master tables. Each master table is kept continually synchronized: changes made to any table needs to be communicated to the master table.

Slave table

The slave table is a slave to the master. It depends on the master table for its changes. SQL clients can only perform read operations (SELECT) from it. There may also be multiple slaves that depend on one or more multiple master tables. The master table communicates all of its changes to all the slaves. The following diagram shows the basic architecture of a master/slave setup:

Slave table

Master and slave (read/write setup)

This continual synchronization adds slight overhead to the database structure; however, it presents important advantages:

Since only SELECT statements can be performed on the slave table while INSERT, UPDATE, and DELETE statements can be performed on the master table, the slave table is free to accept many SELECT statements freely, without having to “wait” for any operations involving the same rows to finish.

An example of this would be a currency exchange rate or stock price table. This table would be continually updated in real time with the latest values, possibly even many times per second. Obviously, a website that allows many users to access this information could potentially have thousands of visitors. Also, the web page used to display this data may make continual multiple requests per user.

Performing many SELECT statements would be slightly slower when there are UPDATE statements that need to access the same data at the same time.

By using a master/slave configuration, the SELECT statements would be performed only on the slave table. This table receives only the data that has changed in an extremely optimized way.

In plain PHP using a library such as mysqli, there could be two database connections configured:

$master=mysqli_connect('127.0.0.1:3306','dbuser','dbpassword','mydatabase');
$slave=mysqli_connect('127.0.0.1:3307','dbuser','dbpassword','mydatabase');

In this simplified example, the slave is set up on the same server machine. In a real application, it would most likely be set up on another server machine to take advantage of separate hardware.

Then, all of the SQL statements which involve a write statement would be performed on the slave and read would be performed on the master.

This would add some overhead to the programming efforts, as a different connection would need to be passed into each SQL statement:

$result= mysqli_real_query($master,"UPDATE exchanges set rate='1.345' where exchange_id=2");
$result= mysqli_query($slave,"SELECT rate from exchanges where exchange_id=2");

In the preceding code example, it would be prudent to remember which SQL statements should be used for the master and which SQL statements should be used for the slave.

Configuring read/write

As stated before, code written in Eloquent is converted into fluent query-builder code. This code is then converted to PDO, which is a standard wrapper around the various database drivers.

Laravel provides the ability to manage master/slave configurations though its read/write configuration. This allows programmers to write Eloquent and fluent query-builder code without having to worry about whether the queries will be executed on the master or slave table. Also, a software project that starts out with a non-master/slave configuration and later needs to scale up to a master/slave setup will only need to change one aspect of the database configuration. The database configuration file is located at config/database.php.

As an element of the connections array, an entry with the key mysql will be created with the following configuration:

'connections' =>
'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
     'password'  => 'slave-Passw0rd', 
    ],
    'write' => [
        'host' => '196.168.1.2',
    'username'  => 'dbhostusername'    
    ],
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'dbusername',
    'password'  => 's0methingSecure',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
],

The read and write represent slave and master respectively. Since the parameters cascade, if the username, password, and database name are the same, then only the IP address of the host name needs to be listed. However, any values can be overridden. In this example, the read has a password that is different from that of the master and the write has a username that is different from the slave.