MYSQL 관리

출처 : http://lanplaza.net

mysql 의 안정적인 종료
mysqladmin -u root -p shutdown

mysql 자동화 접속
./mysql -uroot -p'passwd' mysql


mysql원격접속
default로 local서버에서만 접속가능.
mysql db와 user테이블의 호스트필드를 %또는 접속을 허용할 IP주소설정.
./mysql -u tempuser -p temp_db2 -h 192.168.0.111 -P 3306

패스워드 재설정
mysqladmin -u 계정 -p password 패스워드
mysql>update user set password=password('12345) where user='root';
mysql>set password for root=password('12345');
mysql>flush privileges;

파일로 된 쿼리문 실행
./mysql -u root -p mysql < ./temp3.sql

유저등록과 db등록 스크립트
create database mare_db;
insert into db
values('localhost','mare_db','mare','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
insert into user
values('localhost','mare',password('12345'),'N','N','N','N','N','N','N','N','N','N',
'N','N','N','N','N','N','N','N','N','N','N',1,'','','','','','');

소켓파일위치 지정하여 접속하기
./mysql -u root -p mysql -S /var/lib/mysql/mysql.sock

mysql passwod 복구
mysql의 table 권한을 skip하고 실행시킨후 복구.
./mysqld_safe --skip-grant-table &

update문으로 복구.
flush privileges;

mysql 프로세스리스트 및 환경값 보기
./mysqladmin -u root -p processlist
./mysqladmin -u root -p variables
./mysqladmin -u root -p variables |grep max_connections



too many connections 에러시 조치
./mysqld_safe -O max_connections=500 -O table_cache=256 -O wait_timeout=57600 &
(환경변수값을 조정함)

mysql을 mysql user로 실행시키기
chown -R mysql:mysql /var/lib/mysql

mysql erro 코드보기
perror -v 101

mysqladmin 활용
mysqladmin -u root -p create 데이터베이스
mysqladmin -u root -p drop 데이터베이스
mysqladmin -u root -p reload or flush-privieges
mysqladmin -u root -p processlist
mysqladmin -u root -p kill 8(threads)
mysqladmin -u root -p status
mysqladmin -u root -p extended-status
mysqladmin -u root -p variables
mysqladmin -u root -p version
mysqladmin -u root -p ping

mysql log분석
mysql 주로그(에러로그)파일
db 저장소에 hostname.err로 저장됨.

mysql 쿼리로그 남기기
mysqld_safe --log=mysql_query_log &
(쿼리의 기록이므로 순간에 찰수 있다,조심)

mysql 바이너리(binary)로그 남기기
mysqld_safe --log-bin=mysql_binary_log &
(insert,update,delete쿼리에 데이터가 변경시 그 내역을 기록, 주 목적은 slave와의 replication)

mysql 튜닝
- slow log를 활용한 지정된 시간이상 질의를 계속하는 질의문 잡아내기.
mysqld_safe --log-slow-queries=mysql_slow_log &
(환경값의 long_query_time 에 지정된 값보다 높을시 기록)

원격지서버에서의 접속
mysql DB의 user , db 테이블에 host를 %로 설정 or 192.168.1.%
mysql>grant all on mysql.* to comansun@192.168.1.20;

my.cnf 파일을 이용한 성능튜닝
share 디렉토리에 메모리용량에 따른 *.cnf 파일이 존재
my-innodb-heavy-4G.cnf (4G이상)
my-huge.cnf (1G~2G이상)
my-large.cnf (512M이상)
my-medium.cnf (32M~64M이상)
my-small.cnf (64M이하)

텍스트파일을 특정테이터베이스로 입력
mysqlimport -u root -p 데이터베이스명 텍스트파일명
단, 텍스트파일명은 DB의 테이블명과 동일하여야함.
(테이블은 이미 스키마가 형성된상태)



쉘상에서 mysql 스키마 확인
mysqlshow -u root -p DB table column

mysql 백업과 복구
방법 1. 디렉토리 전체압축
방법 2. mysqldump -u root -p db명 > 저장파일명
mysql -u root -p db명 < 저장파일명

특정테이블 백업 및 복구
mysqldump -u root -p db명 table명 > 저장파일명
mysql -u root -p db명 < 저장파일명

여러개의 테이터베이스 한번에 백업 및 복구
mysqldump -u root -p --database db1, db2, db3 > 저장파일명
mysql -u root -p < 저장파일명
(--database 옵션 사용시 create 명령으로 db를 생성한다.)



전체데이터베이스 한번에 백업
mysqldump -u root -p --all-database > 저장파일명

테이블스키마만 백업하기
mysqldump -u root -p -d mysql > mysql.sql

기존테이블 삭제하는 옵션추가 백업
mysqldump -u root -p --add-drop-table temp_db > temp_db.sql

원격지서버 mysql백업
mysqldump -u root -p -h 192.168.0.111 sspark > sspark.sql
(sspark.sql이 로컬에 생성된다)

포트가 상이할시
mysqldump -u root -p -h 192.168.0.111 -p 3307 sspark > sspark.sql

깨진테이블 복구하기.
*mysql을 내린후 작업한다.
myisamchk -> *.MYI에만 해당된다.
-c 옵션없을때와 동일한점검(-_-)
-s 에러메세지만 출력
-v 상세하게 출력
-i 결과를 상세히 종합
-e 단순한 점검만을 할시 가장 정밀하게 진단. -ev
-r 깨진 테이블파일 복구 -rv
-o -r로 복구하지 못했을시 사용 -r보다 안정성이 높지만 속도가 떨어진다.
-e -r or -o 로 복구가 되지 않았을시 사용. 데이터 백업후 사용. 복구율이 가장 높으면서 가장 전하지 못하다. -re -oe

*.FRM(테이블구조파일)로 인해 복구가 불가능할시
기존 백업본을 복사 , 백업본 없을시 일단 *.MYI나 *.MYD를 백업후 mysql에 접속하여 create table로 재생성.myisamchk 사용

*.MYI(인덱스파일)로 인해 복구가 불가능할시
백업본 복사, 백업본 없을시 *.MYD,*.FRM을 백업후 delete from table명을 사용 해당 테이블의 모든 레코드(데이터)들을 삭제.
MYD와 FRM을 다시 복사후 myisamchk로 복구시도.