How to enable automatic restart of the MySQL server using systemctl

In the previous blog, we saw How to Install Mysql 8 manually using Binaries Tar Click here to read more. Though this method provides full control over the installation process it doesn’t register mysqld in systemctl. So we need to start mysql service manually with mysqld utility and in case of Server restart, mysql services will not start automatically.

In this blog post, we are going to perform the required steps to register mysqld with systemctl.

Add a systemd service unit configuration file with details about the MySQL service, Create file mysqld.service inside folder /usr/lib/systemd/system.

[root@test-machine02 mysql]# cd /usr/lib/systemd/system
[root@test-machine02 system]# touch mysqld.service
[root@test-machine02 system]# chmod 644 mysqld.service

Edit file mysqld.service file and add below configuration information. Make sure to replace the ExecStart parameter value with your mysqld utility location, In our case /u01/mysql-8/bin is the location.

[root@test-machine02 system]# vi mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

# Have mysqld write its state to the systemd notify socket
Type=notify

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/u01/mysql-8/bin/mysqld --defaults-file=/etc/my.cnf $MYSQLD_OPTS

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

# Set environment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

Enable the mysqld service to start automatically at reboot, execute the below command.

[root@test-machine02 system]# systemctl enable mysqld.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@test-machine02 system]#

Now we are ready to start mysql service using systemctl command, Before attempting make sure to stop mysql service if it’s ran manually already. Otherwise, you can get the below error while starting using systemctl command.

2021-01-07T12:24:37.119945Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-07T12:24:38.122554Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-07T12:24:39.125029Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-07T12:24:40.128718Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-07T12:24:41.130795Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-07T12:24:42.132149Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-07T12:24:43.134906Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11

Start mysql service with systemctl command. And enable mysql service to restart after server reboot.

[root@test-machine02 system]# systemctl start mysqld
[root@test-machine02 system]#
[root@test-machine02 system]# ps -ef|grep mysqld
mysql      3665      1 11 15:25 ?        00:00:01 /u01/mysql-8/bin/mysqld --defaults-file=/etc/my.cnf
root       3711   2922  0 15:25 pts/2    00:00:00 grep --color=auto mysqld
[root@test-machine02 system]#
[root@test-machine02 lib]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@test-machine02 lib]#

[root@test-machine02 system]#
[root@test-machine02 system]# systemctl status mysqld
â mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2021-01-07 15:25:14 +03; 47s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 3665 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           ââ3665 /u01/mysql-8/bin/mysqld --defaults-file=/etc/my.cnf

Jan 07 15:25:13 test-machine02 systemd[1]: Starting MySQL Server...
Jan 07 15:25:14 test-machine02 systemd[1]: Started MySQL Server.
[root@test-machine02 system]#

Add bin location /u01/mysql-8/bin in root user .bash_profile so you can call mysql utiltiy directly without using export PATH=$PATH:/u01/mysql-8/bin

[root@test-machine02 ~]# cd
[root@test-machine02 ~]# cat  .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
PATH=$PATH:/u01/mysql-8/bin
export PATH

[root@test-machine02 ~]# . .bash_profile
[root@test-machine02 ~]#
[root@test-machine02 ~]# mysql -hlocalhost -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          10
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.22 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /u01/mysql-8/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 13 min 30 sec

Threads: 4  Questions: 5  Slow queries: 0  Opens: 117  Flush tables: 3  Open tables: 38  Queries per second avg: 0.006
--------------

mysql>

If you receive any error in systemctl start mysqld, Execute command journalctl -xe to see what is the cause of the error. In my case, I had provided the wrong location of mysqld utility.

[root@test-machine02 system]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@test-machine02 system]#
[root@test-machine02 system]#
[root@test-machine02 system]# systemctl status mysqld.service
â mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Thu 2021-01-07 15:11:26 +03; 28s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3556 ExecStart=/u01/mysql-8/data/bin/mysqld --defaults-file=/etc/my.cnf $MYSQLD_OPTS (code=exited, status=203/EXEC)
 Main PID: 3556 (code=exited, status=203/EXEC)

[root@test-machine02 data]# journalctl -xe
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysqld.service has finished shutting down.
Jan 07 15:11:26 test-machine02 systemd[1]: Starting MySQL Server...
-- Subject: Unit mysqld.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysqld.service has begun starting up.
Jan 07 15:11:26 test-machine02 systemd[3556]: Failed at step EXEC spawning /u01/mysql-8/data/bin/mysqld: No such file or directory
-- Subject: Process /u01/mysql-8/data/bin/mysqld could not be executed

Ref : https://dev.mysql.com/doc/mysql-secure-deployment-guide/8.0/en/secure-deployment-post-install.html

Click here for Database MySql 8 Installation using Yum Repository Method.
Click here for Step by Step Database MySql 8 Installation using Binaries.

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DBsGuru