What is mysqld?

What is the mySQL daemon? Why do developers care about it, and what does it do?

3 months ago   •   6 min read

By Lukas Vileikis
Let's learn about the science behind the mysqld service!
Table of contents

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.

Set Up MySQL 8 Replication with Linux Nodes
This guide will show you how to set up a 2 node replication server by configuring mysqld.
  • 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:

Basic output of the CLI

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

Launching mysqld with the --help option

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

Terminal screenshot
The MySQL Daemon with the --verbose Option

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

Screenshot of mysqld output
Everything the Daemon can do

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:

Screenshot of the mysqld config
The Initial Configuration File for MySQL 5.7.26 on a Windows-based Infrastructure

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.
Big Data + MySQL = Mission InnoPossible?
Sometimes MySQL (or MariaDB) can be great for big data too! In this article, we will explore the things you should consider when answering the question: “Is MySQL a good option for my big data project?”

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.

How To Secure MySQL 8 with SSL/TLS on Ubuntu 20.04
By default, MySQL is configured to only allow connections from the local system. If you want to connect to a MySQL server from a remote system, it is recommended to secure it with SSL/TLS. Enabling SSL/TLS will encrypt the data being sent to and from the database.

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.

JOIN the Arctype Newsletter
Programming stories, tutorials, and database tips every 2 weeks

Follow Arctype's Development
Programming stories, tutorials, and database tips every 2 weeks

Spread the word

Keep reading