Multiple instances howto

View: New views
8 Messages — Rating Filter:   Alert me  

Multiple instances howto

by toutiao :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message


Dear Friends,

I'm learning to create multiple mysqld instances on Linux, where one instance of mysql is already running.

While creating a new my.cnf file, I noticed that I need to assign a socket file to it.

Can you pls. tell me how to create / test this socket file?

Thanks,
Jing Zhang

Re: Multiple instances howto

by toutiao :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message


In addition, can you run multiple instances of mysqld simultaneous?


toutiao wrote:
Dear Friends,

I'm learning to create multiple mysqld instances on Linux, where one instance of mysql is already running.

While creating a new my.cnf file, I noticed that I need to assign a socket file to it.

Can you pls. tell me how to create / test this socket file?

Thanks,
Jing Zhang

R: Multiple instances howto

by Nanni Claudio :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Hi Jing,

Logically speaking,
you can run as many instances of mysql as you want,
but you have to make an installation for each mysql instance.
On each instance you can have as many databases as you want.
From my experience here is the key:

1) One different mysql user (mysql41,mysql50) for each mysql installation.

2) put .my.cnf file in the home directory of each mysql user with the right parameters. This is the way I have found not to conflict between instances.
The socket file is a file name you  have to assign to each instance,
Use a different name for each instance.

:-----.my.cnf------------:
# The MySQL server
[mysqld]
port            = 3515
socket          = /tmp/mysql5015.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
:-------------------------:

3) Of course each mysql instance must bind to a different port(3306,3316,3326)


Let me know.

Aloha!

Claudio Nanni


-----Messaggio originale-----
Da: toutiao [mailto:jing_zhang@...]
Inviato: venerdì 28 marzo 2008 3.03
A: mysql@...
Oggetto: Re: Multiple instances howto



In addition, can you run multiple instances of mysqld simultaneous?



toutiao wrote:

>
>
> Dear Friends,
>
> I'm learning to create multiple mysqld instances on Linux, where one
> instance of mysql is already running.
>
> While creating a new my.cnf file, I noticed that I need to assign a socket
> file to it.
>
> Can you pls. tell me how to create / test this socket file?
>
> Thanks,
> Jing Zhang
>
>

--
View this message in context: http://www.nabble.com/Multiple-instances-howto-tp16338723p16344323.html
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=claudio.nanni@...



"Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto"
*****
"This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...


Re: R: Multiple instances howto

by toutiao :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Nanni,

Thanks for the advices. How to make an installation? I use Fedora Core 8 where MySQL comes with the bundle. Do you think I should download the source code and compile it and configure it using ./configure?

I did changed the cnf file to have 2 different configurations:

[root@FC8-DEV mysqld]# cat /etc/my1.cnf
[mysqld]
port=33061
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@FC8-DEV mysqld]# cat /etc/my2.cnf
[mysqld]
port=33602
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql2.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid



And then I started (or, I believe I started) 2 difference instances with:
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &


I checked the service status:
[root@FC8-DEV mysqld]# service mysqld status
mysqld (pid 3542 3403) is running...


Does it means I have two instances running? If I want to configure them seperately using mysql command, what shall I do to distinguish each instances?

Thanks,
Jing Zhang



Nanni Claudio wrote:
Hi Jing,

Logically speaking,
you can run as many instances of mysql as you want,
but you have to make an installation for each mysql instance.
On each instance you can have as many databases as you want.
From my experience here is the key:

1) One different mysql user (mysql41,mysql50) for each mysql installation.

2) put .my.cnf file in the home directory of each mysql user with the right parameters. This is the way I have found not to conflict between instances.
The socket file is a file name you  have to assign to each instance,
Use a different name for each instance.

:-----.my.cnf------------:
# The MySQL server
[mysqld]
port            = 3515
socket          = /tmp/mysql5015.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
:-------------------------:

3) Of course each mysql instance must bind to a different port(3306,3316,3326)


Let me know.

Aloha!

Claudio Nanni


-----Messaggio originale-----
Da: toutiao [mailto:jing_zhang@emcore.com]
Inviato: venerdì 28 marzo 2008 3.03
A: mysql@lists.mysql.com
Oggetto: Re: Multiple instances howto



In addition, can you run multiple instances of mysqld simultaneous?



