where is mysql server’s default data folder in windows 7

I am looking for the data directory of mysql server (5.1.42) I installed on my machine which runs win7.

There is no ‘data’ direcotory in C:\Program Files\MySQL\MySQL Server 5.1 and also in C:\Users\MyAccount\AppData\Local||LocalLow||Roaming

11 Answers 11

Different MySQL/Windows versions have different data folder locations. To get your ACTUAL data folder location just execute this SQL:

Must be C:\ProgramData\MySQL\MySQL Server 5.1\data.

Was not really a good idea to place data here by default. It is compatible with various Vista compatibility requirements, but is eather hard to find, especially given the ProgramData folder is hidden.

You can find the data directory at this location C:\ProgramData\MySQL\MySQL Server 5.5\data\

The path can be defined in my.ini (located in Windows directory) as datadir .

I have had the problem to find the data directory too and even to start the mysql the second time after installing the server 5.7 on Windows 7.

In contrary to some answers here and to the documentation (the part about location of my.ini), the my.ini location was c:\ProgramData\MySQL\MySQL Server 5.7\my.ini the data location was c:\ProgramData\MySQL\MySQL Server 5.7\Data. In fact it was to see, as default proposed data location, during the installation.

This is what worked for me on Windows 7:

After that, run MySQL server with:

Thanks to those who shared the information that C:\ProgramData was a hidden directory!

If you installed mysql with the MSI installer, look here: C:\ProgramData\MySQL\MySQL Server 5.7\ and you should see the data folder. Even thought my datadir is specified to be located in the default mysql server location, my data is stored in %ProgramData% .

Windows version independent answer.

For MySQL 5.7 on Windows, directory for log files and databases is

«%PROGRAMDATA%\MySQL\MySQL Server 5.7\»

The Windows system variable %PROGRAMDATA% defaults to C:\ProgramData

After changing the directory, the root password is reset. So, keep in mind, if you change the directory, and have access issues, try to access it without a password then set a new password.

For newer users: The data directory lies in C:\Program Files\MySQL\MySQL Server 8.0\data

I currently have MySQL Server 8.0. If you too, are searching for some error solutions and couldn’t find the «data» directory for MySQL, here’s a note from the official MySQL:

«The default data directory location is C:\Program Files\MySQL\MySQL Server 8.0\data , or C:\ProgramData\Mysql on Windows 7 and Windows Server 2008. The C:\ProgramData directory is hidden by default. You need to change your folder options to see the directory and contents.»

Mysql data directory windows

Information managed by the MySQL server is stored under a directory known as the data directory. The following list briefly describes the items typically found in the data directory, with cross references for additional information:

Data directory subdirectories. Each subdirectory of the data directory is a database directory and corresponds to a database managed by the server. All MySQL installations have certain standard databases:

The mysql directory corresponds to the mysql system schema, which contains information required by the MySQL server as it runs. This database contains data dictionary tables and system tables. See Section 5.3, “The mysql System Schema”.

The performance_schema directory corresponds to the Performance Schema, which provides information used to inspect the internal execution of the server at runtime. See Chapter 27, MySQL Performance Schema.

The sys directory corresponds to the sys schema, which provides a set of objects to help interpret Performance Schema information more easily. See Chapter 28, MySQL sys Schema.

The ndbinfo directory corresponds to the ndbinfo database that stores information specific to NDB Cluster (present only for installations built to include NDB Cluster). See Section 23.5.14, “ndbinfo: The NDB Cluster Information Database”.

Other subdirectories correspond to databases created by users or applications.

INFORMATION_SCHEMA is a standard database, but its implementation uses no corresponding database directory.

Log files written by the server. See Section 5.4, “MySQL Server Logs”.

Default/autogenerated SSL and RSA certificate and key files. See Section 6.3.3, “Creating SSL and RSA Certificates and Keys”.

The server process ID file (while the server is running).

The mysqld-auto.cnf file that stores persisted global system variable settings. See Section 13.7.6.1, “SET Syntax for Variable Assignment”.

