Configure another instance of MySql and administer using systemctl

In this article, we will be performing Configure another instance of MySql and administer using systemctl. To fulfill the purpose of this demonstration, We have already configured mysql instance using yum repository method which is running on mysql default port 3306. Click here to read more.

[root@test-machine01 etc]# ps -ef|grep mysqld
mysql     21356      1  0 16:32 ?        00:00:11 /usr/sbin/mysqld
root      24148   4250  0 17:27 pts/1    00:00:00 grep --color=auto mysqld
[root@test-machine01 etc]#
[root@test-machine01 data]# 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>
mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

To demonstrate, We will create one more mysql instance here and then see How to configuring Multiple MySQL Instances using systemd. Our basedir will be /u01/mysql-2 & datadir will be /u01/mysql-2/data. Create below directories as per your requirement and provide ownership to user mysql using chown command

[root@test-machine01 u01]# mkdir mysql-2
[root@test-machine01 ~]# cd /u01/mysql-2/
[root@test-machine01 mysql-2]# pwd
/u01/mysql-2
[root@test-machine01 mysql-2]# mkdir log
[root@test-machine01 mysql-2]# mkdir data
[root@test-machine01 mysql-2]# cd ../
[root@test-machine01 u01]# chown -R mysql:mysql /u01/mysql-2
[root@test-machine01 u01]# ls -ld mysql-2
drwxr-xr-x. 4 mysql mysql 29 Jan 10 17:27 mysql-2
[root@test-machine01 u01]#

Create new configuration file my_inst2.cnf for new mysql instance in /etc folder. Please note we are using here Port 3307 & Server_ID 3.

[root@test-machine01 u01]# cd /etc
[root@test-machine01 etc]# vi my_inst2.cnf
[mysqld]
user=mysql
port=3307
basedir=/u01/mysql-2
datadir=/u01/mysql-2/data
socket=/u01/mysql-2/mysql.sock
pid_file=/u01/mysql-2/mysqld.pid
server_id = 3
log_bin = /u01/mysql-2/data/binlog
log_error = /u01/mysql-2/data/mysqld.log

Initialize the data directory, This is the important step that will help to initialize your data directory and will create a Root user temporary password in the log file. Once Initialization is done you can go to data directory /u01/mysql-2/data and you can see all necessary mysql config files created.

[root@test-machine01 u01]#  mysqld --defaults-file=/etc/my_inst2.cnf --initialize

[root@test-machine01 u01]# cd mysql-2/data
[root@test-machine01 data]# ls -ltr
total 164296
-rw-r-----. 1 mysql mysql 50331648 Jan 10 16:18 ib_logfile1
-rw-r-----. 1 mysql mysql  8585216 Jan 10 16:19 #ib_16384_1.dblwr
drwxr-x---. 2 mysql mysql     8192 Jan 10 16:19 performance_schema
-rw-r-----. 1 mysql mysql       56 Jan 10 16:19 auto.cnf
-rw-r-----. 1 mysql mysql       32 Jan 10 16:19 binlog.index

If you receive the below error, make sure you added the parameter [mysqld] at the top of my_inst2.cnf config file.

[root@test-machine01 u01]#  mysqld --defaults-file=/etc/my_inst2.cnf --initialize
mysqld: [ERROR] Found option without preceding group in config file /etc/my_inst2.cnf at line 1.
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

Once you have done the initialization as above, you are good to start the MySQL services. Please note that we are starting mysql service in the background. You can see the temporary root password in the logfile.

[root@test-machine01 data]# mysqld --defaults-file=/etc/my_inst2.cnf &
[1] 20511
[root@test-machine01 data]#


[root@test-machine01 data]# ps -ef|grep mysqld
mysql     15007      1  0 14:32 ?        00:00:20 /usr/sbin/mysqld
mysql     20511   4250 82 16:20 pts/1    00:00:03 mysqld --defaults-file=/etc/my_inst2.cnf
root      20547   4250  0 16:20 pts/1    00:00:00 grep --color=auto mysqld
[root@test-machine01 data]#

[root@test-machine01 data]# grep 'temporary password' /u01/mysql-2/data/mysqld.log
2021-01-10T13:19:04.833915Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 1p#/p=2pDnOU
[root@test-machine01 data]#

