本文へスキップ

mysqld_multi.txt

16KB · 599行

plaintext
[MySQL] mysqld_multi ���

- �ۼ��� : ��ĥ�� <san2(at)linuxchannel.net>
- �ۼ��� : 2002.02.09(��Ÿ����, ����)
         : 2003.01.29
- �� ��  : mysql
- �� ��  : ��߱�̻�
- �� ��  : �ϳ��� �������� �������� mysqld ���� ���� ��
- ���� : mysql, mysqld, mysqld_multi, safe_mysqld, mysqld_safe

*��1)
�� ������ ���� �ֽ� ���� �Ʒ� URL���� Ȯ���� �� �ֽ��ϴ�.

http://www.linuxchannel.net/docs/mysqld_multi.txt

*��2)
�� ������ ���� ���� �ϳ��� MySQL �������� �������� mysqld ����
���� ���� ���ؼ� �����մϴ�.
mysql ��ġ�� ��� �ǹ����� �ٷ�� ��������̰� ���� ������
�κ�� �ٷ� �ʽ��ϴ�.

*��3)
`mysqld_multi` ��α׷��� MySQL 3.23.29 �̻� ������Դϴ�.

reference :
- http://www.mysql.com/doc/en/mysqld_multi.html
- http://www.mysql.com/doc/en/Multiple_servers.html

---------------------------------------------------------
���
1. mysqld multi ��?
2. ���� �ٸ� �������� mysqld�� ���� ��(�⺻)
3. mysqld_multi �� �̿��Ͽ� �������� mysqld ���� ��
4. MySQL 3.23.x�� MySQL 4.0.x �� ���ÿ� �����ϴ� ��
5. ������ �ϳ��� DB�� �����ϰ� �����ϴ� ��
6. �ı�
---------------------------------------------------------

1. mysqld multi ��?

