IBDATA Shrink

Document ID : KB000102933
Last Modified Date : 21/06/2018
Show Technical Document Details
Question:
My client has Gateway 9.3 and i want to provide him with updated shrink ibdata documentation. 
The one that i found on the CA site is compatible with gateway 9.2 (Mysql 5.6) and it doesn't work with gateway 9.3 (Mysql 5.7) - because in the new version each database has its own folder with ".ibd" files in it and deleting the main IBDATA file doesn't solve the problem. 

Here is the link to the Documentation that i have: 
https://comm.support.ca.com/kb/shrinking-mysql-ibdata-file/kb000042645 

Do you have an updated documentation on that issue? if not could you suggest how to do this process on the new gateway version? 
Environment:
Gateway version : 9.3
Answer:
Please follow these instructions to resolve this problem. 

1. https://comm.support.ca.com/kb/how-to-disable-innodb-file-per-table-in-mysql-after-9-3-00-gateway-upgrade/kb000071696 which disables innodb_file_per_table in MySQL after 9.3.00 Gateway Upgrade 

2. 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 

Then we encountered this error: 
ERROR 1146 (42S02) at line 635: Table 'mysql.slave_master_info' doesn't exist 

In order to resolve the above problem ..we carried out these actions as this is a known bug in MySQL 5.6 reference - 
https://stackoverflow.com/questions/37856155/mysql-upgrade-failed-innodb-tables-doesnt-exist 

Steps to follow: 

1) Drop these tables from Mysql: 

innodb_index_stats 
innodb_table_stats 
slave_master_info 
slave_relay_log_info 
slave_worker_info 

2) Delete *.frm and *.ibd files for the 5 tables above. 

3) restart mysql >> service mysqlid restart 

4) Restore the primary Gateway database backup:mysql < /root/ibdata-shrink-backup.sql 

5) Start the Gateway service on the primary Gateway: service ssg start