MySQL TIPs

MySQL TIPs

2010. 3. 24. 12:55ㆍ99. 정리전 - IT/15. SQL 따라잡기

테이블 스키마 백업

TABLE 스키마백업
/mysql/bin/mysqldump -d --compact --default-character-set=utf8 --extended-insert=FALSE --add-drop-table -uTiger -p'scott' database_name -B -e | egrep -v "(^SET|^/\*\!)" > schema.txt

<TIP>
백업시 한글 코멘트가 깨져 있어도 저장된 인코딩방식과 동일한 방식으로 케릭터셋 옵션을 주었다면 복원시 한글이 깨지지 않는다

+------------+
| 각종 옵션들
+------------+

1. DB명으로 덤프 받기
# /usr/local/mysql/bin/mysqldump –u 계정 –p db명 > 덤프파일명.sql

2. 특정 테이블만 덤프 받기
# /usr/local/mysql/bin/mysqldump -u root -p db명 table명 > db명.table명.sql

3. 스키마 정보만 덤프 받기
// DB명 안에 모든 schema
# /usr/local/mysql/mysqldump -u root -p -d db명 > db명_schema.sql
// DB명 안에 모든 특정 table schema
# /usr/local/mysql/mysqldump -u root -p -d db명 table명 > db명_table명_schema.sql

4. 덤프 파일로 복원
# /usr/local/mysql/bin/mysql -u root -p DB명 < 덤프파일.sql

5. mysqldump 명령의 기본 사용법
mysqldump [옵션] db명 [table명] > 백업파일
mysqldump [옵션] –databases [옵션] db명1, db명2, …. > 백업파일
mysqldump [옵션] –all-databases [옵션] > 백업파일

6. mysqldump 옵션
-f, –force : 에러를 무시
-h, –host : 지정한 호스트의 데이터를 덤프
-t : data만 덤프
-d : 데이터를 제외하고 스키마만 덤프
-p : 사용자의 암호를 지정
-P : 포트번호 지정
-u : 사용자명 지정

-A –-all-databases	모든 DB를 덤프
--add-locks	덤프하기 전에 LOCK table을 이용하여 테이블을 잠그고 덤프후 UNLOCK table을 실행
--add-drop-table	덤프 결과에서 create table 앞에 drop table 절을 추가
-B –-databases	여러 DB를 동시에 덤프 할 때 사용
--quick	C API의 mysql_use_result() 함수를 사용하므로 속도가 빠르
--extended-insert	insert 문 하나에서 모든 레코드를 삽입하는 insert 문을 생성
--opt	위에 나온 옵션을 동시에 사용한 것과 같은 결과를 가져옵니다 옵션을 주어서 백업하면 나중에 원복을 할때 기존 DB와 TABLE, DATA를 삭제하고 백업한 내용으로 Update 됨 ★ --opt 옵션과 -d or -t는 같이 사용할 수 있습니다
-d --no-data	테이블의 스키마만 덤프
-t --no-create-info	create table을 출력하지 않고 데이터만 덤프
--ignore-table=database.table	특정테이블을 제외하고 백업

프로시져 스키마 백업

MySQL Procedure 포함 덤프

mysqldump -R -u[사용자] -p [DB 명] > [파일 이름].sql
mysqldump -R -uroot -p test > test_20120730.sql

MySQL Procedure 만 덤프는 불가 (이하와 같이 하면 펑션,프로시저,트리거 모두 백업됨)

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt -u[사용자] -p [DB 명] > [파일 이름].sql
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt -uroot -p test > test.sql

펑션 스키마 백업

펑션 스키마만 백업 불가

트리거 스키마 뽑기

mysqldump --triggers --no-create-info --no-data --no-create-db --skip-opt -u[사용자] -p [DB 명] > [파일 이름].sql
mysqldump --triggers --no-create-info --no-data --no-create-db --skip-opt -uroot -p test > test.sql

유저추가

mysql> GRANT SELECT , INSERT , UPDATE , DELETE ON *.* TO 유저ID@"localhost" IDENTIFIED BY "패스워드";
or
mysql> GRANT ALL ON *.* TO 유저ID@"localhost"IDENTIFIED BY "패스워드";
mysql> FLUSH PRIVILEGES;

테이블 CREATE 문 뽑기

mysql> SHOW CREATE TABLE TB_LMS_MEMBER\G;
mysql> SHOW CREATE TABLE TB_LMS_MEMBER;

