mysql 복제 진행중

슬래이브 서버에서 복제 오류가 발생 할 때가 있다.

종종 발생하는 경우가 duplicate entry 관련 에러(1062) 였는데,

이 에러는 간단하게 하나씩 skip 하도록 하면 된다.

stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave;show slave status;

다만, 이렇게 했는데도 duplicate entry 관련 에러(1062)가 계속 난다면,

my.cnf 에 설정을 추가 해 주는 방법이 있다.

#vi my.cnf

#slave-skip-errors = 1062

slave-skip-errors 옵션에는 에러코드를 콤파(,)로 연결해서 여러개 해줄 수도 있다.

#slave-skip-errors = 1061,1062

이렇게 my.cnf에 추가해주고, mysqld 를 재시작해주면 된다.

#service mysqld restart 

위와 같이 해결해주고, 

slave DB를 master DB와 비교해 보고, 운영하기 어려울 정도로 정합성이 깨져 있다면

복제를 다시 구성해 줘야 한다.

mysql 복제 오류 해결 하기 관련글

https://servedev.tistory.com/82

#slave에서 로그 확인
> show status slave;
+----------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
| Slave_IO_State | Master_Host    | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error                                                                                                                               | Last_SQL_Errno | Last_SQL_Error |
+----------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+
|                | 192.168.10.1 | repluser    |        3306 |            60 | mysql-bin.000028 |           967473320 | mysqld-relay-bin.000075 |     967473465 | mysql-bin.000028      | No               | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            1 |           967473320 |       967473664 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |          1236 | Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position' |              0 |                |
+----------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+

#에러 스킵 시도
> stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave;
--> 복제 오류 지속됨.


#현재 master binlog 포지션 이후 로그 기록 확인
> show binlog events in 'mysql-bin.000028' from 967473320 limit 3\G;
Empty set (0.00 sec)

ERROR:
No query specified
--> 데이타가 없다고 함


#현재 binlog파일의 마지막 로그 추출후 확인
> mysqlbinlog --no-defaults --database=daara_db --start-date="2021-10-18 04:30:00" --stop-date="2021-10-18 04:40:00" /var/lib/mysql/mysql-bin.000028 > binlog1018.sql
--> 최종 로그 포지션 안맞음.(더이상없음)
#새로운 binlog파일의 시작 체크
> mysqlbinlog --no-defaults --database=daara_db --start-date="2021-10-18 04:30:00" --stop-date="2021-10-18 04:40:00" /var/lib/mysql/mysql-bin.000029 > binlog1018.sql
--> 해당 시작 포지션 확인후 slave에서 master position 변경

--Slave
> STOP SLAVE;
> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=4;
> START SLAVE;
> SHOW SLAVE STATUS\G; -- 에러확인
--> 복제 정상 동작

잘 진행되던 복제가 오류 났을때, 전에는 일반적으로 "SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1"를 실행해서 해결하고는 했다. 그런데 이번에는 그렇게 되지 않아 좀 애먹었다. 

위에서 보는거 처럼, 오류난 로그 포지션을 스킵을 해줘도 안되서, 현재 오류나는 곳의 binlog파일과, master position을 확인해서 해당 binlog파일을 열어 최종 로그를 확인해보니, 오류나는 binlog에는 해당 포지션이 존재하지 않았다.

아마도, 서버의 오류로 인해 binlog 기록이 잘못되었던거 같다.

binlog파일들을 확인해서 현재 오류나는 binlog 다음 파일을 확인 해서 position을 변경해주고 시작하니 이상 없이 복제가 잘되었다.

mysql 서버 쌓이는 binlog를 특정 position에서 최근 로그를 읽어보자.

mysql> show binlog events in 'mysql-bin.000011' from 850378398 limit 3\G;

*************************** 1. row ***************************
   Log_name: mysql-bin.000011
        Pos: 850378398
 Event_type: Intvar
  Server_id: 154
End_log_pos: 850378426
       Info: INSERT_ID=6631643
*************************** 2. row ***************************
   Log_name: mysql-bin.000011
        Pos: 850378426
 Event_type: Query
  Server_id: 154
End_log_pos: 850378632
       Info: use `db`; insert LOW_PRIORITY into log (admin_id, admin_log, remote_addr, im_log_seq) values ('best','message:view','61.250.146.69', 727044)
*************************** 3. row ***************************
   Log_name: mysql-bin.000011
        Pos: 850378632
 Event_type: Intvar
  Server_id: 154
End_log_pos: 850378660
       Info: INSERT_ID=679994370
3 rows in set (0.00 sec)

위와 같이 하면 결과가 검색하는 포지션 위치에서 최근 3개의 로그를 읽을 수 있다.

특정 포지션에서 로그를 확인할때 사용할수 있겠다.

* binlog - mysql binary log는 DDL(create, drom, alter)문과, DML(insert, update, delete)를 통해 수행되는 db query 문들의 정보를 저장한다고 보면 된다. 단, show 나 select문은 저장되지 않는다. 

이 binlog는 DB를 복제(replication)하거나, 특정 시점에 있었던 query를 확인하고 복구하는데도 사용이 가능하다.

