Answers.Body: <p>You may want to move log tables from main database to specially created database via settings in <strong>civicrm.settings.php</strong>.  Of course, new empty database has to be created first. After that please find this in <strong>civicrm.settings.php</strong>: </p>
  
  <pre><code>/**
   * 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);
  } 
  </code></pre>
  
  <p>when replace </p>
  
  <pre><code>  define('CIVICRM_LOGGING_DSN', CIVICRM_DSN);
  </code></pre>
  
  <p>to </p>
  
  <pre><code>  define('CIVICRM_LOGGING_DSN','mysql://DBUSER:DBPASSWORD@DBHOST/LOGS_DBNAME?new_link=true');
  </code></pre>
  
  <p>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.  </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-05T20:45:44.673Z
Answers.LastActivityDate: 2015-11-05T20:45:44.673Z
Answers.UserDisplayName: yurg
Answers.Body: <p>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?</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-06T13:56:37.847Z
Answers.LastActivityDate: 2015-11-06T13:56:37.847Z
Answers.UserDisplayName: DaveFF
Answers.Body: <p>This can be done on the commandline. You can pass a list of tables to <code>mysqldump</code> and omit the tables starting with <code>log_</code>.</p>
  
  <p>(Below, <em>testdb</em> is the name of the DB we're using. You may want to add other parameters, eg auth details to connect.)</p>
  
  <p>To get the list of tables you <em>want</em>, exclude the <code>log_</code> tables using <code>grep</code>. (Seems you can't <em>SHOW TABLES NOT LIKE</em>.)</p>
  
  <pre><code>mysql -ss -e 'SHOW TABLES' testdb | grep -v '^log_'
  </code></pre>
  
  <p>To use this list of tables with mysqldump, use Bash's <code>$( )</code> construct to expand the output of the first command into parameters for mysqldump.</p>
  
  <pre><code>mysqldump testdb $( mysql -ss -e 'SHOW TABLES' testdb | grep -v '^log_' )
  </code></pre>
  
  <p>To exclude also the trigger information, add mysqldump's <code>--skip-triggers</code> parameter.</p>
  
  <pre><code>mysqldump --skip-triggers testdb $( mysql -ss -e 'SHOW TABLES' testdb | grep -v '^log_' )
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-07-07T14:40:13.403Z
Answers.Text: You might then find it useful to do: `mysqldump testdb --no-data $( mysql -ss -e 'SHOW TABLES LIKE "log_%"' testdb )` which will give you a SQL file containing the schema (all the CREATE TABLE log_... commands) for the log tables, but not their data (rows) - otherwise you might find Civi complains it can't find the log tables.
Answers.UserDisplayName: artfulrobot
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-25T12:39:22.120Z
Answers.LastActivityDate: 2016-03-25T12:39:22.120Z
Answers.UserDisplayName: Chris Burgess
Body: <p>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.</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-11-05T16:47:13.450Z
Comments.Text: 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.
Comments.UserDisplayName: Jaap Jansma - CiviCooP
Tags: <database><migration><logging>
Title: How to migrate database without logs
ThreadText: # How to migrate database without logs
  
  (Asked by: username_0 on 2015-11-05)
  
  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.
  
  **Tags:** database, migration, logging
  
  > **Comments:**
  > **username_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.  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_4 on 2016-03-25. Score: 3 | Accepted answer
  
  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_' )
  
  > **Comments:**
  > **username_5**: You might then find it useful to do: `mysqldump testdb --no-data $( mysql -ss -e 'SHOW TABLES LIKE "log_%"' testdb )` which will give you a SQL file containing the schema (all the CREATE TABLE log_... commands) for the log tables, but not their data (rows) - otherwise you might find Civi complains it can't find the log tables.  
  
  ---
  
  ### Answer by username_2 on 2015-11-05. Score: 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 by username_3 on 2015-11-06. Score: 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?
  
  
  ---
  
  