Run multiple mysql instances in mysql

Question

Hi All,

I want to use mysql server on two different ports on same machine. I made two separate cnf files for the same. when I am trying to connect to mysql server with second port which i have added I am unable to do so. I am working on Windows Vista. I tried to start mysqld from command line after specifying port. This is the sample file I have changed for mysql server to read from two different ports

CLIENT SECTION

----------------------------------------------------------------------

#

The following options will be read by MySQL client applications.

Note that only client applications shipped by MySQL are guaranteed

to read this section. If you want your own MySQL client program to

honor these values, you need to specify it as an option during the

MySQL client library initialization.

# [client]

port=3306

[mysql] mysql-path="C:\Program Files\MySQL\MySQL Server 5.1\bin" default-character-set=latin1

[client]

port=3307

[mysql1] mysql1-path="C:\Program Files\MySQL2\MySQL Server 5.1\bin" default-character-set=latin1

SERVER SECTION

----------------------------------------------------------------------

#

The following options will be read by the MySQL Server. Make sure that

you have installed the server correctly (see above) so it reads this

file.

# [mysqld] mysqld-path="C:\Program Files\MySQL\MySQL Server 5.1\bin\"

The TCP/IP Port the MySQL Server will listen on

port=3306

Path to installation directory. All paths are usually resolved relative to this.

basedir="C:/Program Files/MySQL/MySQL Server 5.1/"

Path to the database root

datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/" # server_id=1

[mysqld1]

The TCP/IP Port the MySQL Server will listen on

port=3307 server_id=2 mysqld1-path="C:\Program Files\MySQL2\MySQL Server 5.1\bin\"

Path to installation directory. All paths are usually resolved relative to this.

basedir="C:/Program Files/MySQL2/MySQL Server 5.1/"

Path to the database root

datadir="C:/ProgramData/MySQL2/MySQL Server 5.1/Data/" #

The default character set that will be used when a new schema or table is

created and no character set is defined

default-character-set=latin1

The default storage engine that will be used when create new tables when

default-storage-engine=INNODB

Set the SQL mode to strict

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Thanks in advance.

Answer 1

You need to also specify a different datadir (i.e. the directory which contains the database files) on the command-line. You cannot open the same databases in different instances, as they would lock each other and/or cause data-corruption.

Answer 2

The installation procedure differs slightly based on your server/operating system.

For linux you might find this useful - http://code.openark.org/blog/mysql/manually-installing-multiple-mysql-instances-on-linux-howto

Google it and you might find for others too.