2nd MySQL Instance

1. Add the following line to the default mysql-server.cnf file:

server-id=1
port=3306
mysqlx=0

Create a new configuration file:

vim /etc/my.cnf.d/mysql-server2.cnf

Or copy the default file and edit the configurations:

cp /etc/my.cnf.d/mysql-server.cnf /etc/my.cnf.d/mysql-server2.cnf

2. Now change the contents of the new file to look like:

[mysqld]
server-id=2
port=3307
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/mysql2.sock
log-error=/var/log/mysql2/mysqld2.log
pid-file=/run/mysqld2/mysqld2.pid
mysqlx=0

3. Create new directory for 2nd instance:

mkdir /var/lib/mysql2
mkdir /var/log/mysql2

4. Change directory ownership:

chown -R mysql:mysql /var/lib/mysql2
chown -R mysql:mysql /var/log/mysql2/

5. Install the new instance:

mysqld –defaults-file=/etc/my.cnf.d/mysql-server2.cnf –initialize-insecure –user=mysql –datadir=/var/lib/mysql2/

6. Set-up the 2nd MySQL Instance using Systemd

cp /lib/systemd/system/mysqld.service /lib/systemd/system/mysqld2.service

It should have the following content:

Make sure the ExecStart has the following assigned to it:

/usr/libexec/mysqld –defaults-file=/etc/my.cnf.d/mysql-server2.cnf

Then run:

systemctl daemon-reload

7.Start 1st MySQL Instance:

systemctl start mysqld

8.Start 2nd MySQL Instance:

systemctl start mysqld2

9.Check on which port the MySQL Instances is listening to:

netstat -ntpl | grep mysqld

10. Connect to 2nd Instance using:

mysql -h 127.0.0.1 -P 3307 -u root -p

Since we used the --initialize-insecure option, leave the password field blank.