How to Shrink the ibdata File in MySQL

Document ID : KB000042645
Last Modified Date : 31/08/2018
Show Technical Document Details
Introduction:

The CA API Gateway uses MySQL with the InnoDB engine to store configuration data, audits, and published service policies. This implementation uses a single database tablespace object (the ibdata file) to store all binary data within the database. The MySQL server is configured to prevent this file from exceeding a certain size. If the file must grow to store more data but cannot grow due to the upper limit then the ibdata file will need to be shrunk and certain data within the database must be discarded. This article prescribes removing all audit data from the database as audit records tend to consume the most storage space in the Gateway database.

Please note that this procedure will result in downtime.
 

An alternative shrink process is provided in the following link :
https://comm.support.ca.com/kb/how-to-shrink-the-ibdata-file-in-mysql-2/kb000112511

the current process can be a bit tricky, use wrong IP address or apply to wrong machine etc, - the alternative knowledge doc aspires to be less prone to error. 

Instructions:

This procedure consists of several smaller steps (Note: For a single node cluster, only step 2 and 3 is required):

  1. Blocking MySQL traffic between appliances
  2. Purging existing audit data
  3. Shrinking the existing ibdata file
  4. Unblocking MySQL traffic
  5. Reinitializing replication

1. Block MySQL Traffic

  • Log onto the primary Gateway database node as the ssgconfig user.
  • Select Option 3: Use a privileged shell (root).
  • Close the port used for MySQL for all connections originating from the secondary Gateway database node.
NOTE: The value of "SECONDARY-IP" will need to be the IP address of the management interface of the secondary Gateway database node.
iptables -I INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3306 -j REJECT
iptables -I INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3307 -j REJECT
  • Log onto the secondary Gateway database node as the ssgconfig user
  • Select Option 3: Use a privileged shell (root)
  • Close the port used for MySQL for all connections originating from the primary Gateway database node.
NOTE: The value of "PRIMARY-IP" will need to be the IP address of the management interface of the primary Gateway database node.
iptables -I INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3306 -j REJECT
iptables -I INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3307 -j REJECT

2. Purge Existing Audit Data

  • Stop the Gateway service on each Gateway database node: service ssg stop
  • Back up the existing primary Gateway database: mysqldump --all-databases | gzip > /root/ibdata-full-backup.sql.gz
  • Access the primary Gateway database via the MySQL prompt: mysql ssg
  • Execute the following MySQL queries:
SET FOREIGN_KEY_CHECKS = 0;
truncate table audit_admin;
optimize table audit_admin;
truncate table audit_detail;
optimize table audit_detail;
truncate table audit_detail_params;
optimize table audit_detail_params;
truncate table audit_main;
optimize table audit_main;
truncate table audit_message;
optimize table audit_message;
truncate table audit_system;
optimize table audit_system;
SET FOREIGN_KEY_CHECKS = 1;
exit
  • Back up the primary Gateway database: mysqldump --all-databases | gzip > /root/ibdata-shrink-backup.sql.gz

3. Shrink the ibdata File

  • Stop the MySQL service on the primary Gateway: service mysql stop
  • Remove the existing ibdata files: rm -rf /var/lib/mysql/ib*
  • Start the MySQL service on the primary Gateway: service mysql start
  • Extract the contents of the primary Gateway database backup:gzip -d /root/ibdata-shrink-backup.sql.gz
  • Restore the primary Gateway database backup:mysql < /root/ibdata-shrink-backup.sql
  • Start the Gateway service on the primary Gateway: service ssg start
  • Repeat the this section on the secondary database node.

4. Unblock MySQL Traffic

  • Stop replication on each Gateway database node from the privileged shell:?mysql -e "stop slave"
  • Reopen MySQL communication on the primary Gateway database node:
iptables -D INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3306 -j REJECT
iptables -D INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3307 -j REJECT
  • Reopen MySQL communication on the secondary Gateway database node:
iptables -D INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3306 -j REJECT
iptables -D INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3307 -j REJECT

5. Reinitialize Replication

  • Reinitialize replication on the secondary node: /opt/SecureSpan/Appliance/bin/create_slave.sh
NOTE: Specify the FQDN, credentials, and port of the primary Gateway database node. When prompted, clone the database from the primary Gateway database node.
  • Reinitialize replication on the primary node: /opt/SecureSpan/Appliance/bin/create_slave.sh
NOTE: Specify the FQDN, credentials, and port of the secondary Gateway database node. When prompted, do not clone the database from the secondary Gateway database node.
  • Verify successful initialization of replication: mysql -e "show slave status\G" | grep ": Yes"

NOTE: The console or terminal should return with the following information exactly as depicted:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes