mysqld Startup, Data Directory, Configuration Files, and Option Precedence

Learning outcomes

Suppose the ServiceHub lab server restarts after a maintenance window and suddenly listens on a different port, writes its error log somewhere unexpected, or ignores a value you thought was configured. The tempting reaction is to edit the first my.cnf or my.ini file you can find and restart repeatedly. That approach is risky because MySQL can obtain startup values from several sources, and the last effective source is not always the file you edited.

This lesson turns startup into an observable sequence. You will learn where mysqld gets configuration, how to identify the data directory and endpoint it actually uses, how persisted settings fit into precedence, and why the data directory is server-owned state rather than a folder administrators should hand-edit.

Start with the process, not with a configuration filename

mysqld is the MySQL Server executable. On a workstation it might be launched directly, by a Windows service, by systemd, or as the main process inside a container. The service manager can add command-line options, set environment variables, select a working directory, or choose an alternate configuration file. Therefore, “I changed /etc/mysql/my.cnf” is not yet evidence that the running server saw your change.

The first operational habit is to separate four questions:

This evidence-first workflow is safer than opening files and changing values until something “works.” It also scales to production, where undocumented manual edits are a common source of drift.

Option sources and precedence: the mental model

MySQL programs accept options from environment variables, option files, and the command line. For most MySQL programs, later processing wins, so command-line options generally override option-file values. The server has an important extra layer: settings persisted by SET PERSIST or SET PERSIST_ONLY are stored in mysqld-auto.cnf in the data directory and, when persisted globals are loaded, are processed late enough to override many earlier sources.

Option-file order is platform-specific. On Unix-like systems, MySQL commonly checks global locations such as /etc/my.cnf and /etc/mysql/my.cnf, server-specific locations, an optional --defaults-extra-file, user files, and finally persisted server settings. Windows has its own documented search order. Do not memorize one path as universal.

lowest precedence                                      higher precedenceenvironment -> ordinary option files -> command line -> mysqld-auto.cnf persisted settingsImportant qualifications:- exact option-file order is platform/install dependent- --defaults-file changes which ordinary option file is read- --no-defaults suppresses ordinary option files- persisted_globals_load can suppress loading mysqld-auto.cnf- mysqld has a security exception for repeated --user options

Discover what this installation expects before changing anything

Run the server executable with verbose help from an administrative shell. This does not start a normal serving instance. It prints compiled defaults, recognized options, and—near the beginning of the output—the option files and option groups the executable examines. The exact formatting is platform dependent, so search the output rather than depending on a screenshot.

mysqld --verbose --help 2>&1 | less# Search within the output for: Default options are read from# and for variables such as datadir, port, socket, log-error

mysqld --verbose --help 2>&1 | Select-String -Pattern `  "Default options are read from","datadir","port","socket","log-error"

If your package manager or service uses a full executable path, run that exact binary. Seeing a second mysqld earlier in PATH is a classic way to inspect the wrong installation.

When troubleshooting an option file, remember that --defaults-file, --defaults-extra-file, and related options affect option-file handling and must be placed where the MySQL program expects them on the command line. The official manual documents these rules; do not infer them from generic command-line conventions.

Ask the running server for its effective locations and endpoint

Now switch from startup possibilities to runtime facts. Connect as a lab administrator and query values that describe the running instance. Names and paths can vary, so the result itself—not an assumed default—is the evidence.

SELECT VERSION() AS server_version,       @@hostname AS hostname,       @@port AS tcp_port,       @@socket AS socket_path,       @@datadir AS data_directory,       @@pid_file AS pid_file,       @@log_error AS error_log,       @@basedir AS base_directory;SHOW VARIABLES LIKE 'persisted_globals_load';

@@datadir tells you which directory this server regards as its data directory. It does not grant permission to manipulate InnoDB files manually. The server coordinates redo, undo, metadata, binary logs, temporary files, and table storage as a coherent system. Copying or editing individual internal files while the server is active can create an inconsistent backup or prevent startup.

@@socket is meaningful on platforms/transports that use a local socket. A client connecting over TCP may not use it. Likewise, @@port identifies the classic protocol TCP port configured for the server; it does not prove that a firewall, container mapping, proxy, or router exposes that same number externally.

Find the source of an effective variable

MySQL 8.4 exposes useful provenance in Performance Schema. The variables_info table records how variables were most recently set, including source information. This is far more reliable than comparing several files by eye.

SELECT VARIABLE_NAME,       VARIABLE_SOURCE,       VARIABLE_PATH,       SET_TIME,       SET_USER,       SET_HOSTFROM performance_schema.variables_infoWHERE VARIABLE_NAME IN ('port','max_connections','sql_mode','time_zone')ORDER BY VARIABLE_NAME;SELECT VARIABLE_NAME, VARIABLE_VALUEFROM performance_schema.persisted_variablesORDER BY VARIABLE_NAME;

Do not assume every column is populated for every source. A compiled default, command-line setting, option-file setting, and persisted setting have different provenance. The important question is whether the evidence explains the effective runtime value.

If a setting appears in persisted_variables, that tells you the server has a durable persisted value. It does not mean every current session already inherited it: session variables are copied or initialized at connection time according to each variable's semantics. Existing sessions can therefore differ from new sessions after a global change.

Failure lab: “I changed my.cnf, but nothing changed”

This controlled lab reproduces a common administrative failure without changing a dangerous variable. Use a disposable MySQL lab only. We will use a variable such as max_execution_time later for persistence; here the goal is simply to diagnose precedence.

1. Record the current value and provenance of a harmless dynamic variable.
2. Inspect the option files recognized by your exact mysqld binary.
3. If you already have a persisted setting for that variable, note it rather than editing mysqld-auto.cnf.
4. Compare the intended option-file value with @@GLOBAL and performance_schema.variables_info.
5. Remove or reset the higher-precedence source using its supported mechanism, then verify again.

SELECT @@GLOBAL.max_execution_time AS global_value;SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH, SET_TIME, SET_USERFROM performance_schema.variables_infoWHERE VARIABLE_NAME = 'max_execution_time';SELECT *FROM performance_schema.persisted_variablesWHERE VARIABLE_NAME = 'max_execution_time';

Hands-on lab: build an instance identity record

Create a small text or Markdown record named servicehub-instance-baseline.md outside the data directory. This record becomes the evidence you compare after later configuration experiments.

1. Record the output of SELECT VERSION(), @@hostname, @@port, @@socket, @@datadir, and @@log_error.
2. Record which option files mysqld --verbose --help says this binary reads.
3. Query variables_info for port, max_connections, sql_mode, and time_zone.
4. List persisted variables through performance_schema.persisted_variables.
5. Do not copy credentials, private keys, or secrets into the baseline document.

Production judgment and references

A production configuration should be explainable from source control, deployment automation, service definitions, and server-side provenance. Avoid “mystery state” that exists only because somebody once ran SET GLOBAL or edited an undocumented file. Persisted variables are useful, but they are also configuration state and should be governed deliberately.

Monitor configuration drift, unexpected restart behavior, error-log warnings about unknown persisted variables, endpoint changes, and service-manager overrides. Never treat the data directory as the place to experiment with internal files. Chapter 13 will cover supported backup and restore paths.

Authoritative references

• MySQL 8.4 Reference Manual — Using Option Files
• MySQL 8.4 Reference Manual — Specifying Program Options
• MySQL 8.4 Reference Manual — Persisted System Variables
• MySQL 8.4 Reference Manual — Performance Schema System Variable Tables
• MySQL 8.4 Reference Manual — Server Command Options

Next: once you know what server started, follow a single connection from authentication through session state, statement execution, idling, and disconnect.