MySQL Double Instance Creation and Database Transfer in CentOS

Introduction

Sometimes, several databases in the same instance become problematic. The scenario could be like this:

1. Two projects are using the same database. For some reason, one of the projects needs to restart its database. In this case, both of the databases will be affected.
2. If one of the projects is multi threaded and accidentally it is creating so many MySQL connections that overflow the connection limit. In this scenario, other projects using the same instance will be affected.

Separating the database instance can help to avoid this kind of weird situation.

Procedure

Creating Second MySQL Instance

Step 1

Create a new MySQL directory.

mkdir -p /var/lib/mysqlSecondInstance

Step 2

Set permission using the following commands:

chmod --reference /var/lib/mysql /var/lib/mysqlSecondInstance
chown --reference /var/lib/mysql /var/lib/mysqlSecondInstance

Here , “--reference” is indicating the original ownership and permission of existing mysql instance.

Step 3

Copy the existing configuration file (my.cnf) and rename it in the /etc directory.

/etc/my.cnf file contains the configuration property of mysql. So, to create a second instance of mysql, we have to create a second configuration file in the same directory.

cp -p /etc/my.cnf /etc/mySecondInstance.cnf

Step 4

Edit this newly copied configuration file with any editor, I am using vim here:

vim /etc/mySecondInstance.cnf

Edit the conf file like the following:

[mysqld]
datadir=/var/lib/mysqlSecondInstance
socket=/var/lib/mysql/mysqlSecondInstance.sock
port=3307
[mysqld_safe]
log-error=/var/log/mysqlSecondInstance.log
pid-file=/var/run/mysqld/mysqlSecondInstance.pid

Step 5

Now, we have to install MySQL into a new data directory for port 3307.

Mysql_install_db --user=mysql --datadir=/var/lib/mysqlSecondInstance

This will create our second MySQL instance. Now, we have to learn how to start/stop the instance.

MySQL Second Instance Start/Stop

To start:

mysqld_safe --defaults-file=/etc/mySecondInstance.cnf &

To verify whether 3307 port is listening or not:

netstat -tanp | grep 3307

To connect with newly created instance:

mysql -u root --port=3307 -h 127.0.0.1 -p

[Here, root = database userName and 127.0.0.1=host address, you can use your own address here]

After using this command, it will ask for password. Keep the password prompt blank and hit enter as because we have not set any password yet.

Here, we will get access of “mysql>” prompt. From here, you can set password for the user(s).

To stop the instance:

mysqladmin -S /var/lib/mysql/mysqlSecondInstance.sock shutdown -p

If it is necessary to start/stop this instance frequently, we can create a small script like this:

vim mySecondInstance

Paste the following script into the file:

Bash

#!/bin/bash
start(){
           mysqld_safe --defaults-file=/etc/mySecondInstance .cnf &
}
stop(){
            mysqladmin -S /var/lib/mysql/mySecondInstance .sock shutdown -p
}
case "$1" in
              start)
                      start
                      ;;
               stop)
                      stop
                      ;;
esac

Make the script executable. I am using:

chmod 755 mySecondInstance

Now, we can start stop the second mysql instance as:

service mySecondInstance start
service mySecondInstance stop

(When it will ask for password, provide the user password of the second instance.)

Transferring Database From First Instance to the Second Instance

Say, In the first MySQL instance, we have the following databases:

mysql> show databases;
+-----------------------+
| Database |
+-----------------------+
| DatabaseOne |
| DatabaseTwo |
+-----------------------+

If we want to copy “DatabaseTwo” into the second instance:

Step 1

Dump the database first:

mysqldump -u root -p DatabaseOne > databaseOne.sql

Step 2

Enter into the second mysql instance and create a database where we want to execute the dump:

mysql -u root --port=3307 -h 127.0.0.1 -p
mysql > create database DatabaseOne
mysql > exit;

Step 3

Execute the dump from outside of the MySQL console.

mysql -u root --port=3307 -h 127.0.0.1 -p DatabaseOne < databaseOne.sql

Conclusion

In this way, we can successfully create second MySQL instance and transfer database into it. We can follow the same procedure to create multiple database instance.