6

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.

  • 1
    Probably you should create a dump which does not include the data from the log tabel but does include the structure of the log tables. So probably creating two dumps. One with only the data structure. The second with the data except for the log tables. – Jaap Jansma - CiviCooP Nov 5 '15 at 16:47
2

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_' )
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.

1

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?

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.