[MYSQL]님이 남기신 글:

>안녕하세요 산이님
>궁금한게 있어서 글남깁니다.
>*메인서버
>MYSQL 버전 : MySQL 4.0.13-nt-log
>운영체제  : WIN200
>
>*미러링서버
>MYSQL 버전은 MySQL 4.0.23-nt-max-log 이며
>운영체제는 WIN2000 입니다.
>
>메인서버와 미러링서버로 운영중이며 메인서버와
리플리케이션해서
 사용중에 있습니다.
>문제가 생길 수가 있는지 모르겠으나 ""메인서버
 <--> 미러링서버""
 이렇게 서로 리플리케이션 하게 설정해놨습니다.(양방향)

>
>참고서적이나 홈페이지에서는
 단방향의 리플레케이션 설정법만 나와 있는데 저는
양방향으로 해놨습니다.
>
>보시듯이 메인서버의 MYSQL버전이 약간 낮은
상태입니다.
>문제는 미러링서버에서
 이벤트로그를 보면 특정시간대마다
 계속해서MYSQL에러로그가
 세개씩 쌓이고 있습니다.
>
>해결해보려 했으나 계속해서 쌓이고 있는
상황입니다.
>에러로그를 순서대로 보여드리면
>======================================================================
>미러링서버 에러로그
>1. Error reading packet from server: Lost connection to MySQL server during query
(server_errno=2013)
>
>For more information, see Help and Support Center at http://www.mysql.com.   
>
>2. Slave I/O thread: Failed reading log event, reconnecting to retry, log
'replication.003' position 128073
>
>For more information, see Help and Support Center at http://www.mysql.com.   
>
>3. Slave: connected to master 'backup@210.183.195.120:3306',replication
resumed in log 'replication.003' at position 128073
>
>For more information, see Help and Support Center at http://www.mysql.com.   
>
>=====================================================================
>메인서버 에러로그
>
>050417  2:46:17  Slave: received 0 length packet from server, apparent master
shutdown: 
>050417  2:46:17  Slave I/O thread: Failed reading log event, reconnecting to retry,
log 'replication.005' position 79
>050417  2:47:03  Slave I/O thread: error reconnecting to master 'backup@221.150.135.178:3306': Error:
'Can't connect to MySQL server on '221.150.135.178' (10060)'  errno: 2003 
retry-time: 5  retries: 86400
>
>======================================================================
>
>이렇게 세개의 로그가 메인서버와 미러링서버에 계속해서
쌓이는 상황입니다.
>단, 로그는 생기고 있지만 리플리케이션은
 정상작동하고 있는 상태이구요.
>
>제 생각엔 어떤 원인에 의해서 특정시간대가 되면 반복해서
MYSQL이 죽는것 같습니다만 원인을 찾기가 어렵습니다.
>
>====================================================================
>
>
>미러링 서버 MY.INI 파일 설정은 
>
>#This File was made using the WinMySQLAdmin 1.4 Tool
>#2005-01-15 오후 3:25:29
>
>#Uncomment or Add only the keys that you know how works.
>#Read the MySQL Manual for instructions
>
>[mysqld]
>basedir=C:/Inetpub/PHP_Setup_for_IIS/mysql
>#bind-address=���이피

>datadir=C:/Inetpub/PHP_Setup_for_IIS/mysql/data
>#language=C:/Inetpub/PHP_Setup_for_IIS/mysql/share/koran
>#slow query log#=
>#tmpdir#=
>#port=3306
>
>skip-locking
>skip-name-resolve
>
>set-variable	= back_log=1024
>set-variable	= key_buffer=192M
>set-variable    = max_connections=500
>set-variable    = max_connect_errors=1024
>set-variable	= max_allowed_packet=1M
>set-variable	= max_tmp_tables=1024
>set-variable	= table_cache=10000
>set-variable	= join_buffer_size=1M
>set-variable	= sort_buffer_size=1M
>set-variable	= read_buffer_size=1M
>set-variable	= read_rnd_buffer_size=1M
>set-variable	= myisam_sort_buffer_size=64M
>set-variable	= thread_cache_size=8
># Try number of CPU's*2 for thread_concurrency
>set-variable	= thread_concurrency=2
>set-variable	= wait_timeout=15
>set-variable	= interactive_timeout=3600
>set-variable	= connect_timeout=10
>set-variable	= query_cache_limit=1M
>set-variable	= query_cache_size=2M
>set-variable	= query_cache_type=1
>
>
>log-bin=C:/Inetpub/PHP_Setup_for_IIS/mysql/log/replication.log
>slave-skip-errors      = all
>server-id=2
>master-host=��인서버아이피

