Yesterday I faced a problem where our developer team wanted to have two mysql server with different root passwords.The problem was I am having only one redhat linux testserver.After lots of thinking I searched for “multiple instances of mysql server” in Google.I found lots of different article on this and all are little bit confusing.Finally after lots of testing I succeeded in getting up and running two different instances of mysql in singleserver.Beloware the steps you can follow to do the same.
Step-1:Login to your server as root user
Step-2:Login to your mysql server as root and execute the following command
It means we are giving shutdown privileges to the user “multi_admin”
step-3:come out of mysql prompt and stop mysql server.To stop you can execute
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
Step-6:Now just below [mysqld] section put the following lines
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.
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.
Step-9:For mysql instance 1 we are using the defaults for previously running mysql server But we need to Create the data directory for instance2.Create it by
Step-10: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.
Step-11:Now the two instances are ready to run.We can start them by the folowing command
You can see that the mysqld_multi script has started multiple mysql processes with the following commands.
To stop both instances just execute the below command.
We are also able to control individual instances by referring to the assigned number.
Here you need to remember that both the instances running on different port and also having different socket files.You can refer to the topic how to connect to mysql in php in case you are facing any problem to connect ot mysql through some script