Skip to main content
added 6 characters in body
Source Link
RolandoMySQLDBA
  • 184.4k
  • 33
  • 323
  • 531

#STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 0

#STEP #2

service mysql restart

#STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e""e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

#EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

#Give it a Try !!!

#STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 0

#STEP #2

service mysql restart

#STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

#EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

#Give it a Try !!!

#STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 0

#STEP #2

service mysql restart

#STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

#EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

#Give it a Try !!!

Source Link
RolandoMySQLDBA
  • 184.4k
  • 33
  • 323
  • 531

#STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 0

#STEP #2

service mysql restart

#STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

#EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

#Give it a Try !!!