`mysqld_multi'��� PERL ��ũ��Ʈ(3.23.29 �̻�)�� �̿��Ͽ�
���� �������� �������� ������� mysqld�� ��ϴ� ���� ���մϴ�.

��Ȯ� ���ؼ�, mysql �Ʈ�� ����� ���� �޸��Ͽ� �������� mysqld
��μ����� �� ��ϴ� ���� ���մϴ�.

httpd���� 80, 8080 �Ʈ�� ��ϴ� ��̳� �Ǵ� sendmail����
daemonport�� ���� �޸��Ͽ� ��ϴ� ���� ���� �����Դϴ�.


2. ���� �ٸ� �������� mysqld�� ���� ��(�⺻)

<���>
�� ���� ���� �������� mysqld ���� ���� �⺻ ���׿� ���ؼ�
�ٷ�ϴ�. ����� �̷��� ��� ���� ����� �ణ �����ϱ� ������
������ �ƴմϴ�.
</���>

���� mysql�� �߰��� ������� �ʿ�� �����ϴ�. ���� safe_mysqld
��ũ��Ʈ�� �̿��ϸ� �˴ϴ�.

�̶� �߿��� ���� �ݵ�� �ּ��� pid-file, socket ��ġ�� ���� �ٸ���
�����ؾ� �մϴ�.

*�ݵ�� �ٸ��� �����ؾ��� �ɼ�)

--pid-file=
--socket=
--port= or --skip-network

����� �ɼ�� mysql�� ������Ҷ� �⺻������ ����ϴ� ���� �����ų�
my.cnf ���� [mysqld] ���ǿ� ������ �ɼ�� �����ϴ�.

<�> �ܺ� MySQL client���� ����� ��õ �����Ϸ���?
mysql.host ��̺� ����� �ƿ� mysql �Ʈ�� �����ʵ���
--skip-network �ɼ�� �ְ� mysqld�� ����ϸ� �˴ϴ�.
(`netstat -atnp`�� Ȯ��).
</�>

���� ���� �ܺ� mysql client���� ������ ���� ��찡 �ƴ� ���� client
���� �����ϴ� ���� �����ϰڽ��ϴ�.

���� �ܺ� mysql client(��κ� ������)���� �����ؾ߸� �ϴ� �����
--skip-network ��� --port=# �ɼ���� �ٲپ�� �մϴ�.


[ù��° mysqld ����]

shell> safe_mysqld &
or
shell> safe_mysqld --defaults-file=/root/.my.cnf &

[�ι�° �߰����� mysqld ����]

shell> safe_mysqld \
  --pid-file=/usr/local/mysql/var/`hostname`.pid2 \
  --socket=/tmp/mysql.sock2 \
  --skip-network & /*** or --port=3307 ***/
or
shell> safe_mysqld \
  --defaults-file=/root/.my.cnf \
  --pid-file=/usr/local/mysql/var/`hostname`.pid2 \
  --socket=/tmp/mysql.sock2 \
  --skip-network & /*** or --port=3307 ***/

�ι�° mysqld���� ���Ǵ� ����� �ɼ�� ù��° mysqld�� �⺻ �ɼ��
�״�� �����ϴ�. �� basedir, datadir �� ���� �ɼ�� ���� �������
�ʾұ� ������ � ���� ����ϴ���� �˾ƺ����� ����� ���� Ȯ����
���ϴ�.

[�⺻������ ���Ǵ� �� �˾ƺ���]

shell> /usr/local/mysql/libexec/mysqld --help
...
basedir:     /usr/local/mysql/
datadir:     /usr/local/mysql/var/
tmpdir:      /tmp/
language:    /usr/local/mysql/share/mysql/english/
pid file:    /usr/local/mysql/var/home.pid
TCP port:    3306
Unix socket: /tmp/mysql.sock
...
Possible variables for option --set-variable (-O) are:
back_log              current value: 50
binlog_cache_size     current value: 32768
connect_timeout       current value: 5
...


[�� mysqld�� ������ ����]

��� �ι�° mysqld�� datadir �ɼ�� ���� ������ ù��° mysqld�� datadir
�� �����ϴ�. �� �ϳ��� datadir�� ������ ���� �˴ϴ�.

*��μ��� Ȯ��)
shell> ps -ef --cols 400 | grep mysqld

*���� Ȯ��)
shell> ls /tmp/mysql.sock*
/tmp/mysql.sock  /tmp/mysql.sock2

*������ ����)
shell> mysql -u username -p -S /tmp/mysql.sock2 db_name

'-S'(�Ǵ� --socket) �ɼ���� �ι�° mysqld�� ���� ��ġ�� �Է��� �ָ�
�˴ϴ�.


[�ι�° mysqld �����ϱ�]

�̿� ���� �ι�°, ����° mysqld�� �����ϴ� ���� �Ź� �ɼ�� ������
�ϴ� ������� �ֽ��ϴ�(���� ������ �� �Ų�� ���ϰ�).

�ϴ� �ι�° mysqld ������ �����ϰ� ���� �׽�Ʈ�� ���´ٸ� �����
�ʿ����� �ι�° mysqld�� ������ ���ô�.

���ᵵ �����մϴ�. ��� ���� ��ġ�� �߰� �ɼ�� �ָ� �˴ϴ�.

shell> mysqladmin -u root -p -S /tmp/mysql.sock2 shutdown


3. mysqld_multi �� �̿��Ͽ� �������� mysqld ���� ��

mysqld_multi ��α׷��� PERL�� ¥���� �����, PREFIX/bin/mysqld_multi��
��ġ�մϴ�. (MySQL 3.23.29 �������� �߰��Ǿ�����.)

mysql.server �Ǵ� safe_mysqld ��ũ��Ʈ�� �⺻������ /etc/my.cnf �Ǵ�
~/.my.cnf �Ǵ� PREFIX/var/my.cnf ���� ��Ͽ��� [mysqld] ����� �ɼ� ����
�� �о�鿩 mysqld �� �����մϴ�.

<���>
MySQL 4.0.x ������ safe_mysqld�� �ƴ϶� mysqld_safe���� ��ũ��Ʈ �̸���
����Ǿ����ϴ�.
</���>

���� mysql �������(my.cnf)�� /root/.my.cnf���� ����մϴ�.

�ݸ� mysqld_multi ��ũ��Ʈ�� [mysqld_multi], [mysqld1], [mysqld2], ...,
[mysqld] �� ����� ����մϴ�.

�����ϸ�,

  `safe_mysqld'  <-- [mysqld] <-- defaults options

  `mysqld_multi' <-- [mysqld_multi]
                       |-- [mysqld1] <-- ([mysqld]) <-- defaults options
                       |-- [mysqld2] <-- ([mysqld]) <-- defaults options
                       |-- [mysqld3] <-- ([mysqld]) <-- defaults options
                       `-- [mysqld#] <-- ([mysqld]) <-- defaults options

�̿� ���� ����� �� ����� ����Ͽ� mysqld�� �����մϴ�.
(��ȣ��� ����� �� ����� �ִٸ� ����Ѵٴ� �ɼ�Դϴ�.)

mysqld_multi �� ����ϱ� ���ؼ� /root/.my.cnf ��Ͽ� ����� ����
�߰��ؾ� �մϴ�(���ٸ� ��� ����).

shell> /usr/local/mysql/bin/mysqld_multi --example

�ϸ� �� ������ ��µ˴ϴ�.

-- /root/.my.cnf �Ǵ� /etc/my.cnf (������) ----------
[client]
password	= �� �������..
host		= localhost

[mysql]
ignore-spaces

[mysqld]
default-character-set = euc_kr
safe-show-database
skip-name-resolve
#port		= 3306
skip-network
log
#log-update
user		= mysql
set-variable 	= key_buffer=256M
set-variable 	= thread_cache_size=8
set-variable 	= table_cache=256

[myisamchk]
set-variable 	= key_buffer=100M
set-variable 	= sort_buffer=100M
set-variable 	= read_buffer=3M
set-variable 	= write_buffer=3M
#set-variable 	= sort_key_blocks=20
#set-variable 	= decode_bits=10

[mysqladmin]
#set-variable 	= connect_timeout=100
#set-variable 	= shutdown_timeout=1

[mysqldump]
user		= root
-----------------------------------------------------

-- /root/.my.cnf �Ǵ� /etc/my.cnf (������) ----------
[client]
(����)...

[mysql]
(����)...

[mysqld]
default-character-set = euc_kr
skip-name-resolve
skip-network	## only localhost access
language	= /usr/local/mysql/share/mysql/english
(����)...

[mysqld_multi]
mysqld		= /usr/local/mysql/bin/safe_mysqld
mysqladmin	= /usr/local/mysql/bin/mysqladmin
#user		= root

[mysqld1]
socket		= /tmp/mysql.sock1
#port		= 3307
pid-file	= /usr/local/mysql/var/mysqld1.pid
datadir		= /usr/local/mysql/var
log		= /usr/local/mysql/var/mysqld1.log
user		= mysql

[mysqld2]
socket		= /tmp/mysql.sock2
#port		= 3308
pid-file	= /usr/local/mysql/var2/mysqld2.pid
datadir		= /usr/local/mysql/var2
log		= /usr/local/mysql/var2/mysqld2.log
user		= mysql

[myisamchk]
(����)...

[mysqladmin]
(����)...

[mysqldump]
(����)...
-----------------------------------------------------

��� �������� [mysqld1]�� datadir�� ���� [mysqld] ���ǰ� �����ϹǷ�
�ϳ��� datadir�� �����ϰڴٴ� �ǹ�Դϴ�.

[mysqld2]�� datadir�� ���� �ٸ��Ƿ� ��� ������� �����ͺ��̽��� �
�ϰڴٴ� �ǹ�Դϴ�.

��) [mysqld2]�� datadir �����
shell> mkdir /usr/local/mysql/var2
shell> /usr/local/mysql/bin/mysql_install_db \
  --datadir=/usr/local/mysql/var2
