MySQL Options File

Everything about options file

-----------------------------

* mysql programs can read startup options from a file

* Useful for commonly used options.

* Use "--[help|verbose]" to check if program can use an option file.

* If multiple instance of an option is found, the last one takes precedence

  except for --user.

* Format: OPTION_NAME=VALUE

* Any long option can be provided in the options file.

* tT specify --loose-OPT, use "loose-OPT=" format.

* Comments starts with "#" or ";".

* [group] -> name of program or group on where to set options (case-insensitive)

* Leading/trailing spaces are deleted ("user=root" is same as "user = root").

* Escape sequences that can be used: \b, \t, \n, \r, \\, and \s

* "!include /home/mydir/myopt.cnf" includes another option file

* Option files included via "!include" must end in .cnf for Unix/Linux and

  .ini/.cnf for Windows.

* "!includedir /home/mydir" looks for options files in that dir and include them

* There's no order on how option files are searched in a directory

* In an included option file, only the groups the current program is looking for

  is processed. For example, inside "!include /home/mydir/myopt.cnf", mysqladmin

  will only process options under [mysqladmin] group and will ignore other

  options from other groups.

Sample Option files

-------------------

.mylogin.cnf

- contains login path options - encrypted file created by mysql_config_editor - "login path" is an option group that permit the ff:     * host     * user     * password     * port     * socket - MYSQL_TEST_LOGIN_FILE -> alternative login path

Option files in Windows

-----------------------

* bottom part takes precedence

* MySQL reads from top to bottom

File Name	Purpose
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini, %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf	Global options %PROGRAMDATA% paths: Windows Vista and higher: C:\ProgramData Older versions of Windows: C:\Documents and Settings\All Users\Application Data
%WINDIR%\my.ini, %WINDIR%\my.cnf	Global options %WINDIR% --> commonly C:\WINDOWS
C:\my.ini, C:\my.cnf	Global options
BASEDIR\my.ini, BASEDIR\my.cnf	Global options
defaults-extra-file	The file specified with --defaults-extra-file, if any
%APPDATA%\MySQL\.mylogin.cnf	Login path options (clients only)

Option files for Unix/Linux

---------------------------

* similar to how windows option files are read??

File Name	Purpose
/etc/my.cnf	Global options
/etc/mysql/my.cnf	Global options
SYSCONFDIR/my.cnf	Global options SYSCONFDIR:   -> location specified in Cmake   -> default is /etc
$MYSQL_HOME/my.cnf	Server-specific options (server only)
defaults-extra-file	The file specified with --defaults-extra-file, if any
~/.my.cnf	User-specific options
~/.mylogin.cnf	User-specific login path options (clients only)

CLI Options affecting Options File

----------------------------------

* --print-defaults may be used immediately after --defaults-file,

  --defaults-extra-file, or --login-path.

* --install must come first before --defaults-file (Windows).

* Avoid using "~" on filenames as option values

  (e.g --default-extra-file=~/file)

* Some important options (see manpage for details).

--defaults-extra-file=file_name

--defaults-file=file_name

--defaults-group-suffix=str

--login-path=name

--print-defaults

Some Examples

-------------

Different way of escaping	All of these are the same:   basedir="C:\Program Files\MySQL\MySQL Server 5.7" basedir="C:\\Program Files\\MySQL\\MySQL Server 5.7" basedir="C:/Program Files/MySQL/MySQL Server 5.7" basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.7
Example groups	[mysqld]    -> server options [mysql]     -> client options [client]    -> you can put credentials here [mysqldump] -> for mysqldump only (not for clients); overrides options under [client] [mysql-5.7] -> reads by specific mysql release
Typical global option file	[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock key_buffer_size=16M max_allowed_packet=8M [mysqldump] quick
Typical user option file	[client] # The following password will be sent to all standard MySQL clients password="my password" [mysql] no-auto-rehash connect_timeout=2