Problem

With SecureTransport 5.5-20210729 the embedded MySQL database has been replaced with MariaDB. In this article you will find how to shrink the SecureTransport MariaDB database size or how to clean up the SecureTransport MariaDB database and prevent it from growing in the future.

Before you begin

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

IMPORTANT NOTE: While native MariaDB 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 the execution of the manual instructions below.

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

Refer to the backup procedures outlined in KB 180651: Backup and Restore for Secure Transport installations with embedded MySQL database.

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

Additional notes

The ST MariaDB database executable files are located in $FILEDRIVEHOME/mariadb/bin.

The default database username is root and the default password is tumbleweed. The database name is st (lower case).

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.

MariaDB DB shrink instructions for Linux and UNIX

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

source /etc/fd/env.sh

or

source ./profile.sh

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

mariadb/bin/mariadb-dump --defaults-file=conf/internaldb.conf -uroot -ptumbleweed st --no-data > /tmp/schema.sql

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

mariadb/bin/mariadb-dump --defaults-file=conf/internaldb.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 MariaDB and drop the 'st' database

mariadb/bin/mariadb --defaults-file=conf/internaldb.conf -ptumbleweed --database st

In the MariaDB 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/mariadb/data

Start the database and reconnect

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

$FILEDRIVEHOME/bin/start_db

cd $FILEDRIVEHOME

mariadb/bin/mariadb --defaults-file=conf/internaldb.conf -ptumbleweed

STEP 4: Re-create the 'st' database

In the MariaDB prompt, run the below command:

CREATE DATABASE st;

Exit MariaDB with:

quit

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

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

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

mariadb/bin/mariadb --defaults-file=conf/internaldb.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

MariaDB shrink instructions for Windows

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

Source the profile.shSecureTransport with the following command:

source ./profile.sh

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

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

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

mariadb/bin/mysqldump.exe --defaults-file=conf/internaldb.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 MariaDB, drop and recreate the 'st' database

mariadb/bin/mysql.exe --defaults-file=conf/internaldb.conf -ptumbleweed --database st

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

DROP DATABASE st;

CREATE DATABASE st;

Exit MariaDB with:

quit

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

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

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

mariadb/bin/mysql.exe --defaults-file=conf/internaldb.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