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 latest update:
Registration Link
Telegram Technical Group
Facebook
Facebook Page
Linkedin Group
Linkedin Page
39 total views, 3 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, Linux, Golden Gate, ODA.
Thanks for the visits!
Share Learn Grow!