Some items in the preceding list can be relocated elsewhere by reconfiguring the server. In addition, the —datadir option enables the location of the data directory itself to be changed. For a given MySQL installation, check the server configuration to determine whether items have been moved.

Location of the Data Directory

A default data directory location is compiled into the server. Under UNIX, typical defaults are /usr/local/mysql/var if you install MySQL from a source distribution, /usr/local/mysql/data if you install from a binary distribution, and /var/lib/mysql if you install from an RPM file. Under Windows, the default data directory is C:\mysql\data .

The data directory location can be specified explicitly when you start up the server by using a —datadir= dir_name option. This is useful if you want to place the directory somewhere other than its default location. Another way to specify the location is to list it in an option file that the server reads at startup time. Then you don’t need to include it on the command line each time you start the server. Data directory relocation is covered later in the chapter.

As a MySQL administrator, you should know where your server’s data directory is located. If you run multiple servers, you should know where each one’s data directory is. But if you don’t know the location (perhaps you are taking over for a previous administrator who left poor notes), there are several ways to find out:

Ask the server for the location. The server maintains a number of variables pertaining to its operation, and it can report any of their values. The data directory location is indicated by the datadir variable, which you can obtain using a mysqladmin variables commandor a SHOW VARIABLES statement. From the command line, use mysqladmin . On UNIX, the output might look like this:

On Windows, the output might look like the following instead:

From within mysql , check the variable’s value like this:

If you have multiple servers running, they will be listening on different TCP/IP port numbers, sockets, or named pipes. You can get data directory information from each of them in turn by supplying appropriate —port or —socket options to connect to the port or socket on which each server is listening. Specifying a host of 127.0.0.1 explicitly tells mysqladmin to connect to the server on the local host using a TCP/IP connection:

Under UNIX, specifying a value of localhost causes a UNIX socket connection to be used. You can also specify a —socket option if necessary to indicate the socket file pathname:

Under Windows NT-based systems, a named pipe connection can be specified by giving ‘ . ‘ as a hostname, perhaps with a —socket option to indicate the pipe name:

For any platform, to connect via TCP/IP to a remote server running on another host, specify a —host option that indicates the name of the server host:

Specify a —port option as well if you need to connect to a port number other than the default.

Under UNIX, use the ps command to see the command line of any currently executing mysqld process or processes. By looking for a —datadir option, you may be able to determine the data directory location. If you have a BSD-style ps , try the following command:

For a System V-style ps , try this instead:

The ps command can be especially useful if your system runs multiple servers because you can discover multiple data directory locations at once. The drawbacks are that ps must be run on the server host and that no useful information is produced unless the —datadir option was specified explicitly on the mysqld command line. (On the other hand, some of the startup scripts that invoke mysqld for you attempt to determine the data directory pathname and put it in the mysqld command line, which makes that information available to ps .)

Look in an option file that the server reads when it starts up. For example, if you look in /etc/my.cnf under UNIX or C:\my.cnf under Windows, you may find a datadir line in the [mysqld] option group:

The pathname indicates the location of the data directory.

The server’s help message includes an indication of the default data directory location that is compiled in. This will tell you the directory that the server actually uses when it runs, if the location is not overridden at startup time. To see this output, issue the following command:

If you installed MySQL from a source distribution, you can examine its configuration information to determine the data directory location. For example, the location is available in the top-level Makefile . But be careful: The location is the value of the localstatedir variable in the Makefile , not the value of the datadir variable, as you might expect. Also, if the distribution is located on an NFS-mounted file system that is used to build MySQL for several hosts, the configuration information will be accurate only for the host for which the distribution was most recently built. That may not show you the data directory for the server in which you’re interested.

Failing any of the previous methods, you can use find to search for database files. The following command searches for .frm (description) files:

The .frm files store the definitions of the tables managed by the server, so they are part of any MySQL installation. These files normally will be found in directories that all have a common parent directory; that parent should be the data directory.

In the examples that follow throughout this chapter where I denote the location of the MySQL data directory as DATADIR , you should interpret that as the location of the data directory for the server on your own machine.

MySQL my.ini location

But I am still stuck with the ages old question! «Where is my my.ini» I am using windows server 2008 with mysql 5.5.28. I installed the service using mysqld —install and I am able to use the mysql server using sqlyog. But unfortunately I am not able to find my.ini in installation directory or not in c:\ neither in c:\windows nor in data_dir query show variables like «mysql_home» returned nothing as well.

15 Answers 15

You have to look I the folder C:\Program Files\MySQL\MySQL Server 5.5 but there is a problem. When you perform an MSI install of MySQL, my.ini is not created. There will be sample .ini files in that folder. In order to use one of them, say my-medium.ini , you need to do the following before a MySQL restart:

Once, you do this, my.ini can be read by C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe .

my.ini LOCATION ON WINDOWS MYSQL 5.6 MSI (USING THE INSTALL WIZARD)

Open a Windows command shell and type: echo %PROGRAMDATA% . On Windows Vista this results in: C:\ProgramData .

According to http://dev.mysql.com/doc/refman/5.6/en/option-files.html, the first location MySQL will look under is in %PROGRAMDATA%\MySQL\MySQL Server 5.6\my.ini . In your Windows shell if you do ls «%PROGRAMDATA%\MySQL\MySQL Server 5.6\my.ini» , you will see that the file is there.

Unlike most suggestions you will find in Stackoverflow and around the web, putting the file in C:\Program Files\MySQL\MySQL Server 5.6\my.ini WILL NOT WORK. Neither will C:\Program Files (x86)\MySQL\MySQL Server 5.1 . The reason being quoted on the MySQL link posted above:

On Windows, MySQL programs read startup options from the following files, in the specified order (top items are used first).

The 5.6 MSI installer does create a my.ini in the highest priority location, meaning no other file will ever be found/used, except for the one created by the installer.

The solution accepted above will not work for 5.6 MSI-based installs.

Mysql data directory windows

mysql_install_db is deprecated as of MySQL 5.7.6 because its functionality has been integrated into mysqld , the MySQL server. To initialize a MySQL installation, invoke mysqld with the —initialize or —initialize-insecure option. For more information, see Section 2.10.1, “Initializing the Data Directory”. You should expect mysql_install_db to be removed in a future MySQL release.

mysql_install_db handles initialization tasks that must be performed before the MySQL server, mysqld , is ready to use:

It initializes the MySQL data directory and creates the system tables that it contains.

It initializes the system tablespace and related data structures needed to manage InnoDB tables.

It loads the server-side help tables.

It installs the sys schema.

It creates an administrative account. Older versions of mysql_install_db may create anonymous-user accounts.

Secure-by-Default Deployment

Current versions of mysql_install_db produce a MySQL deployment that is secure by default, with these characteristics:

A single administrative account named ‘root’@’localhost’ is created with a randomly generated password, which is marked expired.

No anonymous-user accounts are created.

No test database accessible by all users is created.

—admin- xxx options are available to control characteristics of the administrative account.

The —random-password-file option is available to control where the random password is written.

The —insecure option is available to suppress random password generation.

If mysql_install_db generates a random administative password, it writes the password to a file and displays the file name. The password entry includes a timestamp to indicate when it was written. By default, the file is .mysql_secret in the home directory of the effective user running the script. .mysql_secret is created with mode 600 to be accessible only to the operating system user for whom it is created.

When mysql_install_db generates a random password for the administrative account, it is necessary after mysql_install_db has been run to start the server, connect using the administrative account with the password written to the .mysql_secret file, and specify a new administrative password. Until this is done, the administrative account cannot be used for anything else. To change the password, you can use the SET PASSWORD statement (for example, with the mysql or mysqladmin client). After resetting the password, remove the .mysql_secret file; otherwise, if you run mysql_secure_installation , that command may see the file and expire the root password again as part of ensuring secure deployment.

Invocation Syntax

Change location to the MySQL installation directory and use this invocation syntax:

The —datadir option is mandatory. mysql_install_db creates the data directory, which must not already exist:

