If your source data is in a database supported by the Drupal database API, such as MySQL or Postgres, and your web server has access to that database, you can migrate directly from that database with the Migrate module.

Defining the connection

First, you have to tell Drupal how to connect to the source database. The simplest way is to define the connection in your settings.php file. For example, with Drupal 7 you might add this to settings.php:

$databases['for_migration']['default'] = array(
  'driver' => 'mysql',
  'database' => 'migration_database',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'prefix' => '',
);

With Drupal 6, it may look like:

$db_url['for_migration'] = 'mysqli://username:password@localhost:3306/migration_database';

Alternatively, you may define the connection at runtime in your migration code (place this in your migration class):

    Database::addConnectionInfo('for_migration', 'default', array(
      'driver' => 'mysql',
      'database' => 'migration_database',
      'username' => 'username',
      'password' => 'password',
      'host' => 'localhost',
      'prefix' => '',
    ));

Querying the external database

Once you've defined the connection, when you define the query to be passed to MigrateSourceSQL, you need to tell the query what connection to use. Normally, the query would be built off db_select() using the default connection - to use an alternate connection replace your db_select() call with:

$query = Database::getConnection('default', 'for_migration')
           ->select('source_table', 'st')
...

Notice a gotcha here: Database::getConnection() has its arguments the other way round: $target as second.

If you're defining an explicit count query for the source, apply the same pattern to that as well.

Map tables

The migrate map table, which tracks the mappings between source IDs and destination IDs, is created by default in the Drupal (destination) database. There are performance advantages if the Migrate module can create queries joining your source query directly to the map table - this can be accomplished in two ways:

1. If it's possible to make joins between the Drupal database and your source database (e.g., under MySQL, if they are on the same server, your connections use the same username, and they're not prefixed), do nothing - it will just work.
2. If it's not possible to make joins between the source and Drupal database, you can instruct Migrate to create the map table in the source database instead of the destination (Drupal) database.
When you construct the MigrateSQLMap object, add a fourth argument to tell it explicitly the connection on which to create the mapping tables.

$this->map = new MigrateSQLMap($this->machineName,
  array(
          'source_id' => array('type' => 'varchar',
                           'length' => 255,
                           'not null' => TRUE,
                           'description' => 'Source ID',
                          )
        ),
        MigrateDestinationNode::getKeySchema(),
        'for_migration'
      );

If neither of these cases applies, you must tell the source class not to try to join the map table:

$this->source = new MigrateSourceSQL($query, array(), NULL, array('map_joinable' => FALSE));

Comments

BTMash’s picture

When connecting to an external database, you can define it in settings.php using the same methodology as your default database (though give your target a different name). As an example (note that this is for Drupal 7):

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'main_database',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'prefix' => '',
);

$databases['default']['for_migration'] = array(
  'driver' => 'mysql',
  'database' => 'migration_database',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'prefix' => '',
);

And to connect with it, you use $query = Database::getConnection('for_migration', 'default');

Note that the getConnection is actually flipped on getting the connection information.

Niklas Fiekas’s picture

Another nice way (barely different) is the getConnection method of MigrateSQLMap.

$this->map = new MigrateSQLMap(
  $this->machineName,
  array(...),
  MigrateDestinationXXXX::getSchema(),
  'for_migration' // defined the way you described it
);

$query = $this->map
  ->getConnection()
  ->select(..., ...)
  ->fields(..., array(
      ...
      ));
lookatthosemoose’s picture

This method worked best for me with tables on the same database, but drupal tables prefixed and source tables not prefixed.
Declared a new database connection like:

$databases['for_migration']['default'] = array(...)

Niklas Fiekas’s picture

Good to see progress on the documentaton.

Just a typo: A quote is missing in the Drupal 6 example $db_url['for_migration].

g089h515r806’s picture

$databases['default']['for_migration'] = array( 
  'driver' => 'mysql',  
  'database' => 'migration_database',  
  'username' => 'username',  
  'password' => 'password',  
  'host' => 'localhost',  
  'prefix' => '',
);

should be(?):

$databases['for_migration']['default'] = array( 
  'driver' => 'mysql',  
  'database' => 'migration_database',  
  'username' => 'username',  
  'password' => 'password',  
  'host' => 'localhost',  
  'prefix' => '',
);

Chinese drupal tutorials Think in Drupal

ndmaque’s picture

i believe this is the correct format for the connections, it works for me

settings.php

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'drupaldb',
  'username' => 'root',
  'password' => 'secret',
  'host' => 'localhost',
  'prefix' => '',
);
$databases['default']['ioflegacy'] = array(
  'driver' => 'mysql',
  'database' => 'a_legacy_db',
  'username' => 'root',
  'password' => 'secret',
  'host' => 'localhost',
  'prefix' => '',
);