toutiao wrote:
>
>
> Dear Friends,
>
> I'm learning to create multiple mysqld instances on Linux, where one
> instance of mysql is already running.
>
> While creating a new my.cnf file, I noticed that I need to assign a socket
> file to it.
>
> Can you pls. tell me how to create / test this socket file?
>
> Thanks,
> Jing Zhang
>
>

--
View this message in context: http://www.nabble.com/Multiple-instances-howto-tp16338723p16344323.html
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=claudio.nanni@galileoavionica.it



"Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto"
*****
"This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@nabble.com

R: R: Multiple instances howto

by Nanni Claudio :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Jing,

First: you can have only ONE mysql instance running for each installation,
but you can have as many installations as you want on each server.
So to answer your statement only the first command started mysql,
while the second command should have given you an error(there is already an instance running).

--:Jing:-------------------------------------------------------------------
And then I started (or, I believe I started) 2 difference instances with:
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &
---------------------------------------------------------------------------

If you issue a 'ps -ef | grep mysql' shell command, you'll see all mysql processes running, for each instance you have two basic processes:

==:ps -ef | grep mysql:====================================================
  mysql5   535   513  6 11:48:56 pts/0    0:01 /.../libexec/mysqld basedir=/data/Esercizio/mysql5015 --......

  mysql5   513   367  0 11:48:56 pts/0    0:00 /bin/sh bin/mysqld_safe
===========================================================================

The one with the lower id(513) is the one you started, the other(535) is the 'son' of 513.


Compiling and installing it is surely a good experience.
What I do NOT like of bundled installations is directory/user sharing.
If you install it yourself compiling it from sources you can
choose your directory, user and parameters for a particular mysql installation.

My advice is to follow this procedure:

1) Create a home directory for the user and installation of mysql
        e.g.: /home/mysql5018

2) Create a new operating system user for the new installation,
        e.g.: mysql5018, mysql323, etc...

3) compile mysql (follow the manual)

4) log on as the new mysql user (e.g.: mysql5018)

5) install mysql (follow the manual) as that user

6) create the .my.cnf file and put it in the home directory of the user (/home/mysql5018/.my.cnf)

IMPORTANT: watch for right permissions on files and directories.

This way you can start mysql as easy as   'bin/mysqld_safe&' with no command line parameters at all, given you are logged as the right mysql user(mysql5018) and the .my.cnf is in his home directory.

This is how I got so far.

Aloha!

Claudio Nanni



-----Messaggio originale-----
Da: toutiao [mailto:jing_zhang@...]
Inviato: sabato 29 marzo 2008 22.05
A: mysql@...
Oggetto: Re: R: Multiple instances howto


Nanni,

Thanks for the advices. How to make an installation? I use Fedora Core 8
where MySQL comes with the bundle. Do you think I should download the source
code and compile it and configure it using ./configure?

I did changed the cnf file to have 2 different configurations:

[root@FC8-DEV mysqld]# cat /etc/my1.cnf
[mysqld]
port=33061
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@FC8-DEV mysqld]# cat /etc/my2.cnf
[mysqld]
port=33602
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql2.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid



And then I started (or, I believe I started) 2 difference instances with:
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &


I checked the service status:
[root@FC8-DEV mysqld]# service mysqld status
mysqld (pid 3542 3403) is running...

Does it means I have two instances running? If I want to configure them
seperately using mysql command, what shall I do to distinguish each
instances?

Thanks,
Jing Zhang




Nanni Claudio wrote:

