Set File Size Limit

Usually MySQL database size is limited by underlying operating system, not by MySQL internal limit. For example, in Linux 2.4+, ext3 file system can support up to 4TB. MySQL InnoDB has a variable (innodb_data_file_path) to specify the location and the size of database file as well as whether the database increases beyond the limit or not. The innodb_data_file_path variable is in in my.cnf:

innodb_data_file_path = filename:size:option[;second_filename:size:option[;::] ]

To locate innodb data file, MySQL use another variable: innodb_data_home_dir. The actual path of file is concatenation of innodb_data_home_dir + innodb_data_file_path. If innodb_data_home_dir is not used, the default directory is MySQL data directory, which is set by datadir variable in my.cnf.

The option consists of [autoextend[:max:max_fize_size]] The “autoextended” option allows the file to increase automatically when the data increase to the prescribed file size. The increased size is 8MB by default and decided by “innodb-autoextend-increment”

On the contrary, the “max:max_file_size” option specify the fixed database file size and the database cannot grow beyond the limit. The autoextend option cannot be used with max:max_file_size option.

The followings show the part of my.cnf file that is used for NexOSS at this moment.

# For InnoDB storage engine
default-storage-engine=INNODB

# InnoDB data directory
# The deault is "./" which means MySQL data directory.
# innodb_data_home_dir = ./

# InooDB data files must be able to hold data and indexes
# But 2G may be a limit for X86
innodb_data_file_path = ibdata1:2000M:autoextend

The above configuration shows that the database file name is “ibdata1” at MySQL home directory and the size is 2G (2000M) with auto-extending option.

However, it cannot increase beyond the size of disk partition. When database is full due to the disk capacity, database file needs to move to another partition, disk or server. This move operation can be done through database dump or file copy (in the same machine).