Because of pressure on my servers and variety of applications on my servers I was wonder if can I have application or proxy to select from slave database and insert into master.
I'm using PHP/Laravel and MySQL database.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.
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.