1

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.

2

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.

|improve this answer|||||
  • 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_vlad Nov 27 '16 at 22:25
  • Sure, moving entire clients (such as report-writers, query-only UIs, etc) to the Slave is simple, safe, and useful. – Rick James Nov 28 '16 at 20:10
  • Can you please provide some insight into stackoverflow.com/questions/43849283/… ? – emilly Jun 10 '17 at 14:24
  • The link is now dba.stackexchange.com/questions/175960/… . – Rick James Jun 27 '17 at 0:03
1

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.

|improve this answer|||||
1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.