How to Shrink the ibdata File in MySQL-2

Document ID : KB000112511
Last Modified Date : 20/11/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. Reset slave and master on secondary server
  4. Reset slave and master on primary server 
  5. Shrink data in primary server: (with backups).
  6. Rebuild MySQL ibdata from shrunk backup on primary server
  7. Rebuild MySQL ibdata on secondary server
  8. Reinitialize replication on secondary server
  9. Reinitialize replication on  primary server
  10. Start gateway/ssg service on both servers

Note: Space ...

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"
  •  
  • Reset the master configuration on both nodes: mysql -e "reset master" 
  • Reset the slave configuration on both nodes: mysql -e "reset slave; reset slave all"

3. Reset slave and master on secondary server

  • 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.
  • Reset the master configuration on secondary 
        >mysql -e "reset master"  
  • Reset the slave configuration on secondary 
        >mysql -e "reset slave all"  

4. Reset slave and master on primary server

  • 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.
  • Reset the master configuration on primary server
        >mysql -e "reset master"  
  • Reset the slave configuration on primary server
        >mysql -e "reset slave all"  

5. 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(s):
         mysqldump --all-databases --routines --skip-lock-tables | gzip > /root/ibdata-full-backup.sql.gz 
  • Take a separate back up the existing primary ssg Gateway database:
         mysqldump --routines --skip-lock-tables ssg | gzip > /root/ibdata-ssg-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 --routines --skip-lock-tables | gzip > /root/ibdata-shrink-backup.sql.gz
  • Take backup of the  "shrunk" primary ssg database :
       mysqldump --all-databases --routines --skip-lock-tables ssg | gzip > /root/ibdata-shrink-ssg-backup.sql.gz


6. 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
  • Restarting the mysql then builds an empty database.
  • 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

7. Rebuild MySQL ibdata on secondary server

  • Log onto the secondary Gateway database node as root user - as explained previously.
  • Optional but recommended 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 secondary Gateway:
      service mysql stop
  • Remove the existing ibdata files:
        rm -rf /var/lib/mysql/ib*
  • Start the MySQL service on the secondary Gateway:
      service mysql start
  • Restarting the service creates an empty 'ssg' database.

8. 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
    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 (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 the data 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
    

9. 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
    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 (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
    

10. 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 : 

If MySQL service fails to start - you may need to reset a broken replication.  
  1. 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 and there can be an extra listen on :3307 for replication for MySQL server - the create_slave.sh script will use port 3307.  


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