How To Run Multiple instances of Mysql Server on a Single Linux Server

Mysql version used : 5.5.29
Step-1:Login to your server as root user

Step-2:Login to your mysql server as root and execute the following command

mysql>GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'secret';
mysql>FLUSH PRIVILEGES;

Step-3:come out of mysql prompt and stop mysql server.To stop you can execute
[root@localhost ~]# service mysql stop


Step-4:Now we need to locate the mysql config file “my.cnf” and change it as per our requirement which is located at /etc/my.cnf
N:B:-If you are not finding the my.cnf file then go to your mysql installation folder.In my case it is /usr/share/mysql.You will find four configuration files like “my-small.cnf”,” my-medium.cnf”,” my-large.cnf “,” my-huge.cnf”.You can take any one and put it in /etc and rename it to my.cnf.
You can also execute the command below to get all of the above file.
[root@localhost ~]# find / -name mysql*.cnf


Step-5:open my.cnf and comment out the following lines in [mysql] section

# The MySQL server
[mysqld]
#port           = 3306
#socket         = /var/run/mysql/mysql.sock
# Change following line if you want to store your database elsewhere
#datadir        = /var/lib/mysql

Step-6:Now just below [mysqld] section put the following lines

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = secret
 

Step -7:Then to create our desired two instances add the below lines after [mysql_multi] section.

We have to define different unique values for each server instance or else the data and socket files

for both servers collide and as a result mysql server will fail to start or your data could be corrupted.
[mysqld1]
port       = 3306
datadir    = /var/lib/mysql
pid-file   = /var/lib/mysql/mysqld.pid
socket     = /var/lib/mysql/mysql.sock
user       = mysql
log-error  = /var/log/mysql1.err

[mysqld2]
port       = 3307
datadir    = /var/lib/mysql-databases/mysqld2
pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
user       = mysql
log-error  = /var/log/mysql2.err
 

Step-8:Save the configuration file and now create the files and folders as we have mentioned in the above

configuration.To do that execute the following commands.
[root@localhost ~]#  mkdir -P /var/lib/mysql-databases/myqld2
 

Step-9:Copy the mysql database files from the original instance to the second instances database directory

and change the ownership of the data directory to the mysql user so the instance can read them.
[root@localhost ~]# cp -r /var/lib/mysql/mysql/ /var/lib/mysql-databases/mysqld2/mysql
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql-databases

Step-10:Now the two instances are ready to run.We can start them by the folowing command

[root@localhost ~]#mysqld_multi start

To view the status of the instances you can run
[root@localhost ~]# mysqld_multi report
 

Output:
 

Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

To stop both instances just execute the below command.

rhys@linux-n0sm:~> mysqld_multi stop

We are also able to control individual instances by referring to the assigned number.

rhys@linux-n0sm:~> mysqld_multi stop 1

To verify this
rhys@linux-n0sm:~> mysqld_multi report

Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is running

rhys@linux-n0sm:~> mysqld_multi start 1
rhys@linux-n0sm:~> mysqld_multi report

Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

Errors And Solutions:
If you find any error like below in /var/log/mysql1.err
 

121225 11:20:36 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure

121225 11:20:36 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure

121225 11:20:36 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure

121225 11:20:36 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure

121225 11:20:36 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure

Solution:
#mysqld_upgrade -u root -p -S /var/lib/mysql/mysql.sock
#mysqld_upgrade -u root -p -S /var/lib/mysql/mysql-databases/mysq/mysql.sock