mysql replication error - 복제 오류 복구 하기

#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 서버 쌓이는 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

on duplicate key update 사용시 last_Insert_Id 가져오기

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 달의 마지막일 구하기

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())

on duplicate key update할때 affected row수는?

mysql의 장점중 하나인

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

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


이럴때,

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

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


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

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

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

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

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


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

문자열 자르기 substirng, substring_index

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 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 사이에 놓으면 된다.

locate(),position()함수

Return the position of the first occurrence of substring

mysql 함수 목록.

http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

mysql charset 문제로 한글깨질때. latian1 -> euckr

mysql 서버의 charset 문제로 한글이 깨져 보이는 경우가 있다.
이때, 테이블별로 charset을 변경해줘서 한글이 깨지는 문제를 해결하기 위해 아래와 같이 하면, db전체를 dump받아서 변경하지 않아도 된다. 다만, 변경해야할 table이 많이면 좀 힘들것이다.

가정.
table : university , charset latin1
한글이 깨진 컬럼 : u_name

- 해당 컬럼을 binary 또는  blob 형식을 변경한다. (mysql메뉴얼의 의하면, binary형식으로 별도의 컨버젼이 일어나지 않아서 올바른 형전환을 할수 있다고 한다.... 참고, http://dev.mysql.com/doc/refman/5.1/en/alter-table.html)
- 해당컬럼을 다시 원래의 형식을 변경하고 collate를 원하는 형식으로 지정한다.
- 해당 테이블을 원하는 charset으로 변경한다.
ex)
alter
table university modify u_name blob; alter table university modify u_name varchar(150) collate euckr_korean_ci; alter table university convert to charset euckr;

이렇게 한후 조회를 해보면, 한글이 잘 보일것이다.

PDO 소개 및 예제

PDO에 대해서 소개해주는 곳이다.

http://docs.cena.co.kr/?mid=textyle&document_srl=11910
http://kr.php.net/manual/kr/book.pdo.php
http://www.phpwelt.net/handbuecher/korean/ref.pdo.html

--PDO설치하기.
http://www.barney.pe.kr/blog/1181
http://ezgreg.tistory.com/46

MySQLi VS PDO

원글 : http://wildchry.tistory.com/81

php5버전에 추가된 mysqli와 PDO에 관한 테스트를 찾아보았습니다.
역시 친절하게도 결과를 잘 올려놓은 사이트가 있네요.

대충 보니 mysqli가 select 속도가 더 빠르네요. insert 속도는 prepared statements를 아용할경우 pdo가 더 빠른것을 알수 있으나 왜 그런지는 잘 모르겠네요.^^; 어쨌든 insert 문에서는 Prepared Statements사용하는거를 고려해 보면 좋을듯 합니다.
그리고 다수의 insert를 할때는 MySQL의 extended inserts를 이용하면 훨신 빠르네요. 아무래도 Query문이 하나로 처리가 되니 그렇치 않을까 생각이 듭니다. 흥미로운 부분이네요....

mysqli를 사용하기 위한 php 컴파일 옵션은 2가지가 있는것으로 보이네요.
  --with-mysqli[=FILE]    Include MySQLi support.  FILE is the optional pathname 
                          to mysql_config [mysql_config]
  --enable-embedded-mysqli  MYSQLi: Enable embedded support

참조 :
http://dealnews.com/developers/php-mysql.html
http://brian.moonspot.net/2006/08/25/pdo-turbo-button/

MySQL - mysqli 를 사용할 수 없을때 멀티쿼리 및 MYSQL 내부 변수 선언 활용

원글 : http://www.lovelgw.com/Blog/143  

웹 개발시에  불필요한 내부 질의를 없애기 위해 MySQL 내부 변수를 활용하고 또, mysqli가 지원하지 않는 서버에서 멀티 쿼리를 이용하는 방법이다. 

실제적으로 mysqli_multi_query에서 사용하는 방식은 아니지만 해당 쿼리에 대한 질의를 버퍼링 하지 않고 그대로 다음 행으로 진행을 한다. 

내부 변수 사용은 질의 내용에서 사용되는 값이 계속적으로 질의 하거나 또는 서브쿼리를 지원하기 힘든 MySQL 4.0 이하 서버에서 활용 가능하다. 

내부 변수는 PHP에서의 커넥션 에서만 유효하다. 한 커넥션이 끊기면 그 이후에는 다시 선언을 해주어야 한다. 

내부 변수 활용으로 얻는 잇점은 활용면에서 많다.
01.// MySQL 내부변수를 초기화 시킨다.
02.$strQuery = 'set @nCnt = 0;';
03. 
04.// 질의 결과행을 버퍼링 하지 않는 mysql_unbuffered_query로 질의 한다.
05.mysql_unbuffered_query($strQuery, $rConn);
06. 
07.// 변수에 값을 넣는다.
08.$strQuery = 'select @nCnt:=count(fieldname) from tablename;';
09. 
10.// 질의 결과행을 버퍼링 하지 않는 mysql_unbuffered_query로 질의 한다.
11.mysql_unbuffered_query($strQuery, $rConn);
12. 
13.// 변수에 설정된 값을 이용해 질의 한다.
14.$strQuery= 'SELECT ROUND((count(fieldname) / @nCnt) * 100, 3) AS percent FROM tablename GROUP By fieldtype;';
15. 
16.// 결과를 출력한다.
17.$rRes = mysql_query($strQuery, $rConn);
18.while($arrTest = mysql_fetch_assoc($rRes))
19.{
20.var_dump($arrTest);
21.}
22.mysql_free_result($rRes);
ㅟ와 같이 PHP 에서 내부변수를 활용하는 방식이다. 위 예제는 전체 row수를 구한 후에 해당 타입에 대한 비율을 구하는 방식으로 SubQuery 를 이용하지 않고 내부 변수에 저장된 전체 row수를 이용해 비율을 구한것이다. 

보통 PHP에서 그 결과를 가지고 다시 계산하는 것 보다는 MySQL 에서 결과 값만을 가지고출력하는것이 전체적은 퍼포먼스에 더 좋은 성능을 나타낸다.