MySQL 일반적인 오류를 해결하는 유용한 정보들

MySQL 은 세계에서 가장 많이 쓰이는 오픈 소스의 관계형 데이터베이스 관리 시스템임.
다중 스레드, 다중 사용자 형식의 구조질의어 형식의 데이터베이스 관리 시스템으로서 오라클이 관리 및 지원하고 있으며, Qt 처럼 이중 라이선스가 적용됨.

MySQL 서버의 문제 및 일반적인 오류를 해결하는 몇가지 유용한 정보를 설명함.



( 1 ) 로컬 MySQL 서버에 연결할 수 없을 때

< 로컬 MySQL 서버에 연결할 수 없음 >

"ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)" 에러 메시지는 일반적인 클라이언트와 서버 간의 연결 오류 중 하나임.
이 오류는 호스트 시스템에서 실행 중인 MySQL 서버가 없거나 서버에 연결할 때 잘못된 Unix 소켓 파일 이름 및 TCP/IP 포트를 지정할 때 나타남.

먼저 ps 와 grep 명령어를 이용하여 데이터베이스 서버에서 명명된 mysqld 프로세스가 실행 중인지 점검함.
아래 명령어를 입력하여 출력이 표시되지 않으면 데이터베이스 서버에서 MySQL 서비스가 실행되지 않고 있음.

$ ps xa | grep mysqld | grep -v mysqld

따라서 클라이언트는 연결할 수 없으므로 아래 systemctl 명령어를 이용하여 서비스를 실행시킴.

$ sudo systemctl start mysql        #Debian/Ubuntu $ sudo systemctl start mysqld       #RHEL/CentOS/Fedora

MySQL 서비스 상태를 확인하려면 아래와 같이 명령어를 입력하여 확인함.

$ sudo systemctl status mysql       #Debian/Ubuntu $ sudo systemctl status mysqld      #RHEL/CentOS/Fedora

< MySQL 상태 확인 >

참고로 위 명령어대로 입력하였으나 MySQL 서비스 실행이 실패할 수 있으므로 다시 시작하여 상태를 확인함.

$ sudo systemctl restart mysql $ sudo systemctl status mysql

< MySQL 재시작 및 상태 확인 >

그러나 다음 명령어를 입력했는데도 데이터베이스 서버가 실행 중이지만 실패 메시지가 계속 표시될 경우 방화벽이나 보안장비에서 TCP/IP 포트가 차단되었는지 확인해야 함.

$ ps xa | grep mysqld | grep -v mysqld

서버가 수신 대기 중인 포트를 찾을려면 다음과 같이 netstat 명령어를 입력하여 확인함.

$ sudo netstat -tlpn | grep "mysql"

( 2 ) MySQL 서버에 연결할 수 없을 때

“(2003) Can’t connect to MySQL server on ‘server’ (10061)” 에러 메시지는 네트워크 연결이 거부될 때 나타남.

먼저 시스템에서 실행 중인 MySQL 서버가 있는지 확인함.
그리고 서버에 네트워크 연결이 설정되어 있고 연결에 사용 중인 네트워크 포트가 구성된 네트워트 포트인지 확인함.

MySQL 서버에 연결할려고 할 때 발생할 수 있는 다른 일반적인 오류는 아래와 같음.
이 오류는 서버가 실행 중일 수 있음을 나타내지만 서버가 수신 중인 다른 TCP/IP 포트나 소켓 파일을 사용하여 연결할 때 나타남.

ERROR 2003: Can't connect to MySQL server on 'host_name' (111) ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

( 3 ) MySQL 서버에서 액세스 거부 오류

MySQL 사용자 계정 관점에서 사용자가 서버에 연결할 수 있는 클라이언트 호스트 또는 계정에는 암호와 같은 인증 자격 증명이 필요할 수 있음.

액세스 거부 오류의 원인은 다양하지만 일반적인 원인 중 하나는 서버가 클라이언트 프로그램에서 연결할 때 사용할 수 있는 MySQL 계정과 관련됨.
연결에 지정된 사용자 이름에 데이터베이스 액세스 권한이 없음을 나타냄.

사용 중인 클라이언트 프로그램을 통해 서버에 연결할 수 있는지 그리고 연결이 시작되는 호스트를 사용자 계정에서 허용할 수 있는지 확인해야 함.

SHOW GRANTS 명령어을 이용하여 주어진 계정에 어떤 권한이 있는지 확인함.

> SHOW GRANTS FOR 'tecmint'@'localhost';

위와 같은 명령어를 사용하여 데이터베이스의 특정 사용자에게 원격 IP 주소에 대한 권한을 부여할 수 있음.

> grant all privileges on *.test_db to 'tecmint'@'192.168.0.100'; > flush privileges;

이거 외에도 액세스 거부 오류는 MySQL 에 연결하는데 다른 문제로 인해 발생할 수 있으므로 앞에서 설명한 오류를 참조함.



( 4 ) MySQL 서버와의 연결이 끊어짐

네트워크 연결 불량, 연결 시간 초과 또는 max_allowed_packet 보다 큰 BlOB 값의 문제점으로 인해 오류가 발생할 수 있음.

네트워트 연결 문제가 있는 경우 특히 원격 데이터베이스 서버에 액세스하는 경우 네트워크 연결 상태가 양호한지 확인함.

