Where is MySQL's my.ini located on Windows?

Question

I have already seen http://dev.mysql.com/doc/refman/4.1/en/mysql-config-wizard-file-location.html

How do I find the MySQL my.cnf location

and

http://dev.mysql.com/doc/refman/5.1/en/option-files.html

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.

Any suggestions?

Ezana

In MySQL 5.6 the data and my.ini are stored in (in my case at least) C:\ProgramData\MySQL\MySQL 5.6 – Coterminous

Answer 1

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:

cd C:\Program Files\MySQL\MySQL Server 5.5
copy my-medium.ini my.ini
net stop mysql
net start mysql

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

Novercal

so from what you are saying.. My server is not using any my.ini is it? It also seems like the case because I just renamed all my-[size].ini and mysql still works correctly!!! – Ezana

If you use MSI installer, it creates a my.ini in %PROGRAMDATA%\MySQL\MySQL Server X.Y\ and uses it. So this solution will not work with it. – Woodcraft

To run the MySQL service with the my.ini file you created, you need to stop the current service first net stop mysql and then remove it: sc remove mysql. Then you need to install a new service: "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --install newMySQL --defaults-file="C:\Program Files\MySQL\MySQL Server 5.7\my.ini" Now you can run the service with net start newmysql command. – Woodcraft

Just want to improve comment, it should be 'sc delete' not 'sc remove' on windows 10. – Undrape

that was really helpful thanks. Just wanted to point out that it can be installed pointing to the ini file in the service so (in windows obviously) it is worth looking in services panel first to see if there is a --defaults-file switch pointing to the ini file in a different location. Many thanks to RolandoMySQLDBA & NurShomik for getting me 99% of the way there ! – Faliscan

For MySQL 5.7 on windows my.ini location may be at "C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" – Dup

This answer may not be correct. I have no *.ini files whatsoever in my installation directory. @Woodcraft is right. – Lineolate

Yes, Me too could not find in my MySQL 5.7, I am trying to upgrade to MySQL 8.0, Is it the same case for MySQL 8.0 as well? I could not see the my.ini and other sample my.ini files as well. – Forcefeed

Answer 2

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.

Lockard

Yep, mine was in %PROGRAMDATA%\MySQL\MySQL Server 5.6. I would have prefered for it to be in the program files though... – Siberia

@amateur-barista, do you know how to make the installer to use the my.ini file in Program Files instead of the one in ProgramData? Also, how to make MySQL Workbench to use the one I provide, instead of the one created by installer? – Woodcraft

The whole sentence is "On Windows, MySQL programs read startup options from the following files, in the specified order (top files are read first, later files take precedence)." I don't understand, Why is that the highest priority location? – Aylesbury

