Multiple MySQL instances on the single machine

There are various methods to run multiple instances of MySQL on the single machine (definitely configuring on different ports). We can either compile the MySQL binary with different defaults and paths, use mysqld_multi.
Here I am going to explain the simplest solution, using the same binary but separate configuration file ( obvious separate Port, PID, Socket and Data Directory)

Step 1:  Create separate Data and Log Directories
First create new directories for Data Directory and Log folder with proper privileges:

mkdir /var/lib/mysql2
chown -R mysql:mysql /var/lib/mysql2/

mkdir /var/log/mysql2
chown -R mysql:mysql /var/log/mysql2

Step 2:  Create a new MySQL configuration file
Next create separate MySQL configuration file. The simplest way is by copying the existing (my.cnf) file and changing the needed values.
Here in this tutorial I am using Ubuntu machine that holds the mysql configurations under /etc/mysql/my.cnf (same applicable for Debian). So just copy this folder and modify it from there:

cp -R /etc/mysql/ /etc/mysql2

Next, EDIT new configuration file and at least update the MySQL default Port value (default port value is 3306), PID and Socket values to be different than the default ones, as well as point the Data and Log folders to the new directories created before (Step 1).

cd /etc/mysql2/
sed -i 's/3306/3307/g' my.cnf
sed -i 's/mysqld.sock/mysqld2.sock/g' my.cnf
sed -i 's/mysqld.pid/mysqld2.pid/g' my.cnf
sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf
sed -i 's/var\/log\/mysql/var\/log\/mysql2/g' my.cnf

Note: Using “sed” tool - a special editor for modifying files automatically.

Step 3:  Initializing and starting
Now it is time to initialize the default dbs:

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

Note: Alternatively you can copy the existing /var/lib/mysql if this is needed (Prior to doing this , you have to shut down MySQL).


Now finally we can start our new MySQL instance with:

mysqld_safe --defaults-file=/etc/mysql2/my.cnf &

We can connect to our new instance using:

mysql -S /var/run/mysqld/mysqld2.sock

                             OR

mysql -h 127.0.0.1 -P 3307

To stop the MySQL instance:

mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown