[Query] Small Tip!

종종 쓰는데 매번 찾아야 하는것들..

1. create user (copy)

계정 복사할 일이 종종있다.

새장비에 리플계정을 만든다던지, 같은 계정에 접근하는 장비(웹서버)가 하나 더 생긴다던지..

현재 내가 로그인한 계정의 권한을 확인할때

mysql> SHOW GRANTS;

GRANTS FOR willow@192.168.xxx.xxx                                                                                      

--------------------------------------------------------------------------------------
GRANT USAGE ON *.* TO 'willow'@'192.168.xxx.xxx' IDENTIFIED BY PASSWORD '*비밀번호'    // USAGE 는 딱! 접근만 허용

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, EXECUTE ON `디비`.* TO 'willow'@'192.168.xxx.xxx'  //특정디비에 특정 권한 부여

root 지만 정말 레알 root가 아닐수도있다.

GRANT 권한이 없는 경우도 있으니 계정 생성해놓고 "왜 안되지??!!" 하는 일이 없길 바란다.

꼼꼼한 dba 라면 그대에게 root를 열어줬어도 필수 권한은 빼고 열어주기도 한다는 사실~!

ROOT 이지만 IP가 localhost가 아니라면 체크해봅시다.

mysql> SHOW GRANTS;

Grants for root@127.0.0.1                                                                                                               

--------------------------------------------------------------------------------------
GRANT ALL PRIVILEGES ON *.* TO 'root'@'접근하는 IP주소' IDENTIFIED BY PASSWORD '*비밀번호' WITH GRANT OPTION   //이것이 있어야 권한도 부여할수 있는 진정한 뿌리

특정 계정, 아이피를 확인할때

mysql> SHOW GRANTS FOR '계정'@'192.168.xx.xxx';      

--------------------------------------------------------------------------------------
GRANT USAGE ON *.* TO '계정'@'192.168.xx.xxx' IDENTIFIED BY PASSWORD '*비밀번호';    // 복사!! , 5.7 부터 PASSWORD구문 삭제 

grants 해서 나온 결과에 추가(변경)된 IP로 수정 후 실행!   flush privileges 는 안해도 된당~

참~ 쉽죵~?!

계정 생성

CREATE USER '사용자아이디'@'접근아이피(호스트)' IDENTIFIED BY '비밀번호';  //생성구문 사용자 생성

GRANT USAGE ON *.* TO '사용자아이디'@'접근아이피(호스트)' IDENTIFIED BY '비밀번호';   // USAGE 는 딱! 접근만 허용

계정삭제

drop user 'willow'@'192.168.xxx.xxx'; //특정host 계정 삭제시

권한 생성

GRANT ALL ON `데이터베이스명`.* TO '사용자아이디'@'접근아이피(호스트)';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE ON `데이터베이스명`.`테이블명 또는 *` TO '사용자아이디'@'접근아이피(호스트)'; 

권한 제거

REVOKE ALL PRIVILEGES ON 데이터베이스명.* FROM '사용자아이디'@'192.168.xx.xxx'; //특정DB 에 대한 계정 제거

2. quickly Insert 

2014/01/06 18:26

+ auto_increment 컬럼을 빼면 자동으로 1부터 순서가 부여된다. * 를 쓰면 auto_increment값까지 Insert!

INSERT INTO to_table (occ_date,p1,p2,p3,p4,p5,p6,p7) SELECT occ_date,p1,p2,p3,p4,p5,p6,p7 FROM from_talbe WHERE occ_date LIKE '2014-01%';

3. backup

이벤트와 스키마, 데이터 덤프시 사용 

/mysql/bin/mysqldump -uroot -p --events --single-transaction watchdog > watchdog_event.sql

 --single-transaction 

                      Creates a consistent snapshot by dumping all tables in a

                      single transaction. Works ONLY for tables stored in

                      storage engines which support multiversioning (currently

                      only InnoDB does); the dump is NOT guaranteed to be

                      consistent for other storage engines. While a

                      --single-transaction dump is in process, to ensure a

                      valid dump file (correct table contents and binary log

                      position), no other connection should use the following

                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,

                      TRUNCATE TABLE, as consistent snapshot is not isolated

                      from them. Option automatically turns off --lock-tables.

                      