>
> Hi Jing,
>
> Logically speaking,
> you can run as many instances of mysql as you want,
> but you have to make an installation for each mysql instance.
> On each instance you can have as many databases as you want.
> From my experience here is the key:
>
> 1) One different mysql user (mysql41,mysql50) for each mysql installation.
>
> 2) put .my.cnf file in the home directory of each mysql user with the
> right parameters. This is the way I have found not to conflict between
> instances.
> The socket file is a file name you  have to assign to each instance,
> Use a different name for each instance.
>
> :-----.my.cnf------------:
> # The MySQL server
> [mysqld]
> port            = 3515
> socket          = /tmp/mysql5015.sock
> skip-locking
> key_buffer = 256M
> max_allowed_packet = 1M
> :-------------------------:
>
> 3) Of course each mysql instance must bind to a different
> port(3306,3316,3326)
>
>
> Let me know.
>
> Aloha!
>
> Claudio Nanni
>
>
> -----Messaggio originale-----
> Da: toutiao [mailto:jing_zhang@...]
> Inviato: venerdì 28 marzo 2008 3.03
> A: mysql@...
> Oggetto: Re: Multiple instances howto
>
>
>
> In addition, can you run multiple instances of mysqld simultaneous?
>
>
>
> toutiao wrote:
>>
>>
>> Dear Friends,
>>
>> I'm learning to create multiple mysqld instances on Linux, where one
>> instance of mysql is already running.
>>
>> While creating a new my.cnf file, I noticed that I need to assign a
>> socket
>> file to it.
>>
>> Can you pls. tell me how to create / test this socket file?
>>
>> Thanks,
>> Jing Zhang
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Multiple-instances-howto-tp16338723p16344323.html
> Sent from the MySQL - General mailing list archive at Nabble.com.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:  
> http://lists.mysql.com/mysql?unsub=claudio.nanni@...
>
>
>
> "Questo messaggio ed ogni suo allegato sono confidenziali e possono essere
> riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é
> il destinatario diretto del presente messaggio, é pregato di contattare
> l'originario mittente e di cancellare questo messaggio ed ogni suo
> allegato dal sistema di posta. Se il ricevente non é il destinatario
> diretto del presente messaggio, sono vietati l'uso, la riproduzione e la
> stampa di questo messaggio e di ogni suo allegato, nonché la diffusione
> del loro contenuto a qualsiasi altro soggetto"
> *****
> "This message and any attachment are confidential and may be privileged or
> otherwise protected from disclosure. If you are not the intended
> recipient, please contact the sender and delete this message and any
> attachment from your system. If you are not the intended recipient you
> must not use, copy or print this message or attachment or disclose the
> contents to any other person."
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...
>
>
>

--
View this message in context: http://www.nabble.com/Multiple-instances-howto-tp16338723p16374504.html
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=claudio.nanni@...



"Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto"
*****
"This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...


Re: R: R: Multiple instances howto

by toutiao :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Nanni,

You know, it's really strange. On 2 different terminals, I ran

[root@FC8-DEV etc]mysql -S /var/lib/mysql/mysql.sock -u root -p

AND

[root@FC8-DEV etc]mysql -S /var/lib/mysql/mysql2.sock -u root -p

I was able to log in. Then I run "show variables", I could actually see them listening on different ports (33061 and 33062). If I can start only ONE mysql instance from the same installation, why am I able to log in 2 different instances? Where did I do wrong?

Thanks,
Jing Zhang



Nanni Claudio wrote:
Jing,

First: you can have only ONE mysql instance running for each installation,
but you can have as many installations as you want on each server.
So to answer your statement only the first command started mysql,
while the second command should have given you an error(there is already an instance running).

--:Jing:-------------------------------------------------------------------
And then I started (or, I believe I started) 2 difference instances with:
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &
---------------------------------------------------------------------------

If you issue a 'ps -ef | grep mysql' shell command, you'll see all mysql processes running, for each instance you have two basic processes:

==:ps -ef | grep mysql:====================================================
  mysql5   535   513  6 11:48:56 pts/0    0:01 /.../libexec/mysqld basedir=/data/Esercizio/mysql5015 --......

  mysql5   513   367  0 11:48:56 pts/0    0:00 /bin/sh bin/mysqld_safe
===========================================================================

The one with the lower id(513) is the one you started, the other(535) is the 'son' of 513.


Compiling and installing it is surely a good experience.
What I do NOT like of bundled installations is directory/user sharing.
If you install it yourself compiling it from sources you can
choose your directory, user and parameters for a particular mysql installation.

My advice is to follow this procedure:

1) Create a home directory for the user and installation of mysql
        e.g.: /home/mysql5018

2) Create a new operating system user for the new installation,
        e.g.: mysql5018, mysql323, etc...

3) compile mysql (follow the manual)

4) log on as the new mysql user (e.g.: mysql5018)

5) install mysql (follow the manual) as that user

6) create the .my.cnf file and put it in the home directory of the user (/home/mysql5018/.my.cnf)

IMPORTANT: watch for right permissions on files and directories.

This way you can start mysql as easy as   'bin/mysqld_safe&' with no command line parameters at all, given you are logged as the right mysql user(mysql5018) and the .my.cnf is in his home directory.

This is how I got so far.

Aloha!

Claudio Nanni



-----Messaggio originale-----
Da: toutiao [mailto:jing_zhang@emcore.com]
Inviato: sabato 29 marzo 2008 22.05
A: mysql@lists.mysql.com
Oggetto: Re: R: Multiple instances howto


Nanni,

Thanks for the advices. How to make an installation? I use Fedora Core 8
where MySQL comes with the bundle. Do you think I should download the source
code and compile it and configure it using ./configure?

I did changed the cnf file to have 2 different configurations:

[root@FC8-DEV mysqld]# cat /etc/my1.cnf
[mysqld]
port=33061
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@FC8-DEV mysqld]# cat /etc/my2.cnf
[mysqld]
port=33602
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql2.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid



And then I started (or, I believe I started) 2 difference instances with:
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &


I checked the service status:
[root@FC8-DEV mysqld]# service mysqld status
mysqld (pid 3542 3403) is running...

Does it means I have two instances running? If I want to configure them
seperately using mysql command, what shall I do to distinguish each
instances?

Thanks,
Jing Zhang




Nanni Claudio wrote:
>
> Hi Jing,
>
> Logically speaking,
> you can run as many instances of mysql as you want,
> but you have to make an installation for each mysql instance.
> On each instance you can have as many databases as you want.
> From my experience here is the key:
>
> 1) One different mysql user (mysql41,mysql50) for each mysql installation.
>
> 2) put .my.cnf file in the home directory of each mysql user with the
> right parameters. This is the way I have found not to conflict between
> instances.
> The socket file is a file name you  have to assign to each instance,
> Use a different name for each instance.
>
> :-----.my.cnf------------:
> # The MySQL server
> [mysqld]
> port            = 3515
> socket          = /tmp/mysql5015.sock
> skip-locking
> key_buffer = 256M
> max_allowed_packet = 1M
> :-------------------------:
>
> 3) Of course each mysql instance must bind to a different
> port(3306,3316,3326)
>
>
> Let me know.
>
> Aloha!
>
> Claudio Nanni
>
>
> -----Messaggio originale-----
> Da: toutiao [mailto:jing_zhang@emcore.com]
> Inviato: venerdì 28 marzo 2008 3.03
> A: mysql@lists.mysql.com
> Oggetto: Re: Multiple instances howto
>
>
>
> In addition, can you run multiple instances of mysqld simultaneous?
>
>
>
> toutiao wrote:
>>
>>
>> Dear Friends,
>>
>> I'm learning to create multiple mysqld instances on Linux, where one
>> instance of mysql is already running.
>>
>> While creating a new my.cnf file, I noticed that I need to assign a
>> socket
>> file to it.
>>
>> Can you pls. tell me how to create / test this socket file?
>>
>> Thanks,
>> Jing Zhang
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Multiple-instances-howto-tp16338723p16344323.html
> Sent from the MySQL - General mailing list archive at Nabble.com.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:  
> http://lists.mysql.com/mysql?unsub=claudio.nanni@galileoavionica.it
>
>
>
> "Questo messaggio ed ogni suo allegato sono confidenziali e possono essere
> riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é
> il destinatario diretto del presente messaggio, é pregato di contattare
> l'originario mittente e di cancellare questo messaggio ed ogni suo
> allegato dal sistema di posta. Se il ricevente non é il destinatario
> diretto del presente messaggio, sono vietati l'uso, la riproduzione e la
> stampa di questo messaggio e di ogni suo allegato, nonché la diffusione
> del loro contenuto a qualsiasi altro soggetto"
> *****
> "This message and any attachment are confidential and may be privileged or
> otherwise protected from disclosure. If you are not the intended
> recipient, please contact the sender and delete this message and any
> attachment from your system. If you are not the intended recipient you
> must not use, copy or print this message or attachment or disclose the
> contents to any other person."
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@nabble.com
>
>
>

--
View this message in context: http://www.nabble.com/Multiple-instances-howto-tp16338723p16374504.html
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=claudio.nanni@galileoavionica.it



"Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto"
*****
"This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@nabble.com

Re: R: R: Multiple instances howto

by toutiao :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message


Nanni,

To make things even creepier, I tried to access the 2 "alleged" instances from a different WinXP PC by starting 2 instances of MySQL Administrator, using different port number 33061 and 33062 at the same time. And it worked!

I went further to create a database on each instances of MySQL Administrator, test1 and test2. It worked too! The changes are revealed on both instances immediately.

I will go ahead to write a LabVIEW test program and try to write to both test1 and test2 alternatively, see how is it work. If so, I will try to configure replication for each "alleged" instances.

