MySQL 관련(메모)

Prepared Statement는 5.1.21버전 이상에서 지원
Prepared Statement 사용 시 “?” 인자가 사용 시에도 캐싱됨
5.1.17에서는 “?”가 포함된 Prepared Statement 결과는 캐싱되지 않음
5.1.17 이전 버전에서는 모든 결과값이 캐싱되지 않음

MySQL shutdown 시에도 포트를 물고 있고 프로세스에는 없는 경우(유령ㅡㅡ;;)
MySQL SLAVE 리플리케이션 중에 mysqladmin 으로 shutdown 시 프로세서도 죽고 socket 파일, pid 파일도 삭제 되었는데도 포트가 그대로 열려있고(사용하는 프로세서는 공백) 
telnet localhost 3306 으로 접속하면 정상적으로 mysql에 접속되나 mysql 커맨드 라인은 socket 파일이 없어 접속이 안되는 난감한 경우가 발생함
정확한 원인은 파악되지 않았으나 unix 소켓 프로토콜은 정지 되었으니 tcp 프로토콜이 살아 있는 경우였음.
mysqladmin -uroot -p --port=3306 --protocol=TCP 
명령으로 정지시키니 정상적으로 모든 작업이 종료됨 

외래키 제한 설정

SET FOREIGN_KEY_CHECKS = 0; //외래키 제한 해제

SET FOREIGN_KEY_CHECKS = 1; //외래키 제한 설정

PHP로 password 함수 와 동일한 결과값을 얻을때(old_password아님)

strtoupper('*'.sha1(sha1('문자열', true)));

프로시저/function/트리거등을 백업하고 import 할때 주의할 점

dump 시에는 주석이 저장되나 import 할때 --comments 옵션을 사용하지 않으면 주석은 제거되어 import 되므로 주의할 것.

특정한 페이지에서만 myql connection time을 늘려야 할때 아래쿼리를 날려주면 됨

SET INTERACTIVE_TIMEOUT=86400

프로시저(procedure) exit 하기

begin 앞에 라벨명을 붙이고 LEAVE 라벨명; 하면 해당 블럭을 탈출한다.

CREATE
    PROCEDURE 프로시저명()
    라벨명:BEGIN
        IF 조건 THEN
            LEAVE 라벨명;
        END IF;
    END$$

my.cnf 에 클라이언트쪽 설정

[mysql]

no-auto-rehash  ##db변경시 테이블정보를 읽어들이지 않음(자동완성용, 테이블 많을 시 느려짐)
show-warnings  ##warning 발생 시 출력
prompt=\\u [\\d]>\\_ ##프롬프트 변경(참고)

my.cnf 에 언어셋

character_set_server = utf8

선언없을 경우 latin1이 기본이 되서 create database하면 latin1 언어셋의 db가 생성된다.

show variables like '%char%'; 하면 character_set_database 가 latin1으로 나오는게 그런 이유인데.

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;

해서 변경해주던지 utf8로 새로 생성해야 한다. 꼬이면 귀찮아짐

Root 비밀번호 변경
MySQL 5.7.6 이후

ALTER USER `root`@`localhost` IDENTIFIED BY '비밀번호';

MySQL 5.7.5 이전

SET PASSWORD FOR `root`@`localhost` = PASSWORD('비밀번호');

mysql 로그인 후 You must reset your password using ALTER USER statement before executing this statement 같은 메세지가 뜬다면 아래처럼 최초비번을 다시 변경해줘야 함

SET PASSWORD = PASSWORD('비밀번호');

Your password does not satisfy the current policy requirements 와 같이 비번 설정이 안되는 경우 validate_password 플러그인 때문에 비밀번호 형식에 제한이 생긴다.

my.cnf 에 validate_password = off 로 하면 플러그인을 사용하지 않는다.

레벨만 설정할 경우 validate_password_policy = LOW(기본 8자이상), MEDIUM(숫자/소문자/대문자/특문포함)등으로 설정

기본 SQL-MODE 설정

my.cnf에 sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" 추가

STRICT_TRANS_TABLES = not null 등의 조건을 엄격히 검사

NO_ENGINE_SUBSTITUTION = 잘못된 엔진이거나 엔진정의가 없으면 기본엔진 사용