shell> chown mysql.mysql -R /usr/local/mysql/var2

�̿� ���� �⺻ mysql �����ͺ��̽��� ����� �ָ� �˴ϴ�.

MySQL ���� �Ŵ��� ���ϸ� [mysqld_multi] ���ǿ� multi_admin ������
����� ���� ���� MySQL�� �߰��Ͽ� �����ϵ��� �ϴ� ������ �ֽ��ϴ�.

shell> mysql -u root -S /tmp/mysql.sock -proot_password -e \
  "GRANT SHUTDOWN ON *.* TO multi_admin@localhost \
  IDENTIFIED BY 'multipass'"

�׷��� �ݵ�� �̿� ���� multi_admin ������ �߰��� �ʿ�� ����
���� root ������ �̿��ϸ� �׸��Դϴ�.

<���>
[mysqld1] ���ǿ��� user�� �ش� mysqld�� ��μ��� ������
�ǹ��մϴ�. /etc/passwd ��Ͽ� ������ �ٸ� �����ε� ���������մϴ�.
���� �ϳ��� ������ ���� � ����(ulimt)�� �ִٸ� mysql ���� ���
�ٸ� ������ /etc/passwd ��Ͽ� �߰��ϰ� �ش� ������ ����ϸ� �˴ϴ�.
</���>