>master-user=backup
>master-password=backup2
>master-connect-retry=5
>
>
>
>
>[WinMySQLadmin]
>Server=C:/Inetpub/PHP_Setup_for_IIS/mysql/bin/mysqld-max-nt.exe
>user=root
>password= 
>
>[mysqldump]
>quick
>set-variable	= max_allowed_packet=16M
>
>[mysql]
>no-auto-rehash
># Remove the next comment character if you are not familiar with SQL
>#safe-updates
>
>[isamchk]
>set-variable	= key_buffer=256M
>set-variable	= sort_buffer=256M
>set-variable	= read_buffer=2M
>set-variable	= write_buffer=2M
>
>[myisamchk]
>set-variable	= key_buffer=256M
>set-variable	= sort_buffer=256M
>set-variable	= read_buffer=2M
>set-variable	= write_buffer=2M
>
>[mysqlhotcopy]
>interactive-timeout
>
>================================================================
>
>메인서버 MY.INI 설정은
>
>#This File was made using the WinMySQLAdmin 1.4 Tool
>#2003-07-18 오후 4:58:06
>
>#Uncomment or Add only the keys that you know how works.
>#Read the MySQL Manual for instructions
>
>[mysqld]
>basedir=C:/Inetpub/program/mysql
>#bind-address=���이피

>datadir=C:/Inetpub/program/mysql/data
>language=C:/Inetpub/program/mysql/share/korean
>#slow query log#=
>#tmpdir#=
>#port=3306
>
>skip-locking
>skip-name-resolve
>
>set-variable	= back_log=1024
>set-variable	= key_buffer=512M
>set-variable    = max_connections=300
>set-variable    = max_connect_errors=1024
>set-variable	= max_allowed_packet=1M
>set-variable	= max_tmp_tables=1024
>set-variable	= table_cache=10000
>set-variable	= join_buffer_size=1M
>set-variable	= sort_buffer_size=1M
>set-variable	= read_buffer_size=1M
>set-variable	= read_rnd_buffer_size=1M
>set-variable	= myisam_sort_buffer_size=64M
>set-variable	= thread_cache_size=8
># Try number of CPU's*2 for thread_concurrency
>set-variable	= thread_concurrency=2
>set-variable	= wait_timeout=15
>set-variable	= interactive_timeout=3600
>set-variable	= connect_timeout=10
>set-variable	= query_cache_limit=1M
>set-variable	= query_cache_size=2M
>set-variable	= query_cache_type=1
>
>log-bin=C:/Inetpub/program/mysql/log/replication.log
>slave-skip-errors      = all
>server-id=1
>master-host=미���링서버아이피

>master-user=backup
>master-password=backup2
>master-connect-retry=5
>
>
>[WinMySQLadmin]
>Server=C:/Inetpub/program/mysql/bin/mysqld-nt.exe
>user=root
>password= 
>
>[mysqldump]
>quick
>set-variable	= max_allowed_packet=16M
>
>[mysql]
>no-auto-rehash
># Remove the next comment character if you are not familiar with SQL
>#safe-updates
>
>[isamchk]
>set-variable	= key_buffer=256M
>set-variable	= sort_buffer=256M
>set-variable	= read_buffer=2M
>set-variable	= write_buffer=2M
>
>[myisamchk]
>set-variable	= key_buffer=256M
>set-variable	= sort_buffer=256M
>set-variable	= read_buffer=2M
>set-variable	= write_buffer=2M
>
>[mysqlhotcopy]
>interactive-timeout
>======================================================================
>
>이렇게 구성이 되어 있습니다.
>의심이 갈만한게 있다면 미러링서버는 일반데스크탑피시를
 이용해서 만들었는데 메인보드가 온보드로 보드에 붙어 있는
리얼텍랜카드를
 이용해서 서비스중입니다.

>MYSQL이 죽는게 랜카드의 이상으로 웹자체가 죽어서 생기는
문제가 아닐까요? 랜카드를 따로 다는게 나을까요?
>
>답변좀 부탁드리겠습니다.
 

========================================

같은 DB 에 대해서 양방향(master/slave)
 리플리케이션을
 설정하면 당연히
깨집니다.

단방향(master -> slave) 리플리케이션을
 설정하세요.