Posts Tagged innodb

MySQL: moving innodb tables out of ibdata1

Note-to-self style post again.

MySQL’s innodb will store all table data in one file called ibdata1, unless you tell it to do otherwise. This can become an issue sometimes, particularly when the file gets really big.

Thankfully there’s a way to fix it.

Add innodb_file_per_table to my.cnf

[mysqld]
innodb_file_per_table

Update the running server with the same value
SET GLOBAL innodb_file_per_table=1;

Then
ALTER TABLE tablename ENGINE=Innodb;
for each table to move it out of the ibdata1 file.

Alternatively, you can use this little bit of trickery to generate the sql needed to do it for all tables:

SELECT concat('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=Innodb;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB';

Then paste the result as sql and run it (it may take some time though).

,

No Comments

css.php