How do I find the MySQL my.cnf location
Is there a MySQL command to locate the my.cnf configuration file, similar to how PHP’s phpinfo() locates its php.ini ?
30 Answers 30
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.
Those are the default locations MySQL looks at. If it finds more than one, it will load each of them & 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.)
Run mysql —help and you will see:
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
Run mysql —help and you will see 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
in my case there was an symlink etc/mysql/my.cnf pointing to another symlink /etc/alternatives/my.cnf which points to etc/mysql/mysql.cnf .
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:
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 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.
Looked promising but on my distro (opencsw.org), there is not mysqld. It runs mysqld_safe. «mysqld_safe —verbose —help» is not recognized.
when I ran it, I got this: Default options are read from the following files in the given order: /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf — I expected the first 2 files in reverse order.
The information posted by mysqld —help —verbose contradicts what’s stated in dev.mysql.com/doc/refman/5.5/en/option-files.html. In my «recent as of today» experience with version 5.6, the information in the website is the most correct and relevant. The priority of the files location given by the help command is misleading and will lead to negative results.
Note that I had to increase the cmd buffer size to 3000 to be able to actually see the first lines, otherwise the text was simply overwritten. When they say «verbose», apparently, they mean it.
You could always run find in a terminal.
it’s the hard way 🙁 Is there any mysql command like the phpinfo() to know the config file location ?
find / -name my.cnf is your best bet, but you could also check your home directory and /etc/mysql/my.conf You can also see if your MYSQL_HOME is set by typing echo $MYSQL_HOME in a terminal
Wow, that would take forever on most machines. Most modern linuxes have locate installed and so long as updatedb is run regularly you can do a: locate my.cnf | less
This only shows what files are named my.cnf. It doesn’t tell you which on the mysqld process found and opened.
This doesn’t find ~/.my.cnf — note the leading dot in the file name. Also running a find over the entire file system will usually generate scads of «Permission denied» errors unless you are root. So the find command should be find / -name ‘*my.cnf’ 2>/dev/null .
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
I know I am late to the party — but what if my.cnf doesn’t exist anywhere? ls: /etc/my.cnf: No such file or directory ls: /etc/mysql/my.cnf: No such file or directory ls: /usr/local/mysql/etc/my.cnf: No such file or directory ls: ~/.my.cnf: No such file or directory
This is the only solution that worked for me. The rest were waste of time. To avoid an error message, use it this way: mysql —help | grep /my.cnf . I am using centos.
locate my.cnf whereis my.cnf find . -name my.cnf whereis won’t work here; it searches for the locations corresponding to a command, and can’t find arbitrary files.
strace mysql ";" 2>&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", ) = 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.
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
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=, MASTER_PORT=, # MASTER_USER=, MASTER_PASSWORD= ; # # where you replace , , by quoted strings and # 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 = # # The username the slave will use for authentication when connecting # to the master - required #master-user = # # The password the slave will authenticate with when connecting to # the master - required #master-password = # # The port the master is listening on. # optional - defaults to 3306 #master-port = # # 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 Where is mysql’s configuration file in ubuntu server 18.04
I recently installed MySql Server 8.0.19 on and EC2 (t2.micro) instance running Ubuntu 18.04. I’m trying to find the MySql configuration file that’s like the my.ini on Windows. I’ve looked through almost every file in /etc/mysql/ but none of them are similar to the my.ini that windows uses. Where is this file located on Ubuntu 18.04 and where are the configuration settings read from?
3 Answers 3
Do an update of the locate database and search for my.cnf
sudo locatedb locate my.cnf /etc/mysql/my.cnf /home/$USER/.my.cnf That is the GENERIC method of finding files on a server.
That does not use a generic configuration. The name depends on the type of instance: my-small.cnf , my-medium.cnf , my-large.cnf . Use that for the locate .
Mind also that EC settings often need to be done from within the console and not on command line!
I’ve looked through /etc/mysql/my.cnf, which finally points to /var/mysql/mysql.conf.d/mysqld.cnf which only has [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log So where is it getting the values for long_query_time etc?
You did not find one of my-small.cnf, my-medium.cnf, my-large.cnf? Please edit the question to include what the type is of the instance. I would start with the EC2 console; that is where I mosttimes see these things happen. It is totally possible that this is done in memory and not on-disk. (we have EC2 instances and have a my-large.cnf but the file is in a virtual directory).