WHAT IS GOING ON?

Jing Zhang


Nanni,

You know, it's really strange. On 2 different terminals, I ran

[root@FC8-DEV etc]mysql -S /var/lib/mysql/mysql.sock -u root -p

AND

[root@FC8-DEV etc]mysql -S /var/lib/mysql/mysql2.sock -u root -p

I was able to log in. Then I run "show variables", I could actually see them listening on different ports (33061 and 33062). If I can start only ONE mysql instance from the same installation, why am I able to log in 2 different instances? Where did I do wrong?

Thanks,
Jing Zhang



Nanni Claudio wrote:
Jing,

First: you can have only ONE mysql instance running for each installation,
but you can have as many installations as you want on each server.
So to answer your statement only the first command started mysql,
while the second command should have given you an error(there is already an instance running).

--:Jing:-------------------------------------------------------------------
And then I started (or, I believe I started) 2 difference instances with:
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &
---------------------------------------------------------------------------

If you issue a 'ps -ef | grep mysql' shell command, you'll see all mysql processes running, for each instance you have two basic processes:

==:ps -ef | grep mysql:====================================================
  mysql5   535   513  6 11:48:56 pts/0    0:01 /.../libexec/mysqld basedir=/data/Esercizio/mysql5015 --......

  mysql5   513   367  0 11:48:56 pts/0    0:00 /bin/sh bin/mysqld_safe
===========================================================================

The one with the lower id(513) is the one you started, the other(535) is the 'son' of 513.


Compiling and installing it is surely a good experience.
What I do NOT like of bundled installations is directory/user sharing.
If you install it yourself compiling it from sources you can
choose your directory, user and parameters for a particular mysql installation.

My advice is to follow this procedure:

1) Create a home directory for the user and installation of mysql
        e.g.: /home/mysql5018

2) Create a new operating system user for the new installation,
        e.g.: mysql5018, mysql323, etc...

3) compile mysql (follow the manual)

4) log on as the new mysql user (e.g.: mysql5018)

5) install mysql (follow the manual) as that user

6) create the .my.cnf file and put it in the home directory of the user (/home/mysql5018/.my.cnf)

IMPORTANT: watch for right permissions on files and directories.

This way you can start mysql as easy as   'bin/mysqld_safe&' with no command line parameters at all, given you are logged as the right mysql user(mysql5018) and the .my.cnf is in his home directory.

This is how I got so far.

Aloha!

Claudio Nanni



-----Messaggio originale-----
Da: toutiao [mailto:jing_zhang@emcore.com]
Inviato: sabato 29 marzo 2008 22.05
A: mysql@lists.mysql.com
Oggetto: Re: R: Multiple instances howto


Nanni,

Thanks for the advices. How to make an installation? I use Fedora Core 8
where MySQL comes with the bundle. Do you think I should download the source
code and compile it and configure it using ./configure?

I did changed the cnf file to have 2 different configurations:

[root@FC8-DEV mysqld]# cat /etc/my1.cnf
[mysqld]
port=33061
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@FC8-DEV mysqld]# cat /etc/my2.cnf
[mysqld]
port=33602
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql2.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid



And then I started (or, I believe I started) 2 difference instances with:
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
[root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &


I checked the service status:
[root@FC8-DEV mysqld]# service mysqld status
mysqld (pid 3542 3403) is running...

Does it means I have two instances running? If I want to configure them
seperately using mysql command, what shall I do to distinguish each
instances?

Thanks,
Jing Zhang




Nanni Claudio wrote:
>
> Hi Jing,
>
> Logically speaking,
> you can run as many instances of mysql as you want,
> but you have to make an installation for each mysql instance.
> On each instance you can have as many databases as you want.
> From my experience here is the key:
>
> 1) One different mysql user (mysql41,mysql50) for each mysql installation.
>
> 2) put .my.cnf file in the home directory of each mysql user with the
> right parameters. This is the way I have found not to conflict between
> instances.
> The socket file is a file name you  have to assign to each instance,
> Use a different name for each instance.
>
> :-----.my.cnf------------:
> # The MySQL server
> [mysqld]
> port            = 3515
> socket          = /tmp/mysql5015.sock
> skip-locking
> key_buffer = 256M
> max_allowed_packet = 1M
> :-------------------------:
>
> 3) Of course each mysql instance must bind to a different
> port(3306,3316,3326)
>
>
> Let me know.
>
> Aloha!
>
> Claudio Nanni
>
>
> -----Messaggio originale-----
> Da: toutiao [mailto:jing_zhang@emcore.com]
> Inviato: venerdì 28 marzo 2008 3.03
> A: mysql@lists.mysql.com
> Oggetto: Re: Multiple instances howto
>
>
>
> In addition, can you run multiple instances of mysqld simultaneous?
>
>
>
> toutiao wrote:
>>
>>
>> Dear Friends,
>>
>> I'm learning to create multiple mysqld instances on Linux, where one
>> instance of mysql is already running.
>>
>> While creating a new my.cnf file, I noticed that I need to assign a
>> socket
>> file to it.
>>
>> Can you pls. tell me how to create / test this socket file?
>>
>> Thanks,
>> Jing Zhang
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/Multiple-instances-howto-tp16338723p16344323.html
> Sent from the MySQL - General mailing list archive at Nabble.com.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:  
> http://lists.mysql.com/mysql?unsub=claudio.nanni@galileoavionica.it
>
>
>
> "Questo messaggio ed ogni suo allegato sono confidenziali e possono essere
> riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é
> il destinatario diretto del presente messaggio, é pregato di contattare
> l'originario mittente e di cancellare questo messaggio ed ogni suo
> allegato dal sistema di posta. Se il ricevente non é il destinatario
> diretto del presente messaggio, sono vietati l'uso, la riproduzione e la
> stampa di questo messaggio e di ogni suo allegato, nonché la diffusione
> del loro contenuto a qualsiasi altro soggetto"
> *****
> "This message and any attachment are confidential and may be privileged or
> otherwise protected from disclosure. If you are not the intended
> recipient, please contact the sender and delete this message and any
> attachment from your system. If you are not the intended recipient you
> must not use, copy or print this message or attachment or disclose the
> contents to any other person."
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@nabble.com
>
>
>

--
View this message in context: http://www.nabble.com/Multiple-instances-howto-tp16338723p16374504.html
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=claudio.nanni@galileoavionica.it



"Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto"
*****
"This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@nabble.com


R: R: R: Multiple instances howto

by Nanni Claudio :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Weird things happen!

Which MySQL server version are you using?

It looks like you managed to start two instances,
and from a unix point of view it is no strange.
But the problem is that two MySQL instances not only
differ for the TCP/IP port number and the Unix Socket file,
but there is the data directory, binary log files, server pid, error log file etc, that means that an instance is more related to more installation parameters that to only a port number/socket file configuration.
So my humble opinion is that even if it is possible to start multiple instances from a single installation you will possibly experiment some sort of side-effects.

I have found an interesting manual page regarding to this:
http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html


Let me know!

Aloha!

Claudio Nanni



-----Messaggio originale-----
Da: toutiao [mailto:jing_zhang@...]
Inviato: martedì 1 aprile 2008 2.10
A: mysql@...
Oggetto: Re: R: R: Multiple instances howto



Nanni,

To make things even creepier, I tried to access the 2 "alleged" instances
from a different WinXP PC by starting 2 instances of MySQL Administrator,
using different port number 33061 and 33062 at the same time. And it worked!

I went further to create a database on each instances of MySQL
Administrator, test1 and test2. It worked too! The changes are revealed on
both instances immediately.

I will go ahead to write a LabVIEW test program and try to write to both
test1 and test2 alternatively, see how is it work. If so, I will try to
configure replication for each "alleged" instances.

WHAT IS GOING ON?

Jing Zhang


toutiao wrote:

