MySQL InnoDB ib_logfile0, ib_logfile1 & ibdata1 file size issue

Following steps would solve the issue:

Step-1: MySQLDump all databases into a SQL text file (e.g. SQLDataBackup.sql)

Step-2: Drop all databases (except mysql, information_schema and performance_schema schemas)

Step-3: Shutdown MySQL

Step-4: Add the following lines to (/etc/my.cnf if linux) or (C:\xampp\mysql\data\my.ini if xampp in windows)

[mysqld]

innodb_file_per_table 

innodb_flush_method=O_DIRECT 

innodb_log_file_size=1G 

innodb_buffer_pool_size=4G 

innodb_data_file_path=ibdata1:10M:autoextend 

Note: Whatever you set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step-5: Delete ibdata1, ib_logfile0 and ib_logfile1  

There should only be the mysql schema in data location.

Step-6: Restart MySQL

This will recreate ibdata1 at 10MB (do not configure the option) , ib_logfile0 and ib_logfile1 at 1G each

Step-7: Reload SQLDataBackup.sql into MySQL

ibdata1 will grow but only contain table metadata and intermittent MVCC data. Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into path/mysql/data, you will see two files representing the table

mytable.frm (Storage Engine Header) 

mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable) 

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in (/etc/my.cnf if linux) or (C:\xampp\mysql\data\my.ini if xampp in windows), you can run OPTIMIZE TABLE mydb.mytable OR ALTER TABLE mydb.mytable ENGINE=InnoDB; and the file will actually shrink.

Query to Identify Primary Table is being used as Reference in which Tables in the SQL Database

Just write down the PrimaryTableName

and execute below query.

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', OBJECT_NAME(FK.parent_object_id) 'Referring Table', FK.name 'Foreign Key', COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',  COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column' FROM sys.foreign_keys AS FK INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.OBJECT_ID WHERE OBJECT_NAME (FK.referenced_object_id) = 'PrimaryTableName' If you want to check multiple Primary Table then just replace OBJECT_NAME (FK.referenced_object_id) = 'PrimaryTableName' by OBJECT_NAME (FK.referenced_object_id) in ('PrimaryTable1','PrimaryTable2','PrimaryTable2')