Setup multiple mysql instance on single machine

Sometimes we may want to run multiple instances of MySQL on single machine, may be on our localhost for experimenting new functionality or on server to distribute load of our app. We may encounter several other occasions where we want to try something new in new releases of MySQL. I have encountered a similar situation where I wanted to try master slave setup on my local machine, so I needed two instances of MySQL to be running. After trying for few days, finally I was able to setup MySQL-5.6.21 on my local machine in addition to the default installation, so I thought to share how to make it up and running.

Before starting on the steps I am assuming you have a default installation of MySQL up and running in your local on port 3306, which means you already have “cmake” installed and user/group “mysql” present in your machine.

Preparation for new install:

• First of all download the zipped file from http://dev.mysql.com/downloads/mysql/.
• Lets put the downloaded file “mysql-5.6.21.tar.gz” to ~/Desktop and navigate to Desktop location from command line.
• Extract the zipped file using tar-xzf mysql-5.6.21.tar.gz command.
• Now, navigate to the mysql source directory, cd ~/Desktop/mysql-5.6.21.

Installation Steps:

Run following from the mysql source code directory:

$ cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql

$ make

$ make install

You may need to run above commands as root user based on what permission is given to current user for /opt/ directory.

We have used -DCMAKE_INSTALL_PREFIX option for configuring mysql source code to indicate the installation path. There are several other options which can be set during configuring the source code.

Once the installation is successful, run following command to setup the system database for mysql.

$ cd /opt/mysql

$ chown -R mysql:mysql .

$ scripts/mysql_install_db –user=mysql –datadir=/opt/mysql/data

$ chown -R root .

$ chown -R mysql data

We need to pass --datadir option to mysql_install_db command to specify where to install the system database tables. That’s it, mysql_install_db command would have created mysql system tables for you and added a sample my.cnf file in the installation directory by default.

NOTE: We should also pass the –defaults-file=<conf file path> to the above command as to prevent it to use the conf file from existing installation (/etc/my.cnf) it searches for an available conf file and uses it. So, if we have some option enabled in existing conf file which is not valid for new installation above command may not work or behave unexpectedly. Check generating system tables for more info.

Let’s add some options to my.cnf file to prevent any possible conflict with the existing installation.

[client]
port            = 3307
socket          = /var/run/mysqld56/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld56/mysqld.pid
socket          = /var/run/mysqld56/mysqld.sock

[mysqld]
user              = mysql
pid-file         = /var/run/mysqld56/mysqld.pid
socket           = /var/run/mysqld56/mysqld.sock
port              = 3307
basedir         = /opt/mysql56
datadir         = /opt/mysql56/data
tmpdir          = /tmp

log-error       = /var/log/mysql56/error.log

Here we are specifying to run mysql server on port 3307 and specifying where to store the data. We can change the values as it suites our need. But, we need to make sure the values do not conflict with the existing installation. Once the values are modified in my.cnf file we can start the server by passing the configuration file to the following command

$ bin/mysqld –defaults-file=/opt/mysql56/my.cnf –user=mysql

Above command should start mysqld service with specified option in my.cnf file.

Additional Info:

Optionally we can run following command to add the new mysql service to init.d

$ cp support-files/mysql.server /etc/init.d/mysql56

Now we can start new mysql server using following command

$ service mysql56 start

NOTE: We will need to modify a line in /etc/init.d/mysql56 around line number 283 to add the default file option to the start command. Change

$bindir/mysqld_safe –datadir=”$datadir” –pid-file=”$mysqld_pid_file_path” $other_args >/dev/null 2>&1 &

to

$bindir/mysqld_safe –defaults-file=”$basedir/my.cnf” –datadir=”$datadir” –pid-file=”$mysqld_pid_file_path” $other_args >/dev/null 2>&1 &

Make sure –defaults-file is the first argument, for some reason mysqld_safe ignores it if it is not the first argument.

That’s it. Hope it helps 🙂

Via — Setup multiple mysql instance on single machine

• install mysql
• mysql
• ubuntu