�׷� �ι�° [mysqld1], ����° [mysqld2] mysqld�� ������ ���ô�.

shell> /usr/local/mysql/bin/mysqld_multi --help
...
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or     mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
..

GNR�� 'Group NumbeR'�� �ǹ��ϸ� [mysqld#]���� # �κ�� ���մϴ�.
�� ���� ����� 1���� �����ϸ� GNR �ɼ�� ���ٸ� ��� GNR�� �⺻������
����մϴ�.

*mysqld �����ϱ�)
shell> /usr/local/mysql/bin/mysqld_multi start
or
shell> /usr/local/mysql/bin/mysqld_multi start 1,2

*����Ȯ��)
shell> /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

*�ι�° mysqld�� �����ϱ�
shell> mysql [OPTIONS] -S /tmp/mysql.sock1 db_name

*����° mysqld�� �����ϱ�
shell> mysql [OPTIONS] -S /tmp/mysql.sock2 db_name

*�ι�° mysqld �����ϱ�)
shell> /usr/local/mysql/bin/mysqld_multi stop 1

*�ι�°, ����° mysqld �����ϱ�)
shell> /usr/local/mysql/bin/mysqld_multi stop 1,2

*��� mysqld multi �����ϱ�)
shell> /usr/local/mysql/bin/mysqld_multi stop
or
shell> /usr/local/mysql/bin/mysqld_multi stop 1,2

mysqld_multi�� safe_mysqld�� �����Դϴ�. � �ɼ�� �ְ� �����ϴ��Ŀ�
���� �ΰ��� ���� ���踦 ���� �� ����� ��Ȯ� ���ϸ� �ΰ��� ������ ������
mysqld �Դϴ�.

��,

shell> safe_mysqld &
shell> mysqld_multi start
shell> mysqladmin shutdown
shell> mysqld_multi stop

�̿� ���� ������ ���� �����Ͽ� ���/������ �� �ֽ��ϴ�.

���� �̿� ���� ���� ���� �����Ͽ� ��ϰ� ��� �ʴٸ�
���� safe_mysql�� [mysqld] ����� mysqld_multi ��� [mysqld1]����
�����Ͽ� �⺻ mysqld�� ��� �� �ֽ��ϴ�.

<���> ���ý� �ڵ� ����ϱ�(/etc/rc.d/rc.local)
/usr/local/mysql/bin/mysqld_multi \
  --defaults-file=/root/.my.cnf start
