IBDATA Shrink

Document ID : KB000102933
Last Modified Date : 19/09/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 
or alternative:  https://comm.support.ca.com/kb/how-to-shrink-the-ibdata-file-in-mysql-2/kb000112511 

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 mysqld 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 
 
Additional Information:

Commands to cut-and-paste: 
use mysql;

drop table innodb_index_stats;
drop table innodb_table_stats; 
drop table slave_master_info; 
drop table slave_relay_log_info;
drop table slave_worker_info;

Note: show tables will show these tables, the drop table may then give an error, but then show tables will show the table has been deleted. 

The *.frm and *.idb files  are in the /var/lib/mysql/mysql directory - only some of them will exist.