Modifying the MySQL File Systems

If you have upgraded from older operating systems (Windows 3.1, for example), you may still have a FAT file system that limits your database size or expansion beyond 2GB. The database is a file as far as the operating system is concerned, and FAT limits file size. There is also a 4GB limit on early versions of NTFS that may linger because of upgrades.

To change the installed database sizes, you must edit the configuration file:

• Windows: %SystemRoot%\my.ini

• Solaris: /etc/my.cnf

The following line controls maximum database size (at end):

innodb_data_file_path = d:/work/oware3rd/mysql/ibdata/ibdata1:600M:auto­extend:max:2000M

To recreate database after modifying config file, use the following command from the application server:

loaddb -q -d -m

Syntax details:

innodb_data_file_path = pathtodatafile:sizespecification;pathtodatafile:sizespecification;...

innodb_data_file_path = ... ;pathtodatafile:sizespecification[:autoextend[:max:sizespecification]]

If you specify the last datafile with the autoextend option, InnoDB will extend the last datafile if it runs out of free space in the tablespace. The increment is 8 MB at a time. An example:

innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

This instructs InnoDB to create just a single datafile whose initial size is 100 MB and which is extended in 8 MB blocks when space runs out.

If the disk becomes full you may want to add another datafile to another disk, for example. Then you must look at the size of `ibdata1', round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of `ibdata1' explicitly in innodb_data_file_path. After that you can add another datafile:

innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Be cautious on filesystems where the maximum file-size is 2 GB. InnoDB is not aware of the operating system’s maximum file-size. On those filesystems you might want to specify the max size for the datafile:

innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

Some additional caveats:

• You must use foreslashes (/) instead of backslashes (\) when you specify the path.

• The subdirectory iblogs must be used by MySQL exclusively

• Make sure you enough disk space available on the data path specified

• You can add as many entries as you like. However, you can use initial, max and autoextend only in the last entry, and must change the first entry to reflect the actual size of the database.

• The name of filepath must be valid on the filesystems. However, you must always have your leaf directory in the path as ibdata.