Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Run multiple MySQL server on a single machine

Tags:

mysql

Can we run multiple MySQL servers on a single machine?

Thanks.

like image 713
Warrior Avatar asked Apr 04 '09 05:04

Warrior


People also ask

Can we run multiple MySQL server on single machine?

It is possible to use a different MySQL server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches. For example, you might run a server from MySQL 5.7 and one from MySQL 8.0, to see how different versions handle a given workload.

How do I run multiple MySQL servers on the same computer?

Yes, you just need to run them on separate ports and point them at different lib directories for their data. Show activity on this post. Next we need a separate configuration file same as a default mysql configuration file. So start by copying the existing one and changing the needed values.

Can you have multiple MySQL databases?

Within an Azure Database for MySQL server, you can create one or multiple databases. You can opt to create a single database per server to use all the resources or to create multiple databases to share the resources.


2 Answers

Yes, you just need to run them on separate ports and point them at different lib directories for their data.

Here's a good reference: 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)

like image 173
Jeremy Stanley Avatar answered Oct 11 '22 21:10

Jeremy Stanley


There are various methods to run multiple instances of mysql (on different ports) on the same machine. Here I have used the same binary and used a separate configuration file (with separate port, pid, socket and data directory). We need to create new directories for our datadir and log folder (if used). Also we need to assign proper permissions on those 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 same as a default mysql configuration file. So start by copying the existing one and changing the needed values.

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

(or change the path appropriately for your configuration file is in a different place).

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

# 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 the default dbs:

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

Finally we can start our new mysql instance with:

# 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 no longer need it, stop it with:

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

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

like image 44
Mari Criss Avatar answered Oct 11 '22 23:10

Mari Criss