>
> Nanni,
>
> You know, it's really strange. On 2 different terminals, I ran
>
> [root@FC8-DEV etc]mysql -S /var/lib/mysql/mysql.sock -u root -p
>
> AND
>
> [root@FC8-DEV etc]mysql -S /var/lib/mysql/mysql2.sock -u root -p
>
> I was able to log in. Then I run "show variables", I could actually see
> them listening on different ports (33061 and 33062). If I can start only
> ONE mysql instance from the same installation, why am I able to log in 2
> different instances? Where did I do wrong?
>
> Thanks,
> Jing Zhang
>
>
>
>
> Nanni Claudio wrote:
>>
>> Jing,
>>
>> First: you can have only ONE mysql instance running for each
>> installation,
>> but you can have as many installations as you want on each server.
>> So to answer your statement only the first command started mysql,
>> while the second command should have given you an error(there is already
>> an instance running).
>>
>> --:Jing:-------------------------------------------------------------------
>> And then I started (or, I believe I started) 2 difference instances with:
>> [root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
>> [root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &
>> ---------------------------------------------------------------------------
>>
>> If you issue a 'ps -ef | grep mysql' shell command, you'll see all mysql
>> processes running, for each instance you have two basic processes:
>>
>> ==:ps -ef | grep
>> mysql:====================================================
>>   mysql5   535   513  6 11:48:56 pts/0    0:01 /.../libexec/mysqld
>> basedir=/data/Esercizio/mysql5015 --......
>>
>>   mysql5   513   367  0 11:48:56 pts/0    0:00 /bin/sh bin/mysqld_safe
>> ===========================================================================
>>
>> The one with the lower id(513) is the one you started, the other(535) is
>> the 'son' of 513.
>>
>>
>> Compiling and installing it is surely a good experience.
>> What I do NOT like of bundled installations is directory/user sharing.
>> If you install it yourself compiling it from sources you can
>> choose your directory, user and parameters for a particular mysql
>> installation.
>>
>> My advice is to follow this procedure:
>>
>> 1) Create a home directory for the user and installation of mysql
>> e.g.: /home/mysql5018
>>
>> 2) Create a new operating system user for the new installation,
>> e.g.: mysql5018, mysql323, etc...
>>
>> 3) compile mysql (follow the manual)
>>
>> 4) log on as the new mysql user (e.g.: mysql5018)
>>
>> 5) install mysql (follow the manual) as that user
>>
>> 6) create the .my.cnf file and put it in the home directory of the user
>> (/home/mysql5018/.my.cnf)
>>
>> IMPORTANT: watch for right permissions on files and directories.
>>
>> This way you can start mysql as easy as   'bin/mysqld_safe&' with no
>> command line parameters at all, given you are logged as the right mysql
>> user(mysql5018) and the .my.cnf is in his home directory.
>>
>> This is how I got so far.
>>
>> Aloha!
>>
>> Claudio Nanni
>>
>>
>>
>> -----Messaggio originale-----
>> Da: toutiao [mailto:jing_zhang@...]
>> Inviato: sabato 29 marzo 2008 22.05
>> A: mysql@...
>> Oggetto: Re: R: Multiple instances howto
>>
>>
>> Nanni,
>>
>> Thanks for the advices. How to make an installation? I use Fedora Core 8
>> where MySQL comes with the bundle. Do you think I should download the
>> source
>> code and compile it and configure it using ./configure?
>>
>> I did changed the cnf file to have 2 different configurations:
>>
>> [root@FC8-DEV mysqld]# cat /etc/my1.cnf
>> [mysqld]
>> port=33061
>> datadir=/var/lib/mysql
>> socket=/var/lib/mysql/mysql.sock
>> user=mysql
>> # Default to using old password format for compatibility with mysql 3.x
>> # clients (those using the mysqlclient10 compatibility package).
>> old_passwords=1
>>
>> [mysqld_safe]
>> log-error=/var/log/mysqld.log
>> pid-file=/var/run/mysqld/mysqld.pid
>> [root@FC8-DEV mysqld]# cat /etc/my2.cnf
>> [mysqld]
>> port=33602
>> datadir=/var/lib/mysql
>> socket=/var/lib/mysql/mysql2.sock
>> user=mysql
>> # Default to using old password format for compatibility with mysql 3.x
>> # clients (those using the mysqlclient10 compatibility package).
>> old_passwords=1
>>
>> [mysqld_safe]
>> log-error=/var/log/mysqld2.log
>> pid-file=/var/run/mysqld/mysqld2.pid
>>
>>
>>
>> And then I started (or, I believe I started) 2 difference instances with:
>> [root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my1.cnf &
>> [root@FC8-DEV etc]# mysqld_safe --defaults-file=/etc/my2.cnf &
>>
>>
>> I checked the service status:
>> [root@FC8-DEV mysqld]# service mysqld status
>> mysqld (pid 3542 3403) is running...
>>
>> Does it means I have two instances running? If I want to configure them
>> seperately using mysql command, what shall I do to distinguish each
>> instances?
>>
>> Thanks,
>> Jing Zhang
>>
>>
>>
>>
>> Nanni Claudio wrote:
>>>
>>> Hi Jing,
>>>
>>> Logically speaking,
>>> you can run as many instances of mysql as you want,
>>> but you have to make an installation for each mysql instance.
>>> On each instance you can have as many databases as you want.
>>> From my experience here is the key:
>>>
>>> 1) One different mysql user (mysql41,mysql50) for each mysql
>>> installation.
>>>
>>> 2) put .my.cnf file in the home directory of each mysql user with the
>>> right parameters. This is the way I have found not to conflict between
>>> instances.
>>> The socket file is a file name you  have to assign to each instance,
>>> Use a different name for each instance.
>>>
>>> :-----.my.cnf------------:
>>> # The MySQL server
>>> [mysqld]
>>> port            = 3515
>>> socket          = /tmp/mysql5015.sock
>>> skip-locking
>>> key_buffer = 256M
>>> max_allowed_packet = 1M
>>> :-------------------------:
>>>
>>> 3) Of course each mysql instance must bind to a different
>>> port(3306,3316,3326)
>>>
>>>
>>> Let me know.
>>>
>>> Aloha!
>>>
>>> Claudio Nanni
>>>
>>>
>>> -----Messaggio originale-----
>>> Da: toutiao [mailto:jing_zhang@...]
>>> Inviato: venerdì 28 marzo 2008 3.03
>>> A: mysql@...
>>> Oggetto: Re: Multiple instances howto
>>>
>>>
>>>
>>> In addition, can you run multiple instances of mysqld simultaneous?
>>>
>>>
>>>
>>> toutiao wrote:
>>>>
>>>>
>>>> Dear Friends,
>>>>
>>>> I'm learning to create multiple mysqld instances on Linux, where one
>>>> instance of mysql is already running.
>>>>
>>>> While creating a new my.cnf file, I noticed that I need to assign a
>>>> socket
>>>> file to it.
>>>>
>>>> Can you pls. tell me how to create / test this socket file?
>>>>
>>>> Thanks,
>>>> Jing Zhang
>>>>
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://www.nabble.com/Multiple-instances-howto-tp16338723p16344323.html
>>> Sent from the MySQL - General mailing list archive at Nabble.com.
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:  
>>> http://lists.mysql.com/mysql?unsub=claudio.nanni@...
>>>
>>>
>>>
>>> "Questo messaggio ed ogni suo allegato sono confidenziali e possono
>>> essere
>>> riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non
>>> é
>>> il destinatario diretto del presente messaggio, é pregato di contattare
>>> l'originario mittente e di cancellare questo messaggio ed ogni suo
>>> allegato dal sistema di posta. Se il ricevente non é il destinatario
>>> diretto del presente messaggio, sono vietati l'uso, la riproduzione e la
>>> stampa di questo messaggio e di ogni suo allegato, nonché la diffusione
>>> del loro contenuto a qualsiasi altro soggetto"
>>> *****
>>> "This message and any attachment are confidential and may be privileged
>>> or
>>> otherwise protected from disclosure. If you are not the intended
>>> recipient, please contact the sender and delete this message and any
>>> attachment from your system. If you are not the intended recipient you
>>> must not use, copy or print this message or attachment or disclose the
>>> contents to any other person."
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...
>>>
>>>
>>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Multiple-instances-howto-tp16338723p16374504.html
>> Sent from the MySQL - General mailing list archive at Nabble.com.
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:  
>> http://lists.mysql.com/mysql?unsub=claudio.nanni@...
>>
>>
>>
>> "Questo messaggio ed ogni suo allegato sono confidenziali e possono
>> essere riservati o, comunque, protetti dall'essere diffusi. Se il
>> ricevente non é il destinatario diretto del presente messaggio, é pregato
>> di contattare l'originario mittente e di cancellare questo messaggio ed
>> ogni suo allegato dal sistema di posta. Se il ricevente non é il
>> destinatario diretto del presente messaggio, sono vietati l'uso, la
>> riproduzione e la stampa di questo messaggio e di ogni suo allegato,
>> nonché la diffusione del loro contenuto a qualsiasi altro soggetto"
>> *****
>> "This message and any attachment are confidential and may be privileged
>> or otherwise protected from disclosure. If you are not the intended
>> recipient, please contact the sender and delete this message and any
>> attachment from your system. If you are not the intended recipient you
>> must not use, copy or print this message or attachment or disclose the
>> contents to any other person."
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...
>>
>>
>>
>
>

--
View this message in context: http://www.nabble.com/Multiple-instances-howto-tp16338723p16400907.html
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=claudio.nanni@...



"Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto"
*****
"This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...