All for Joomla All for Webmasters

How to Start multiple instances of MySQL on the same machine


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


Like it? Share with your friends!

2 SHARES
0
2 SHARES, 0 points

How to Start multiple instances of MySQL on the same machine

log in

Become a part of our community!

reset password

Back to
log in
Choose A Format
Personality quiz
Trivia quiz
Poll
Story
List
Meme
Video
Audio
Image