세이박스

MYSQL DB에서 램덤으로 값 받기

데이타베이스
SELECT * FROM 테이블명 ORDER BY rand() LIMIT 1
 
위 쿼리는 램덤으로 1건 받아 옵니다

2건 이상 중복되는 회수 구하기 쿼리 예

데이타베이스
SELECT * FROM 테이블명 group by 컬럼명 having count(컬럼명) > 1

MySQL 튜닝 18 가지

데이타베이스

HEAP 테이블이 가장 빠르다

일반적으로 가장 많이 쓰이는 테이블 타입은 MyISAM 타입 입니다.
MyISAM 타입은 무자게 빠르며, 대용량에도 강합니다. 그러나 트랜잭션은 지원되지 않습니다.
이노디비(InnoDB) 는 트랜잭션이 지원 됩니다. 쇼핑몰에서는 반드시 사용해야 합니다 ^^

HEAP 테이블 타입은 가장 빠르며, 단점은 메모리에 있기 때문에, MySQL에 중지 될 경우 모두 날아 갑니다.

검색을 하고 재검색을 다시 하는 경우, 임시 검색 테이블을 만들어 놓는 것도 좋은 방법입니다.
mysql>CREATE TABLE email_addresses TYPE=HEAP (
->email char(55) NOT NULL,
->name char(30) NOT NULL,
->PRIMARY KEY(email) );


int,smallint,tinyint 데이터형
int 는 굉장히 큰수 입니다. 4바이트를 차지 하구요.
tinyint 는 몇백 까지만 됩니다. 1바이트 구요.

쓸데 없이 int 를 사용하지 마세용 !!
4바이트와 1바이트는 4배 차이 입니다.조그만것 1개 1개가 모여 서버 부하를 일으 킵니다.!!

데이터 량이 얼마만큼 들어가는지 체크 하고 데이터형을 선택 하세요 ^^
만약 쓸데없는 데이터 형이다 싶으면 alter table 로 데이터 형을 바꾸세요 !


인덱스의 사용
인덱스는 반드시 필요한 곳에만 넣으세요 !
인덱스를 줄 경우 하드 용량을 더 차지 하기 때문에 속도를 떨어 뜨릴 수 있습니다.

모든 칼럼에 인덱스를 주는 것은 절대 추천 하지 않습니다.
1개의 테이블에 주키외에 2-3 개 이상의 인덱스는 주지 마세요!

주키는 당근 인덱스 입니다. ^^

CREATE TABLE albums (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(80)NOT NULL,

INDEX title_idx (title)
);

☞Alter Table 로 인덱스 추가
ALTER TABLE albums ADD INDEX title_idx (title)

결합 인덱스의 경우 너무 많은 인덱스를 사용할 경우 CPU 오버헤드나 하드 오버헤드를 불러 일으 킵니다.
적당히 사용하세요 ^^


인덱스는 %$search% 가 먹지 않습니다.
그런디 게시판 제목(Subject) 에 인덱스 걸어 놓고 , 검색을 %$search% 이렇게 하면 될까요?
인덱스 거나 안거나 똑같습니다. !!


기타
BLOB과 TEXT 칼럼은 테이블을 분리 하는 것이 좋다. 다른 칼럼의 내용 보다 크기 때문이다 !

OPTIMIZE TABLE 명령을 자주 사용해라 !
Not null 로 지정 하는 것이 빠르다.
varchar 보다 char 이 훨빠르다.

mysql 유용한 팁

데이타베이스
202. myisamchk(isamchk)

MySql에 존재하는 실제 테이블의 데이터가 잘못되었을 경우 복구하는 유틸

테이블 하나당 3가지 파일형식 생성

*.MYD : 테이블 데이터 파일

*.MYI : 테이블 인덱스 파일

*.frm : 테이블 스키마 파일



*.ISM : isamchk로 복구(MySql 3.23이전)

*.MYI : myisamchk로 복구(MySql 3.23이후-대부분)



위치 : /usr/local/mysql/bin/isamchk

/myisamchk

[주의] MySql종료후에 사용

myisamchk점검모드와 복구보드

-점검모드 : 에러발견시 보고, 수정작업은 하지 않음(-c, --check, 無)

-복구모드 : 테이블파일을 직접 수행하여 에러부분 수정(-r, -o)



op> -s : 테이블 점검시 이상 발견시에만 메시지 출력

-v : 상세하게 메시지 출력

-i : 에러사항을 종합적으로 점검하여 결과를 상세하게

-ev : 점검할 때에 가장 정확한 결과를 보여줌(--extend-check)

-rv : 보통의 복구

-o : 시간은 더 소요되지만 -r로 복구하지 못하는 것도 복구 할 수 있다.

-e : 복구대상 데이터파일로부터 가능한 모든 레코드들을 복구, 복구율은 가장 높지만 가징 불안정한 방법

복구시에는 -re 혹은 -oe를 사용

수동 복구

1. 테이블 구조파일(*.frm)으로 인해 복구가 불가능할때

백업파일이 없다면,

-create table문으로 테이블 구조를 다시 만든다.

-만들기 전에 .MYI와 .MYD를 백업

-다시 myisamchk로 복구

2.테이블 인덱스 파일(*.MYI)로 인해 복구가 불가능 할때.

백업파일이 없다면,

-원본 .MYD와 .frm은 백업

-delete from으로 해당 테이블 의 모든 레코드를 삭제.

-백업해둔 .MYD와 frm파일을 원래 위치로 복사

-다시 mysamchk로 복구



203. mysql

MySql DB실행과 실행 확인

-/usr/local/mysql/bin/mysql_safe & : 백그라운드 작업으로 실행.



MySQL DB접속

-mysql -u 계정 -p '(password)' 접속할DB명



에러 메시지 : can't connect to local MySQL server through socket 'tmp/mysql.sock' (2)

-원인: 1.MYSQL이 종료되어서

2. mysql.sock의 위치가 다르기 때문

-해결 : 1. MySQL을 실행 한 뒤 접속

2. -S옵션으로 mysql.sock의 위치를 지정(예 : -S /var/lib/mysql/mysql.sock)



포트지정하여 접속

-기본포트 3306

-포트 지정 -P 2222 : 2222번포트로 접속

cf> 3306대신 다른 포트로 서비스 : /usr/local/mysql/bin/mysql_safe -P3306 &



원격서버 MySQL접속

-h 원격서버IP주소

cf> 다른 서버에서 MYSQL로 접속이 가능하게 하려면...

-db테이블과 user테이블의 host필드값을 '%'나 접속 허용 IP주소를 설정하면 원격서버의 접속을 허용한다.



리눅스 쉘상태에서 특정파일에 저장된 SQL문 실행