아래 해당 내용이 잘 소개된 이웃글이 있으니 상세한 부분이 궁금하면 방문해길..

https://myinfrabox.tistory.com/20

mysql 에서 insert 와 update를 한번의 query에서 할 수 있는 방법은

"on duplicate key update" 기능을 이용하면된다.

그런데, insert 구문으로만 등록시 lastInsertId는 잘 가져오는데,

"on duplicate key update"를 붙여주면 lastInsertId를 못가져오는 경우가 발생한다.

아마도 update구문이 같이 있다보니, 그러는거 같은데... 흠..

이를 해결하기 위해서는,

당연히 auto_increment 컬럼(expr)이 있어야 하며,

이를 on duplicate key update 구문에 추가해 주면 된다.

insert into ebook (expr, name,title) values (0, 'test','asfdafd') on duplicate key update name='test',title='asfdafd', expr=last_insert_id(expr);

이렇게 하면 insert 가 실행되고 나서 last_insert_id()를 가져올 수 있다.

mysql query를 작성하다보면

날짜 관련 처리를 할때가 많다.

이럴때 달의 마지막 날짜를 구해야 하는 경우가 있는데...

쉽게 last_day() 함수를 사용하면 간단히 구할수 있다.

last_day()함수는 mysql 4.1.1 버젼 이상에서 가능하니...버젼 잘 확인해보고 사용하면 되겠다.

last_day("날짜")를 이용하면 되는데, "날짜" 부분에 원하는 달의 아무날이나 넣어줘도 된다.

select last_day('2017-06-23');

-> 2017-06-30

ex)

select * from board where regdate between date_format(now(),'%Y-%m-01') and last_day(now())

mysql의 장점중 하나인

on duplicate key update 문을 이용할때 쿼리 실행으로 영향받은 row를 확인하고,

성공여부를 판단하고 이후 프로세스를 만들어 가는 경우가 있다.

이럴때,

insert into 테이블명 () values () on duplicate key update aa='a';

이렇게 하게 되면 affected row는 얼마일까?

이 쿼리 구문은 유니크키가 있는 테이블에서 실행할때 유용한데...

insert를 먼저 실행한후에 유니크키가 중복되는 데이타가 있으면 업데이트를 하게 된다.

따라서, 실행후 영향받은 row수는

중복되는 데이타가 없을때는 insert만 실행되므로 1 이고,

중복되는 데이타가 있을때는 insert실행시 중복 오류발생후, update되므로 2 가 된다.

쿼리 실행 함수를 만들어 쓸때, 이 부분을 잘 체크해서 만들어 가면 좋을것이다.

mysql에서 문자의 일정부분을 잘라야 하는경우가 있다.

이때 간단하게 많이 사용하는 것이 substring 함수 일건데, 

substring은 원하는 부분에서 원하는 글자수 만큼 자르는데 유용하다, 

사용법은 간단해서

substring(자를문자열, 시작위치, 반환할문자수)

이다.

시작위치는 -(음수)로 지정이 가능한데, 이때는 뒤에서 부터 가져오게 된다.

기본 자르기 말고, 특정 구분자를 통해서 글자를 자르고 싶을때가 있는데,.

이때는 

substring_index 함수를 이용하면 아주 쉽게 된다.

이 함수는 구분자를 지정해 앞쪽과 뒤쪽을 구분해서 가져올수 있다.

예를 들어보면 "aaa@bbb.ccc" 이라는 메일에서 도메인 부분을 가져가기 위해서는 

select substring_index("aaa@bbb.ccc", "@", -1);

--> bbb.ccc 

가 결과로 리턴된다.

substring_index(자를문자열,구분자,반환할수)

반환할수는 음수로 하면 뒤에서부터 가져오게 되고, 양수면 앞에서부터 가져오게 된다.

회원이나, 입력된 메일의 유효성을 체크 하는 방법이다.

mysql의 정규식을 이용하면 쉽게 파악할 수 있다.

이메일이 유효하지 않은 회원은 

SELECT * FROM member WHERE email NOT REGEXP '^[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9._-]@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]\\.[a-zA-Z]{2,4}$';

로 추출이 가능하다.

mysql의 REGEXP  기능을 이용하면 된다.

다양한 정규식 패턴을 이용하면 원하는 필터링을 쉽게 할수 있겠다.

http://dev.mysql.com/doc/refman/5.7/en/regexp.html

mysql join을 이용한 데이타 삭제하기

mysql에서는 두개 이상의 table에서 join을 이용해 필터링 되는 데이타를 삭제하기 위해서는 select에서와 같이 join문을 이용해야 하며 여기에서 중요한 점은 using 이라는 문을 더 추가한다는것이다.

예제로 보면, aaa. bbb 라는 테이블을 조인해서  aaa에서 특정 데이타를 삭제하는 구문을 보면

delete from T1 using aaa T1 join bbb T2 on T1.seq = T2.seq where T2.name='홍길동'

이렇게 할경우  T1의 데이타를 삭제하는데 T2와 join 되어 name='홍길동' 인 경우만 삭제하게 된다.
즉, 삭제할 테이블을 FROM 과 USING 사이에 놓으면 된다.