AWS RDS Read Replica Load Balancing by defining multiple databases in PHP
With high traffic and more read requests, it is always advisable to split read and writes where our write queries goes to master database and read queries goes to slaves i.e., read replicas in AWS. Amazon provides option of creating 5 read replicas for our master database. When we have multiple read replicas, we need to load balance read requests between them. Already few people have made a feature request to Amazon to introduce load balancer at read replica level. Also, there are multiple solutions available to achieve this i.e., using HAProxy or MySQL Proxy.
Below mentioned is sample code :
(Disclaimer: I’m not a php expert. In fact this is the first time, I have written code in php. Please forgive me if coding standards are not met)
<?php
$slaves=array(
       array( //Slave 1
               'name' => 'read-replica-1',
               'server' => 'read-replica-1.c6nho91ui7yl.us-west-1.rds.amazonaws.com',
               'user' => 'master',
               'password' => 'master@password',
               'port' => '3306'),
       array( //Slave 2
               'name' => 'read-replica-2',
               'server' => 'read-replica-2.c6nho91ui7yl.us-west-1.rds.amazonaws.com',
               'user' => 'master',
               'password' => 'master@password',
               'port' => '3306'),
       );
$slave = $slaves[array_rand($slaves)];
$con = mysqli_connect($slave['server'], $slave['user'], $slave['password'], "phptest");
// Check connection
if (mysqli_connect_errno())
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM records");
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Name</th>
<th>Read Replica Name</th>
</tr>";
while($row = mysqli_fetch_array($result))
 {
 echo "<tr>";
 echo "<td>" . $row['id'] . "</td>";
 echo "<td>" . $row['str'] . "</td>";
 echo "<td>" . $slave['server'] . "</td>";
 echo "</tr>";
 }
echo "</table>";
mysqli_close($con);
?>
 If you access this code on your browser, you will see something like this mentioned below :
Above mentioned output displays name of the read replica i.e., read replica from which other two records were fetched. If we refresh our screen, we will witness different read replica name which means request is served from different read replica.
Anya is live and ready to show you everything. Watch her strip, dance, and perform exclusive shows just for you. Interact in real-time and make your fantasies come true.
✓ Live Streaming✓ Interactive Chat✓ Private Shows✓ HD Quality✓ Free Actions
Free to watch • No registration required • HD streaming