-SQL문이 담긴 파일 생성

-./mysql -u root -p mysql < ./SQL문이 담긴 파일명



PATH설정

-심볼릭링크사용 : ln -s /원본파일경로/파일명 만들링크이름

-계정생성시 절대경로를 PATH에 추가 : .bash_profile에서 PATH=$PATH1:/usr/local/mysql/bin

export USERNAME BASH_ENV PATH1 PATH 추가



204. mysqladmin

MYSQL관리유틸

형식> mysqladmin -u root -p 명령어

작업목록 : 명령어는 mysqladmin -u root -p 뒤에 붙는다

MySql의 root 패스워드 변경 : mysqladmin -u root -p password 변경할패스워드

-설치 직후의 root 패스워드 변경 : mysqladmin -u root password 설정할패스워드

일반계정 사용자 패스워드 변경 :mysqladmin -u 계정명 -p password 변경할패스워드

-혹은 set password for 계정명=password('변경할 패스워드) : root로 접속했을 경우

DB생성 : create DB명

DB 삭제 : drop DB명

현재 상황 살피기 : status

-자세히 살피기 : extened-status

접속 client리스트 확인 : processlist

캐쉬 데이터 동기화 : reload

종료 : shutdown

실행 환경변수 확인 : variables

접속한 사용자 접속 끊기 : processlist에서 확인후 kill UID

버젼 및 실행 정보 확인 : version

MYSQL이 죽었는지 살았는지 확인 : ping



205. mysql_safe

MYSQL DB를 실행하는 명령어

형식> mysql_safe [op] &

포트번호 지정 시작

-P포트번호



grant table권한없이 실행

-MYSQL실행시에 table grant권한을 생략하고 실행. 모든 사용자들이 패스워드 없이 바로 접속

- --skip-grant-table &



root패스워드 잊어버렸을시에 복구

1. MYSQL종료

2. --skip-grant-table로 권한없이 실행

3. 패스워드 없이 root로 접속

4. update문으로 root패스워드 변경

-update user set password=password('변경할PW') where user='root';

5. flush privileges로 변경된 내용 동기화

6. MYSQL종료

7. MYSQL실행

8. root로 접속하여 확인



여러 옵션으로 MYSQL실행 : -O로 연결

max_connection= (최대 연결 가능한 프로세스 수)

table_cache= (사용할 테이블캐쉬크기)

wait_timeout= (타임아웃 시킬 최대 연결 대기 지속 시간)



too many connection에러 발생시

max_connection= (최대 연결 가능한 프로세스 수)

table_cache= (사용할 테이블캐쉬크기)

wait_timeout= (타임아웃 시킬 최대 연결 대기 지속 시간)

이 세가지 값을 적절한 값을 넣고 MYSQL시작



보안을 위한 MYSQL관리 전용계정으로 시작

-관리 전용 계정을 생성한 뒤에 chown으로 /usr/local/mysql/의 모든 파일의 owner를 mysql로 변경

-chown -R mysql:mysql /usr/local/mysql/

-여기서 계정은 리눅스 시스템 계정



에러로그파일 관리

/usr/local/mysql/data/호스트명.err로 저장



쿼리로그 남기기

-쿼리로그 사용시에는 한순간에 disk full이 될 수 있음

-op> --log=쿼리로그파일명(혹은 경로명/파일명) &

- /usr/local/mysql/data/지정된파일명



바이너리로그파일 관리

-변경된 데이터의 내역을 기록

-속도와 보안을 위해 텍스트가 아닌 바이너리로 저장

-op> --log-bin=바이너리로그파일명(혹은 경로명/파일명) &

- /usr/local/mysql/data/지정된파일명



SLOW로그 파일 관리

-long_query_time의 설정시간 이상의 쿼리 시간을 가지는 특정 SQL쿼리문에 대한 로그를 기록

-op> --log-slow-queries=슬로우쿼리파일명(혹은 경로명/파일명) &



206. mysqldump

MYSQL백업방법

1. /usr/local/mysql/전체를 매일 백업

2. mysqldump로 MYSQL스키마와 데이터만을 백업



형식

mysqldump [op] DB[TABLES.....] > 파일명

--database [op] DB1 [DB2 DB3...] > 파일명

--all-databases [op] >파일명



백업형식 : mysqldump -u 계정 -p 백업대상DB > 저장할 파일명

복구할DB < 저장한 파일명



기존테이블 삭제후 백업된 파일로 복구하기위한 백업방법

-백업된 파일로 복구를 할 때 기존의 테이블을 완전히 삭제하고 테이블을 다시 만들어서 데이터를 입력하는 방법으로 복구하기 위해

-복구할때 백업후와 복구시점 사이의 데이터는 사라지므로 주의

-op> --add-drop-table 백업할 DB



백업시에 에러무시 : -f

원격호스트의 DB백업 : -h 호스트IP -P 포트

CREATE DATABASE문을 생략하고 백업(--databases, --all-databases로 백업할때) : -n , --no-create-db

CREATE TABLE문 생략하고 백업 : -t, --no-create-info

테이블 스키마만 백업 : -d, --no-data

조건주기 : --where="조건문"



207. mysqlimport

특정텍스트 파일의 전체내용을 특정 DB의 특정 테이블로 입력

형식> mysqlinport -u root -p DB명 입력받을텍스트파일명



208. mysqlshow

특정DB의 스키마를 확인

mysqlshow [op] [DB[table[column]]]

-DB명이 없을 경우 존재하는 모든 데이터베이스를 나열

-테이블 테이블을

-컬럼 컬럼을

mysql 동시접속자 수

데이타베이스
인터넷 사용자들이 많아지면서 인기있는 웹 사이트에서는

Max connections 에러가 발생되는 것을 볼 수있을겁니다.

우선, MySQL은 동시에 연결될 수 있는 클라이언트의 수가

100입니다.

이런 에러가 발생한다면 먼저 접속되어있는 클라이언트의 수를
확인하셔야겠죠. 확인방법은 아래와 같습니다.
$ mysqladmin -u root -p패스워드 variables | grep max_connection

| max_connections | 100

이제는, 클라이언트의 동시 접속자를 늘리는 명령입니다.
먼저 mysqld - 이 데몬을 kill 하셔야 겠죠...
$ safe_mysqld -O max_connections=200 &
참고로, 리눅스나 솔라리스 계열에서는 클라이언트의 동시 접속자수가 500 ~ 1,000 까지 가능하다고 합니다.

이렇게 하신 후 다시 확인합니다.
$ mysqladmin -u root -p패스워드 variables | grep max_connection

그럼 제한되어 있지만 원하시는 만큼의 동시접속자 수를

늘리시게 된겁니다.

./configure 할때 다음과 같이 에러가 생길땐

데이타베이스
에러 메세지~!!

checking whether build environment is sane... configure: error: newly created file is older than distributed files!




요건~!!! mysql 개발일이랑 지금 시간이랑 논리적인 오류가 있을때 생겨남

해결은... 서버 타임을 확인해보자 ㅋㅋ

/usr/bin/rdate -s time.bora.net

checking "LinuxThreads"... "Not found"

데이타베이스
설치 시에...

checking "LinuxThreads"... "Not found"
configure: error: This is a linux system and Linuxthreads was not
found. On linux Linuxthreads should be used. Please install Linuxthreads
(or a new glibc) and try again. See the Installation chapter in the
Reference Manual for more information.

가 나타나면....


/usr/include/pthread.h 파일에서

암때나

/* Linuxthreads */

를 입력

이유 :
mysql configure 과정에서
pthread.h 파일을 찾아 위 문구가 있는지 조사하는데 Fedora Core 4의 glibc는 없으니까 에러남

MYSQL 랜덤 추출

데이타베이스
ORDER BY rand() desc

MS-sql에서는 ORDER BY NEWID()

Host '...........' is blocked Error

데이타베이스
Host '...........' is blocked Error


[ 오류 ]

Host 'hostname' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'

[ 원인 ]

해당오류는 호스트로부터의 접속요청중 max_connect_errors와 관련된 접속의 설정값보다 초과하여 발생할 때 생기는
오류이다. 설정값 이상의 요청이 들어올 경우 mysqld는 해킹 및 잘못된 요청으로 관주하여 host 부터의 접속을 블락하
게된다.

[ 해결 ]

1.flush-hosts 명령어를 사용하여 초기화한다.

[root@soma]# /usr/local/mysql/bin/mysqladmin -u root -p flush-hosts

2.mysql 데몬시작시 max_connect_errors 값을 올려준다.

[root@soma mysql]# ./bin/safe_mysqld --user=mysql -O max_connect_errors=10000 &

Can't open file: '테이블이름.MYI'

데이타베이스
테이블에 이상이 생기면 나타난 대표적인 증상은 테이블을 확인했을때(desc 테이블이름) 나오는 에러메시지의 내용은 다음과 같다.

ERROR 1016: Can't open file: '테이블이름.MYI'. (errno: 145)



이런 메시지를 처음 접했을때 취할 행동은 repair table 을 실행할 수 있는 계정으로 접속하여 복구해본다.

mysql> repair table 테이블이름



이렇게 복구가 된다면 좋겠지만. 이래도 복구가 안된다면. myisamchk 를 이용한다.

myisamchk 는 테이블의 정의와 데이터, 인덱스를 저장하는 .frm, .MYI, .MYD 파일 자체를 검사하는 프로그램이다. 따라서 MySQL 서버가 테이블에 쓰기(UPDATE, INSERT)를 하는 것은 이러한 파일에 쓰기를 하는 것을 의미하므로 MySQL 서버가 실행중일때 myisamchk 를 실행하는 것은 바람직하지 않다.

myisqmchk를 사용할때는 MySQL 서버를 종료하거나 LOCK TABLE 을 이용하여 테이블을 잠그는 것이 좋다.



★ 테이블 확인

mysql> desc returnmail40; (returnmail40 은 테이블 이름)

ERROR 1016: Can't open file : \returnmail40.MYD\. (errno: 145)



★ 에러코드 확인

\mysql\bin\perror 145

Error code 145: Unknown error

145 = Table was marked as crashed and should be repaired



★ 기본 오류 검사

\mysql\data\ReturnMail\myisamchk --extend-check returnmail40

myisamchk: error: File \returnmail40;\doesn't exist



★ 세부 오류 검사

\mysql\data\ReturnMail\myisamchk --extend-check returnmail40

myisamchk: error: File \returnmail40;\ doesn't exist



★ 인덱스 복구 시도

\mysql\bin\myisamchk --recover --quick returnmail40

myisamchk: error: File \returnmail40;\doesn't exist



★ 데이터포함 복구 시도

\mysql\bin\myisamchk --recover returnmail40

myisamchk: error: File \returnmail40;\ doesn't exist



★ 완전 복구 시도

\mysql\bin\myisamchk --safe-recover returnmail40

myisamchk: error: File \returnmail40;\ doesn't exist

MYSQL 필드속에 일괄적으로 내용 집어넣기

데이타베이스
update member set code = concat(reg_date,"00")

mysql 자주 사용하는 쿼리 정리

데이타베이스
# root암호설정 - root로 로그인하여 해야함
% mysqladmin -u root password '변경암호'
% mysqladmin -u root -p기존암호 password '변경암호'

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'

DB작업
DB생성: mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )
DB삭제: mysql> drop database DB명
DB사용: mysql> use DB명 (엄밀히 말하자면, 사용할 'default database'를 선택하는 것이다.)
DB변경: mysql> alter database db명 DEFAULT CHARACTER SET charset (4.1이상에서만 available)

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

