Connecting to a remote database in D8

Question

I have the following params in settings.php to connect to an external database .

$databases['d8243']['default'] = array (
  'database' => 'drupal8',
  'username' => 'root',
  'password' => '123123',
  'prefix' => '',
  'host' => '192.168.1.243',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
);

And am using the following code to access some data from the db .

\Drupal\Core\Database\Database::setActiveConnection('d8243');
$query = "Select * from node__field_target_config_controller";
$result = db_query($query);
$obj = $result->fetchObject();
echo json_encode($obj);
exit;

My current server and the external db server are in a same network .

But am geting the following error :

exception 'PDOException' with message 'SQLSTATE[28000] [1045] Access denied for user 'root'@'192.168.1.245' [error] (using password: YES)' in /var/www-drupal/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php:149

Is there a different method to connect to remote db in D8 ? or how to do it ?

When i checked d6/d7 versions have an option to specify remote db like

$db_url = 'mysql://username:[email protected]/databasename';

how to do this in D8 ?

Answer 1

You can use like:

$other_database = array(
  'database' => 'drupal8',
  'username' => 'root', // assuming this is necessary
  'password' => '123123', // assuming this is necessary
  'host' => '192.168.1.243', // assumes localhost
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql', // replace with your database driver
);
// replace 'YourDatabaseKey' with something that's unique to your module
Database::addConnectionInfo('d8243', 'default', $other_database);
db_set_active('d8243');
// execute queries here
db_set_active()

You can access your other database like above. For reference check this link.

Answer 2

Set up your main database and external database in your settings file:

$databases['default']['default'] = array (
  'database' => 'mysite_local',
  'username' => 'root',
  'password' => 'root',
  'prefix' => '',
  'host' => 'localhost',
  'port' => '',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
);

$databases['migration_database']['default'] = array (
  'database' => 'drupal8',
  'username' => 'root',
  'password' => '123123',
  'prefix' => '',
  'host' => '192.168.1.243',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
);

Then in your code that needs to access the other database, you can switch between databases, using:

db_set_active('migration_database');

and to switch back:

db_set_active('default');