If you need to specify startup options when you run the server, you can indicate them on the command line or place them in an option file. For options that are used every time the server starts, you may find it most convenient to use an option file to specify your MySQL configuration. This is particularly true under the following circumstances:
The installation or data directory locations differ from the
default locations (C:\mysql
and
C:\mysql\data
).
You need to tune the server settings. For example, to use
the InnoDB
transactional tables in MySQL
3.23, you must manually add some extra lines to the option
file, as described in
Section 13.2.3, “InnoDB
Configuration”. (As of MySQL 4.0,
InnoDB
creates its data files and log
files in the data directory by default. This means you need
not configure InnoDB
explicitly. You may
still do so if you wish, and an option file is also useful
in this case.)
When the MySQL server starts on Windows, it looks for option
files in several locations, such as the Windows directory,
C:\
, and the MySQL installation directory
(for the full list of locations, see
Section 4.2.3.3, “Using Option Files”). The Windows directory typically
is named something like C:\WINDOWS
or
C:\WINNT
. You can determine its exact
location from the value of the WINDIR
environment variable using the following command:
shell> echo %WINDIR%
MySQL looks for options in each location first in the
my.ini
file, and then in the
my.cnf
file. However, to avoid confusion,
it is best if you use only one file. If your PC uses a boot
loader where the C:
drive is not the boot
drive, your only option is to use the
my.ini
file. Whichever option file you use,
it must be a plain text file.
You can also make use of the example option files included with your MySQL distribution; see Section 4.2.3.3.2, “Preconfigured Option Files”.
An option file can be created and modified with any text editor,
such as the Notepad program. For example, if
MySQL is installed in E:\mysql
and the data
directory is E:\mydata\data
, you can create
the option file and set up a [mysqld]
section
to specify values for the basedir
and
datadir
options:
[mysqld] # set basedir to your installation path basedir=E:/mysql # set datadir to the location of your data directory datadir=E:/mydata/data
Note that Windows path names are specified in option files using forward slashes rather than backslashes. If you do use backslashes, double them:
[mysqld] # set basedir to your installation path basedir=E:\\mysql # set datadir to the location of your data directory datadir=E:\\mydata\\data
The rules for use of backslash in option file values are given in Section 4.2.3.3, “Using Option Files”.
MySQL Enterprise. For expert advice on the start-up options appropriate to your circumstances, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
On Windows, the MySQL installer places the data directory
directly under the directory where you install MySQL. If you
would like to use a data directory in a different location, you
should copy the entire contents of the data
directory to the new location. For example, by default, the
installer places MySQL in C:\mysql
, and the
data directory in C:\mysql\data
. If you
want to use E:\mydata
as the data
directory, you must do two things:
Move the data directory from
C:\mysql\data
to
E:\mydata
.
Use a --datadir
option to
specify the new data directory location each time you start
the server.
User Comments
If you have installed mysql using xampp, moving your data directory is not a matter of creating a new option file, but modifying the one used by xampp to start your mysql server.
Look for the batch file mysql_start.bat in the xampp directory. My mysql_start.bat file includes this line:
mysql\bin\mysqld --defaults-file=mysql\bin\my.cnf --standalone --console
By editing the mysql\bin\my.cnf file (that path is relative to the xampp installation directory), I was able to change the default data directory.
Add your own comment.