데이터파일 실행(sql*loader기능)
mysql>load data infile "데이터파일" into table 테이블명 ;
데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력
데이터파일의 위치는 /home/kang/load.txt 와 같이 절대경로로 지정할것.

질의 파일 실행
쉘프롬프트상에서
mysql -u 사용자 -p DB명 < 질의파일
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)

CASE 2: GRANT명령을 이용한 사용자 생성(이 방법이 권장된다)
kang이라는 DB를 만들고, 이 DB를 아래에서 나열된 권한을 가진 kang이라는 사용자를 생성
create database kang;
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@localhost identified by 'kang';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@'%' identified by 'kang';

mysql> create database kang;
Query OK, 1 row affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@localhost identified by 'kang';
Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@'%' identified by 'kang';
Query OK, 0 rows affected (0.01 sec)

mysql>

여러가지 명령정리
mysql> show variables; 서버의 variables(설정사항)출력
mysql> show variables like 'have_inno%' 조건에 맞는 variables만 출력
mysql> show databases; database목록
mysql> show tables; 현재DB의 테이블목록(temporary table은 출력하지 않음)
mysql> show tables from db명; 지정된 db명이 소유한 테이블목록
mysql> show tables like 'mem%'; 조건에 맞는 테이블목록만 출력
mysql> show index from 테이블명; 인덱스 보기
mysql> show columns from 테이블명; 테이블구조(describe 테이블명, explain 테이블명)
mysql> show table status; 현재 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show table status from db명; 지정된 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show create table 테이블명; 해당 테이블 생성 SQL문 출력
mysql> rename table 테이블1 to 테이블2; 테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)
mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4; rename multiple tables
mysql> rename table db1명.테이블명 to db2명.테이블명; 테이블을 다른 DB로 이동
mysql> alter table 테이블명 add 컬럼명 데이터타입; 컬럼추가
mysql> alter table 테이블명 del 컬럼명; 컬럼제거
mysql> alter table 테이블명 modify 컬럼명 컬럼타입; 컬럼명에 지정된 컬럼타입의 변경
mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입 컬럼명 변경
mysql> alter table 테이블명 type=innodb; 테이블type변경
mysql> create table 테이블명(..) type=heap min_rows=10000; 10000row를 수용할 수 있을 만큼 메모리할당(heap type이므로)
mysql> select version(); MySQL서버버전 출력
mysql> create table 테이블2 as select * from 테이블1; 테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)
mysql> create table 테이블2 as select * from 테이블1 where 1=2; 테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)
mysql> insert into 테이블2 select * from 테이블1; 테이블1의 데이터를 테이블2에 insert


