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
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):
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.
Only this one worked for me
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(...)
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
should be(?):
Chinese drupal tutorials Think in Drupal
i believe this is the correct
i believe this is the correct format for the connections, it works for me
settings.php
and then i ran this query on the old legacy db
Do not use connection target
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'] …
.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' => '',
);
I don't think you missed anything...
...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
"Alternatively, you may define the connection at runtime in your migration code (place this in your migration class):"
- where - in the constructor ?
*-pike
*-pike
yes in the constructor
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.
same here...
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.
Ensure your target DB's account has access to the source DB
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
Migrate database from MySQL to Postgres in drupal8
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
Migrate database from MySQL to Postgres in drupal8
were you able to resolve this?
i have trying to migrate my db for the last week to no success