Run multiple mysql servers on single machine

mysql

Here is how to run multiple mysql instances on the same machine. This will be helpful when you need to run different mysql version on same computer or have separate mysql servers for each system.

Here I assume you have already installed one instance of mysql. Following steps are for installing another instance of mysql on a linux system.

Download the required mysql version from dev.mysql.com

Create a separate directory for new mysql instance

We will install new mysql instance on a separate directory. Therefore we can make sure current mysql installation is not affected. This directory will hold mysql databases and other files related to this installation.

sudo mkdir /var/lib/mysql2
cd /var/lib/
chown user.user mysql2

replace user with your current user name.

my.conf Configurations

We have to use some custom configurations for new mysql instance. First copy the existing my.conf file to newly created directory (how to find existing my.cnf file?). Update following settings in the newly copied my.cnf file,

  • port
  • pid
  • socket
  • datadir

Here is a sample configuration,

port=3307
datadir=/var/lib/mysql2/
socket=/var/lib/mysql2/mysql.sock
pid=/var/lib/mysql2/mysql.pid 

Make sure to use a separate port number which will not conflict with current system.

Initializing mysql

Once you have done the configurations you have to run the install script to setup required initial data for mysql.

Go to the downloaded mysql directory and execute following command,

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

Make sure you don’t run above code with root permissions. That will avoid any permission issue in the future.

If you run the script successfully you will see mysql data files created in /var/lib/mysql2 directory.

Starting mysql server

Start mysql server using following command

bin/mysqld_safe --user=mysql &

Connecting to mysql server

You can connect to mysql server using following command.

mysql -hlocalhost -P3307 -uroot

Now you can use new mysql server as usual.

Image courtesy : Jim Winstead

Enjoyed this article? Share with friends

Subscribe

Subscribe to our email feed and never miss a post

Join Us