Owner's Manual

Database Management 137
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
The following line controls maximum database size (at end):
innodb_data_file_path = d:/work/oware3rd/mysql/ibdata/
ibdata1:600M:autoextend: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