databasecakephp-2.1

cakePHP 2.x connecting to a remote database using key


I need to get connected with a mysql database on a different server, i have the RSA key to connect to the server and i also have all the credentials needed to connect to the database, and using this following codes, i am able to get connected perfectly

try {
    $this->db = new PDO("mysql:host=$this->host;port=$this->port;dbname=$this->dbname", $this->sqlUser, $this->sqlPass);
} catch (Exception $e) {
    shell_exec("ssh -i /path/to/rsa/key/the_key myusername@some.ip.address -L3307:localhost:3306 -N");
};

try {
    $this->db = new PDO("mysql:host=$this->host;port=$this->port;dbname=$this->dbname", $this->sqlUser, $this->sqlPass);
} catch (Exception $e) {
    die($e->getMessage() . " (line:" . __LINE__ . ")");
};

however, i am building the application using cakePHP 2.x, how can i connect to that remote server and database using cakePHP's configuration?

Thanks


Solution

  • Okay i manage to do this by creating an extended class called FarAwayMysql.php under app/Model/DataSource/Database

    App::uses('DboSource', 'Model/Datasource');
    
    class FarAwayMysql extends DboSource {
    public $description = "Faraway MySQL DBO Driver";
    
    protected $_baseConfig = array(
        'persistent' => true,
        'host' => 'localhost',
        'login' => 'root',
        'password' => '',
        'database' => 'cake',
        'port' => '3306',
        'rsa' => '',
        'remote_username' => '',
        'remote_ip' => ''
    );
    protected $_connection = null;
    
    protected $_useAlias = true;
    
    public function connect() {
        $config = $this->config;
        $this->connected = false;
        try {
            $flags = array(
                PDO::ATTR_PERSISTENT => $config['persistent'],
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            );
            if (!empty($config['encoding'])) {
                $flags[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $config['encoding'];
            }
            if (empty($config['unix_socket'])) {
                $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']}";
            } else {
                $dsn = "mysql:unix_socket={$config['unix_socket']};dbname={$config['database']}";
            }
            $this->_connection = new PDO(
                $dsn,
                $config['login'],
                $config['password'],
                $flags
            );
            $this->connected = true;
        } catch (PDOException $e) {
            echo "ssh -i {$config['rsa']} {$config['remote_username']}@{$config['remote_ip']} -L3307:{$config['host']}:3306 -N";
            shell_exec("ssh -i {$config['rsa']} {$config['remote_username']}@{$config['remote_ip']} -L3307:{$config['host']}:3306 -N");
        }
    
    
        try {
            $flags = array(
                PDO::ATTR_PERSISTENT => $config['persistent'],
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            );
            if (!empty($config['encoding'])) {
                $flags[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $config['encoding'];
            }
            if (empty($config['unix_socket'])) {
                $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']}";
            } else {
                $dsn = "mysql:unix_socket={$config['unix_socket']};dbname={$config['database']}";
            }
            $this->_connection = new PDO(
                $dsn,
                $config['login'],
                $config['password'],
                $flags
            );
            $this->connected = true;
        } catch (PDOException $e) {             
            throw new MissingConnectionException(array(
                'class' => get_class($this),
                'message' => $e->getMessage()
            ));
        }
    
        $this->_useAlias = (bool)version_compare($this->getVersion(), "4.1", ">=");
    
        return $this->connected;
    }
    }
    

    by adding 3 new configs

    protected $_baseConfig = array(
        'persistent' => true,
        'host' => 'localhost',
        'login' => 'root',
        'password' => '',
        'database' => 'cake',
        'port' => '3306',
        'rsa' => '',
        'remote_username' => '',
        'remote_ip' => ''
    );
    

    and then on your app/Config/database.php, it would be

    public $faraway = array(
        'datasource' => 'Database/FarAwayMysql',
        'persistent' => false,
        'host' => 'localhost',
        'login' => 'faraway_username',
        'password' => 'faraway_password',
        'database' => 'faraway_db_name',
        'port' => '',
        'rsa' => 'path/to/rsa/key',
        'remote_username' => 'your_username',
        'remote_ip' => 'your.ip.address'
    );