As mysql service is up and running now. We can connect to our newly created mysql instance using a root temporary password. Please note no command will work until we change the root temporary password.

[root@test-machine01 system]# mysql -hlocalhost -uroot -S/u01/mysql-2/mysql.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22

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> show variables like '%port%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>

Let’s change the root temporary password using mysqladmin and connect with the new root password using -S (socket file) option.

You have to use -S option to connect to new mysql instance with using -S option you will be connected to first instance.

[root@test-machine01 system]# mysqladmin -uroot -p password  -S/u01/mysql-2/mysql.sock
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@test-machine01 system]#
[root@test-machine01 system]#


[root@test-machine01 system]# mysql -hlocalhost -uroot -S/u01/mysql-2/mysql.sock -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>


mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

To administer using systemctl, please follow the below steps. Add all parameters from my_inst2.cnf into my.cnf under name [mysqld@inst2], Now you have two sections [mysqld] default first instance and [mysqld@inst2] new instance.

[root@test-machine01 etc]# vi my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid_file=/var/run/mysqld/mysqld.pid
innodb_directories="/u01/mysql/"
server_id = 1
log_bin = /var/lib/mysql/binlog
log_error = /var/lib/mysql/mysqld.log

[mysqld@inst2]
user=mysql
port=3307
basedir=/u01/mysql-2
datadir=/u01/mysql-2/data
socket=/u01/mysql-2/mysql.sock
pid_file=/u01/mysql-2/mysqld.pid
server_id = 3
log_bin = /u01/mysql-2/data/binlog
log_error = /u01/mysql-2/data/mysqld.log

Now we are ready to start mysql service using the systemctl command, Before attempting make sure to stop mysql service if it’s running manually. Otherwise, you can get the “Unable to lock” error while starting using systemctl command. Please note pid 15007 is for our first mysql instance and pid 20511 is for new mysql instance.

[root@test-machine01 etc]# ps -ef|grep mysqld
mysql     15007      1  0 14:32 ?        00:00:21 /usr/sbin/mysqld
mysql     20511   4250  1 16:20 pts/1    00:00:05 mysqld --defaults-file=/etc/my_inst2.cnf
root      21050   4250  0 16:29 pts/1    00:00:00 grep --color=auto mysqld
[root@test-machine01 etc]#
[root@test-machine01 etc]# kill 20511

2021-01-10T13:29:16.747778Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-10T13:29:17.750835Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-10T13:29:18.753151Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-10T13:29:19.757898Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11
2021-01-10T13:29:20.762305Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11

Please note we are using the keyword mysqld@inst2 which we define in my.cnf file for our second mysql instance. Use command systemctl enable to enable instances to run at boot time.

[root@test-machine01 etc]# systemctl start mysqld@inst2

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

[root@test-machine01 usr]# systemctl status mysqld@inst2
â [email protected] - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/[email protected]; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-01-11 09:41:15 +03; 55min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1143 ExecStartPre=/usr/bin/mysqld_pre_systemd %I (code=exited, status=0/SUCCESS)
 Main PID: 1236 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/system-mysqld.slice/[email protected]
           ââ1236 /usr/sbin/mysqld --defaults-group-suffix=@inst2

Jan 11 09:41:13 test-machine01.saudiacatering.local systemd[1]: Starting MySQL Server...
Jan 11 09:41:15 test-machine01.saudiacatering.local systemd[1]: Started MySQL Server.
[root@test-machine01 usr]#

[root@test-machine01 etc]# ps -ef|grep mysqld
mysql     15007      1  0 14:32 ?        00:00:22 /usr/sbin/mysqld
mysql     21185      1 11 16:30 ?        00:00:00 /usr/sbin/mysqld --defaults-group-suffix=@inst2
root      21234   4250  0 16:31 pts/1    00:00:00 grep --color=auto mysqld
[root@test-machine01 etc]#

[root@test-machine01 data]# mysql -hlocalhost -uroot -S/u01/mysql-2/mysql.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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:          8
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-2/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 1 min 28 sec

Threads: 2  Questions: 5  Slow queries: 0  Opens: 115  Flush tables: 3  Open tables: 36  Queries per second avg: 0.056
--------------
mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+
1 row in set (0.00 sec)

Reference : https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html

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

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.