Last updated January 27, 2012. Created by mikeryan on January 4, 2011.
Edited by naught101, pferlito, drewish. 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')
...
?>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. Otherwise, you can instruct Migrate to create the map table in the source database instead of the destination (Drupal) database).
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));
?>In the second case, when you construct the MigrateSQLMap object, you need to tell it explicitly what connection on which to create it by specifying it in the fourth argument:
<?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'
);
?>
Comments
To connect to an external database
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
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(
...
));
?>
For more discussion on this...
See #946306: Problems migrating between different databases
Good to see progress on the
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_mi
$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' => '',
);
i believe this is the correct
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) ;
}
Joining map table between two databases
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?
migration using dbtng_migrator module
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
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' => '',
);