Running multiple MySQL servers on the same machine

Question

Can I run multiple MySQL servers on the same machine?

Thanks.

Answer 1

Yes, you just need to run them on separate ports and list them in different lib directories for your data.

Here is a good link: http://dev.mysql.com/doc/refman/5.1/en/mutiple-servers.html

(If you want to use this for testing, I suggest checking out MySQL Sandbox, which is now replaced by dbdeployer )

Answer 2

There are various ways to run multiple mysql instances (on different ports) on the same computer. Here I used the same binary and used a separate configuration file (with a separate port, pid, socket and data directory). We need to create new directories for our data folder and log (if used). We also need to assign the appropriate permissions for these folders:

# mkdir /var/lib/mysql2 # chown -R mysql.mysql /var/lib/mysql2/ # mkdir /var/log/mysql2 # chown -R mysql.mysql /var/log/mysql2 

Next, we need a separate configuration file, the same as the default mysql configuration file. Therefore, start by copying an existing one and changing the necessary values.

  # cp /etc/my.cnf /etc/my2.cnf 

(or change the path corresponding to your configuration file is in a different place).

Then we need to edit our new configuration file with a different mysql port (3306 by default), pid and socket than the default, and also point the data and log folders to the ones created earlier.

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

Finally, we need to initialize dbs by default:

 # mysql_install_db –user=mysql –datadir=/var/lib/mysql2/ 

Finally, we can start our new mysql instance using

 # mysqld_safe – -defaults-file=/etc/my2.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 

and if we don’t need it anymore, stop it:

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

Ref Site: https://linuxinpakistan.com/start-multiple-instances-mysql-machine