What is mysqld?

Tags: mysql, tutorial, programming, technology
Published: 2022-02-17 19:16:05

## 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.

{% link https://dev.to/arctype/set-up-mysql-8-master-slave-replication-on-ubuntu-2004-3f21 %}

- **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](https://proxysql.com/documentation/ProxySQL-Configuration/), [MaxScale](https://www.percona.com/blog/2015/06/08/maxscale-a-new-tool-to-solve-your-mysql-scalability-problems/), [HAProxy](https://towardsdatascience.com/high-availability-mysql-cluster-with-load-balancing-using-haproxy-and-heartbeat-40a16e134691), 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:

![](https://lh5.googleusercontent.com/_Qc104G-MFbrqjwihGao0hjBUuFOZ6bx9z4y8UKLWSwnjvtrES-91PSe-Rc1QQu_NQXrii4JNIWTmdD5BlIoyQ3Q7aPyzTejNb2aoYp-9tIepqLbsKHokeLn_ynQPIIqswP0HZMz)

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

![](https://lh4.googleusercontent.com/BMAsIzovJCAdo_uT2OAnE_ogm0mw8XeAsyiBhInT7tFULGAFwElvQOr0Omp0OHuJRjZBgngZdzyotY4XFipjk6T9axwzm7fpdAjU2CN9d7oEmzVo_PUWsNZmGNdj80B9snQElOmF)

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

![Terminal screenshot](https://lh6.googleusercontent.com/WOQ69NBBffLHv_GVlQvq6fd1OJoBLLQhNeB9dJQAODOqZc_YpPf_y6fkpcI6JTFXLf0xuPBNg0wyq6dlIFaEZ7CMhptZiTl67o8Ilu7v9vwj_vro5CxL0-tblztq1UtvDXvm-Lmg)

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

![Screenshot of mysqld output](https://lh5.googleusercontent.com/xfWaJJDpKo8LR9cbIi_igi2Yn4pPUiGTUp5a9yJuvEL8vdxnwAHyYBMzJ8h3upvcVIA_1IVEAs7FZDs2HDZpONc8OCA2hv1e-dfjyojbbsuiscz5I490--7e3kaN26FOOqLGZfUL)

## 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](https://arctype.com/blog/content/images/2021/10/image-60.png)

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](__GHOST_URL__/mysql-load-data-infile/) is being used. The default value for [MySQL on Windows](https://arctype.com/mysql/install/windows-mysql) 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](__GHOST_URL__/mysql-storage-engine-big-data/).
- 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](__GHOST_URL__/mysql-text-type/).

{% link https://dev.to/arctype/big-data-mysql-mission-innopossible-573 %}

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](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html). 

{% link https://dev.to/arctype/how-to-secure-mysql-8-with-ssltls-on-ubuntu-2004-332k %}

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](https://arctype.com/mysql/connect/aws-ssl-mysql) 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](https://breachdirectory.com), 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](https://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](http://lukasvileikis.com/).