We all
know how to run mysql using either MySQL work bench or command prompt.
Just a
single click and our mysql server is ready to use. But there is lot of moment.
We have to run multiple applications on single windows server.
It is due
to some constrains like costing and management efforts.
Same time
we need to separate our Database servers for some privacy concern.
Here I
will show you can run multiples instances using command line or window
services.
There are
two way to run multiple instances of MySQL
1.
Using windows command line
2.
Using windows service
Here in
this document i will explain how to run multiple instances using command line
Using
windows command line:
Before
proceeding further first learn how MySQL work
Like
every application, it is also works on config file. Here config file is
"my.ini". it’s file which mysql use for configuration. You can find
it somewhere in location "mysql_home". In this default config file, basiclly
some properties are defined like datadir( where mysql will store data),
port(which port mysql will use for connection) , size of cluster etc. You can
pass all these parameters while running it using command line i will explain it
later.
Starting MySQL
from the Windows Command Line :
As I said
MySQL server can be started manually from the command line. This can be done on
any version of Windows.
To start
the mysqld server from the command line,
you should start a console window (or “DOS window”) and enter this
command:
shell> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld"
The path
to mysqld may vary depending on the
install location of MySQL on your system.
You can
stop the MySQL server by executing this command.
shell> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" -u root shutdown
Note:-
By
default mysql user is root and its password is the password of the root
user.
You can
pass all this using -u username , -p password respectively.
As I
said, by default mysql server loads “my.ini” config file from root dir of mysql.
You can
put your configuration in separate file and pass it using command line.
Code
would be something like this.
C:\> C:\mysql\bin\mysqld
--defaults-file=C:\my-opts1.cnf
This is
how you will run multiple instance. You have to install mysql server in two
separate locations.
Let’s
assume my two mysql locations are
C:\> C:\mysql1\
C:\> C:\mysql2\
Now you
have to create two separate config file
mysetting1.ini
[mysqld]
datadir = C:/mydata1
port = 3307
mysetting2.ini
[mysqld]
datadir = C:/mydata2
port = 3308
Here I
have chosen different port, to differentiate servers in application
layer.
Use
the --defaults-file option to start each server
with its own option file
C:\> C:\mysql\bin\mysqld --defaults-file=C:\mydettings1.cnf
C:\> C:\mysql\bin\mysqld-debug --defaults-file=C:\mysetting2.cnf
Each
server starts in the foreground (no new prompt appears until the server exits
later), so you will need to issue those two commands in separate console
windows.
To shut
down the servers, connect to each using the appropriate port number:
C:\> C:\mysql\bin\mysqladmin --port=3307 shutdown
C:\> C:\mysql\bin\mysqladmin --port=3308 shutdown
There is
lots of parameter that you can pass using command line. Those are
--shared-memory-base-name,
--shared-memory.
You can explore it more. You can also create simple .bat file which will run
these command on single click.
Sources:
