How to Shrink the ibdata File in MySQL-2

Document ID : KB000112511
Last Modified Date : 19/09/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.

Note: There is older knowledge article for this process,
000042645   https://comm.support.ca.com/kb/shrinking-mysql-ibdata-file/kb000042645
It has been the process for a long time - but we have had some cases where confusion has led to mix up of replication and need to wipe and rebuild the replication setup.   This revised method offers a similar, but hopefully less error prone, method. 

Note2; If you are upgrading Gateway 9.3 you are likely to also encounter this issue: - ERROR 1146 (42S02) at line 635: Table 'mysql.slave_master_info' doesn't exist 

000102933 https://comm.support.ca.com/kb/ibdata-shrink/kb000102933

Please note that this procedure will result in downtime.
 
Environment:
APIM Gateway 9.2
APIM Gateway 9.3
 
Instructions:

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

  1. Stop gateway/ssg service and MySQL replication on secondary server
  2. Stop gateway/ssg service and MySQL replication on primary server 
  3. Shrink data in primary server: (with backups).
  4. Rebuild MySQL ibdata from shrunk backup on primary server
  5. Rebuild MySQL ibdata from shrunk backup on secondary server
  6. Reinitialize replication on secondary server
  7. Reinitialize replication on  primary server
  8. Start gateway/ssg service on both servers

1. Stop gateway service and MySQL replication on secondary server

       Note: we stop the service on the secondary node first to ensure latest data is on primary. 
  • Log onto the secondary Gateway database node as root user. This is done via login as the ssgconfig user, then select Option 3: Use a privileged shell (root) to login as root user.
  • Stop the ssg service :
        >service ssg stop
  • Stop replication on primary 
        >mysql -e "stop slave"

2. Stop gateway service and MySQL replication on primary server

  • Log onto the primary Gateway database node as root user - as explained previously.
  • Stop the ssg service :
        >service ssg stop
  • Stop replication on primary 
        >mysql -e "stop slave"

3. Shrink data in primary server: (with backups)

  • Log onto the primary Gateway database node as root user - as explained previously.
  • 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 "shrunk" primary Gateway database:
       mysqldump --all-databases | gzip > /root/ibdata-shrink-backup.sql.gz
  • Copy the backup of the "shrunk" primary Gateway database to the secondary Gateway database node:
       scp ibdata-shrink-backup.sql.gz ssgconfig@<secondary gateway name>:


4. Rebuild MySQL ibdata from shrunk backup on primary server

  • Log onto the primary Gateway database node as root user - as explained previously.
  • 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

5. Rebuild MySQL ibdata from shrunk backup on secondary server

  • Log onto the secondary Gateway database node as root user - as explained previously.
  • Optional Step: -Backup current data from secondary gateway database:
         mysqldump --all-databases | gzip > /root/ibdata-full-backup-secondary.sql.gz
  • 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
  • Copy the 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

6. Reinitialize replication on secondary server

  • Log onto the secondary Gateway database node as root user - as explained previously.
  • Reinitialize replication on the secondary node:
         /opt/SecureSpan/Appliance/bin/create_slave.sh
    When prompted  
    Enter hostname or IP for the MASTER:  gw-primary:3306
    Enter replication user: [repluser]
    Enter replication password: [replpass]
    Enter MySQL root user: [root]
    Enter MySQL root password: []
    Do you want to clone a database from gw-primary:3306 (yes or no)? [yes]
    Ensure you enter the correct hostname or IP for the primary gateway.  
    Make sure you enter "YES" when prompted to clone from the primary database..
  • 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
    

7. Reinitialize replication on primary server

  • Log onto the primary Gateway database node as root user - as explained previously.
  • Reinitialize replication on the primary node:
         /opt/SecureSpan/Appliance/bin/create_slave.sh
    When prompted  
    Enter hostname or IP for the MASTER:  gw-secondary:3306
    Enter replication user: [repluser]
    Enter replication password: [replpass]
    Enter MySQL root user: [root]
    Enter MySQL root password: []
    Do you want to clone a database from gw-secondary:3306 (yes or no)? [no]
    Ensure you enter the correct hostname or IP for the secondary gateway.  
    Make sure you enter "NO" when prompted to clone from the secondary database.
  • 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
    

8. Start gateway/ssg service on both servers

  • Log onto the primary Gateway database node as root user - as explained previously.
  • Stop the MySQL service on the primary Gateway:
      service ssg start
  • Log onto the secondary Gateway database node as root user - as explained previously.
  • Stop the MySQL service on the primary Gateway:
      service ssg start

 

Additional Information:

Some notes based on the previous method : 

 
  1. If MySQL service fails to start - you may need to reset a broken replication.  
    Hopefully you do not need it but this kb article shows how to reset replication when it is completely broken and stopping the mysql service from starting. 
    https://comm.support.ca.com/kb/mysql-partition-is-100-full-mysql-wont-operate/kb000044691

    iptables command can be used to block traffic to MySQL ports 3306 and 3307.

  2. However only Gateway ssg service and replication should be writing to the ssg database - so as long as these processes are not active they are not needed. 
    The iptables rules were also a bit tricky to apply, and easy to make an error (blocking wrong IP address etc)  

  3. Does MySQL use ports 3306 and 3307 ?
    The port MySQL listens on depends on your /etc/my.conf file.  Generally it is :3306 but some setups (not APIM Gateway appliance) may use port 3307 for a slave MySQL server.  


For more information on the original procedure - see the original kb article : 
https://comm.support.ca.com/kb/shrinking-mysql-ibdata-file/kb000042645