There are times when your application is at a stage where one database for read & write is not enough and you might want to use separate databases for read & write. Luckily Laravel provides you with the flexibility to setup a separate database connection for read & write.
Let's say you have two MySQL databases with same credentials and you want to setup one for read and other for write. In you config/database.php
file change the mysql
configuration as follow:
'mysql' => [
'read' => [
'host' => '192.168.1.1',
],
'write' => [
'host' => '196.168.1.2',
],
'sticky' => true,
'driver' => 'mysql',
'database' => 'database_name',
'username' => 'root',
'password' => 'secret',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
],
Let's say you want to setup two read databases, the configuration will be as follow:
'mysql' => [
'read' => [
'host' => [
'192.168.1.1',
'192.168.1.2',
],
],
'write' => [
'host' => [
'196.168.1.3',
],
],
'sticky' => true,
'driver' => 'mysql',
'database' => 'database_name',
'username' => 'root',
'password' => 'secret',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
],
Now, let's assume you have two different MySQL servers with different credentials then the configuration will be as follow:
'mysql' => [
'read' => [
'host' => '192.168.1.1',
'username' => 'root',
'password' => 'secret',
'database' => 'database_name',
],
'write' => [
'host' => '192.168.1.2',
'username' => 'root',
'password' => 'secret',
'database' => 'database_name',
],
'sticky' => true,
'driver' => 'mysql',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
],
Now, if you want to setup two read databases with different credentials and the configuration will be as follow:
'mysql' => [
'read' => [
[
'host' => '192.168.1.1',
'username' => 'root',
'password' => 'secret',
'database' => 'database_name',
],
[
'host' => '192.168.1.2',
'username' => 'root',
'password' => 'secret',
'database' => 'database_name',
],
],
'write' => [
'host' => '192.168.1.2',
'username' => 'root',
'password' => 'secret',
'database' => 'database_name',
],
'sticky' => true,
'driver' => 'mysql',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
],
For more configuration and options details you can refer to Laravel database connections.
Top comments (4)
This is what I was searching for! Thank you!!
I am glad that it was helpful for you.
How can I do the same for MS SQL SERVER with Asp .ner core?
Hi @wisam_saif I am not that familiar with Asp.net, however this might be of help: stackoverflow.com/a/48321008