7

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?

6

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>
|improve this answer|||||
  • Thanks! I installed PV and it's tracking its progress. I was wrong, the initial db was 1GB compressed, uncompressed it's around 6. Probably why it's taking so long! – James Ives Aug 13 '15 at 19:59
5

The Backup and Migrate module has a feature to exclude selected tables, such as:

  • all cache_* tables
  • ctools_views_cache
  • ctools_object_cache
  • flood
  • history
  • queue
  • semaphore
  • watchdog
  • search_index (usually saves a lot of database size!)

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
|improve this answer|||||
5

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)
|improve this answer|||||
5

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:

|improve this answer|||||
0

You seem to have resolved the issue, but here comes one for me:

drush sql-dump > mydb.sql

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.