I'm using PHP/Laravel and MySQL databases.
Briefly, I want to use slave databases for reads and master database for writes.
What Laravel config, Laravel package, or database tool do you suggest to handle my issue?
0
Add a comment
|
3 Answers
Dangerous. Read about "Critical Read". If you blindly send all SELECTs to a Slave, you could screw up transactions on the master. Or you could miss data when replication is "behind".
Instead, modify your application to connect to either the Master or to some Slave, based on whether it is safe to get the data just from a Slave.
answered Nov 27, 2016 at 22:17
-
just for not create similar answer - often just move report loading from Master to Slave resolve a lot of narrow necks on the Master. For re-direct query You can use or ProxySQL or MaxScale or few other load balancer, which all work +- similar, one port point to Master, other to Slave (like 3306, 3307)– a_vladCommented Nov 27, 2016 at 22:25
-
Sure, moving entire clients (such as report-writers, query-only UIs, etc) to the Slave is simple, safe, and useful. Commented Nov 28, 2016 at 20:10
-
Can you please provide some insight into stackoverflow.com/questions/43849283/… ?– emillyCommented Jun 10, 2017 at 14:24
-
The link is now dba.stackexchange.com/questions/175960/… . Commented Jun 27, 2017 at 0:03
To config Laravel to split reads and writes you can edit config/database.php this way:
'connections' => [
'mysql' => [
'driver' => 'mysql',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'read' => [
'host' => env('DB_READ_HOST', 'localhost'),
'database' => env('DB_READ_DATABASE', ''),
'username' => env('DB_READ_USERNAME', ''),
'password' => env('DB_READ_PASSWORD', ''),
],
'write' => [
'host' => env('DB_WRITE_HOST', 'localhost'),
'database' => env('DB_WRITE_DATABASE', ''),
'username' => env('DB_WRITE_USERNAME', ''),
'password' => env('DB_WRITE_PASSWORD', ''),
],
],
//...
]
As for the point which @rick-james mentioned you can use MySQL Router to balance requests between some databases so you can use both master and slave for reads.
Yes, you can use ProxySQL for this use case. https://proxysql.blogspot.com/2015/09/proxysql-tutorial-setup-in-mysql.html scroll down to the part where it says Read-Write split.
