Last updated June 11, 2013. Created by mikeryan on January 4, 2011.
Edited by StryKaizer, axel.rutz, naught101, pferlito. Log in to edit this page.

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:

<?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:

<?php
$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):

<?php
    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:

<?php
$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.

<?php
$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:

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

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

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.

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

<?php
$this
->map = new MigrateSQLMap(
 
$this->machineName,
  array(...),
 
MigrateDestinationXXXX::getSchema(),
 
'for_migration' // defined the way you described it
);
$query = $this->map
 
->getConnection()
  ->
select(..., ...)
  ->
fields(..., array(
      ...
      ));
?>

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(...)

Good to see progress on the documentaton.

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

$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

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

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'] ….

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?

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

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

...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.

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

- where - in the constructor ?

*-pike

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.

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.

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

Gary

---
Victoria, BC
Canada