</���>


4. MySQL 3.23.x�� MySQL 4.0.x �� ���ÿ� �����ϴ� ��

���ݱ��� ���� �����ߴٸ� �ΰ��� ���� �ٸ� MySQL ������ �����ϴ�
���� ��� �����ϴ�.

  MySQL 3.23.x : /usr/local/mysql
  MySQL 4.0.x  : /usr/local/mysql4

�� ���� ��ġ�ߴٴ� �����Դϴ�.

<���>
MySQL 4.0.x ������ safe_mysqld�� �ƴ϶� mysqld_safe���� ��ũ��Ʈ �̸���
����Ǿ����ϴ�.
</���>

-- /root/.my.cnf �Ǵ� /etc/my.cnf ----------------
[client]
(����)...

[mysql]
(����)...

[mysqld]
(����)...

[mysqld_multi]
mysqld		= /usr/local/mysql4/bin/mysqld_safe ## <-- ���
mysqladmin	= /usr/local/mysql4/bin/mysqladmin
#user		= root

[mysqld1]
socket		= /tmp/mysql4.sock1
#port		= 3307
skip-name-resolve
skip-network	## only localhost access
default-character-set = euc_kr
pid-file	= /usr/local/mysql4/var/mysqld1.pid
datadir		= /usr/local/mysql4/var
language	= /usr/local/mysql4/share/mysql/english
log		= /usr/local/mysql4/var/mysqld1.log
user		= mysql

[myisamchk]
(����)...

[mysqladmin]
(����)...

[mysqldump]
(����)...
-----------------------------------------------------

*MySQL 3.23.x ����
shell> safe_mysqld &

*MySQL 4.0.x ����
shell> mysqld_multi start

��ڰ� �� ���� �Ұ��� ����� MySQL 4.0.x �������� ���׷��̵��� ���
�����ڰ� �����ϰ�(?) �׽�Ʈ�� ����, �̻�� ���ٸ� �ƹ��� �𸣰�(?)
�����ϰ� ���׷��̵��� �� �ִٴ� ���Դϴ�.

���� ����� URL�� ��� �˾Ƶξ�� �մϴ�.

  - http://www.mysql.com/doc/en/Upgrading-from-3.23.html


5. ������ �ϳ��� DB�� �����ϰ� �����ϴ� ��

�� ���� ���� �ٸ� �������� mysqld ����� datadir�� ��� �����ϰ�
�����Ͽ� ��ϴ� ���Դϴ�.

�������� mysqld ���� ���� �ϹǷ� �ý��ۿ� ����� �޸𸮰� �־��
�մϴ�(512M �̻�).

*��� �ϴ� ���)
1. �ý��� ���ϰ� �ص��� �� �ʴ� ���.
2. �ϳ�(socket)�� mysqld�� �ý���� ��ϱ⿡�� �ڿ��� ���� ���
3. �ϳ��� mysqld�� ���񽺰� �ȭ ����� ���
4. ��Ÿ �׽�Ʈ�� ������ ���� ����� �� ���


<���>
��ڰ� �׽�Ʈ�� �� �ٷδ� �ý��� ����(CPU ��뷮 95%�̻�)�� ����
���, ���� ���� �� �� �����ϴ�(ū ȿ���� ����).
(�������� mysqld ���� ��� �ʾƵ� �󸶵� �� ����ս���
�� �� �ֽ��ϴ�)
�ٸ�, �� ���� �������� �� ���� �׽�Ʈ�� �غ���, ������ ���� ����
����� �� ��쿡�� ����ϵ��� �ϼ���.
</���>

-- /root/.my.cnf �Ǵ� /etc/my.cnf ----------------
[client]
(����)...

[mysql]
(����)...

[mysqld]
default-character-set = euc_kr
skip-name-resolve
skip-network	## only localhost access
datadir		= /usr/local/mysql/var
language	= /usr/local/mysql/share/mysql/english
user		= mysql
(����)...

