mysql replication error

http://cafe.rootcenter.com/130078704068

mysql replication 진행도중 slave서버의  error-log 파일내용을 확인한 후 대처법

 에러유형-4) - 2010년 5월 3일 추가

100503  8:41:05 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)
100503  8:41:05 [ERROR] Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when reading data from binary log
100503  8:41:05 [Note] Slave I/O thread exiting, read up to log 'binlog.021419', position 196980

 

해결1)  master서버의 max_allowed_packet 값이 작을 경우 문제가 발생할수 있다.

          - my.cnf파일을 수정해 준후 데몬을 재시작한다.

          - 데몬 재시작이 힘들경우 쉘 상에서 값을 변경한다.

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SET GLOBAL max_allowed_packet = 1024*1024*100;

mysql> SET SESSION max_allowed_packet = 1024*1024*100;

해결2) master서버의 max_allowed_packet값이 충분히 큰데 위와 같은 오류가 발생할 경우..

        - 먼저 master서버의 binlog파일을 확인해본다.

[root@db11 slave_log]#mysqlbinlog  binlog.021419 | more

--중략--
# at 196800
#100503  7:16:31 server id 1  end_log_pos 196827  Xid = 3282913394
COMMIT/*!*/;
# at 196827
#100503  7:16:31 server id 1  end_log_pos 138  Query thread_id=235254463 exec_time=0 error_code=0
SET TIMESTAMP=1272838591/*!*/;
Update tablename Set count0/*!*/;
# at 196965
#100503  7:16:31 server id 1  end_log_pos 196992  Xid = 3282913395
COMMIT/*!*/;
# at 196992
#100503  7:16:31 server id 1  end_log_pos 133  Query thread_id=232937265 exec_time=0 error_code=0
SET TIMESTAMP=1272838591/*!*/;
Update tablename Set size = 295976960/*!*/;
# at 197125

        - 확인 결과 196980 위치가 존재하지 않는다.

        - master_log_pos를 다음값으로 변경하면 끝.

 

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> stop slave;

mysql> change master to master_log_pos = 196992;

mysql> start slave;

 

에러유형-3) 2010.01.30 추가

100130 12:57:07 [ERROR] Slave: Error ''SQL 구문에 오류가 있습니다.' 에러 같읍니다. ('' 명령어 라인 1)' on query. Default database: 'dbname'. Query: 'UPDATE PARTNER_CNT SET .... WHERE ...', Error_code: 1064
100130 12:57:07 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.007704' position 70234030

해결) 키값이 중복되어 슬레이브에서 쿼리가 실행되지 못하고 있는것이다.

        정상적인 리플리케이션에서는 문제가 없지만 갑작스런 서버 다운, 네트워크 단절이 발생했을 경우 위와  같은 오류가 발생한다.

        아래 명령을 입력하여 해결 할 수 있다.      

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave; 

 

 

 에러유형-1)

100122  0:22:44 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
100122  0:22:44 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'binlog.017528' position 90025355
100122  0:22:48 [Note] Slave: connected to master 'xxxxxx@아이피',replication resumed in log 'binlog.017528' at position 90025355

 

 

해결) master서버와의 연결이 끊어지면 위와 같은 오류 메세지를 출력하고 리플리케이션이 중지된다.

        master서버가 정상적으로 돌아왔다면 slave 서버에 mysql을 접속한후 아래 명령을 입력한다.

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> stop slave;

mysql> start slave;

 

에러유형-2)

 100122  1:35:56 [Note] Slave I/O thread killed while reading event
100122  1:35:56 [Note] Slave I/O thread exiting, read up to log 'binlog.017530', position 33231841
100122  1:36:05 [Note] Slave SQL thread initialized, starting replication in log 'binlog.017528' at position 195, relay log '/db/slave_log/relay_log.002684' position: 90005865
100122  1:36:05 [ERROR] Error in Log_event::read_log_event(): 'Event too big', data_len: 811157539, event_type: 0
100122  1:36:05 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
100122  1:36:05 [ERROR] Slave: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 0
100122  1:36:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.017528' position 195
100122  1:36:05 [Note] Slave I/O thread: connected to master 'xxxx@아이피',  replication started in log 'binlog.017530' at position 33231841

 

해결) slave서버가 갑작스럽게 다운 되었을때 로그 파일이 깨져서 위와 같은 오류가 발생한다.

        위 에러로그에서는 relay_log.002684 로그에서 중지 되었다.

        slave_log가 쌓이는 디렉토리( /db/slave_log/)에  relay_log.002685 파일이 있는지 확인후 아래 명령을 입력한다.

 

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> stop slave;

mysql> CHANGE MASTER TO relay_log_file='/db/slave_log/relay_log.002685', relay_log_pos=0;

mysql> start slave;