Usually MySQL database size is limited by the underlying operating system, not by MySQL internal limit. (In Linux 2.4+, ext3 file system can support up to 4TB)
In the beginning, MySQL sets the initial size of InnoDB data file. After the data reaches the limit, there are two options:
Stop to write data
Write data by increasing data file size automatically. The option is set by a variable, innodb_data_file_path, which is in /etc/my.cnf. For example:
innodb_data_file_path = ibdata1:2G:autoextend
This says the initial data file name is ibdata1 and the initial size is 2G. When the data size reaches 2G, it will extend the file automatically by the prescribed size. The increased size is 8MB by default and decided by “innodb-autoextend-increment”. Without the autoextend option, it will generate an error when the size goes beyond 2G.
Another option is max:max_file_size. This works as a hard limit to restrict the operation of autoextend so the data file cannot be auto-extended beyond max_file_size. For example:
innodb_data_file_path = ibdata1:2G:autoextend:max:20G
Here, MySQL starts with a 2G data file and auto-extends the data file up to 20G. But it cannot extend more than 20G. The location (path) of InnoDB data file is decided by innodb_data_home_dir. If innodb_data_home_dir is not specified, MySQL will use the default directory location specified by datadir variable in my.cnf.
The following shows the part of my.cnf file that is used for NexOSS 5.15.x version.
# For InnoDB storage engine
default-storage-engine=INNODB
# InnoDB data directory
# The deault is "./" which means MySQL data directory.
# innodb_data_home_dir = ./
# InnoDB 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.