How Does MySQL Configuration Work?

If you’ve found yourself in the MySQL space at least for a little while, chances are that you have heard about one of its well-known files: my.cnf. my.cnf is a configuration file exclusive to MySQL and all of its flavors and it’s widely regarded as the “go-to” file whenever MySQL configuration errors occur. Sure, we can set up a couple of settings when we start MySQL with the “--” options, but that doesn’t do much – it’s much more effective to set the settings inside of the configuration file instead.

Why Does MySQL Need a Configuration File?

First things first, we will look into why does MySQL need a configuration file in the first place. After all, MySQL is a powerful beast as-is, right? Well, not quite. You see, these days MySQL is running on a very wide variety of infrastructures and database servers – some servers might have 20TB of hard drive space and 256GB of RAM allocated to them, others – like small virtual private servers or the like – might only have 2GB of space and 256MB of RAM. The difference here is huge and the primary purpose of configuration files in this scenario is to provide an “endpoint” for MySQL DBAs and developers to configure it according to their requirements.

How Does MySQL Configuration Work?

Here’s how the initial MySQL configuration file – my.ini – looks like on Windows (do note that such a file is called my.cnf on Linux and that there it doesn't have any comments inside of it, so you will see lesser options over there.)

Do not get too taken aback here – as noted, the my.ini file has a lot of comments because it’s being run in a Windows-based environment. my.cnf files based on Linux do not offer such a thing – there, you would just have a couple of settings relevant to one of MySQL’s storage engines – InnoDB – and that’s it; you would need to define everything else yourself.

However, back to the configuration file. You are able to see that the configuration has many settings unique to itself – most configuration files would have the following settings and while some of these settings (the verbosity of error logs, etc.) might not be visible in my.cnf, they are always available and can be added at any time.

1. Settings relevant to the MySQL client itself – we would be able to see a port and a socket that is defined, we would be able to see the default authentication plugin that is being used and, of course, change it if we wish, etc.
2. Next up, we would see the definitions of directories that are necessary for MySQL to function correctly. The settings defining directories would also define where error and other logs are stored, where the data directory of MySQL is located, etc.
3. Next, we would see the settings relevant to various storage engines that are being used. Here, MySQL defines settings one of the flagman storage engines – InnoDB (we have also discussed how you can use such a storage engine to work with bigger data sets in the past) – and also MyISAM.
4. We will also be able to set the verbosity of error logs. In other words, define what errors will be shown, etc.
5. We would be able to set the default language that is used by MySQL by observing or changing the “ lc-messages ” parameter.
6. To avoid warning messages, we will also be able to set the secure_file_priv directory. This directory is used when LOAD DATA INFILE queries are being run – we will only be able to run queries that acquire files from this directory.
7. We will be able to make MySQL avoid using certain storage engines. for example, by using the “ skip-federated ” parameter, we would make MySQL skip the Federated storage engine by default.
8. We will be able to set up replication and modify the behavior of MySQL’s default scripts like mysqldump, etc.
9. Last but not least, we will also be able to fiddle with the SQL modes a little meaning that we can make our MySQL server operate differently in different scenarios. Some database modes can make MySQL compliant with “standard” SQL operations (for example, the ANSI mode would change the MySQL behavior in such a way that it would be able to conform to standard SQL operations), and other modes are used in order to throw or overcome certain errors, etc.: setting an “ ALLOW_INVALID_DATES ” mode would not perform the full checking of dates, an ANSI_QUOTES mode would treat ‘”’ characters the same what that it would treat “” characters (backticks),ERROR_FOR_DIVISION_BY_ZERO` would raise an error if someone would try to divide by zero, etc.

MySQL configuration options can also be set at runtime by employing the “--” parameters. For example, if we want to set a certain parameter without restarting MySQL and it is not specified in the my.cnf file, we could run MySQL by specifying options after mysqld : mysqld --ansi would make MySQL run in ANSI mode, mysqld --innodb-write-io-threads=# would specify the amount of I/O threads InnoDB would be able to employ, options like --unique-checks=0|1 would enable or disable uniqueness checks for secondary indexes inside of InnoDB (one of the flagship storage engines), and finally, mysqld --help would display all of the options available to us. There are many other options and all of them are specified in the MySQL documentation, so if you are interested, keep an eye out for any changes there as well.

Summary

In a nutshell, MySQL’s configuration allows us to change how the database management system interacts with our applications and how it works as a whole – and even allows us to change its behavior. MySQL’s configuration file is a really powerful tool whose power should not in any circumstances be underestimated – the way your MySQL instances are configured could be the matter of life and death for your databases both now and in the future.

With that being said, don’t get too dazed and confused on that front – the MySQL documentation and blogs like the one you’re reading right now are a great resource to learn something new and advance your knowledge in the database realm, so to keep up with news in the database space, keep an eye on them both. Database-related blogs usually have information that the documentation lacks covering a wide variety of topics including providing advice on how to adjust your databases to run big data, they usually have some information on partitioning, indexes, and other things too. It's also useful to learn some security shenanigans so if you're into security, have a read. If you're into security, also consider checking whether you or your company might be at risk of identity theft by employing the search engine provided by BreachDirectory, and we'll see you in the next one. Bye for now!