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
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'
);