MySQL 관련 TIP 정리

By | 2015년 2월 8일

################################################################################
# phpmyadmin에서 고정으로 패스워드 등록해 놓는 방법
################################################################################
// config.inc.php 파일에 다음과 같이 설정해 놓는다.
/* Authentication type */
$cfg[‘Servers’][$i][‘auth_type’] = ‘config’;
/* Server parameters */
$cfg[‘Servers’][$i][‘host’] = ‘localhost’;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘compress’] = false;
/* Select mysql if your server does not have mysqli */
$cfg[‘Servers’][$i][‘extension’] = ‘mysqli’;
$cfg[‘Servers’][$i][‘AllowNoPassword’] = false;
$cfg[‘Servers’][$i][‘user’] = ‘root’;
$cfg[‘Servers’][$i][‘password’] = ‘1234’;

################################################################################
CSV 형식 파일 넣는 방법
################################################################################
mysqlimport –fields-terminated-by=”|” –local -umysql -p1234 【DBNAME】 [FILENAME】

################################################################################
MYSQL Character Set 보는 방법
################################################################################
show variables like ‘char%’;

################################################################################
바이너리 데이터 넣는 방법
################################################################################
@@ ‘0x’ 넣기
INSERT INTO test
VALUES ( 0x19c0300dc90e7cedf64703ed8ae8683b, 0x19c0300dc90e7cedf64703ed8ae8683b )

################################################################################
UPDATE 구문처럼 INSERT 방법
################################################################################
INSERT INTO MEMBER SET USR_SEQ = ${useq}, USR_ID = ${usr_id}, USR_NM = ${usr_nm},
USR_VNO = ${usr_vno}, PWD = ${passwd}, EMAIL = ${email}, USR_TYPE = ${utype},
REG_DATE = now(), LAST_ACCESS_DATE = now(), MOD_DATE = now() {#1}

################################################################################
ERROR : Illegal mix of collations (euckr_korean_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation ‘like’
################################################################################
@@ 우분투 기준입니다
character-set 설정이 일정하지 않아 발생하는 문제입니다.
리눅스 서버의 경우 my.cnf 파일을 아래와 같이 charset 설정을 수정하면 됩니다.

[client]
default-character-set = euckr

[mysqld]
character-set-client-handshake = FALSE
init_connect = “SET collation_connection = euckr_korean_ci”
init_connect = “SET NAMES euckr”
default-character-set = euckr
character-set-server = euckr
collation-server = euckr_korean_ci

[mysql]
default-character-set = euckr

@@ 우분투에서 설정하기
sudo vi /etc/mysql/conf.d/change_characterset.cnf

[client]
#default-character-set = euckr

[mysqld]
#default-character-set = euckr
character-set-server = euckr
character-set-client-handshake = FALSE
collation-server = euckr_korean_ci
init-connect=’SET NAMES euckr’
init_connect = “SET collation_connection = euckr_korean_ci”

[mysql]
#default-character-set = euckr

################################################################################
phpMyAdmin 쿠키 타임 늘리기
################################################################################
sudo vi /etc/phpmyadmin/config.inc.php
$cfg[‘LoginCookieValidity’] = 60 * 60 * 24;

sudo vi /etc/php5/apache2/php.ini
sudo vi /etc/php5/cli/php.ini
@@ 위의 파일들을 모두 아래처럼 세팅해준다.
session.gc_maxlifetime = 86400

################################################################################
MySQL 전체 다죽이기
################################################################################
sudo kill -9 ps -Af | grep [m]ysql | awk '{print $2}'

################################################################################
우분투에서 서버 캐릭터셋이 UTF-8로 세팅되어 있다면, my.cnf에서도 다음과 같이 세팅해야 한다.
################################################################################
character-set-server = utf8
@@ 자바에서 소스에 하드코딩된 한글이 MySQL에 도착할때에 시스템 인코딩형식이 latin1으로 되어 있으면,
java 코드에 적혀있는 UTF-8 형식의 한글이 깨져서 SQL쿼리로 들어오게 된다. 위 처럼 바꿔놔야 한다.

################################################################################
MySQL 유저 추가
################################################################################
INSERT INTO user VALUES(‘1xx.2xx.1xx.2xx’,’username’,PASSWORD(‘1234′),’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,”,”,”,”,0,0,0,0);
INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES(‘1xx.2xx.1xx.2xx’,’dbname’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다