In this tutorial we will learn about how to create multiple mysql instance in CentOS 6.4 and Red Hat 6.4. Multiple instance means in single system we can create multiple MySQL server. These multiple mysql server will run in different ports.In this practical we expect you already have MySQL server installed in your system.

To create multiple MySQL instance,follow the given below steps

Login as a root in a system

Step (1): Create a new MySQL data directory.

mkdir -p /var/lib/mysql2

Step 2: Now give permission and ownership .Here we are taking ownership and permission reference from original /var/lib/mysql data directory.
Note: /var/lib/mysql is bydefault data directory path

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

Step 3: Now create a new my2.cnf file and paste the below given contents. We will run the new mysql instance in port no. 3337.

Copy the my.cnf file and make it blank

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

Now edit with vi editor or your favorite editor.

vi /etc/my2.cnf

Edit in [mysqld] and [mysqld_safe] section as given below in my2.cnf file

[mysqld]
datadir=/var/lib/mysql2
socket=/var/lib/mysql/mysql2.sock
port=3337

[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid

Step 4: Now install the database in the new mysql data directory

mysql_install_db --user=mysql --datadir=/var/lib/mysql2

Below given is the output from my server (as a Reference)

[root@localhost etc]# mysql_install_db --user=mysql --datadir=/var/lib/mysql2
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

[root@localhost etc]# 

Step 5: Now the new instance is ready.Now in below section we will learn how to start,stop the mysql instance and how to connect with it.

Note: new mysql instance is running in port no. 3337 as per this tutorial

To start new mysql instance,use below given command

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

Now check the port no. 3337 is listening or not

netstat -tanp |grep 3337

To connect new mysql instance

Syntax: mysql -u username -p -h 127.0.0.1 -P port-no.

As per our tutorial:

mysql -u root -p -h 127.0.0.1 -p 3337

It will ask password,hit enter because root password is blank
Now set root password (It is upto your choice, for security reason it is recommended).You can see mysql prompt.

mysql > use mysql;
mysql > update user set password=PASSWORD("Give-here-new-root-PASSWORD") where User='root';
mysql > flush privileges;
mysql > exit

Recheck if MySQL root password is applied or not

mysql -u root -p -h 127.0.0.1 -p 3337

To stop new mysql instance use below given command

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

Note: By using same method you can create multiple instance ,only you have to change the values like mysql2 to mysql3 or so on