Cross-database migrations

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