Then why the heck does even MySQL workbench suggests in many places, that the one from Installation directory is used? And why is this not mentioned in MySQL docs (linked from my-default.ini). I am very frustrated :-( – Amenity

Answer 3

1. Enter "services.msc" on the Start menu search box.
2. Find MySQL service under Name column, for example, MySQL56.
3. Right click on MySQL service, and select Properties menu.
4. Look for "Path To Executable" under General tab, and there is your .ini file, for instance, "C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MYSQL56

Feoff

I just see "C:\...\mysql\bin\mysqld MySQL" with MariaDB install on Windows Server – Leishaleishmania

Answer 4

I've found mine in

\ProgramData\MySQL\MySQL Server 8.0\

(It is a hidden folder)

You can type win+R and write %PROGRAMDATA% to access that folder, or just enable show hidden folder.

Rickierickman

Answer 5

Start MySQL Workbench, then Server -> Options File and look at bottom of the window; it will say something like "Configuration File: C:\ProgramData\MySQL\MySQL Server 5.6\my.ini"

(And note the subtle difference between "ProgramData" and "Program Files" - easy to gloss over if you're looking for a quick answer.)

Hydracid

FWIW: In MySQL Workbench 6.1, there's no Server item on the main menu, unless you open a database. So first you need to open the localhost database. Then you can select Server > Options File. In my system, that brought up an dialog box saying that no my.ini file was found at C:\Program Files\MySQL\MySQL Server 5.0\my.ini (but one would be created). A bit puzzling since I'm running a later version of MySQL, and it's installed somewhere else. So, not quite at the bottom of this mystery. – Dissatisfactory

Answer 6

Answered for only MySQL Workbench users,

Bashkir

Answer 7

You can find the my.ini file in windows at this location- C:\ProgramData\MySQL\MySQL Server 5.6

the ProgramData folder is a hidden folder, so make the according setting to see that folder. And open my.ini file as an administrator to edit and then save that.

Pleomorphism

Answer 8

In my case, the folder ProgramData was hidden by default on windows 7, so I was unable to find my.ini file.

After selecting show hidden files and folders option, I was able to find the my.ini file at the location: C:\ProgramData\MySQL\MySQL Server 5.6.

Display hidden files and folders on windows 7:

1. Right-click the Windows Logo button and choose Open Windows Explorer.

2. Click Organize and choose Folder and Search Options.

3. Click the View tab, select Show hidden files and folders and then clear the checkbox for Hide protected system operating files.

4. Click Yes on the warning and then click OK.

Kilmarx

Answer 9

MySQL and MariaDB

According to the documentation of both MySQL and MariaDB you need to run mysql with --help --verbose to know the location of the my.ini file

Go to the command or terminal

cd yourMySQLBinDirectory
mysqld --verbose --help

You will see large output but first few lines will show the order the server is looking for the file. I got the following response. It will be different for your computer or server.

Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf D:\Databases\mariadb-10.3.30-winx64\my.ini D:\Databases\mariadb-10.3.30-winx64\my.cnf D:\Databases\mariadb-10.3.30-winx64\data\my.ini D:\Databases\mariadb-10.3.30-winx64\data\my.cnf

Maryannamaryanne

On Windows PowerShell you could simply run: mysqld --verbose --help | sls -Context 1 "Default options are read from the following files in the given order:" And on Mac/Linux: mysqld --verbose --help | grep -A 1 "Default options are read from the following files in the give n order:" – Shrier

Answer 10

Press the windows key > type services > press enter > Look up mysql in the list > right click > properties > Path to Executable will have the location of the defaults file right below it (my.ini)

Nikolia

Answer 11

it is there at C:\Program Files\MySQL\MySQL Server 5.5 there are various .ini files with small, medium & large names. generally medium is used or it depends on your requirement.

Bootie

Oops... I thought by default my.ini should be used. You are correct about all these files. They are there in the install directory. How would you confirm which one is used? Well, I will just move the file temporarily and see if mysql service restarts or not. – Ezana

you can use any of them based on your requirement. crate my.ini file by copying contents from any of these files (follow below mentioned answer for commands) then you can use or customize your my.ini & mysql will also use this my.ini file – Bootie

Answer 12

programData is hidden folder so you have to change the option from setting to show hidden folder and then make the change in my.ini file present in that.

Be sure to update the correct my.ini file because it can waste a lot of your time if you keep updating wrong file.

You can look into service to see which my.ini is configured in this service.

Teniacide

Answer 13

on Windows if MySQL is install as a service you can change the binpath of the service. For example

sc config MySQL57 binPath= "\"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe\" --defaults-file=\"<myini path>" MySQL57"

space after binpath is important. You must escape double quotes

Beare

Answer 14

Open your run console type: services.msc look for: mysql right click properties where is written "path to executable", click and move the cursor to the right until you see the directory of my.ini, it's written "defaults-file-". to reach it manually on your explore folders you have to enable the visualization of hidden elements (explore folder>top menu>visualize>visualize hidden elements)

as explained by this video

https://www.youtube.com/watch?v=SvCAa2XuQhg

Formic

Answer 15

For MySql Server 8.0 The default location is %WINDIR% or C:\Windows.

You need to add a "my.ini" file there.

Here's a sample of what I put in the ini file.

[mysqld]

secure_file_priv=""

Make sure to restart the MySQL service after that.

Ligula

Didn't work for me with MySql 8.0.32 and global variable sql_mode. – Sumptuary

Answer 16

On Windows, I could find my.ini at the location below. *ProgramData folder is hidden by default so you need to show it following this:

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Rato

Answer 17

I met with the same problem when I did MSI install of MySQL and there were no my-medium.ini files too when I tried the above steps. Only installing the ZIP file of MySQL helped me. So, I suggest you to uninstall the MSI installed folder and reinstall using the ZIP file.

Disagree