[mysqld_multi]
mysqld		= /usr/local/mysql/bin/safe_mysqld
mysqladmin	= /usr/local/mysql/bin/mysqladmin
#user		= root

[mysqld1]
socket		= /tmp/mysql.sock1
#port		= 3307
pid-file	= /usr/local/mysql/var/mysqld1.pid
log		= /usr/local/mysql/var/mysqld1.log

[mysqld2]
socket		= /tmp/mysql.sock2
#port		= 3308
pid-file	= /usr/local/mysql/var/mysqld2.pid
log		= /usr/local/mysql/var/mysqld2.log

[mysqld3]
socket		= /tmp/mysql.sock3
#port		= 3309
pid-file	= /usr/local/mysql/var/mysqld3.pid
log		= /usr/local/mysql/var/mysqld3.log

[myisamchk]
(����)...

[mysqladmin]
(����)...

[mysqldump]
(����)...
-----------------------------------------------------

��� ������ ����ϸ� �� �׷� [mysqld#]�� datadir �ɼ�� �����Ƿ�
[mysqld] ����� datadir�� �״�� �����ϴ�.
�� ��� ���� �ϳ��� datadir�� �����ϴ� ���� �˴ϴ�.

���� log �ɼ�� �����ϸ� �⺻ [mysqld] ����� log �ɼ�� �����ϴ�.
��� ������ ���� Ȯ��� �˾ƺ��� ���ؼ� ���� �ٸ� log ��Ͽ� ���
�ϵ��� ��� ��Դϴ�.

���� �ϳ��� ������ ��� �����ϰ� ��� �ʴٸ� �� [mysqld#] ���ǿ�
�ش� user �ɼ�� ������ �ָ� �˴ϴ�.
(�ϳ��� ������ ��� ���Ѽ��� ���� ���)

shell> mysqld_multi start

�̷��� ��ɾ ������ �߰��� 3���� ���� ������ mysqld�� �����Ǹ�,
��� ������ �ϳ��� datadir�� �����մϴ�.

���� 1, 2�� mysqld�� ����ϵ��� �ϰ� �ʹٸ�

shell> mysqld_multi start 1,2

�̷��� �߰� �ɼ�� �ָ� �˴ϴ�.

shell> mysqld_multi report

��� ��ɾ�� 3�� ��� �����ϴ�� Ȯ���� ������ �ϼ���.

����� �����ϰ� 1, 2, 3���� �����ϵ��� socket ��ϸ� �����ϰ� ������
�ָ� �˴ϴ�.

���� PHP�� �����Ѵٸ�(�˻����� ����),

----------------------------------------------------
<?php
##
## �˻���忡�� ����
if('�˻����ΰ��') {
  $sock = '/tmp/mysql.sock' . rand(1,3);
  ini_set('mysql.default_socket',$sock);
}

(����: ������� ���� ����)

?>
----------------------------------------------------

�̷��� �����ϴ� �⺻ socket ��� ��ġ�� �����ϰ� ����� �ָ� �˴ϴ�.

���� ���� datadir�� ���� �ٸ��� ��������� ��� ������ MySQL ���񽺸�
�Ѵٸ�, ������ ��鿡�� ����� ���� ������ �� �ֽ��ϴ�.

-- httpd.conf --------------------------------------
...
<VirtualHost xxxx>
  ...
  php_value mysql.default_socket "/tmp/mysql.sock1"
</VirtualHost>

<VirtualHost xxxx>
  ...
  php_value mysql.default_socket "/tmp/mysql.sock2"
</VirtualHost>

<VirtualHost xxxx>
  ...
  php_value mysql.default_socket "/tmp/mysql.sock3"
</VirtualHost>
----------------------------------------------------

or

-- DOCUMENT_ROOT/.htaccess -------------------------
...
php_value mysql.default_socket "/tmp/mysql.sock2"
----------------------------------------------------


6. �ı�

(����)


E