If the data directory does already exist, you are performing an upgrade operation (not an install operation) and should run mysql_upgrade , not mysql_install_db . See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.

If the data directory does not exist but mysql_install_db fails, you must remove any partially created data directory before running mysql_install_db again.

Because the MySQL server, mysqld , must access the data directory when it runs later, you should either run mysql_install_db from the same system account used for running mysqld , or run it as root and specify the —user option to indicate the user name that mysqld runs under. It might be necessary to specify other options such as —basedir if mysql_install_db does not use the correct location for the installation directory. For example:

After mysql_install_db sets up the InnoDB system tablespace, changes to some tablespace characteristics require setting up a whole new instance. This includes the file name of the first file in the system tablespace and the number of undo logs. If you do not want to use the default values, make sure that the settings for the innodb_data_file_path and innodb_log_file_size configuration parameters are in place in the MySQL configuration file before running mysql_install_db . Also make sure to specify as necessary other parameters that affect the creation and location of InnoDB files, such as innodb_data_home_dir and innodb_log_group_home_dir .

If those options are in your configuration file but that file is not in a location that MySQL reads by default, specify the file location using the —defaults-extra-file option when you run mysql_install_db .

If you have set a custom TMPDIR environment variable when performing the installation, and the specified directory is not accessible, mysql_install_db may fail. If so, unset TMPDIR or set TMPDIR to point to the system temporary directory (usually /tmp ).

Administrative Account Creation

mysql_install_db creates an administrative account named ‘root’@’localhost’ by default.

mysql_install_db provides options that enable you to control several aspects of the administrative account:

To change the user or host parts of the account name, use —login-path , or —admin-user and —admin-host .

—insecure suppresses generation of a random password.

—admin-auth-plugin specifies the authentication plugin.

—admin-require-ssl specifies whether the account must use SSL connections.

For more information, see the descriptions of those options.

mysql_install_db assigns mysql.user system table rows a nonempty plugin column value to set the authentication plugin. The default value is mysql_native_password . The value can be changed using the —admin-auth-plugin option.

Default my.cnf File

As of MySQL 5.7.18, my-default.cnf is no longer included in or installed by distribution packages.

With one exception, the settings in the default option file are commented and have no effect. The exception is that the file sets the sql_mode system variable to NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES . This setting produces a server configuration that results in errors rather than warnings for bad data in operations that modify transactional tables. See Section 5.1.10, “Server SQL Modes”.

Command Options

mysql_install_db supports the following options, which can be specified on the command line or in the [mysql_install_db] group of an option file. For information about option files used by MySQL programs, see Section 4.2.2.2, “Using Option Files”.

Table 4.7 mysql_install_db Options

Option Name	Description
—admin-auth-plugin	Administrative account authentication plugin
—admin-host	Administrative account name host part
—admin-require-ssl	Require SSL for administrative account
—admin-user	Administrative account name user part
—basedir	Path to base directory
—builddir	Path to build directory (for out-of-source builds)
—datadir	Path to data directory
—defaults	Read default option files
—defaults-extra-file	Read named option file in addition to usual option files
—defaults-file	Read only named option file
—extra-sql-file	Optional SQL file to execute during bootstrap
—help	Display help message and exit
—insecure	Do not generate administrative account random password
—lc-messages	Locale for error messages
—lc-messages-dir	Directory where error messages are installed
—login-file	File to read for login path information
—login-path	Read login path options from .mylogin.cnf
—mysqld-file	Path to mysqld binary
—no-defaults	Read no option files
—random-password-file	File in which to write administrative account random password
—skip-sys-schema	Do not install or upgrade the sys schema
—srcdir	For internal use
—user	Operating system user under which to execute mysqld
—verbose	Verbose mode
—version	Display version information and exit

Display a help message and exit.

The authentication plugin to use for the administrative account. The default is mysql_native_password .

The host part to use for the adminstrative account name. The default is localhost . This option is ignored if —login-path is also specified.

