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명이 없을 경우 존재하는 모든 데이터베이스를 나열

-테이블 테이블을

-컬럼 컬럼을