How to migrate database without logs

Question

I’d like to move my CiviCRM installation from one Drupal server to another. But I enabled logging in the current server and I don’t want that data to be transferred to the new server. How can I delete the logs before I migrate the database? Or, if there’s a way to migrate without the logs that doesn’t involve deleting them, that would be equally helpful.

Answer 1

This can be done on the commandline. You can pass a list of tables to mysqldump and omit the tables starting with log_.

(Below, testdb is the name of the DB we're using. You may want to add other parameters, eg auth details to connect.)

To get the list of tables you want, exclude the log_ tables using grep. (Seems you can't SHOW TABLES NOT LIKE.)

mysql -ss -e 'SHOW TABLES' testdb | grep -v '^log_'

To use this list of tables with mysqldump, use Bash's $( ) construct to expand the output of the first command into parameters for mysqldump.

mysqldump testdb $( mysql -ss -e 'SHOW TABLES' testdb | grep -v '^log_' )

To exclude also the trigger information, add mysqldump's --skip-triggers parameter.

mysqldump --skip-triggers testdb $( mysql -ss -e 'SHOW TABLES' testdb | grep -v '^log_' )

Answer 2

You may want to move log tables from main database to specially created database via settings in civicrm.settings.php. Of course, new empty database has to be created first. After that please find this in civicrm.settings.php:

/**
 * CiviCRM Logging Database
 *
 * Used to point to a different database to use for logging (if desired). If unset defaults to equal CIVICRM_DSN.
 * The CIVICRM_DSN user needs to have the rights to modify the below database schema and be able to write to it.
 */
if (!defined('CIVICRM_LOGGING_DSN')) {
  define('CIVICRM_LOGGING_DSN', CIVICRM_DSN);
} 

when replace

  define('CIVICRM_LOGGING_DSN', CIVICRM_DSN);

to

  define('CIVICRM_LOGGING_DSN','mysql://DBUSER:DBPASSWORD@DBHOST/LOGS_DBNAME?new_link=true');

not sure if "new_link=true" is needed though. This automagically moves all LOG_ tables from the main database to new database. Thus all LOG_ tables in main database could be deleted; main database is clean now and could be moved to new server (guess it's worth making same trick at new server). In theory, there could be some performance benefits as well, but not sure.

Answer 3

Take a full backup (good idea anyway, but particularly if you're interested in keeping the old logs somewhere offline), disable logging, do the transfer without the log_ tables (or drop them first), then re-enable logging on the new server?