{"id":307,"date":"2013-09-03T23:42:08","date_gmt":"2013-09-03T23:42:08","guid":{"rendered":"https:\/\/reviewsignal.com\/blog\/?p=307"},"modified":"2021-08-15T15:09:05","modified_gmt":"2021-08-15T19:09:05","slug":"how-to-configure-mysql-innodb-to-save-tables-in-unique-files","status":"publish","type":"post","link":"https:\/\/reviewsignal.com\/blog\/2013\/09\/03\/how-to-configure-mysql-innodb-to-save-tables-in-unique-files\/","title":{"rendered":"How To Configure MySql InnoDB to Save Tables In Unique Files"},"content":{"rendered":"

MySql is a great database and InnoDB is one of the engines you can use; the other popular choice is MyISAM (a good post<\/a> explaining the differences). If you work with large amounts of data you can sometimes run into weird problems you didn't know about or expect.<\/p>\n

InnoDB saves all the tables in your database into one file: ibdata1 (and an ib_log file). That's all well and good, except it doesn't delete information from them when you erase a table. So your ibfile1 will never decrease in size. This only becomes a problem with large amounts of data and the use of temporary tables. I don't know why this is the default behavior, but there is a very simple solution to get around this problem: use unique files for each table.<\/p>\n

Instructions<\/h2>\n

Make sure you backup everything before doing this and I don't recommend doing this on a live system.<\/strong><\/p>\n

1. Open up your my.cnf (mysql configuration file)<\/p>\n\n

nano<\/span> \/<\/span>etc\/<\/span>mysql\/<\/span>my.cnf<\/pre><\/td><\/tr><\/table><\/div>\n\n
If you don't know where it is try searching for it:<\/p>\n\n
find<\/span> \/<\/span> -name<\/span> my.cnf<\/pre><\/td><\/tr><\/table><\/div>\n\n
2. Add the following under [mysqld] portion of the config:<\/p>\n\n
innodb_file_per_table<\/pre><\/td><\/tr><\/table><\/div>\n\n
3. Remove old files: ibdata1 and ib_log<\/p>\n
4. Restart mysql (a couple examples below)<\/p>\n\n
service mysqld restart<\/pre><\/td><\/tr><\/table><\/div>\n\n\n
\/<\/span>etc\/<\/span>init.d\/<\/span>mysqld restart<\/pre><\/td><\/tr><\/table><\/div>\n\n
MySql will now save innodb tables into unique files and delete them when you delete the tables.<\/p>\n","protected":false},"excerpt":{"rendered":"
MySql is a great database and InnoDB is one of the engines you can use; the other popular choice is MyISAM (a good post explaining the differences). If you work with large amounts of data you can sometimes run into weird problems you didn’t know about or expect. InnoDB saves all the tables in your […]<\/p>\n","protected":false},"author":1,"featured_media":311,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[77,76,67,74],"class_list":["post-307","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-how-tuesday","tag-databases","tag-innodb","tag-mysql","tag-performance"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t