Whether to require SSL for the administrative account. The default is not to require it. With this option enabled, the statement that mysql_install_db uses to create the account includes a REQUIRE SSL clause. As a result, the administrative account must use secure connections when connecting to the server.

The user part to use for the adminstrative account name. The default is root . This option is ignored if —login-path is also specified.

The path to the MySQL installation directory.

For use with —srcdir and out-of-source builds. Set this to the location of the directory where the built files reside.

The path to the MySQL data directory. Only the last component of the path name is created if it does not exist; the parent directory must already exist or an error occurs.

The —datadir option is mandatory and the data directory must not already exist.

This option causes mysql_install_db to invoke mysqld in such a way that it reads option files from the default locations. If given as —no-defaults , and —defaults-file or —defaults-extra-file is not also specified, mysql_install_db passes —no-defaults to mysqld , to prevent option files from being read. This may help if program startup fails due to reading unknown options from an option file.

Read this option file after the global option file but (on Unix) before the user option file. If the file does not exist or is otherwise inaccessible, an error occurs. If file_name is not an absolute path name, it is interpreted relative to the current directory.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

Use only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs. If file_name is not an absolute path name, it is interpreted relative to the current directory.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

This option names a file containing additional SQL statements to be executed after the standard bootstrapping statements. Accepted statement syntax in the file is like that of the mysql command-line client, including support for multiple-line C-style comments and delimiter handling to enable definition of stored programs.

Do not generate a random password for the adminstrative account.

If —insecure is not given, it is necessary after mysql_install_db has been run to start the server, connect using the administrative account with the password written to the .mysql_secret file, and specify a new administrative password. Until this is done, the administrative account cannot be used for anything else. To change the password, you can use the SET PASSWORD statement (for example, with the mysql or mysqladmin client). After resetting the password, remove the .mysql_secret file; otherwise, if you run mysql_secure_installation , that command may see the file and expire the root password again as part of ensuring secure deployment.

The locale to use for error messages. The default is en_US . The argument is converted to a language name and combined with the value of —lc-messages-dir to produce the location for the error message file. See Section 10.12, “Setting the Error Message Language”.

The directory where error messages are located. The value is used together with the value of —lc-messages to produce the location for the error message file. See Section 10.12, “Setting the Error Message Language”.

The file from which to read the login path if the —login-path= file_name option is specified. The default file is .mylogin.cnf .

Read options from the named login path in the .mylogin.cnf login path file. The default login path is client . (To read a different file, use the —login-file= name option.) A “ login path ” is an option group containing options that specify which MySQL server to connect to and which account to authenticate as. To create or modify a login path file, use the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.

If the —login-path option is specified, the user, host, and password values are taken from the login path and used to create the administrative account. The password must be defined in the login path or an error occurs, unless the —insecure option is also specified. In addition, with —login-path , any —admin-host and —admin-user options are ignored.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

The path name of the mysqld binary to execute. The option value must be an absolute path name or an error occurs.

If this option is not given, mysql_install_db searches for mysqld in these locations:

In the bin directory under the —basedir option value, if that option was given.

In the bin directory under the —srcdir option value, if that option was given.

In the bin directory under the —builddir option value, if that option was given.

In the local directory and in the bin and sbin directories under the local directory.

In /usr/bin , /usr/sbin , /usr/local/bin , /usr/local/sbin , /opt/local/bin , /opt/local/sbin .

For behavior of this option, see the description of —defaults .

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

The path name of the file in which to write the randomly generated password for the administrative account. The option value must be an absolute path name or an error occurs. The default is $HOME/.mysql_secret .

mysql_install_db installs the sys schema. The —skip-sys-schema option suppresses this behavior.

For internal use. This option specifies the directory under which mysql_install_db looks for support files such as the error message file and the file for populating the help tables.

The system (login) user name to use for running mysqld . Files and directories created by mysqld are owned by this user. You must be the system root user to use this option. By default, mysqld runs using your current login name; files and directories that it creates are owned by you.

Verbose mode. Print more information about what the program does. You can use this option to see the mysqld command that mysql_install_db invokes to start the server in bootstrap mode.