테이블이 존재여부 파악
DROP TABLE IF EXISTS 테이블명;
CREATE TABLE 테이블명 (...);
프로그래밍 언어에서 COUNT(*)를 사용하여 질의가 성공하면 테이블이 존재함을 파악할 수 있다.
ISAM, MyISAM의 경우 COUNT(*)가 최적화되어 상관없으나, BDB, InnoDB의 경우 full scan이 발생하므로 사용하지 마라.
대신 select * from 테이블명 where 0; 을 사용하라. 질의가 성공하면 테이블이 존재하는 것이고, 아니면 존재하지 않는 것이다.



접속
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


검색조건(where)
regular expression을 지원하다니 신기하군..
mysql> select * from work where 열명 regexp "정규표현식";


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

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

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

테이블 생성구문만을 화면에서 보려면 다음과 같이 --no-data를 사용한다. 테이블명을 생략하면 모든 테이블 출력
mysqldump -u 유저명 -p --no-data db명 테이블명

테이블 검사
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에 저장한 후 질의하는 것이 더 빠를 경우가 있다.
temporary table는 세션내에서만 유효하고(현재 사용자만이 볼수 있다는 뜻), 세션종료시 자동적으로 drop된다.

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

존재하는 permanent table의 테이블명과 동일하게 생성할 수 있으며,
temporary table은 permanent table보다 우선시되어 처리된다.
4.0.7의 감마버전에서 테스트하면 결과는 약간 달라진다. 버그인건지..

mysql> create table test (id varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values('dbakorea');
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table test(id varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----------+
| id |
+----------+
| dbakorea |
+----------+
1 row in set (0.00 sec)



Table Type에 다른 Files on Disk

ISAM .frm (definition) .ISD (data) .ISM (indexes)
MyISAM .frm (definition) .MYD (data) .MYI (indexes)
MERGE .frm (definition) .MRG (list of constituent MyISAM table names)
HEAP .frm (definition)
BDB .frm (definition) .db (data and indexes)
InnoDB .frm (definition)

보통 mysqldump를 사용하여 백업을 수행하여 다른 DB서버에 데이터를 restore하면 된다.
MySQL은 별다른 작업없이 데이터파일을 단순히 복사(copy)하는 것만으로도 다른 서버에
DB을 이동시킬 수 있다. 하지만, 이런 방식이 지원되지 않는 table type도 있다.

ISAM: machine-dependent format하기때문에..
BDB : .db파일에 이미 테이블위치가 encode되어 있기때문에..
MyISAM, InnoDB, MERGE :가능(machine-independent format)

별다른 지정을 하지 않았다면 디폴트 TABLE type이 MyISAM이므로, 무난히 migration할 수 있다.
floating-point컬럼(FLOAT,DOUBLE)이 있다면 이러한 방식이 실패할 수 도 있다.

쉘에서는 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 사용자 각각의 설정('~'문자는 사용자의 홈디렉토리는 의미)

/usr/share/mysql디렉토리에 예제가 있으므로 참고한다.
소켓파일의 지정은 다음줄을 넣어주면 된다.

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

# Here follows entries for some specific programs

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



MySQL에서 통계처리시
orderby, groupby 는 sort_buffer를 늘여준다.(show variables)

live table(smslog)에서 모든 질의를 처리하지 말고 summary table에 질의결과를 저장해 재질의 처리한다.
summary table이 heap-type table가 가능한지 확인할 것.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;


join이 subselect보다 빠르다.
join시 사용되는 컬럼은 동일한 column type과 길이를 가져야만 최적의 속도를 보장한다.
즉, 동일 column type이지만 길이가 다르다면(char(11), char(10)), 동일한 컬럼도메인으로 변경해주는 것이 좋다.
where의 in은 optimize되어 있으므로 빠르다
insert,select는 동시에 수행가능하다.(어떻게?)
explain으로 질의과정 점검


varchar to/from char
conversion varchar를 char로 변경할 경우 모든 컬럼타입을 동시에 변경해야 한다.
반대의 경우, 하나만 char->charchar변경시 다른 모든 컬럼도 varchar로 변경됨
참.. 특이하구만..

mysql> CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(80));
Query OK, 0 rows affected (0.05 sec)

mysql> desc chartbl;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(40) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> alter table chartbl modify name char(40);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(40) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table chartbl modify name char(40), modify address char(80);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(40) | YES | | NULL | |
| address | char(80) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>


"For each article, find the dealer(s) with the most expensive price."

표준안
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);

수정안(최적화)
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);

LOCK TABLES shop read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;



==============================================================================
MySQL 특성정리
==============================================================================
primary key, foreign key지원
index 지원(15개컬럼, 256byte까지)
MySQL에서의 Stored Script-x개념 => SQL server language
commit-rollback개념 => lock tables(lock table test write -> 트랜잭션.. -> unlock tables)
컬럼명길이: 64자까지, 컬럼 Alias: 256자까지
not case-sensitive: keywords, functions, column, index명
case-sensitive: database, table, alias명
키워드,함수명은 대소문자구별이 없지만, db명과 table명은 Unix계열이라면 case-sensitive하다.
(이는 오브젝트명이 OS의 fs에 따라 저장되기 때문이다. 서버의 lower_case_table_names 변수를
1로 설정하면 오브젝트명은 모두 소문자로 저장되므로 유닉스-윈도간 호환성을 높일 수 있다.

지원되지 않는 부분:
Stored Procedure(5.0이상부터 지원된다고 함)
View(5.0이상부터 지원된다고 함)
Trigger(5.0이상부터 지원된다고 함)
subquery(4.1이상부터 지원된다고 함)
union, union all(4.0이상부터 지원됨)

[테이블 type에 따른 인덱스 특성]
Index Characteristic ISAM MyISAM HEAP BDB InnoDB
NULL values allowed No Yes As of 4.0.2 Yes Yes
Columns per index 16 16 16 16 16
Indexes per table 16 32 32 31 32
Maximum index row size (bytes) 256 500 500 500/1024 500/1024
Index column prefixes allowed Yes Yes Yes Yes No
BLOB/TEXT indexes allowed No Yes(255 bytes max) No Yes (255 bytes max) No


인덱스 생성
- alter table을 이용한 인덱스 생성이 더 flexible함
- 인덱스명은 생략가능

ALTER TABLE 테이블명 ADD INDEX 인덱스명 (인덱스컬럼);
ALTER TABLE 테이블명 ADD UNIQUE 인덱스명 (인덱스컬럼);
ALTER TABLE 테이블명 ADD PRIMARY KEY (인덱스컬럼);
ALTER TABLE 테이블명 ADD FULLTEXT (인덱스컬럼);

CREATE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);
CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);
CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (인덱스컬럼);

