들어가며


 여러 이유가 있지만, RDBMS를 사용하며 DB 커넥션을 확인해줘야 할 때가 있습니다. 이번 포스팅에선 여러 RDBMS중에 mysql의 커넥션을 확인하는 방법과 프로세스를 죽이는 방법에 대하여 알아보도록 하겠습니다.


query로 확인하기

 

 서비스를 운영하다 보면 개발 뿐만 아니라 DB에서도 체크해야 할 많은 부분이 있습니다. 우선적으로, DB에서 커넥션을 확인하고 임시적으로 조치를 취하는 방법에 대하여 알아보도록 하겠습니다.


# db 커넥션 최대값

show variables where Variable_name = 'max_connections';

# 해당 DB의 맥시멈 커넥션 값을 알 수 있습니다. 이 이상으로 커넥션은 맺어지지 않습니다.



# 현재 맺어진 커넥션 풀 갯수

show status like 'Threads_connected';

# 현재 DB에 맺어진 커넥션 수 입니다. 최대값보단 적거나 같게 유지됩니다. 

# 만약 이 수가 같거나 비슷하다면 DB의 성능을 스케일링 할 필요가 있습니다.



# 커넥션이 맺어져 있는 프로세스 리스트 (필요 칼럼만)

show full processlist;

# 커넥션의 리스트를 볼 수 있습니다. 아래와 같이 나오며 'TIME'과 'COMMAND' 상태를 유심히 보아야 합니다.

+-------+------+-----------------+--------+---------+------+-----------+----------------------------------------------------------+
| ID    | USER | HOST            | DB     | COMMAND | TIME | STATE     | INFO                                                     |
+-------+------+-----------------+--------+---------+------+-----------+----------------------------------------------------------+
|     5 | ssss | localhost:41060 | somedb | Sleep   |    3 |           | NULL                                                     |
| 58169 | root | localhost       | somedb | Query   |    0 | executing | select * from sometable where tblColumnName = 'someName' |



# 문제가 있는 쿼리에 대해서는 조치를 취해야 합니다

select * from information_schema.processlist where command!='sleep' and time>10;

# 여기서 나오는 쿼리는 실질적으로 10초 이상 걸리는 쿼리입니다. 인덱싱을 확인해보고 튜닝을 하는게 좋습니다.

# 하지만 이러한 쿼리들이 동시다발적으로 프로세스를 선점하고 있어서 문제가 발생시키고 있다면 임시적으로 조치를 취해야 합니다.

kill 해당프로세스ID


우선적으로 서비스 되고 있다면 해당 쿼리가 수행되지 않게 막고 튜닝하는게 좋습니다.



# 커넥션이 맺어져 있는 프로세스 리스트 (자세히)

SELECT * FROM information_schema.processlist;



# ip 별 커넥션 수

SELECT tmp.ipAddress

     , COUNT(*) AS ipAddressCount

     , tmp.db

     , tmp.USER

  FROM (

        SELECT pl.id

             , pl.user

             , pl.host

, pl.db

, pl.command

, pl.time

, pl.state

, pl.info

, LEFT( pl.host, ( LOCATE( ':', pl.host ) - 1 )) AS ipAddress

  FROM INFORMATION_SCHEMA.PROCESSLIST pl ) AS tmp

GROUP BY tmp.ipAddress

ORDER BY ipAddressCount DESC;

# 여러 서버에서 DB에 접근할 경우 커넥션을 적절히 분배해야 합니다. 해당 서버의 커넥션을 확인하고 트래픽 대비 커넥션이 많거나 적게 잡혀 있다면 조절해야 합니다!!

 

임시 조치를 취한것은 다음에도 반드시 재현되는 문제이므로 DB를 향상시키거나 프로그램 로직상의 문제를 개선하는 것이 중요합니다!!


참조

https://mariadb.com/kb/en/show-processlist/

https://mariadb.com/kb/en/kill/

https://mariadb.com/kb/en/thread-command-values/

블로그 이미지

사용자 yhmane

댓글을 달아 주세요

db 조회용 계정을 추가 하였고, 비밀번호를 바꾸기 위해 접속하였는데..

비밀번호가 생각이 나지 않았습니다.


따라서 비밀번호를 수정하려던 중 


update user set password=password('비밀번호') where user='root';


db 조회용 계정의 비밀번호를 바꾸려고 하였지만, password가 없는 column이라고 실패 하였습니다.



update mysql.user SET authentication_string=PASSWORD('비밀번호') WHERE user='root'; 


flush privileges;


적용을 위해 flush privileges; 입력


mysql 5.7 이상 버전부터는 password 칼럼이 아닌 authentication_string로 대체 되었다고 합니다.




[참조]


https://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx

블로그 이미지

사용자 yhmane

댓글을 달아 주세요


개요



일반적으로 root 권한의 계정과 비밀번호를 알려주는 것은 매우 위험한 행위 입니다.

데이터베이스는 아주 중요한 정보이기에, select 권한만 주는 것을 권장하고 있습니다.



따라서, 다른 업체에서 DB 참고를 원할 때에는 

select 권한만 부여한 계정을 생성하고 알려주는 것이 좋습니다.




DB 접속


mysql -u root -p  

 

사용중인 계정 조회


 use mysql;     // mysql db 사용

 select host, user, password from user;     // 사용중인 계정 조회



계정 생성


create user 계정이름@localhost identified by '비밀번호';                  

create user 계정이름@'%' identified by '비밀번호';                           



권한 생성


grant select on db스키마.* to `계정이름`@`localhost` identified by '비밀번호';        

grant select on db스키마.* to `계정이름`@`%` identified by '비밀번호';         


모든 권한 부여

grant all privileges on 스키마.테이블 to '계정'@'localhost' identified by '비밀번호';

grant all privileges on 스키마.테이블 to '계정'@'%' identified by '비밀번호';



여기서 추가적으로 설명을 덧 붙이면

스키마.테이블의 경우 특정 테이블에 대한 권한만 부여 한다.

만약, 스키마.* 를 줄 경우 해당 스키마의 모든 테이블에 대한 권한을 부여 한다



@'host 주소'가 오게 된다.

@'localhost'의 경우 로컬 사용자 이고

@'%' 외부 접근을 모두 허용하는 것이다 ... 

따라서, 특정 대역만 열어 주려면 @'222.10. ......' 뭐 이런식으로 권한을 부여 하면 된다.

ex) @'200.100.%'   @'200.100.50.1'



수정사항 반영

mysql > flush privileges;


위 명령어를 입력하여 메모리에 반영하던가,

db를 내렸다가 올리면 변경된 사항이 반영된다.



db스키마.* 로 select 권한을 주게 되면 모든 테이블에 대한 select 권한을 주게 됩니다.

따라서, db스키마.해당 테이블로 주는 것을 권장 합니다.



블로그 이미지

사용자 yhmane

댓글을 달아 주세요