How do I find the MySQL my cnf location

Question

Is there a MySQL command to locate the my cnf configuration file  similar to how PHP s phpinfo    locates its php ini

User · Answer

If you re on a Mac with Homebrew  use      brew info mysql   You ll see something like     brew info mysql mysql  stable 5 6 13  bottled  http   dev mysql com doc refman 5 6 en  Conflicts with  mariadb  mysql-cluster  percona-server  usr local Cellar mysql 5 6 13  9381 files  354M      That last line is the INSTALLERDIR per the MySQL docs

User · Answer

In case you are in a VPS and are trying to edit a my cnf on an already running server you could try   ps aux   grep mysql   You will be show the parameters the mysql command is being run and where the --defaults-file points to  Note that your server might be running more than one MySQL MariaDB server s  If you see a line without --defaults-file parameter  that instance might be retrieving the configuration from the  cnf s that are mentioned on mysqladmin --help as others have pointed out

User · Answer

mysql --help   grep  my cnf   xargs ls   will tell you where my cnf is located on Mac Linux  ls  cannot access   etc my cnf   No such file or directory ls  cannot access     my cnf   No such file or directory   etc mysql my cnf   In this case  it is in  etc mysql my cnf  ls   etc my cnf  No such file or directory ls   etc mysql my cnf  No such file or directory ls     my cnf  No such file or directory  usr local etc my cnf   In this case  it is in  usr local etc my cnf

User · Answer

Be aware that although mariadDB loads configuration details from the various my cnf files as listed in the other answers here  it can also load them from other files with different names    That means that if you make a change in one of the my cnf files  it may get overwritten by another file of a different name  To make the change stick  you need to change it in the right  last loaded  config file - or  maybe  change it in all of them   So how do you find all the config files that might be loaded  Instead of looking for my cnf files  try running   grep -r datadir  etc mysql    This will find all the places in which datadir is mentioned  In my case  it produces this answer    etc mysql mariadb conf d 50-server cnf datadir        var lib mysql    When I edit that file   etc mysql mariadb conf d 50-server cnf  to change the value for datadir  it works  whereas changing it in my cnf does not  So whatever option you are wanting to change  try looking for it this way

User · Answer

This might work   strace mysql     2 gt  amp 1    grep cnf   on my machine this outputs   stat64   etc my cnf   0xbf9faafc          -1 ENOENT  No such file or directory  stat64   etc mysql my cnf    st mode S IFREG 0644  st size 4271          0 open   etc mysql my cnf   O RDONLY O LARGEFILE    3 read 3      etc mysql my cnf  The global       4096    4096 stat64   home xxxxx  my cnf   0xbf9faafc    -1 ENOENT  No such file or directory    So it looks like  etc mysql my cnf is the one since it stat64   and read   were successful

User · Answer

There is no internal MySQL command to trace this  it s a little too abstract   The file might be in 5  or more   locations  and they would all be valid because they load cascading     etc my cnf  etc mysql my cnf  MYSQL HOME my cnf  datadir  my cnf    my cnf   Those are the default locations MySQL looks at   If it finds more than one  it will load each of them  amp  values override each other  in the listed order  I think    Also  the --defaults-file parameter can override the whole thing  so    basically  it s a huge pain in the butt   But thanks to it being so confusing  there s a good chance it s just in  etc my cnf    if you just want to see the values  SHOW VARIABLES  but you ll need the permissions to do so

User · Answer

By default  mysql search my cnf first at  etc folder  If there is no  etc my cnf file inside this folder  I advise you to create new one in this folder as indicated by the documentation  https   dev mysql com doc refman 5 6 en option-files html    You can also search for existing my cnf furnished by your mysql installation  You can launch the following command  sudo find   -name    cnf    You can use the following configuration file with myisam table and without innodb mysql support  from port installation of mysql on mac os x maverick   Please verify each command in this configuration file     Example MySQL config file for large systems      This is for a large system with memory   512M where the system runs mainly   MySQL      MySQL programs look for option files in a set of   locations which depend on the deployment platform    You can copy this option file to one of those   locations  For information about these locations  see    http   dev mysql com doc mysql en option-files html     In this file  you can use all long options that a program supports    If you want to know which options a program supports  run the program   with the  --help  option     The following options will be passed to all MySQL clients  client   password     your password port          3306 socket         opt local var run mysql5 mysqld sock    Here follows entries for some specific programs    The MySQL server  mysqld  port          3306 socket         opt local var run mysql5 mysqld sock skip-locking key buffer size   256M max allowed packet   1M table open cache   256 sort buffer size   1M read buffer size   1M read rnd buffer size   4M myisam sort buffer size   64M thread cache size   8 query cache size  16M   Try number of CPU s 2 for thread concurrency thread concurrency   8    Don t listen on a TCP IP port at all  This can be a security enhancement    if all processes that need to connect to mysqld run on the same host    All interaction with mysqld must be made via Unix sockets or named pipes    Note that using this option without enabling named pipes on Windows    via the  enable-named-pipe  option  will render mysqld useless      skip-networking    Replication Master Server  default    binary logging is required for replication log-bin mysql-bin    binary logging format - mixed recommended binlog format mixed    required unique id between 1 and 2 32 - 1   defaults to 1 if master-host is not set   but will not function as a master if omitted server-id     1    Replication Slave  comment out master section to use this      To configure this host as a replication slave  you can choose between   two methods       1  Use the CHANGE MASTER TO command  fully described in our manual  -      the syntax is         CHANGE MASTER TO MASTER HOST  lt host gt   MASTER PORT  lt port gt        MASTER USER  lt user gt   MASTER PASSWORD  lt password gt           where you replace  lt host gt    lt user gt    lt password gt  by quoted strings and       lt port gt  by the master s port number  3306 by default          Example         CHANGE MASTER TO MASTER HOST  125 564 12 1   MASTER PORT 3306       MASTER USER  joe   MASTER PASSWORD  secret       OR     2  Set the variables below  However  in case you choose this method  then      start replication for the first time  even unsuccessfully  for example      if you mistyped the password in master-password and the slave fails to      connect   the slave will create a master info file  and any later      change in this file to the variables  values below will be ignored and      overridden by the content of the master info file  unless you shutdown      the slave server  delete master info and restart the slaver server       For that reason  you may want to leave the lines below untouched       commented  and instead use CHANGE MASTER TO  see above      required unique id between 2 and 2 32 - 1    and different from the master    defaults to 2 if master-host is set   but will not function as a slave if omitted  server-id         2     The replication master for this slave - required  master-host          lt hostname gt      The username the slave will use for authentication when connecting   to the master - required  master-user          lt username gt      The password the slave will authenticate with when connecting to   the master - required  master-password      lt password gt      The port the master is listening on    optional - defaults to 3306  master-port         lt port gt      binary logging - not required for slaves  but recommended  log-bin mysql-bin    Uncomment the following if you are using InnoDB tables  innodb data home dir    opt local var db mysql5  innodb data file path   ibdata1 10M autoextend  innodb log group home dir    opt local var db mysql5   You can set    buffer pool size up to 50 - 80     of RAM but beware of setting memory usage too high  innodb buffer pool size   256M  innodb additional mem pool size   20M   Set    log file size to 25   of buffer pool size  innodb log file size   64M  innodb log buffer size   8M  innodb flush log at trx commit   1  innodb lock wait timeout   50   mysqldump  quick max allowed packet   16M   mysql  no-auto-rehash   Remove the next comment character if you are not familiar with SQL  safe-updates   myisamchk  key buffer size   128M sort buffer size   128M read buffer   2M write buffer   2M   mysqlhotcopy  interactive-timeout

