EJBCA Wiki

MySQL Tuning

Exporting old logentries from the database

Some old entries can be removed from the database, like old CRLs and old posts from LogEntryData can be exported.

1. Get a grip of the span of LogEntryData ids start and end:

• select id from LogEntryData order by id asc limit 1;
• select id from LogEntryData order by id desc limit 1;

2. Dump the span to file except the 100 000 last ones:

• mysqldump —user=ejbca -p —where="id <= (end-id - start-id - 100000) " ejbca LogEntryData > LogEntryData_start-id_end-id.sql

3. OPTIONAL: Create a new db and test that the backup can be impoted

• $ sudo mysql
• > create database ejbcalogtest;
• $ sudo mysql ejbcalogtest < LogEntryData_start-id_end-id.sql
• $ sudo mysql ejbcalogtest
• > select count(*) from LogEntryData;
• > drop database ejbcalogtest

4. Remove the exported segment from the database if everything went well

• delete from LogEntryData where id <= (end-id - start-id - 100000);

Removing old CRLs

1. Display all CAs

• select subjectDN from CAData;

2. Find highest cRLNumber for each CA

• select cRLNumber from CRLData where issuerDN='CN=AdminCA1,O=EJBCA Sample,C=SE' order by cRLNumber DESC limit 1;

3. Remove all older CRLs for each CA

• delete from CRLData where issuerDN='CN=AdminCA1,O=EJBCA Sample,C=SE' and cRLNumber < lastNumber;

4. Verify that CRLData contains as many posts as the number of CAs

• select count(*) from CRLData;

5. If not, list any CA that has issued an CRL to see if there are any old CA CRLs left.

• select distinct issuerDN from CRLData;