Problem

How to shrink the SecureTransport MySQL database size or how to clean up the SecureTransport MySQL database and prevent it from growing in future. Below is outlined the SecureTransport MySQL database maintenance and size shrink procedure for ST5.3.3 and above. For older version please use article KB174307

Resolution

1. General information
2. Before you begin
   
   1. Create a SecureTransport backup under Linux and UNIX
   2. Create a SecureTransport backup under Windows
   3. Additional notes
3. MySQL DB shrink instructions for UNIX and Linux
4. MySQL DB shrink instructions for Windows

General information

SecureTransport 5.3.3 and above uses InnoDB storage engine within its MySQL deployment. More information on the specific background behind the design of the table space is available in article KB 161787 that covers it extensively.

As explained in article KB 161787, SecureTransport MySQL DB cannot natively shrink on records deletion. As a direct consequence of this limitation the database has to be cleaned and maintained manually since it grows over time. This article provides information on how to shrink the SecureTransport MySQL database size to a minimum, while preserving all SecureTransport configuration and account data. Additionally recommended step in this regard is to configure logging in flat files, but this is not a subject of this article and is covered in the SecureTransport's Administrator Guide, chapter Redirect log4j output from the database.

IMPORTANT NOTE:

This procedure will remove all Server Log and File Tracking information!

IMPORTANT NOTE:

While native MySQL functions are used, the instructions outlined in this article as well as the automated scripts are not officially certified. These are to be used with great caution and customers are recommended that they MUST ALWAYS create a backup before proceeding with execution of either the provided script or the manual instructions below.

The time duration of the process strongly depends on the size of the database, speed of the disks, filesystem utilization, processing power, and is hardly predictable. The process in most cases should not take longer than one hour.

Before you begin?

Make sure you have a full (complete) backup of $FILEDRIVEHOME (or /opt/Axway/SecureTransport) and that the backup is created while SecureTransport is fully stopped and not operational.

Create a SecureTransport backup under Linux and UNIX:

Stop all SecureTransport services: /opt/Axway/SecureTransport/bin/stop_all

Go to the root directory: cd /

Archive the necessary ST files (this is a single line command):

