I'm using Vagrant and OracleVM to run Drupal 7. I've got the site up and running and I'm trying to get my database imported. So far I've run drush cc and cleared the drush cache, and then I did
drush sql-cli < mydb.sql
The drush command is taking a super long time and I'm not sure if it's even doing anything. The database file is just under a gigabyte.
What can I do to get this working, or is there a better method?
I'm not sure how much overhead drush puts on top of large SQL imports, I've only used it for smaller imports and queries. You can try the mysql client as an alternative that would remove the drush overhead.
If you can install pipe viewer on your VM you can view the progress of the command consuming the import.
pv mydb.sql | drush sql-cli
Or without the drush overhead
pv mydb.sql | mysql -u <DB_USER> -p<DB_PASS> <YOUR_DB>
Smaller databases will import more quickly, so drush cc all and drush watchdog delete all will speed things up considerably, if dropping these is an option.
Also, to expand on Shawn Conn's answer, you can use pipe viewer without the Drush overhead, and still use Drush to look up the database credentials for you if you use drush sql-connect instead of drush sql-cli. Just use $() to execute the mysql line that drush sql-connect prints out:
pv mydb.sql | $(drush sql-connect)
The Backup and Migrate module has a feature to exclude selected tables, such as:
The issue about Default tables/data to exclude, also includes an interesting list of tables to not be migrated (pick what fits for you), i.e. (quote from it):
When creating a backup profile, the module by default sets the following tables to have their data excluded according to issue #209647: Exclude advice:
- cache
- cache_filter
- cache_calendar_ical
- cache_menu
- cache_page
- cache_views
- sessions
- search_dataset
- search_index
- search_keywords_log
- search_total
- watchdog
- accesslog
- devel_queries
- devel_times
It's better to trim the big database on dump by using drush sql-dump to dump the SQL file. This gives you the advantage to specify the additional options to skip the specific tables when dumping (such as cache or migrate tables).
This can be either specified in the command line (see: drush help sql-dump) or configure it in your drushrc file as shown in example.drushrc.php like:
$options['structure-tables']['common'] = array('cache', 'cache_*', 'history', 'search_*', 'sessions', 'watchdog');
$options['skip-tables']['common'] = array('migration_*');
or per host basis like if you're using sql-sync or sql-dump between remotes:
$aliases['global'] = array(
// These options will only be set if the alias is used with the specified command.
'command-specific' => array(
'sql-sync' => array(
'structure-tables-key' => 'common',
'skip-tables-key' => 'common',
'structure-tables' => array(
// You can add more tables which contain data to be ignored by the database dump
'common' => array('cache', 'cache_*', 'history', 'search_*', 'sessions', 'watchdog'),
),
'skip-tables' => array(
'common' =>array('migration_*'),
),
),
'sql-dump' => array(
'ordered-dump' => FALSE,
'structure-tables-key' => 'common',
'skip-tables-key' => 'common',
),
), // end: command-specific
);
Then you can load this settings into your host via:
$aliases['stage'] = array(
// ...
) + $aliases['global'];
Then you can try something like:
drush @source sql-dump | drush @self sql-cli
To monitor the progress, you can add pipe viewer (pv) in between (like ... | pv | ...).
See also:
You seem to have resolved the issue, but here comes one for me:
drush sql-dump > mydb.sql