unique인덱스와 primary key인덱스와의 차이
unique은 null허용하지만, primary key는 null허용 안함
unique은 하나의 테이블에 여러개 올 수 있지만, primary key는 하나만 존재

테이블생성시 지정
CREATE TABLE 테이블명
(
... column declarations ...
INDEX 인덱스명 (인덱스컬럼),
UNIQUE 인덱스명 (인덱스컬럼),
PRIMARY KEY (인덱스컬럼),
FULLTEXT 인덱스명 (인덱스컬럼),
...

);


index prefix 생성
- 컬럼의 전체길이중 일부만 인덱스로 사용
- supported for ISAM, MyISAM, HEAP, and BDB tables, but not for InnoDB tables
- 지정되는 길이는 byte단위가 아닌 charater단위이므로, multi-byte character일 경우 주의
- blob, text 컬럼타입일 경우, index prefix 가 유용(255 길이까지 가능)

CREATE TABLE 테이블명
(
name CHAR(30) NOT NULL,
address CHAR(60) NOT NULL,
INDEX (name(10),address(10))
);


인덱스 삭제
DROP INDEX 인덱스명 ON 테이블명;
ALTER TABLE 테이블명 DROP INDEX 인덱스명;
ALTER TABLE 테이블명 DROP PRIMARY KEY;


outer join

[MySQL]
left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;
right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;

[Oracle]
left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);
right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2;

SELECT
student.name, student.student_id,
event.date, event.event_id, event.type
FROM
student, event
LEFT JOIN score ON student.student_id = score.student_id
AND event.event_id = score.event_id
WHERE
score.score IS NULL
ORDER BY
student.student_id, event.event_id;


:= 문장을 이용한 변수의 설정

현재 dbakorea의 데이터베이스강좌게시판에 등록된 총 게시물은 43개이다. 43개의 강좌를 읽은 수(hit수)는 각각 다르다.
평균 hit수를 구해 보자.

mysql> select @total_hit := sum(hit), @total_record := count(*) from zetyx_board_database;
+------------------------+---------------------------+
| @total_hit := sum(hit) | @total_record := count(*) |
+------------------------+---------------------------+
| 3705 | 43 |
+------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> select @total_hit/@total_record as 평균HIT;
+-----------------+
| 평균HIT |
+-----------------+
| 86.162790697674 |
+-----------------+
1 row in set (0.00 sec)



select substring(subject from 9) from zetyx_board_database where substring(subject, 1, 8) = '[ORACLE]';


보통 상용DBMS들이 row-level locking을 지원한다. 쉽게 말해 레코드단위로 락킹한다는 말이다.
반면, MySQL의 MyISAM 테이블타입은 table-level locking을 사용한다.
쉽게 말하면, insert, update, delete작업은 전체 테이블에 락을 걸고 처리된다는 것이다.
row-level락보다 비효율적이지만,.. MySQL은 빠르기 때문에 이 단점이 상쇄된다.

Compressed MyISAM(packed MyISAM)
정적인 테이블데이터는 압축하여 20-60%정도의 공간을 절약할 수 있다.
Production데이터를 CD로 받아서 차후 디스크에 풀지 않고 CD자체로 바로 사용할 수도 있다.
gzip등으로 백업받으면 이를 푸는 과정이 필요할 것이다.
% myisampack dbakorea.myi

데이터베이스 게시판의 Merge Table에 좀 더 자세한 내용을 적어 두었다.


RAID Table
1개의 테이블은 OS상에 3개의 파일로 구성된다.
스키마파일(.frm), data파일(.myd), index파일(.myi)
MySQL의 RAID테이블은 데이터파일(.myd)을 여러개의 파일들로 구성하는 것이다.

create table raid_test (...)
type=myisam raid_type=striped raid_chunks=4 raid_chunsize=8

테이블을 4개의 데이터파일로 나누고, 8kb단위로(8kb stripe) 라운드로빈 방식으로 write가 이루어진다.

This article comes from dbakorea.pe.kr (Leave this line as is)

mysql 세션들 끊어주기

데이타베이스

/usr/local/mysql/bin/mysqladmin flush-hosts

무거운 쿼리를 날린 후에나 접속량 많은 디비를 관리 시에 ..

/usr/local/mysql/bin/mysqladmin flush-hosts

mysql> check table 'test';

MySQL 동시 접속자 수 늘리기

데이타베이스
리눅스 Q&A자료실에 올라왔던 내용이기도 합니다.

인터넷 사용자들이 많아지면서 인기있는 웹 사이트에서는

Max connections 에러가 발생되는 것을 볼 수있을겁니다.

우선, MySQL은 동시에 연결될 수 있는 클라이언트의 수가

100입니다.

이런 에러가 발생한다면 먼저 접속되어있는 클라이언트의 수를
확인하셔야겠죠. 확인방법은 아래와 같습니다.
$ mysqladmin -u root -p variables | grep max_connection

| max_connections | 100

이제는, 클라이언트의 동시 접속자를 늘리는 명령입니다.
먼저 mysqld - 이 데몬을 kill 하셔야 겠죠...
$ safe_mysqld --set-variable max-connections=200 &
참고로, 리눅스나 솔라리스 계열에서는 클라이언트의 동시 접속자수가 500 ~ 1,000 까지 가능하다고 합니다.

이렇게 하신 후 다시 확인합니다.
$ mysqladmin -u root -p variables | grep max_connection

그럼 제한되어 있지만 원하시는 만큼의 동시접속자 수를

늘리시게 된겁니다.

MYSQL 데이터 자료형

데이타베이스

TINYINT

·부호 있는 정수 -128 ∼ 127
·부호 없는 정수 0 ∼ 255
·1 Byte

SMALLINT

·부호 있는 정수 -32768 ∼ 32767
·부호 없는 정수 0 ∼ 65535
·2 Byte