and then i ran this query on the old legacy db

$result = db_query("SELECT name FROM users", array(), array("target" => "ioflegacy"));

foreach ($result as $record) {
  dpm($record) ; 
}
fuerst’s picture

While this will work the primary use of targets is for master/slave replication, see Database configuration. May be you get in trouble when some code tries to access your ioflegacy database while thinking it is a slave of the default database. Be safe by configuring ioflegacy using a connection key as described above like $databases['ioflegacy']['default'] ….

spyrkob’s picture

Don't think that's correct:
1. If it's possible to make joins between the Drupal database and your source database (e.g., under MySQL, if they are on the same server, your connections use the same username, and they're not prefixed), do nothing - it will just work

The map table is created in the Drupal (destination) DB, but since the source query doesn't add the schema name to the joined table, the query expects that table to be in the same DB as source tables. You need to add the DB name either to source query or add it in the join statement in MigrateSourceSQL.

Or did I miss something?

bipsid’s picture

I used the following in settings.php and this worked (from oracle to mysql). Testing the dbtng_migrator module

$databases['mig_to_mysql']['default'] = array (
'database' => 'mysql1_dev',
'username' => 'mysql1_dev',
'password' => 'mysql1_dev',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => false,
);

$databases['default']['default'] = array (
'database' => 'XE',
'username' => 'drupal',
'password' => 'drupal',
'host' => 'localhost',
'port' => '1521',
'driver' => 'oracle',
'prefix' => '',
);

jmm42’s picture

Has anyone attempted to query from a mysql to a sql?

$databases['mig_to_mysql']['default'] = array (
'database' => 'mysql1_dev',
'username' => 'mysql1_dev',
'password' => 'mysql1_dev',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => false,
);

$databases['default']['default'] = array (
'database' => 'XE',
'username' => 'drupal',
'password' => 'drupal',
'host' => 'localhost',
'port' => '1521',
'driver' => 'sql',
'prefix' => '',
);

Ken Ficara’s picture

...because I've been banging my head against this for an hour or so. My setup meets all the conditions of case 1 -- same server, both online, same username, same password, not prefixed -- and it most certainly does not "just work." I get an error saying the column for the ON clause of the left join is not found, and when you look at the query, it indeed has no schema name in front of it, so I don't see any way that it could work. Other than setting map_joinable to false, I see no simple way to fix this.

commonpike’s picture

"Alternatively, you may define the connection at runtime in your migration code (place this in your migration class):"

- where - in the constructor ?

*-pike

*-pike

tinflute’s picture

also a word of caution: I'm trying to get cross-DB migrate to work with source:postgres and destination:mysql on a WAMP server. And i had issues with the postgresql drivers that come with apache/php. So if you keep getting database errors, you might want to debug your drivers, starting in php.ini and apache.conf / httpd.conf files.

paralax’s picture

Cross-Database migration just doesn't work unfortunately. Either the Map table is created with no destid1 field or it creates no map table at all.

GaryWong’s picture

As per https://drupal.org/node/1811382#comment-6595686 you need to ensure the right priv's. I had two separate account ID's and could log onto my source DB with its account credentials, but during Migrate kept getting:

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to use...

May be obvious to others, but I spun my wheels for a bit there before sparking up mysql as root and:

mysql> grant all privileges on *.* to 'target-user'@'localhost' identified by 'target-password';

HTH

mysere’s picture

Hi,
I have implemented migration from MySQL to Postgres in Drupal 8 with dbtng migrator module.
I have a question there is any module in Drupal 8 which supports this feature?
Please let me know how to migrate d8 database from MySQL to PostgreSQL

josina08’s picture

were you able to resolve this?

i have trying to migrate my db for the last week to no success