테이블의 컬럼명 보기(순수컬럼명만)

SELECT column_name
FROM information_schema.columns
WHERE table_name
    IN (
        SELECT table_name
          FROM information_schema.tables
         WHERE table_schema = 'DB명'
           AND table_name = 'TABLE명'
      ORDER BY table_name DESC
    )

테이블의 컬럼명 보기(셋팅값포함)

mysql> DESC 表名
mysql> SHOW COLUMNS FROM 表名
mysql> SHOW INDEXES FROM 表名
mysql> SHOW CREATE TABLE 表名
mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='表名'

컬럼명은 알고 있는데 어떤 테이블에 속하여있나?


SELECT * 
  FROM information_schema.columns
 WHERE table_name
    IN ( 
        SELECT table_name
          FROM information_schema.tables
         WHERE table_schema = 'DB명'
      ORDER BY table_name DESC 
    )
   AND column_name like '%찾고싶은컬럼명%'

csv의 데이터를 테이블에 넣다 뺐다 넣다 뺐다


넣기
mysql> LOAD DATA LOCAL INFILE 'master_data.csv' INTO TABLE master_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

빼기
mysql> SELECT * FROM data INTO OUTFILE "master_data.csv" FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"';

DROP TABLE IF EXISTS 테이블명


SELECT '타이틀' as title;
DROP TABLE IF EXISTS 테이블x;
CREATE TABLE 테이블x SELECT * FROM 테이블x와 같은형식의 테이블명
또는
CREATE TABLE `DB명`.`TABLE명` (
      `xxx_id` char( 5 ) NOT NULL COMMENT '沿線ID',
      `xxx_name` varchar( 15 ) NOT NULL COMMENT '沿線名称\n',
      `xxx_company_id` int( 11 ) default NULL COMMENT '鉄道会社ID',
) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT = '沿線・駅マスタ'";

DUMP BACKUP


백업하기
mysqldump -u [DB사용자] -p [패스워드] [백업할 데이터베이스 이름] > [파일이름]

복구하기
mysql -u [DB사용자] -p [패스워드] [복구할 데이터베이스 이름] < [파일이름]

테이블 이름 바꾸기

ALTER TABLE 변경전이름 RENAME TO 변경후이름;


 

Table 정보조회 (with comment)



명령어를 통한 테이블정보 조회 
SHOW TABLE STATUS;

Query를 통한 테이블정보 조회 SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='database_name'; 
SELECT TABLE_SCHEMA,TABLE_NAME,AUTO_INCREMENT,TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='database_name';
 
COLUMN 정보조회 (with comment)



명령어를 통한 컬럼정보 조회 
SHOW FULL COLUMNS FROM TABLE_NAME;

Query를 통한 테이블정보 조회 SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='database_name';
 
SELECT  TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT
       ,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,EXTRA,COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='database_name'  AND TABLE_NAME='table_name';
 
TABLE COMMENT 설정



테이블 생성시 COMMENT 설정 
CREATE TABLE TABLE_NAME (
  column1 INT,
  ...
) COMMENT = 'table comment';

TABLE COMMENT 만 별도 수정하는 방법 ALTER TABLE TABLE_NAME COMMENT = '테이블설명';
 
COLUMNS COMMENT 설정





테이블 생성시 COLUMN COMMENT 설정 
CREATE TABLE TABLE_NAME (
  column1 INT COMMENT 'column1 comment',
  ...
);

TABLE COMMENT 만 별도 수정하는 방법 ALTER TABLE TABLE_NAME CHANGE COLUMN column_name column_name data_type NULL여부 COMMENT 'column comment'; 
ALTER TABLE `callcenter` CHANGE `regdate``regdate` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
NOT NULL DEFAULT CURRENT_TIMESTAMP 
COMMENT 'callcenter 등록일';

ALTER TABLE `테이블명` CHANGE `컬럼명`
`컬럼명` VARCHAR(14) DEFAULT NULL
COMMENT '학습시간(복습도 포함됨)';

기본 문법
http://www.bitscope.co.jp/tep/MySQL/quickMySQL.html

저작자표시

DonzBox

DonzBox

태그

최근글

댓글

공지사항

아카이브

Table 정보조회 (with comment)

COLUMN 정보조회 (with comment)

TABLE COMMENT 설정

COLUMNS COMMENT 설정

관련글

티스토리툴바