Optimize slave .ibd tables, where slave has innodb_file_per_table and master does not We are trying to reclaim file space on our replication server. The space we want to reduce are .ibd files on the slave. We have a simple Master -> Slave (read only) replication setup, for some reason, we have innodb_file_per_table set up on the slave but not the master. So on the slave we have very large .ibd files, but not on the slave, of tables that have already been purged, but have not reduced in size. 

Is there a way we can reduce those .ibdfiles on the slave with out dropping the db and re importing it? 

Would running optimize table on the master reduce them? 

Thanks
Running OPTIMIZE TABLE on all the tables in the Master is a very bad idea. Why ???

Since the Master has innodb_file_per_table disabled, running OPTIMIZE TABLE on all the table in the Master would make ibdata1 grow. I discussed this in my old StackOverflow post like 5 years ago : Howto: Clean a mysql InnoDB storage engine?


  Can I run OPTIMIZE TABLE ?
  
  Unfortunately, running OPTIMIZE TABLE against an InnoDB
  table stored in the shared table-space file ibdata1 does two things:
  
  
  Makes the table’s data and indexes contiguous inside ibdata1
  Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1
  


You could run OPTIMIZE TABLE on the Slave as follows:

OPTIMIZE_SCRIPT=/tmp/OptimizeInnoDBTables.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE engine='InnoDB' ORDER BY (data_length+index_length) DESC"
mysql ${MYSQL_CONN} -ANe"${SQL}" &gt; ${OPTIMIZE_SCRIPT}
mysql ${MYSQL_CONN} &lt; ${OPTIMIZE_SCRIPT}


This will shrink every .ibd file. Again, DO NOT RUN THIS ON THE MASTER !!!

GIVE IT A TRY !!!