Install MySQL community edition | multiple instances same machine | configure systemctld

Install MySQL 5.7.22 Community edition:

#yum install mysql-community-server
#yum install perl-DBD-MySQL-4.023-6.el7.x86_64.rpm
#yum install percona-release-0.1-4.noarch.rpm

Configure multiple instances instance1 and instance2 on the same machine:

01.    Backup existing my.cnf file to my.cnf.bkup
02.    Edit my.cnf with following

[root@server_name]# cat /etc/my.cnf
[mysqld@instance1]
datadir = /mysql/instance1/data
socket = /mysql/instance1/data/instance1.sock
port = 3777
server-id = 3777
log-error = /mysql/instance1/data/instance1.err
 
[mysqld@instance2]
datadir = /mysql/instance2/data
socket = /mysql/instance2/data/instance2.sock
port = 3999
server-id = 3999
log-error = /mysql/instance2/data/instance2.err

Start mysql instance using system:#systemctl start mysqld@instance1
#systemctl start mysqld@instance2

It will create database files at /mysql/instance1/data/ and /mysql/instance2/data/

Check status:
[root@mysqlhost system]# systemctl status mysqld@instance1
● mysqld@instance1.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld@.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2018-06-05 14:40:33 PDT; 1h 51min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 90452 (mysqld)
   CGroup: /system.slice/system-mysqld.slice/mysqld@instance1.service
           └─90452 /usr/sbin/mysqld --defaults-group-suffix=@instance1 --daemonize --pid-file=/var/run/mysqld/mysqld-instance1.pid
Jun 05 14:40:30 mysqlhost systemd[1]: Starting MySQL Server...
Jun 05 14:40:33 mysqlhost systemd[1]: Started MySQL Server.

[root@mysqlhost system]# systemctl status mysqld@instance2
● mysqld@instance2.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld@.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2018-06-05 14:40:57 PDT; 1h 53min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 90563 (mysqld)
   CGroup: /system.slice/system-mysqld.slice/mysqld@instance2.service
           └─90563 /usr/sbin/mysqld --defaults-group-suffix=@instance2 --daemonize --pid-file=/var/run/mysqld/mysqld-instance2.pid
Jun 05 14:40:54 mysqlhost systemd[1]: Starting MySQL Server...
Jun 05 14:40:57 mysqlhost systemd[1]: Started MySQL Server.

Enable systemd so that it start when server re-boot:

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

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

Check mysqld:
[root@mysqlhost system]# ps -ef | grep mysqld
mysql     90452      1  0 14:40 ?        00:00:02 /usr/sbin/mysqld --defaults-group-suffix=@instance1 --daemonize --pid-file=/var/run/mysqld/mysqld-instance1.pid
mysql     90563      1  0 14:40 ?        00:00:02 /usr/sbin/mysqld --defaults-group-suffix=@instance2 --daemonize --pid-file=/var/run/mysqld/mysqld-instance2.pid
root      91927  68721  0 16:38 pts/0    00:00:00 grep --color=auto mysqld

Use temporary password from the /var/log/mysqld.log file to login first time.
#grep 'temporary password' /var/log/mysqld.log

Connect to mysqld:
[mysql@mysqlhost data]$ mysql -uroot -p --socket=/mysql/instance1/data/instance1.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.22
Copyright (c) 2000, 2018, 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.
Set password for mysql root user:

[mysql@mysqlhost data]$ mysql -uroot -p --socket=/mysql/instance2/data/instance2.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22
Copyright (c) 2000, 2018, 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> alter user 'root'@'localhost' identified by '<code>';
Update root user setting to connect remotely:
mysql> UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='root';
mysql> UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='root';
mysql> FLUSH PRIVILEGES;

[root@mysqlhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2018-06-05 17:19:02 PDT; 54min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3082 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1508 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3085 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3085 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Create Backup user:
CREATE USER 'bkusr'@'localhost' IDENTIFIED BY '<code>';
GRANT CREATE, INSERT, UPDATE, SELECT, DROP, RELOAD, ALTER, SUPER, PROCESS, LOCK TABLES,REFERENCES,CREATE ROUTINE,TRIGGER ON *.* TO 'bkusr'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'bkusr'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'bkusr'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'bkusr'@'localhost';
Ref.: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html