Using Amazon route 53 to create DNS failover for MYSQL

AWS Route 53 allows you to set a timer to periodically check the connection status of MYSQL via TCP or HTTP protocol. There are many approaches to achieve this, such as LAMP (PHP + Apache), Java + Tomcat, and C/C++ Sock Programming.
I used LAMP method to save time and efforts in terms of development and testing.

Create a health check  trigger by the following settings. 

IP Address : 192.168.0.1

Port  :  80

Site :  testdb.php

domain name: test.com

Create a testdb.php to check mysql connection.
If the connection is fine, then respond 200 OK.
Otherwise respond 500 error code to AWS Route 53. 

testdb.php 

<?php

/**

 * Description of Connection

 */

class Connection {

/** Instance */

private  static $_singleton = null;

// DB Settings

    private $db;

private $dbHost;

private $dbName;

private $dbUserName;

private $dbPassword;

private $dbPort = "3306";

/**

*　DB Instance

*/

public static function getInstance() {

if(self::$_singleton == null) {

self::$_singleton = new Connection();

}

return self::$_singleton;

}

private function __construct(){

}

  

public function createConnection($host, $dbname, $username, $password, $port = '3306')

{

$this->dbHost = $host;

$this->dbName = $dbname;

$this->dbUserName = $username;

$this->dbPassword = $password;

try

        {

            $this->db = new PDO('mysql:host='.$this->dbHost.';port='.$this->dbPort.';dbname='.$this->dbName.'', $this->dbUserName, $this->dbPassword);

$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$sql = "SELECT host from user LIMIT 1";

$result = $this->db->query($sql);

foreach ($result as $row) {

echo "SUCC";

break;

}

 

$this->db = null; // close the database connection

        }

        catch (PDOException $e)

        {

            throw new Exception("Connection to database  failed. (".$e->getMessage().")");

die();

        }

    

}

}

/**

 * Initial Database Connection

 *DbManager::getInstance()->dbConnection($DBi, $db1, $DBu, $DBp)

* @param $DBi  Database server IP

* @param $db1  Database name

* @param $DBu  user name

* @param $DBp  user passwoard

*/

// DEV 

$DBi   = '127.0.0.1';

$db1   = 'mysql';

$DBu   = 'account';

$DBp   = 'password';

// Initial DB Connection

Connection::getInstance()->createConnection($DBi, $db1, $DBu, $DBp);

?>

Create a Primary hosted zone for test.com.

Now, in the Edit Record Set panel on the right side of the page, do the following:

1. Set the TTL to 60 seconds. This limits the amount of time this DNS record will be cached within the Internets DNS system, which means that there will be a shorter delay between the time failover occurs and the time that end users begin to be routed to your backup site.
2. Set the Routing Policy to Failover.
3. Select Primary as the Failover Record Type.
4. Select Yes for Associate Record Set with Health Check.
5. Select the health check to associate with this record. In the drop-down that appears, you should see the health check we just created. Select this health check.
6. Click Save Record Set.

Create a Slave Record Set. 

1. Set the TTL to 60 seconds. This limits the amount of time this DNS record will be cached within the Internets DNS system, which means that there will be a shorter delay between the time failover occurs and the time that end users begin to be routed to your backup site.
2. Set the Routing Policy to Failover.
3. Select Slave as the Failover Record Type.
4. Select NO for Associate Record Set with Health Check.
5. Select NO for health check to associate with this record. 
6. Click Save Record Set.

The switching time between MYSQL Master DB and Slave DB details as below.

test.com -> DB Master IP Address

DB Master  or Appache is down

test.com  --> DB Slave  IP Address

time : 30 sec 

DB Master is UP.

test.com  --> DB Master  IP Address

time : 60 sec    

DNS failover approach is not a perfect solution for MYSQL HA, because it will take at
least 60 sec to switch database. It means the system will have 60 sec of down time.
An architecture of  a load balancer and a MYSQL cluster in sync mode might be superior to this.