API Gateway: relocate the mysql data file location

Products

• CA API Gateway

Issue/Introduction

Our primary and backup mysql servers have filled up filesystem space on the mount due to service_metrics table exploding in size. As a result, eventually the mysql can't start. we are looking for a way to get these mysql boxes back up.

Environment

Release: 11.x
Component: CA API Gateway Software Appliance

Cause

When a mysql database continues to receive new data without a purging process, it is a matter of time when the file system that is holding the database may run out of space and prevent the mysql to function correctly. As a result, the mysql will not even start to allow normal data purging to be performed.

Resolution

The out-of-box Software Appliance (ova) actually allocates more disks space for the mysql data file location, under /var/lib/mysql. You can use the following command to examine the current available filesystem and their available space:

df

If you are able to find an existing writeable filesystem that can hold the current usage of your mysql data filew, then you can first copy all the mysql datafiles there or you may have to mount another file system that has enough capacity to your Software Appliance.  More information about it can be found in the product docment at the following link:

Increase Disk Space in Virtual Appliance Using Logical Volume Management

Once you have another filesystem that is capable of accommodating the original mssql data file, assuming it is under /newspace, you can then do the following:

1. stop mysql if not already using

   systemctl stop mysql

2. copy the whole current mysql data file subdirectory recursively to the /newspace

   cp -Rp /var/lib/mysql /newspace

   This will create /newspace/mysql that contains the right content and permission.
3. Modify the /etc/mysql/mysql.conf.d/mysqld.cnf, look for datadir, its original value out-of-the-box is /var/lib/mysql, change it to use the new location:
   

   datadir = /newspace/mysql

4. Reboot the API Gateway machine to allow everything to start automatically and then to manually confirm the system is functioning.

Once you are able to resolve the original disk space issue, you may want to consider changing the datadir back to the its original location that has enough space for your API Gateway to continue functioning. For that, you can just go though the similar steps above and only to apply the right subdiretories accordingly.