title: innodb_file_per_table link: https://onemoretech.wordpress.com/2016/05/14/innodb_file_per_table/ author: phil2nc description: post_id: 11346 created: 2016/05/14 12:09:24 created_gmt: 2016/05/14 16:09:24 comment_status: closed post_name: innodb_file_per_table status: publish post_type: post

innodb_file_per_table

If there is one MySQL/MariaDB server option I’d say would be most important for sysadmins to know about it would be innodb_file_per_table. The thing that makes innodb_file_per_table important is that it improves the survivability of your data. It is enabled by default starting with MySQL 5.6 but is not in MariaDB – it must be explicitly set. The traditional behavior of MySQL’s innodb engine is to store all its indexes in a single file, the monolithic ibdata, which tended to grow YUGE over time. The innodb_file_per_table option instead creates a separate index file (with an .idb extension) for each table in the database. As a result your changes of recovery after the corruption or accidental deletion of any given file is vastly improved. Google around for discussions over the details of why I say that, and for other pros cons (the biggest con I’ve experienced is a marked drop in performance when doing bulk queries – like massive search and replace operations when cloning a WordPress instance).

Copyright 2004-2019 Phil Lembo