The MySQL Server – mysqld

mysqld is widely known as one of the main executable files related to MySQL and its functionality. The mysqld is the MySQL daemon – the main functions related to the database management system are accomplished using it. In this blog post, we will tell you all about it.

The MySQL D(a)emon

The mysqld, as already previously noted, translates to MySQL daemon  (not the demon). The daemon allows database administrators and other kinds of developers to complete all kinds of operations relevant to MySQL including the ability to start, stop, and pause the beast.

However, starting, stopping, and pausing MySQL-related operations is not everything that the MySQL daemon is used for: this program (mysqld is an executable file) has many options that can be used. To figure those out, start it by using the –help option (--verbose might also help with formatting): mysqld --help [--verbose] will do. Use such a command and you will instantly see that MySQL comes out with a lot of useful information:

The Information Provided by MySQL

As you can see, MySQL will tell you what files does it read and in what order, and what options can be specified when mysqld is invoked. Scroll down a little and you will be able to observe the available variables. Some of them can be seen below:

MySQL Daemon - Available Variables

The understanding of the basic options and variables that mysqld provides is an absolutely essential task to every developer and database administrator working with MySQL or any of its flavors – since the usage of mysqld is inevitable, our knowledge of at least some of the commands that are given to us by MySQL can be very helpful.

Basic Options

Some of the basic options that mysqld provides include:

1. The ability to specify a default file from which MySQL would read information by specifying it inside of the --defaults-file parameter or specify a file that would be read after all of the default files would be by using the --defaults-extra-file parameter.
2. Options related to certain storage engines available inside of MySQL (InnoDB being the main one): developers can change the default directory that InnoDB stores files in by specifying the --innodb-data-home-dir parameter, files can be stored in another location specified inside of the --innodb-data-file-path location, etc.
3. The ability to set when certain operations (think opening ports, connections, etc.) would time out.
4. The ability to log all changes relevant to a specific storage engine into a file (the option is called log-isam=filename where filename is the name of the file, and is only relevant to the MyISAM storage engine inside of MySQL.)
5. The ability to display a default list of options and exit.
6. The daemon also comes with operating system-specific options that are displayed at the top. For Windows, the options look like this:

MySQL Daemon - Windows-based Options

Of course, there are a lot of other options that can be specified and used, but you get the idea by now. The majority of developers and engineers working with MySQL or any of its flavors aren’t too fussed about the option list provided by the daemon because they wouldn’t be able to remember them all anyway; rather, people just pick the option that solves their specific problem and uses it. Here’s the problem though – with so many available options, how do you know which option is the most suitable for your use case?

Choosing Suitable Options

We just said that the majority of developers working with MySQL and its flavors don’t worry too much about mysqld – it’s because they know what options they need and roll with them! Here’s what they will keep in mind when working with the daemon and scrolling through the available options:

The use case and the factors the storage engine is used together with will determine what options will mysqld be invoked with. If our storage engine is used for testing purposes and we want to “lock down” our entire workstation (bear in mind it should be running InnoDB in this case) for one or another reason, we might enable a read-only mode by specifying the “ --innodb-read-only ” parameter and setting it to 1, if we want to change the location of the slow query log available in MySQL, we could mess with the slow-query-log-file parameter and set a different file path, and should we want to dive deeper, we can even enable deadlock detection (use the innodb-deadlock-detect parameter), change the format rows are stored in by specifying a parameter after the innodb-default-row-format value, and so on.) The MySQL daemon will even let us change how tables are stored (e.g. if they are stored in a file-per-table format or not), and let us perform a wide variety of other things, but as always, keep in mind that the tasks that are performed would generally depend on our specific use case. In this blog, we have provided you with a list of options that will be relevant to some of the most widely-used use cases across the MySQL world, but we won’t bore you with the entire list – you already see that certain use cases have specific options of interest, and you can probably sense that the daemon is able to set all options also available to be set inside of my.cnf: and you’re not wrong! The reason why people set options by using the daemon and not using my.cnf, though, have to do with practicality: as soon as the daemon (MySQL) is restarted, the options will be nullified (in other words, MySQL will restart and start looking at the options available in my.cnf, rather than the options which were previously set using the daemon): such a feature may be incredibly useful if you have a specific use case that solves a specific problem on-the-go!

MySQL and Data Breaches

If you are a developer working with the daemon for quite some time, you will know that the performance, availability, and capacity features provided by mysqld are not the only features that can  be optimized. MySQL is also a frequent target of data breaches – and MySQL developers know that very well. Thankfully, MySQL can be secured by following a couple of basic security practices:

1. All developers having MySQL as their database of choice should follow basic input sanitization procedures.
2. Developers should familiarize themselves with the “defense in depth” principle: the more security layers protect their web appliactions, the harder it gets for a hacker to penetrate them.
3. Those developers that want to take the security measures of their web applications up a notch should consider using information security services such as web application firewalls that protect web applications from attacks like SQL injection, cross-site scripting and the like or use data breach API services that protect the employees of companies from identity theft and similar attacks – web application firewalls protect web applications from aforementioned attacks, while data breach API services help protect people from identity theft and credential stuffing attacks. One does work without the other – however, protecting your web applications does you little favor if you don’t protect your online wellbeing at the same time.
4. Developers familiar with security measures should also familiarize themselves with the OWASP Top 10 list – the OWASP Top 10 list outlines all of the most popular flaws targeting web applications, and you can bet the attackers are well versed in all of them. Familiarize yourself with those principles, then protect your web applications accordingly.

Summary

The mysqld stands for the MySQL daemon and it’s one of the most popular tools in the toolset of a modern developer or a DBA – most developers and database administrators know that in order to improve the performance, availability, or capacity of their database instances they should look into what the daemon can offer by looking into my.cnf on Linux or my.ini on Windows – however, performance, availability, and capacity advancements are not the only things this file can be used for – combine everything mentioned in this article with using a properly built web application firewall and using information security services provided by BreachDirectory to protect yourself and your team from identity theft attacks both now and in the future, and you will be golden.

If you’ve read this article to the end, we have something to offer you – ping us over email, and both you and the entire company you represent will receive the unlimited version of the BreachDirectory API to use for 3 months – at absolutely no cost. Sounds good? After you’ve done protecting your applications, shoot us an email and within 24 hours, you can start protecting protect the identities of your team members. It doesn’t get better than that!

Be safe, and we will see you in the next blog.