--single-transaction : 

InnoDB 스토리지 엔진을 주로 사용하는 DB의 백업에서는 데이터 정합성을 보장하기 위해 이 옵션을 반드시 사용할 것을 권장한다. 

MyISAM 스토리지 엔진과는 달리 이 옵션을 사용해 백업을 수행할 때는 테이블을 잠그지 않으므로 서비스용 쿼리가 테이블의 레코드를 변경하는것이 가능.

Export Dump & Import Dump

/mysql/bin/mysqldump -d -uroot -p watchdog > watchdog.sql   --  Only 스키마만 덤프시
mysql -uroot -p willow_db < /Users/willow/Downloads/willow_origin.sql   -- 덤프파일 디비에 부어넣기

+ 사고친 사례 T^T

/mysql/bin/mysqldump -E -uroot -p watchdog > watchdog_event.sql

  -E, --events        Dump events            // 분명.. help 엔 이랬다..

난 이벤트만 덤프 받을려고 했다고!!

그런데..통 data 덤프가 된다.. OTL 

몇초만에.. 1.5G... 후덜덜

Table Lock 걸려서.. DB 얼음되고.. 모니터링 폭풍 알람오면서... 나는 멘붕됐다.

[root@cydbtool01 /home/willow]# ls -alh

total 1.5G

-rw-r--r--  1 root   root  1.5G Mar  7 16:34 watchdog_event.sql

4. command line query

2009/09/09 18:05

종종 터미널에서 query를 날려서 바로 저장할 일이 생기는데 
휘발성 메모리인 탓에 기록~!

mysql 옵션중에 아래 e 옵션을 사용하면 간단하다.

[root@localhost willow]# mysql --help
mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0
Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?

  -e, --execute=name  Execute command and quit. (Disables --force and history
                     file)
  -N, --skip-column-names
                     Don't write column names in results. WARNING: -N is
                     deprecated, use long version of this options instead.

사용방법은?

[shell]# mysql -u root -pxxx -N -e " query(질의문) " > excel.csv

N 옵션은 query 결과에 필드를 보여주는 여부를 결정
ex)

[shell]# mysql -u root -pxxx -N -e "select * from member where userid='willow'"
+------------+--------+---+
| willow        | 홍길순  | N | 
+------------+--------+---+

[shell]# mysql -u root -pxxx -e "select * from member where userid='willow'"   
+------------+--------+----------+
| userid        | name    | sForeign |
+------------+--------+----------+
| willow        | 홍길순  | N            | 
+------------+--------+----------+

5. set @variable:=

Set 변수 선언.

예시1)

SET @tDate:= NOW();

SELECT * FROM `view_willow` WHERE id = 3 AND update_time >= DATE_SUB(@tDate, INTERVAL 1 MINUTE)

예시2)

SET @sList:= (SELECT list FROM tbl WHERE no=1005);

UPDATE tbl SET list=@sList WHERE no=1004;

5. table 정보

SQLyog 가 아닌 다른 툴에서는 테이블 정보 일괄 보여주는게 없어서 불편! 그래서 쿼리로 질의~!

-- 특정 데이터베이스 테이블 정보
SELECT TABLE_NAME AS "Tables", TABLE_COMMENT, CREATE_TIME, TABLE_ROWS, ENGINE, 
       round(((data_length + index_length) / 1024 / 1024), 2) "Total Size in MB", round(((data_length) / 1024 / 1024), 2) "Data Size in MB",round(((index_length) / 1024 / 1024), 2) "index Size in MB", TABLE_TYPE 
FROM information_schema.TABLES
WHERE table_schema = "common"
ORDER BY (data_length + index_length) DESC;

-- 전체 데이터베이스 테이블 정보
SELECT table_schema, TABLE_NAME AS "Tables", TABLE_COMMENT, CREATE_TIME, TABLE_ROWS, ENGINE, 
       round(((data_length + index_length) / 1024 / 1024), 2) "Total Size in MB", round(((data_length) / 1024 / 1024), 2) "Data Size in MB",round(((index_length) / 1024 / 1024), 2) "index Size in MB", TABLE_TYPE 
FROM information_schema.TABLES
ORDER BY table_schema, (data_length + index_length) DESC;