Mysql server 8.0 my ini

2.3.4.2 Creating an Option File

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 are different from the default locations (C:\Program Files\MySQL\MySQL Server 8.0 and C:\Program Files\MySQL\MySQL Server 8.0\data).

• You need to tune the server settings, such as memory, cache, or InnoDB configuration information.

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.2.2, “Using Option Files”). The Windows directory typically is named something like C:\WINDOWS. You can determine its exact location from the value of the WINDIR environment variable using the following command:

C:\> 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 C: 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.

Note

When using the MySQL Installer to install MySQL Server, it creates the my.ini at the default location, and the user executing MySQL Installer is granted full permissions to this new my.ini file.

In other words, be sure that the MySQL Server user has permission to read the my.ini file.

You can also make use of the example option files included with your MySQL distribution; see Section 5.1.2, “Server Configuration Defaults”.

An option file can be created and modified with any text editor, such as Notepad. For example, if MySQL is installed in E:\mysql and the data directory is in E:\mydata\data, you can create an option file containing 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

Microsoft 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.2.2, “Using Option Files”.

The ZIP archive does not include a data directory. To initialize a MySQL installation by creating the data directory and populating the tables in the mysql system database, initialize MySQL using either --initialize or --initialize-insecure. For additional information, see Section 2.10.1, “Initializing the Data Directory”.

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, if you want to use E:\mydata as the data directory instead, you must do two things:

1. Move the entire data directory and all of its contents from the default location (for example C:\Program Files\MySQL\MySQL Server 8.0\data) to E:\mydata.

2. Use a --datadir option to specify the new data directory location each time you start the server.

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.

answered Dec 1, 2013 at 10:46

amateur baristaamateur barista

4,2303 gold badges24 silver badges37 bronze badges

4

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

answered Feb 23, 2015 at 22:35

Bae Cheol ShinBae Cheol Shin

1,4001 gold badge10 silver badges10 bronze badges

1

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.

sɐunıɔןɐqɐp

2,95515 gold badges34 silver badges38 bronze badges

answered Jul 3, 2018 at 6:13

Mangue SutcliffMangue Sutcliff

1,2091 gold badge10 silver badges16 bronze badges

0

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.

superjos

11.7k5 gold badges85 silver badges128 bronze badges

answered Jan 30, 2013 at 6:51

RolandoMySQLDBARolandoMySQLDBA

43.2k16 gold badges89 silver badges130 bronze badges

8

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.)

answered Sep 2, 2014 at 15:29

ChiraelChirael

2,9354 gold badges27 silver badges28 bronze badges

1

Answered for only MySQL Workbench users,

answered Jul 3, 2017 at 11:27

MAXMAX

1,5044 gold badges16 silver badges23 bronze badges

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.

answered Aug 28, 2015 at 9:12

anubhsanubhs

5568 silver badges26 bronze badges

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.

answered Feb 6, 2017 at 14:38

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.

answered Jan 30, 2013 at 6:28

RaulRaul

5791 gold badge5 silver badges17 bronze badges

2

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.

answered Apr 5, 2018 at 10:06

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)

answered Jul 31, 2019 at 20:50

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

answered Feb 11, 2019 at 11:35

Tms91Tms91

2,7842 gold badges34 silver badges61 bronze badges

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

answered Oct 3, 2021 at 14:56

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.

answered Jan 22, 2017 at 4:58

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

answered May 14, 2020 at 10:59

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.

answered Dec 10, 2020 at 20:33

user890332user890332

1,21714 silver badges15 bronze badges

Not the answer you're looking for? Browse other questions tagged mysql windows mysql-5.5 or ask your own question.