MySQL Basics 04: systemd Service Management, Config Files & Data Directories

1 Verify Instance and Directories

The article continues from Part 03 where MySQL 8.0.46 was installed manually. The environment is Kylin Advanced Server V11 (Swan25), x86_64, IP 192.168.19.201, using directory /opt/mysql-course-8046 and port 3307. The author first confirms system, MySQL binary, systemd, and port status with commands:

cat /etc/os-release
uname -m
/opt/mysql-course-8046/mysql/bin/mysqld --version
systemctl --version | head -1
ss -lnt 'sport = :3307'

Initially port 3307 is not listening. A table maps each component to its path and role:

Program: /opt/mysql-course-8046/mysql (symlink to 8.0.46)

Config: /opt/mysql-course-8046/conf/my.cnf (explicit --defaults-file)

Data: /opt/mysql-course-8046/data (server-managed internal files)

Error log: /opt/mysql-course-8046/logs/mysql-error.log

Runtime: /opt/mysql-course-8046/run (socket, PID)

Scripts: /opt/mysql-course-8046/sql (user SQL files)

Config file permissions are 640 (root:mysqlcourse) because MySQL ignores world-writable option files per official option-file rules (https://dev.mysql.com/doc/refman/8.0/en/option-files.html).

2 Manage Service with systemd

2.1 Create Custom Unit

A dedicated unit file mysql-course.service is created at /etc/systemd/system/mysql-course.service (not a distribution default). The unit uses Type=exec, runs as user/group mysqlcourse, and starts mysqld with --defaults-file=/opt/mysql-course-8046/conf/my.cnf (no --daemonize). KillSignal=SIGTERM ensures graceful shutdown per MySQL shutdown process (https://dev.mysql.com/doc/refman/8.0/en/server-shutdown.html). TimeoutStopSec=120, Restart=on-failure, RestartSec=3. The unit is validated with systemd-analyze verify and systemctl daemon-reload.

2.2 Start and Verify

Commands executed:

systemctl start mysql-course
systemctl --no-pager --full status mysql-course
/opt/mysql-course-8046/mysql/bin/mysqladmin --no-defaults --protocol=SOCKET --socket=/opt/mysql-course-8046/run/mysql.sock -u root -p ping
ss -lnt 'sport = :3307'

Service becomes active, ping returns "mysqld is alive", port 3307 listens. SQL queries confirm version 8.0.46, port 3307, max_connections=151, and paths match expectations.

2.3 Stop, Start, Enable/Disable

Stop/start cycle shows inactive/dead after stop, new MainPID after start. systemctl enable/disable toggles boot-time start without affecting current runtime (active remains active). is-enabled may return non-zero when disabled; this reflects unit setting not runtime state per systemctl docs (https://www.freedesktop.org/software/systemd/man/latest/systemctl.html).

3 Modify Configuration and Verify Effect

3.1 Configuration Groups

Groups: [mysqld] for server, [mysql] for mysql client, [client] for common client options. Wrong group = no effect. Unit changes need systemctl daemon-reload; my.cnf changes need MySQL restart (or dynamic SET if supported). This article uses restart to demonstrate.

3.2 Backup, Edit, Check

Set max_connections=120 in [mysqld]:

cp -p /opt/mysql-course-8046/conf/my.cnf /opt/mysql-course-8046/conf/my.cnf.before-04
sed -i '/^\[mysqld\]/a max_connections=120' /opt/mysql-course-8046/conf/my.cnf
sed -n '1,6p' /opt/mysql-course-8046/conf/my.cnf
/opt/mysql-course-8046/mysql/bin/mysqld --defaults-file=/opt/mysql-course-8046/conf/my.cnf --validate-config

Config check exits 0. However, SHOW GLOBAL VARIABLES LIKE 'max_connections' still shows 151 because the running instance hasn't reloaded the file.

3.3 Restart and Confirm

systemctl restart mysql-course

yields new MainPID. Re-query shows max_connections=120. Then restore backup, validate, restart, and confirm reversion to 151.

3.4 Allow Remote Connections

Change bind-address=127.0.0.1 to bind-address=0.0.0.0 in my.cnf, validate, restart. Verify with ss -lnt showing 0.0.0.0:3307. This prepares for Windows client connection in next part. Reference: bind_address docs (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_bind_address).

4 Examine Data and Logs

4.1 Data Directory

ls -lh /opt/mysql-course-8046/data

shows server-managed files (ibdata1, redo logs, .ibd files). These are not editable scripts; renaming directory does not rename databases. See MySQL data directory (https://dev.mysql.com/doc/refman/8.0/en/data-directory.html).

4.2 Two Log Sources

MySQL error log ( tail -n 8 /opt/mysql-course-8046/logs/mysql-error.log) shows startup/shutdown/ready messages. systemd journal ( journalctl -u mysql-course -n 6 --no-pager) shows unit management events. Both are needed; journal alone doesn't capture full database logs. Initialization passwords may remain in old logs — sanitize before sharing.

5 Exercises and Review

Key questions: Which service manages the instance? ( mysql-course.service) What is the MainPID? (changes on restart) Data directory? ( /opt/mysql-course-8046/data/) Current listen address? ( 0.0.0.0:3307) Why can disabled and active coexist? (disabled = no auto-start; active = currently running). Max_connections experiment reverted; bind-address kept at 0.0.0.0 for next part. Service remains running, not enabled at boot.

6 Official References

MySQL with systemd (https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html)

Option files (https://dev.mysql.com/doc/refman/8.0/en/option-files.html)

systemd.service (https://www.freedesktop.org/software/systemd/man/latest/systemd.service.html)

Server shutdown (https://dev.mysql.com/doc/refman/8.0/en/server-shutdown.html)

systemctl (https://www.freedesktop.org/software/systemd/man/latest/systemctl.html)

Configuration validation (https://dev.mysql.com/doc/refman/8.0/en/server-configuration-validation.html)

bind_address (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_bind_address)

Data directory (https://dev.mysql.com/doc/refman/8.0/en/data-directory.html)

7 Appendix: Error Scenarios

7.1 Misspelled Option

Test with a copy my.invalid.cnf adding max_connectionz=120. --validate-config fails with "unknown variable 'max_connectionz'" and exit code 1. Correct spelling is max_connections. The article emphasizes not to repeatedly restart or reinitialize data; instead fix config and re-validate. The main instance remains unaffected because the test uses a separate config file.