연결 시간 초과 문제인 경우, MySQL 서버에 대한 초기 연결을 사용하려고 할 때 connect_timeout 매개 변수의 값을 늘려야 함.

그리고 BlOB 값이 max_allowed_packet 보다 큰 경우에는 다음과 같이 [mysqld] 또는 [client] 섹션의 /etc/my.cnf 구성 파일에서 mysql_allowed_packet 에 대한 더 높은 값을 설정해야 함.

[mysqld] connect_timeout=100 max_allowed_packet=500M

MySQL 구성 파일에 액세스할 수 없는 경우 MySQL 쉘에서 다음 명령어를 사용하여 값을 설정할 수 있음.

> SET GLOBAL connect_timeout=100; > SET GLOBAL max_allowed_packet=524288000;

( 5 ) 너무 많은 MySQL 연결할 경우

MySQL 클라이언트에서 "너무 많은 연결(too many connections)" 오류가 발생할 경우 사용 가능한 모든 연결이 다른 클라이언트에 의해 사용 중임을 의미함.
연결 수에 대한 설정은 max_connections 시스템 변수에 의해 제어됨. (연결 수 기본 값은 151 임)

/etc/my.cof 구성 파일에 더 많은 연결을 허용하기 위해 값을 증가시켜 문제를 해결할 수 있음.

[mysqld] max_connections=1000

( 6 ) 메모리 부족

MySQL 클라이언트 프로그램을 사용하여 쿼리를 실행하고 메모리 부족 오류가 발생하면 MySQL 에서 전체 쿼리 결과를 저장할 메모리가 충분하지 않다는 의미임.

첫 번째 단계는 쿼리가 올바른지 확인하며 그렇지 않을 경우 다음과 같이 수행함.

- MySQL 클라이언트가 직접 --quick switch 사용하는 경우 캐시된 결과를 사용하지 않도록 설정함.
- MyODBC 드라이버를 사용하는 경우 사용자 인터페이스(UI) 에서 플래그에 대한 고급 탭으로 이동한 후 "결과를 캐시하지 마십시오(Do not cache result)" 옵션을 선택함.

MySQL 서버에 더 높은 성능을 지원하는 MySQL Tuner 프로그램을 사용함.

$ sudo apt-get install mysqltuner     #Debian/Ubuntu $ sudo yum install mysqltuner         #RHEL/CentOS/Fedora $ mysqltuner

( 7 ) MySQL 서버가 계속 충돌함

이 문제가 발생하면 MySQL 서버가 죽었는지 또는 문제가 있는 클라이언트인지 여부를 확인해야 함.
많은 서버 충돌은 손상된 데이터 파일이나 인덱스 파일 때문에 발생함.

서버 상태를 확인하여 실행 및 실행 기간을 설정할 수 있음.

$ sudo systemctl status mysql       #Debian/Ubuntu $ sudo systemctl status mysqld      #RHEL/CentOS/Fedora

또는 다음 mysqladmin 명령어를 실행하여 MySQL 서버의 가동 시간을 찾을 수 있음.

$ sudo mysqladmin version -p

< MySQL 서버 가동 시간 찾기 >

또 다른 방법으로는 MySQL 서버를 중지하고 디버깅을 활성화한 다음 서비스를 다시 시작하는 방법이 있음.
테스트로 반복시켜 원인 파악을 확인하고자 함.

다른 터미널 창을 열고 다음과 같은 명령어를 실행하여 다른 쿼리를 실행하는 동안 MySQL 프로세스 통계를 표시함.

$ sudo mysqladmin -i 5 status OR $ sudo mysqladmin -i 5 -r status

추가적으로 문제 또는 오류를 유발하는 원인 파악

몇 가지 일반적인 MySQL 문제와 오류를 살펴본 결과 문제를 해결하는 방법들을 제공했지만 오류를 진단할 때 가장 중요한 점은 그것이 무엇을 의미하는 지 이해하는 것임.

어떻게 하면 그런 의미들을 이해할 수 있을 지는 다음과 같이 확인하는 방법들이 있음.

- 가장 중요한 첫 번째 단계는 /var/log/mysql 디렉토리에 저장된 MySQL 로그를 조사하는 것임.
tail 명령어를 사용하여 로그 파일을 읽을 수 있음.

- MySQL 서비스가 시작되지 않으면 systemctl 명령어를 사용하여 상태를 확인하거나 journetctl -xe 명령어를 사용하여 문제를 검사함.

- 시스템 로그 파일(/var/log/messages)을 검사함.

- mytop, glances, top, ps, htop 와 같은 프로그램을 사용하여 CPU, 메모리 , 디스크 공간 등 중요한 자원의 부족 여부를 검사함.

- 확실한 MySQL 서버의 재기동을 할 경우 pkill, kill 명령어를 사용함.

- mysqld 서버가 문제를 발생한다고 가정할 경우 mysqladmin -u root ping 또는 mysqladmin -u root processlist 명령어를 사용하여 응답 반응을 확인함.



참고 사이트 : 
https://www.tecmint.com/troubleshoot-common-errors-in-mysql/



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

본 게시물은 KOROMOON 님께서 작성하였으며 CCL (Creative Commons License) 에서 "저작자표시-비영리-동일조건변경허락" 이용조건으로 자료를 이용하셔야 합니다.