User · Answer

Found mine using  mysqld --help --verbose   grep my cnf

User · Answer

I installed xampp bundle with apache  php and mysql in ubuntu  There my cnf file is located in  opt lampp etc  folder  Hope it ll help somebody

User · Answer

Answered for only MySQL Workbench users

User · Answer

You will have to look through the various locations depending on your version of MySQL    mysqld --help -verbose   grep my cnf  For Homebrew   usr local Cellar mysql 8 0 11 bin mysqld  mysqld 8 0 11   Default possible locations   etc my cnf  etc mysql my cnf    my cnf  Found mine here    usr local etc my cnf

User · Answer

If you are using MAMP  access Templates   MySQL  my cnf     version   If you are running MAMP windowless you may need to customize the toolbar using the Customize button

User · Answer

As noted by konyak you can get the list of places mysql will look for your my cnf file by running mysqladmin --help  Since this is pretty verbose you can get to the part you care about quickly with     mysqladmin --help   grep -A1  Default options    This will give you output similar to   Default options are read from the following files in the given order   etc my cnf  etc mysql my cnf  usr local etc my cnf    my cnf   Depending on how you installed mysql it is possible that none of these files are present yet  You can cat them in order to see how your config is being built and create your own my cnf if needed at your preferred location

User · Answer

Another option is to use the whereis command   E g  whereis my cnf

User · Answer

Try running mysqld --help --verbose   grep my cnf   tr       n   Output will be something like   etc my cnf  etc mysql my cnf  usr local etc my cnf    my cnf

User · Answer

It depend on your access right but for me this work on phpmyadmin sql console  SHOW VARIABLES   then after to change some variables you can do   SET GLOBAL max connections   1000   or  SET   GLOBAL max connections   1000   give a try

User · Answer

You can actually  request  MySQL for a list of all locations where it searches for my cnf  or my ini on Windows   It is not an SQL query though  Rather  execute     mysqladmin --help   or  prior 5 7     mysqld --help --verbose   In the very first lines you will find a message with a list of all my cnf locations it looks for  On my machine it is   Default options are read from the following files in the given order   etc my cnf  etc mysql my cnf  usr etc my cnf    my cnf   Or  on Windows   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 C  Program Files MySQL MySQL Server 5 5 my ini C  Program Files MySQL MySQL Server 5 5 my cnf   Note however  that it might be the case that there is no my cnf file at any of these locations  So  you can create the file on your own - use one of the sample config files provided with MySQL distribution  on Linux - see  usr share mysql   cnf files and use whichever is appropriate for you - copy it to  etc my cnf and then modify as needed    Also  note that there is also a command line option --defaults-file which may define custom path to my cnf or my ini file  For example  this is the case for MySQL 5 5 on Windows - it points to a my ini file in the data directory  which is not normally listed with mysqld --help --verbose  On Windows - see service properties to find out if this is the case for you   Finally  check the https   dev mysql com doc refman 8 0 en option-files html - it is described there in more details

User · Answer

I don t know how you ve setup MySQL on your Linux environment but have you checked     etc my cnf

User · Answer

On Ubuntu  direct edit        sudo nano  etc mysql conf d mysqld cnf

User · Answer

All great suggestions  in my case I didn t find it in any of those locations  but in  usr share mysql  I have a RHEL VM and I installed mysql5 5

User · Answer

For Ubuntu 16   etc mysql mysql conf d mysqld cnf

User · Answer

You can use    locate my cnf whereis my cnf find   -name my cnf

User · Answer

You could always run find in a terminal   find   -name my cnf

User · Answer

You can also run this command    mysql --help   grep cnf

User · Answer

for me it was that i had  ENGINE MyISAM  kind of tables   once i changed it to   ENGINE InnoDB  it worked   in PhpMyAdmin on Azure App Service

[mysql] How do I find the MySQL my.cnf location

Examples related to mysql

Examples related to linux