MEDIUMINT

·부호 있는 정수 -8388608 ∼ 8388607
·부호 없는 정수 0 ∼ 16777215
·3 Byte

INT 또는
INTEGER

·부호 있는 정수 -2147483648 ∼ 2147483647
·부호 없는 정수 0 ∼ 4294967295
·4 Byte

BIGINT

·부호 있는 정수 -9223372036854775808 ∼ -9223372036854775807
·부호 없는 정수 0 ∼ 18446744073709551615
·8 Byte

FLOAT

·단일 정밀도를 가진 부동 소수점
·-3.402823466E+38 ∼ 3.402823466E+38

DOUBLE

·2 배 정밀도를 가진 부동 소수점
·-1.79769313486231517E+308 ∼ 1.79769313486231517E+308

DATE

·날짜를 표현하는 유형
·1000-01-01 ∼ 9999-12-31

DATETIME

·날짜와 시간을 표현하는 유형
·1000-01-01 00:00:00 ∼ 9999-12-31 23:59:59

TIMESTAMP

·1970-01-01 00:00:00부터 2037년 까지 표현
·4 Byte

TIME

·시간을 표현하는 유형
·-839:59:59 ∼ 833:59:59

YEAR

·년도를 표현하는 유형
·1901년 ∼ 2155년

CHAR(M)

·고정길이 문자열을 표현하는 유형
·M = 1 ∼ 255

VARCHAR(M)

·가변길이 문자열을 표현하는 유형
·M = 1 ∼ 255

TINYBLOB
TINYTRXT

·255개의 문자를 저장
·BLOB : Binary Large Object의 약자

BLOB
TEXT

·63535개의 문자를 저장

MEDIUMBLOB
MEDIUMTEXT

·16777215개의 문자를 저장

LONGBLOB
LONGTEXT

·4294967295(4Giga)개의 문자를 저장

MySQL의 root패스워드를 잊어버렸을 때 복구하기

데이타베이스

mysqld_safe 으로 MySQL을 시작할 때에 --skip-grant-table 옵션을 사용하요 MySQL의 사용자인증과정을 생략하여 실행한 후에 MySQL의 root계정으로 패스워드 없이 접속하여 원하는 패스워드로 변경하는 방법이다. 즉, MySQL의 root 계정 패스워드를 잊어버렸을 경우의 복구절차는 아래와 같다.

1. 실행중인 MySQL을 종료한다.
 # killall mysqld

2. "./mysqld_safe --skip-grant-table &" 로 사용자 인증과정 없이 MySQL를 실행한다.
 # ./mysqld_safe --skip-grant-table &

3. 패스워드 없이 MySQL root 계정으로 MySQL에 접속한다.
 # ./mysql -u root mysql

4. update문으로 MySQL 데이터베이스의 user 테이블에 있는 MySQL root사용자의 패스워드를 변경한다.
 mysql> update user set password=password('1111') where user = 'root';

5. "flush privileges" 명령어로 변경된 MySQL 권한테이블(db, user 테이블들)들에 변경된 권한설정들을 적용한다.
 mysql> flush privileges;

6. MySQL을 빠져나와서 실행했던 MySQL을 종료한다.
 mysql> exit
 # killall mysqld

7. 일반적인 방법으로 MySQL을 실행한다.
 
# ./mysqld_safe &

8. 정상적인 방법으로 패스워드를 사용하여 MySQL의 root계정으로 접속하여 확인한다.
 
# ./mysql -u root -p mysql

MYSQL 쿼리에 if 문 사용하기 예제

데이타베이스
Select
ADate,
Sum(Amnt) As Total,
Sum(If(Pay = 1, Amnt, 0)) As Pay1,
Sum(If(Pay = 2, Amnt, 0)) As Pay2
From ATable
Group By ADate;

트랜잭션 개념과 mysql에서의 적용

데이타베이스
출처: 네이버, phpschool

은행 현금인출기(ATM)에서 돈을 인출하는 과정을 생각해 봅시다.

현금인출을 하겠다고 기계에게 알려준뒤 현금카드를 넣어서 본인임을 인증받고,
인출할 금액을 선택하면 ATM 기는 돈을 내어줍니다.

이러한 거래에 있어서 지켜져야 할 중요한 것이 있습니다.
기계의 오동작 등으로 인하여 전산상으로는 돈을 인출한 것으로 입력이 되었는데 돈은 안나온다거나,
돈은 나왔는데 일련의 에러나 문제로 인하여서 돈을 인출한 것이 전산상으로 입력이 안되면
상당히 심각한 문제가 발생하겠죠.

때문에 전산상으로도 입력이 정상적으로 잘 되고, 돈도 인출이 정상적으로 잘 됨을 확인하고 나서야,
인출하는 하나의 과정이 정상적으로 처리되었음을 확인할 수 있습니다.
여기서 돈을 인출하는 일련의 과정이 하나의 묶음으로 처리되어야 함을 이해하실 수 있을것입니다.
그리고 혹시 처리도중 중간에 무슨 문제가 발생한다면 진행되던 인출과정 전체를 취소하고
다시 처음부터 시작하도록 해야 될 것입니다.

이것을 트랜잭션이라고 합니다.
transaction 의 사전적의미와도 상통합니다만, 거래함에 있어서 거래하는 양측이 다 원하는
결과물을 얻어야만 정상적으로 처리되는 것이고 그렇지 않다라면 거래자체가 무산되어 원점으로
되돌려져(roll back) 버리는 것이죠.
전산적으로나 데이터베이스에 있어서도 이처럼 하나의 묶음으로 처리가 이루어져야 하는 모든것에
트랜젝션이란 개념이 사용됩니다.

롤백(roll back) 이란 것은 트랜잭션으로 인한 하나의 묶음처리가 시작되기 이전의 상태로
되돌려지는 것을 말합니다.

커밋(commit) 이란 것은 정상적으로 처리되어서 확정(실행을 위해서 엔터키를 치는 것처럼) 한다는
것이구요. 커밋하면 트랜잭션의 처리과정이 모두 반영되며 하나의 트랜잭션 과정이 끝나게 됩니다.

트랜잭션, 롤백... 이러한 단어들은 모든 처리가 정상적으로만 이루어진다고 한다면 필요없는
것이겠죠. 그러나 항상 예외나 에러는 발생하는 것이며, 이러한 것이 발생할 때에 적절한 처리를
위해서 존재하는 것입니다.

-----------------------------------------------------------------------------------

트랙젝션의 전체 프로시져를 살펴보면,

Begin Transaction - (작업) - if(작업완료) -> Commit
else(작업실패) -> Roll Back


