Programming - cpueblo.com

[웹글] MySQL 관련 팁 정리


글쓴이 : 유광희 날짜 : 2007-09-04 (화) 16:59 조회 : 12228
_ xml_ns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr"> MySQL 관련 팁 정리 - Biocourse <script type= "text/javascript"> var skin = "monobook"; var stylepath = "/skins"; var wgArticlePath = "/index.php/$1"; var wgscriptPath = ""; var wgServer = "http://biocourse.org"; var wgCanonicalNamespace = ""; var wgNamespaceNumber = 0; var wgPageName = "MySQL_관련_팁_정리"; var wgTitle = "MySQL 관련 팁 정리"; var wgArticleId = 2729; var wgIsArticle = true; var wgUserName = null; var wgUserLanguage = "en"; var wgContentLanguage = "en"; <script type="text/javascript" src="/skins/common/wikibits.js?1"> <script type="text/javascript" src="/index.php?title=-&action=raw&gen=js">

MySQL 관련 팁 정리

From Biocourse

Jump to: navigation, search
 

MySQL 관련 팁 정리


root암호변경설정

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root -p password 'new-password'
/usr/bin/mysqladmin -u root -h ns.dbakorea.pe.kr -p password 'new-password'


쉘에서는 mysql이 되는데 PHP에서 mysql.sock error를 내면서 MySQL이 안되는 경우

mysql.sock은 /tmp 아니면 /var/lib/mysql에 생기게 된다. 나의 경우, /var/lib/mysql에 mysql.sock파일이 있는데 PHP에서는 /tmp에서 찾으려하면서 에러를 발생했다.
/usr/bin/safe_mysqld파일에서 다음과 같이 수정한다.

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}


MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

 

주석(#)이 달린 것이 원래것이고 그 밑에 있는것이 수정한 것이다.
위와 같이 하니 /usr/bin/mysql이 /var/lib/mysql/mysql.sock에서 소켓파일을 찾으려 했다.


socket file을 지정하는 --socket이라는 옵션으로 다음과 같이 지정하면 된다.

mysql --socket=/tmp/mysql.sock -u dbakorea -p db_test


하지만 mysql실행시마다 이렇게 써줘야한다는 것이 상당히 귀찮다. 옵션이 바로 적용되게 설정하자.
 

* mysql은 설정사항을 다음 3가지 파일에서 검색한다.
 

/etc/my.cnf global options (MySQL 전체적으로 사용되는 옵션 정의)
mysql-data-dir/my.cnf
특정 DB에 적용되는 option (/var/lib/mysql/my.cnf)
~/.my.cnf
사용자 각각의 설정('~'문자는 사용자의 홈디렉토리는 의미)



*소켓파일의 지정은 다음줄을 넣어주면 된다.
 

socket = /tmp/mysql.sock



* == /etc/my.cnf예 ==

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock



root암호설정


% mysqladmin -u root password '변경암호'
root로 로그인하여 해야함
or
% mysqladmin -u root -p기존암호 password '변경암호'
root로 로그인하여 해야함
 



DB생성

mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )



DB삭제

mysql> drop database DB명



MySQL 연결

mysql -u 사용자 -p DB명 ( or % mysqladmin -u root -p drop DB명 )


데이터파일 실행(sql*loader기능)

mysql>load data local infile "데이터파일" into table 테이블명 ;


데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력



질의 파일 실행

쉘프롬프트상에서


mysql -u
사용자 -p DB명 < </font>질의파일
or
mysql프롬프트상에서

mysql> source 질의파일


쉘프롬프트상에서 질의 실행

dbakorea@lion board]$ mysql mysql -u root -pxxxx -e \\
> "INSERT INTO db VALUES(
> 'localhost', 'aaa', 'aaa',
> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"


사용자 생성 & 사용자에게 DB할당

shell> mysql --user=root -p mysql

mysql> INSERT INTO user VALUES('localhost','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES ('localhost','DB명','사용자','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES('%','DB명','사용자','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)


DB, 테이블목록보기

mysql> show databases;
mysql> show tables;


사용 DB선택

mysql> use DB명



테이블구조

mysql> describe 테이블명



인덱스 보기

show index from 테이블명



테이블명 변경

mysql> rename table test to test2;



접속

mysql {-h 접속호스트} -u 사용자 -p 사용DB


-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.


mysql> INSERT INTO user VALUES('
접근을 허용할 호스트ip','사용자',PASSWORD('비밀번호'),'Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES('
접근을 허용할 호스트ip','사용DB','사용자','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

or

쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges



버전체크

mysql> select version();



검색조건(where)

regular expression을 지원하다니 신기하군..


mysql> select * from work where 열명 regexp "정규표현식";

컬럼의 추가, 삭제(오라클과 동일)


alter table 테이블명 add 컬럼명 데이터타입;
alter table 테이블명 del 컬럼명;



백업 & 복구

mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일
mysql {-h
호스트} -u 사용자 -p DB명 < </font>백업파일

mysqldump -u root -p --opt db_dbakorea > dbakorea.sql
mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database
mysql -u dbakorea -p db_dbakorea < dbakorea.sql <br />

mysqldump -u root -p --opt db_dbakorea | mysql ---host=ns.dbakorea.pe.kr -C db_dbakorea



테이블 검사

isamchk



오라클 sysdate와 동일

insert into test values('12', now());


유닉스 time()함수 리턴값 사용

FROM_UNIXTIME(954788684)
UNIX_TIMESTAMP("2001-04-04 :04:04:04")


MySQL 디폴트 DB&로그파일 위치

/var/lib/mysql


/var/lib디렉토리는 여러 프로세스들이 사용하는 데이터를 저장하는 일종의 파일시스템상의 데이터베이
스라고 볼 수 있다.


replace

해당 레코드 존재하면 update하고, 존재하지 않는다면 insert한다.(insert문법과 동일)

replace into test values('maddog','kang myung gyu')'


explain


explain 질의문: 지정한 질의문이 어떻게 실행될 건지를 보여줌

mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| u | ALL | PRIMARY | NULL | NULL | NULL | 370 | |
| a | ref | sm_addr_uid_idx | sm_addr_uid_idx | 11 | u.uid | 11 | |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
2 rows in set (0.01 sec)


temporary table

크기가 큰 테이블에 있는 subset에 대한 질의라면 subset을 temporary table에 저장한 후
질의하는 것이 더 빠를 경우가 있다.

create temporary table (...);
create temporary table (...) type=heap;
디스크가 아닌 메모리에 테이블 생성


MySQL 특성정리


primary key지원
index 지원(15개컬럼, 256byte까지)
foreign key, check, references기능 지원하지 않음(형식상 있음)

mysql> CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));

* MySQL에서의 Stored script개념 => SQL server language
* commit-rollback개념 => lock tables



자료출처 : http://www.superuser.co.kr/home/lecture/index.php?cateNo=3&secNo=16&theNo=49&leccode=10178
Personal tools
<script type="text/javascript"> if (window.isMSIE55) fixalpha();
KoBIC service
<script type="text/javascript">if (window.runOnloadHook) runOnloadHook();