tar -cvzPf /tmp/st_full_backup.tgz /opt/Axway/SecureTransport/ /etc/fd/ --exclude=/opt/Axway/SecureTransport/var/db/hist/*

Move the archive to a safe location

For an automated backup procedure refer to the attached st_backup.sh script

Create a SecureTransport backup under Windows:

Stop all SecureTransport services: open CMD and type stop_all

Make a copy or archive the install location of your SecureTransport instance, for example C:\Program Files\Axway\SecureTransport. You can use WinZip or WinRar for the archiving.

Make a backup of your registry as per the Install/Upgrade Guide, chapter Back up the existing installation before upgrading

Additional notes

• The ST MySQL database executable files are located in $FILEDRIVEHOME/mysql/bin
• The default database username is root and default password is tumbleweed. The database name is st (lower case)

MySQL DB shrink instructions for UNIX and Linux systems?

Source the /etc/fd/env.sh or /opt/Axway/Securetransport/profile.shSecureTransport (or the *.csh* version the scripts, depending on what Shell you are using). Examples:

source /etc/fd/env.sh

or

source ./profile.shSecureTransport

All actions below are to be carried with great caution and always after a backup has been created based on the instructions above!

All lines listed below represent actual commands as executed via command line (i.e. you can use copy/paste in order to follow the steps). It is assumed that the commands are executed from the $FILEDRIVEHOME folder.

STEP 1: Back up the database schema with no data

bin/stop_all

bin/start_db

cd $FILEDRIVEHOME

mysql/bin/mysqldump --defaults-file=conf/mysql.conf -uroot -ptumbleweed st --no-data > /tmp/schema.sql

STEP 2: Back up the database without the large server log and file tracking tables

For clean install of SecureTransport 5.3.3 and above use:

mysql/bin/mysqldump --defaults-file=conf/mysql.conf --database st -ptumbleweed --ignore-table=st.logging_event --ignore-table=st.logging_event_exception --ignore-table=st.logging_event_property --ignore-table=st.TransferResubmitData --ignore-table=st.TransferProtocolCommands --ignore-table=st.TransferDetails --ignore-table=st.SubtransmissionStatus --ignore-table=st.TransferData --single-transaction --disable-keys --skip-set-charset > /tmp/clean-db-dump.sql

For upgraded installations to SecureTransport 5.3.3 from older versions (and in case the old File Tracking tables have not been dropped, see KB 178036) use:

mysql/bin/mysqldump --defaults-file=conf/mysql.conf --database st -ptumbleweed --ignore-table=st.logging_event --ignore-table=st.logging_event_exception --ignore-table=st.logging_event_property --ignore-table=st.TransferStatus --ignore-table=st.TransferResubmitData --ignore-table=st.TransferProtocolCommands --ignore-table=st.TransferDetails --ignore-table=st.SubtransmissionStatus --ignore-table=st.TransferData --single-transaction --disable-keys --skip-set-charset > /tmp/clean-db-dump.sql

STEP 3: Connect to MySQL and drop the 'st' database

mysql/bin/mysql --defaults-file=conf/mysql.conf -ptumbleweed

In the MySQL prompt, run each of the below commands on a separate line:

DROP DATABASE st;

STEP 3-A: Additional step ONLY for AIX environments

Stop the database and remove all ib* files:

$FILEDRIVEHOME/bin/stop_db

cd $FILEDRIVEHOME/var/db/mysql/data

STEP 4: Start the database and re-create the 'st' database

NOTE: It is expected to receive a database exception while starting the database - this is normal.

$FILEDRIVEHOME/bin/start_db

cd $FILEDRIVEHOME

mysql/bin/mysql --defaults-file=conf/mysql.conf -uroot -ptumbleweed

In the MySQL prompt, run the below command:

CREATE DATABASE st;

WARNING:*** DO NOT DELETE ANY FILES FROM $FILEDRIVEHOME/var/db/mysql/data ***

STEP 5: Import the database schema and import the clean database (without any Server Log and File Tracking information)

mysql/bin/mysql --defaults-file=conf/mysql.conf -ptumbleweed st < /tmp/schema.sql

mysql/bin/mysql --defaults-file=conf/mysql.conf -ptumbleweed st < /tmp/clean-db-dump.sql

STEP 6: Stop the database and start all ST services

$FILEDRIVEHOME/bin/stop_db

$FILEDRIVEHOME/bin/start_all

MySQL DB shrink instructions for Windows systems ?

NOTICE: All commands listed in the below steps are to be executed from the Cygwin shell. Refer to KB 178648 for infromation how to launch and use the Cygwin shell.

Source the profile.shSecureTransport with the following command:

source ./profile.shSecureTransport

All actions below are to be carried with great caution and always after a backup has been created based on the instructions above!

All lines listed below represent actual commands as executed via command line (i.e. you can use copy/paste in order to follow the steps). It is assumed that the commands are executed from the $FILEDRIVEHOME folder.

STEP 1: Back up the database schema with no data

bin/stop_all

bin/start_db

cd $FILEDRIVEHOME

mysql/bin/mysqldump --defaults-file=conf/mysql.conf -uroot -ptumbleweed st --no-data > /tmp/schema.sql

STEP 2: Back up the database without the large server log and file tracking tables

For clean install of SecureTransport 5.3.3 and above use:

mysql/bin/mysqldump --defaults-file=conf/mysql.conf --database st -ptumbleweed --ignore-table=st.logging_event --ignore-table=st.logging_event_exception --ignore-table=st.logging_event_property --ignore-table=st.TransferResubmitData --ignore-table=st.TransferProtocolCommands --ignore-table=st.TransferDetails --ignore-table=st.SubtransmissionStatus --ignore-table=st.TransferData --single-transaction --disable-keys --skip-set-charset > /tmp/clean-db-dump.sql

For upgraded installations to SecureTransport 5.3.3 from older versions (and in case the old File Tracking tables have not been dropped, see KB 178036) use:

mysql/bin/mysqldump --defaults-file=conf/mysql.conf --database st -ptumbleweed --ignore-table=st.logging_event --ignore-table=st.logging_event_exception --ignore-table=st.logging_event_property --ignore-table=st.TransferStatus --ignore-table=st.TransferResubmitData --ignore-table=st.TransferProtocolCommands --ignore-table=st.TransferDetails --ignore-table=st.SubtransmissionStatus --ignore-table=st.TransferData --single-transaction --disable-keys --skip-set-charset > /tmp/clean-db-dump.sql

STEP 3: Connect to MySQL, drop and recreate the 'st' database

mysql/bin/mysql --defaults-file=conf/mysql.conf -ptumbleweed

In the MySQL prompt, run each of the below commands on a separate line:

DROP DATABASE st;

CREATE DATABASE st;

Exit MySQL with:

quit

WARNING:*** DO NOT DELETE ANY FILES FROM $FILEDRIVEHOME/var/db/mysql/data ***

STEP 4: Import the database schema and import the clean database (without any Server Log and File Tracking information)

mysql/bin/mysql --defaults-file=conf/mysql.conf -ptumbleweed st < /tmp/schema.sql

mysql/bin/mysql --defaults-file=conf/mysql.conf -ptumbleweed st < /tmp/clean-db-dump.sql

STEP 5: Stop the database and start all ST services

$FILEDRIVEHOME/bin/stop_db

$FILEDRIVEHOME/bin/start_all