그러니까, 모든 작업이 성공한 경우 Commit 을 해주고, 중간에 조금이라도 실수가 있었다면 Roll Back 을 하게 됩니다. 롤백을 하면 Begin Transaction 을 하기전까지의 상태로 돌릴 수가 있습니다.

Commit 은 Begin Transaction 부터 지금까지의 작업 완료하게 되고, Commit 을 해야만 비로소 DB 가 업데이트 되는겁니다.

--------------------------------------------------------------------------------------

innodb로 테이블을 작성할 수 있는지 없는지를 먼저 확인하세요.
mysql에 접속해서 show varibles;라는 명령을 치면 설정들이 흘러 내립니다..-_-;
그 중에 have_innodb라는 옵션을 찾고 설정이 disabled라고 되어있으면 죽어도 innodb를 사용할 수 없습니다. 아무리 mysql버전이 높아도 말이죠..
have_innodb를 yes로 설정을 바꾸려면 my.ini 파일에서..
[mysqld]
innodb_data_file_path = ibdata1:10M:autoextend
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
저장할 파일명과 크기, 버퍼 크기, 로그 파일 크기 등이 나와 있니다. 원하는 크기로 설정, 저장을 하고 mysql을 재시작하면 됩니다.

트랜젝션 설정은 간단합니다. (Win32 + Mysql)
Mysql 4.x 를 설치하시구 테이블 타입을 type=innodb 으로 만드시고
mysqld-max-nt --bdb-home=c:\temp
이와 같이 실행시키면 됩니다.
글구 php 소스 상에서
mysql_query("BEGIN"); <-- 소스 맨위에 넣기 (트랜잭션 시작)
글구 쿼리 처리를 하다가
각각의 쿼리를 체크해서 오류가 생기면
mysql_query("ROLLBACK");
모든 쿼리의 오류가 없으면
mysql_query(COMMIT");
쿼리에 대한 오류체크는 한번 생각해보세요.
제가 이렇게 사용하고 있으니 함 해보세요.^^;

테이블 검사 및 복구하기 - myisamchk

데이타베이스
[ myisamchk 란 ? ]

- DB 테이블에대한 오류 검사 및 오류 복구 유틸리티
- 버전 3.22.x : isamchk 유틸리티 사용
3.23.x : myisamchk 유틸리티 사용

[ myisamchk 사용전 주의사항 ]

- mysql 데몬을 stop 시킨후 이 유틸리티를 사용해야한다.
- mysql 데몬을 중지시킬수 없는 사항이라면 검사할 테이블에대한 rock을 걸고
검사를 수행하여야만 검사도중에 발생할수있는 오류를 막을수있다.
- 모든작업이 그렇듯이 항상 백업을 한후 작성을 수행하는것이 좋을것이다.

[ myisamchk 사용법 및 옵션 ]

- 해당 테이블이있는 디렉토리로 이동 ( 일반적으로 /usr/local/mysql/var 밑에
위치함 )

1.일반적인 검사

[root@angelsoma var]myisamchk [table 명]

Checking MYISAM file: [table 명]
Data records: 271 Deleted blocks: 0
- check file-size
- check delete-chain
- check index reference
- check record links
에러메시지가 없으면 테이블에 오류가 없다는것이다.

2.Global 옵션

[root@angelsoma var]myisamchk -s,--silent [table 명]

에러만 출력한다.

[root@angelsoma var]myisamchk -v,--verbose [table 명]

-s 옵션보다 많은 정보를 출력한다.

[root@angelsoma var]myisamchk -V

myisamchk 버젼을 표시한다.

3.Check 옵션

[root@angelsoma var]myisamchk -c,--check [table 명]

테이블의 에러를 check 한다.

[root@angelsoma var]myisamchk -e,--extend-check [table 명]

테이블을 좀더 세밀하게 check 한다. 일반적인 방법으로 error를 찾을수없
경우 사용하는 옵션이다.

[root@angelsoma var]myisamchk -F,--fast [table 명]

빠른게 테이블 check 한다.정교한 체크는 하지않느다.

[root@angelsoma var]myisamchk -C,--check-only-changed [table 명]

테이블을 check 하고,테이블을 check 이후의 상태로 변경한다.

[root@angelsoma var]myisamchk -f,--force [table 명]

테이블에 error에 있을경우 강재로 check 한다.

[root@angelsoma var]myisamchk -i,--information [table 명]

check한 결과의 정보를 통계화하여 보여준다.

Checking MyISAM file: insertdb
Data records: 8962 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 2
Total: Keyblocks used: 97% Packed: 0%

- check record links
Records: 8962 M.recordlength: 241 Packed: 0%
Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00
Record blocks: 8962 Delete blocks: 0
Record data: 2166962 Deleted data: 0
Lost space: 6796 Linkdata: 33634

User time 0.21, System time 0.02
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 92, Physical pagefaults 198, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 0

[root@angelsoma var]myisamchk -m,--medium-check [table 명]

extend-check 옵션보다 check 속도가빠르며,99.9 % 의 에러을 찾을수있다.

4.Repair 옵션

[root@angelsoma var]myisamchk -o -B,--backup [table 명]

- recovering (with sort) MyISAM-table 'insertdb.MYI'
Data records: 8962
- Fixing index 1

MYD파일을 백업한다. 형식은 [filename-time.BAK]의 파일이 생긴다.

[root@angelsoma var]myisamchk -e,--extend-check [table 명]

세부적인 파일까지 복구를해준다.일반적으로 아주 하찮은 에러까지 찾을수
있다.하지만 자포자기의 상태가 아니고서는 이옵션을 사용하지 않는게 좋다.

[root@angelsoma var]myisamchk -f,-force [table 명]

이전것의 temporary file을 덥어쒸운다.

[root@angelsoma var]myisamchk -l,--no-symlinks [table 명]

심복릭 링크를 따르지않겠다는 옵션이다. 일반적으로 myisamchk 는symlink
points를 복구한다.

[root@angelsoma var]myisamchk -r,--recover [table 명]

unique key를 제외한 대부분를 복구한다.

[root@angelsoma var]myisamchk -n,--sort-recover [table 명]

sorting하면서 테이블을 복구한다. 심지어 temporary 파일과 같은 아주 큰
파일역시 sorting하면서 복구한다.

[root@angelsoma var]myisamchk -o,--safe-recover [table 명]

-r 옵션보다 느리게 복구한다.그러나 좀더 섬세한 복구를 지원한다.

[root@angelsoma var]myisamchk -q,--quick [table 명]

테이터 파일의 수정없이 복구한다.

5.기타 옵션

[root@angelsoma var]myisamchk -a,--analyze [table 명]

key의 distribution 을 분석한다.
만약, distribution 을 산출하고 싶을경우에는 --verbose 나 --describe 라
는 옵션과 동행해서 확인할수있다.

[root@angelsoma var]myisamchk -d,--descript-xion [table 명]

테이블에 대한 정보를 출력한다.

[root@angelsoma var]myisamchk -S,--sort-index [table 명]

index 블록을 sort한다.

[root@angelsoma var]myisamchk -R[index번호],--sort-records [table 명]

index 번호를 기준으로 인덱스를 정렬해준다.



6.검사중 아래의 메시지가출력되면 해당테이블을 사용중이라는 의미이므로 테
이블에 LOCK을 걸든가 데몬을 죽이고 나서 검사 및 복구를해야함.

myisamchk: warning: 1 clients is using or hasn't closed the table
properly

7.LOCK 걸기

myisamchk 는 테이블에대한 read 만 할수있으면 되기때문데 read 를 제외한
모든것에 lock을 걸면된다.

mysql> lock tables [table 명] READ ;
mysql> flush tables ;

flush tables 는 mysql이 테이블의 내용을 메모리에만 보관하고 실제 테이
블파일에 기록을하지 않았을경우 실제 테이블파일에 기록하라는 의미이다

8.LOCK 풀기

mysql> unlock talbe;

9.Myisamchk 로 복구를 위한 LOCK 걸기

서비스를 죽이지않고 복구를 해야할경우는 write lock를 걸어주면된다.
복구는 write 를 해야하기때문에 write lock를 걸어줘야한다.

mysql>lock tables [table명] write;
mysql>flush tables;

10.LOCK 풀기

mysql> unlock table;

mysql 상황 체크 - show

데이타베이스
1. 현재 존재하는 데이터베이스보기(show databases)
2. 사용중인 데이터베이스의 테이블보기(show tables)
3. 특정테이블의 구조보기(show columns from 테이블)
테이블은 여러개의 필드로 구성되어 있으며 특정테이블의 필드명과 type, 설정값들을 보기위한 것입니다.
이 명령은 "desc 테이블"과 "explain 테이블"과 동일한 결과를 보여줍니다.
4. 특정테이블의 인덱스보기
5. 데이터베이스의 설정상태 보기(show status)
6. 데이터베이스의 설정환경변수와 값보기(show variables)
7. 현재 데이터베이스에 연결된 프로세스들 보기(show processlist)

MYSQL index 칼럼 추가, 삭제하기

데이타베이스
추가
ALTER TABLE `log` ADD INDEX(`reg_date`);

삭제
ALTER TABLE `log` DROP INDEX `reg_date`;

MYSQL 7일 기간 텀으로 쿼리 부르기

데이타베이스
SELECT count(*) as num FROM table
WHERE UNIX_FROM(signdate, '%Y-%m-%d') <= DATE_SUB(DATE_FORMAT(curdate(),'%Y-%m-%d'), INTERVAL 7 DAY)

MYSQL unix time을 date 형식으로 보기 쿼리

데이타베이스
qry="select no,os,browser,ip, from_unixtime(`reg_date`,'%Y%m%d%H') as hh from weblog where ip='$ip' order by reg_date desc"

MYSQL DB 사용자 추가

데이타베이스
mysql> insert into db values ('%','databaseName','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `user` ( `Host` , `User` , `password` , `Select_priv` , `Insert_priv` , `Update_priv` , `Delete_priv` , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` , `Index_priv` , `Alter_priv` , `Show_db_priv` , `Super_priv` , `Create_tmp_table_priv` , `Lock_tables_priv` , `Execute_priv` , `Repl_slave_priv` , `Repl_client_priv` , `ssl_type` , `ssl_cipher` , `x509_issuer` , `x509_subject` , `max_questions` , `max_updates` , `max_connections` )
VALUES (
'localhost', 'partner', PASSWORD( 'partner' ) , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0'
);
Query OK, 1 row affected (0.00 sec)

MYSQL root 패스워드 바꾸기

데이타베이스
mysql> update user
-> set password = password('testpass')
-> where user = 'root'
-> \g
Query OK, 2 row affected (0.00 sec)
mysql>

MYSQL group by 의 예

데이타베이스

$query="SELECT ex_id, sum(0h) as 0h, sum(1h) as 1h, sum(2h) as 2h, sum(3h) as 3h, sum(4h) as 4h, sum(5h) as 5h, sum(6h) as 6h, sum(7h) as 7h, sum(8h) as 8h, sum(9h) as 9h, sum(10h) as 10h, sum(11h) as 11h, sum(12h) as 12h, sum(search_total) as search_total from log_backup where search_date>='$pre_date' and search_date<='$next_date' group by ex_id ";

MYSQL 날짜와 시간별 데이타 뽑아내기

데이타베이스
SELECT ex_no, from_unixtime(`reg_date`,'%Y%m%d%H') as hh,
COUNT(ex_no) AS `cnt_reg_date`
FROM expose_log
Where from_unixtime(reg_date,'%Y%m%d%H') >= '04050406'
AND from_unixtime(reg_date,'%Y%m%d%H') <= '04050723'
GROUP BY from_unixtime(`reg_date`,'%Y%m%d%H'), `ex_no`
ORDER BY from_unixtime(`reg_date`,'%Y%m%d%H')

MYSQL 백업과 복구 상세 설명

데이타베이스

[MySQL 접속]
mysql -hDB서버명 -uMySQL아이디 -pMySQL패스워드 데이타베이스명

[MySQL 백업하기]
MySQL 명령어로 백업받기 :
mysqldump -hDB서버명 -u유저명 -p패스워드 [백업할 테이터베이스명] > [외부로 저장할 파일명]
ex) mysqldump -hdb1.guideline.co.kr -uDBuser -pPassword MyDB > MyDB.sql

특정 테이블만 백업 할 경우 :
mysqldump -hDB서버명 -u유저명 -p패스워드 [테이타베이스명] [특정테이블명] > [외부로 저장할 파일명]
ex) mysqldump -hdb1.guideline.co.kr -uDBuser -pPassword MyDB MyTable > MyDB_MyTable.sql

[MySQL 복구하기]
mysql -hDB서버명 -u유저명 -p패스워드 [복구할 테이터베이스명] < [외부로 저장된 파일명]
ex) mysql -hdb1.guideline.co.kr -uDBuser -pPassword MyDB < MyDB.sql

MYSQL DB 원격 접속

데이타베이스
1. db 테이블의 host란에 '%'로 표시, 아니면 특정 IP.

2. user 테이블의 host란에 '%'로 표시, 아니면 특정 IP.

MYSQL 중복 체크한 쿼리

데이타베이스

select distinct no,user_id,name,jumin,count(no) as kk from member group by jumin having kk !='1'