Configure another instance of MySql and administer using systemctl
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/mysqld@inst2.service to /usr/lib/systemd/system/mysqld@.service.
[root@test-machine01 usr]# systemctl status mysqld@inst2
â mysqld@inst2.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld@.service; 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/mysqld@inst2.service
ââ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.
450 Total Views, 1 Views Today
Hello and welcome to DBsGuru,
I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.
Thanks for the visits!
Share Learn Grow!
