What is mysqld?

Introduction

The MySQL Server has many different names and has been around for decades. Some people simply call it “MySQL,” some refer to it as “the MySQL daemon,” and others still elect to simply combine the two and call it “mysqld” (short for “MySQL daemon”). Regardless, the fact remains that it is both extremely popular and used in almost every industry we can think of. People working with the server generally use it to support software solutions on the web, but its use cases vary from industry to industry thanks to its high availability, performance including data storage and security.

• High availability. When supporting educational, hospitality, or financial services, the high availability features of MySQL are paramount. Since the daemon has a plethora of load balancers that can be implemented (ProxySQL, MaxScale, HAProxy, and the like), it plays a crucial role in ensuring the uptime of the infrastructure in high schools, universities, and other educational institutions.
• Performance. When behind a highly technical organization, the daemon is used in all kinds of scenarios and can push the performance of an application backing a business to the max. That’s the reason MySQL has documentation behind it in the first place–highly technical people can always direct their staff there to learn how, why, and when MySQL behaves the way it does.
• Simplicity. Finally, there are use cases where the daemon is there just to have some data inside of it. In such a case, people running a service that’s backed by MySQL may not know a lot of things about it, may not care enough to learn, or both–it’s good as long as it works.

The science behind the daemon

The core reason the MySQL daemon exists is to let us “get under the hood” of MySQL and run commands as if we’re inside of the database via the CLI:

To see everything it can do, we launch it with the --help option:

And to see all mysqld options in a nice format, invoke mysqld --verbose.

The above command will let us see everything the daemon can do; from usage options to variables–everything’s here.

How does the configuration work?

The daemon allows us to see everything our MySQL instance is built on, but to properly understand everything that we have to handle, we must look into the configuration itself. The MySQL configuration file is located in the /var/lib/mysql/ directory on Linux and inside of the MySQL version folder on Windows, and it’s called either my.cnf or my.ini, respectively. The contents of the file are pretty much the same on both operating systems, though there are slightly more comments on the Windows version of the file. An example can be seen below:

The file contains every setting that is displayed by the daemon, ranging from settings relevant to the MySQL client itself to SQL modes. All of these options can be also set at runtime by using parameters beginning by “--”: we can make MySQL run in an ANSI mode by specifying mysqld --ansi, we can specify the amount of I/O threads available for use by specifying their number (#) in mysqld --innodb-write-io-threads=#, etc.

The configuration works this way because it contains the parameters (sometimes also called variables) which then interact with the daemon, and consequently alter how the daemon (MySQL) functions as a whole.

Configuring mysqld for storage and security

A lot of parameters impact how MySQL functions. Some of the more interesting ones alter its functionality as an ecosystem, some allow MySQL to remain stable, and some lesser-known ones have less impact. Of the parameters affecting storage and security, secure_file_priv impacts both and deserves a mention.

secure_file_priv and other mysqld parameters

One of the most popular parameters is the secure_file_priv variable. This parameter controls where MySQL is allowed to ingest data when the LOAD DATA INFILE statement is being used. The default value for MySQL on Windows is the tmp directory: secure_file_priv=”c:/wamp64/tmp”

This variable is important because it puts a restraint on which directories can contain data that is eligible for inclusion in your MySQL database instance. If we load data into MySQL from the wrong directory, we will see a message outlining that we should choose a different folder:ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Configure mysqld for big data

Of course, secure_file_priv is not alone. We have a lot of other variables to choose from, including, but not limited to:

• The log-isam=filename variable, which is useful if we want to log all changes relevant to MyISAM into one file.
• The innodb-data-home-dir parameter, which makes it possible for us to change the default home directory for files related to the InnoDB storage engine.
• The innodb-ft-total-cache-size parameter, which gives us the ability to adjust the size of the fulltext index cache to use TEXT type data most efficiently in mysql.

In our big data and MySQL post above, we covered how to configure the buffer pool size, buffer pool instances, and level of ACID compliance so that MySQL can handle the maximum amount of data.

Configure the MySQL daemon for high security

The last example we will cover in this post is encryption. There are hundreds of mysqld parameters spanning hundreds of use cases–if you really want to dive deep, consider taking a look into the list of parameters provided by MySQL.

In the SSL post above we covered how to modify parameters like...

• require_secure_transport
• bind_address
• have_openssl
• have_ssl

... to support securely connecting to MySQL. With the server configured, you can use the SSL options in a MySQL client to be sure your connection is encrypted!

Summary

mysqld, the MySQL daemon, MySQL Server, or simply MySQL works behind-the-scenes on millions of websites across thousands of industries around the world: it is used by worldwide social media giants such as Facebook, by the world’s fastest data breach search engines, and by small startups alike. A proper understanding of what the daemon is, what it does, and why it’s used is absolutely crucial for every developer and database administrator.

Over the past couple of years, MySQL has evolved heavily, adding a lot of new parameters to expand its use cases even further, and it’s now the modern king in the data space for a reason: MySQL has given developers an awesome opportunity to get inside of its “brain,” and mysqld is largely responsible for that adaptability.

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world: BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com.