긍정적인 개발자의 꿈

* mysql 경로 찾기

find / -name mysql 

* 폴더 이동

cd /var/lib/mysql/

* 백업

mysqldump -uroot -p[패스워드] [데이터베이스명] > [백업파일이름]

* 압축하기

tar -cvzf [압축할이름] [백업파일이름]

* 복구

mysql -uroot -p [데이터베이스명] < [백업파일이름]

cd /var/lib/mysql/

mysqldump -uroot -penergyfm bems_efcny_bh > back_160211.sql

tar -cvzf back_160211.tar back_160211.sql

* 외래키 무시

SET foreign_key_checks = 0;

실행;

SET foreign_key_checks = 1;

* 안전 모드 무시

SET SQL_SAFE_UPDATES = 0;

실행;

SET SQL_SAFE_UPDATES = 1;

오라클 정리

* regedit 에서 언인스톨

1. 서비스 중지

2. software에 오라클 삭제

3. system 에 control set service 오라클과 관련된 전부다 삭제

4. 물리적인 삭제 (c:\app , programfiles\oracle)

* 유저 생성 및 권한 설정

create user workshop identified by workshop;

grant resource, connect to workshop;

*ROWNUM

쿼리 결과로 나오게 되는 각각의 행들에 대한 순서값을 가리킨다.

주로 특정 개수난 그 이하의 행를 선택할때 사용된다.

*ROWID

테이블에 저장된 각각의 행들이 저장된 주소값을 가리킨다.

즉 행들의 고유 주소값이다.

*별칭

별칭이 공백이 있으면 ""를 붙인다.

* is null

nvl(컬럼, 0) : 컬럼이 널이면 0

nvl(컬럼, 1, 2) : 컬럼이 널이면 1, 아니면 2

* 문자열 합치기 

||

* 리터널

문자, 숫자, 날짜 값은 ''을 붙인다.

*비교 연산자

다르자는 <>, !=, ^=, <>는 동일한 의미를 같는다.

*like연산자

_L% 는 경우 앞에 한글자가 있고 뒤에 L이 와야 한다는 뜻

* in 연산자

in연산자를 쓸경우 내부적으로 자동으로 or연산자로 변경된다고 함.

* order by

컬럼, 식, 인덱스번호 등 3가지로 정렬이 가능하다

* 주민등록번호 별표 마킹

select RPAD(SUBSTR(STUDENT_SSN, 1, 8), 14, '*') FROM TB_STUDENT;

예) 811111-1******

* 음수 양수 확인

sign 함수로 0이면 0, 양수 1, 음수 -1를 반환한다.

* 날짜 저장

오라클은 RR, YY라는 의미가 있음

RR은 현재 날짜, YY 해당 날짜 임

* 날짜 함수

select sysdate, systimestamp from dual;

 <- to_number          to_date ->

숫자 문자 날짜

 to_char -> <- to_char

* 그룹 함수는 널을 무시하지 않고 카운트에 포함한다.

* 제약조건 확인

select * from USER_CONSTRAINTS;

select * from USER_CONS_COLUMNS;

*안시 조인

  안시 조인은 where 절이 없다?

* 서브 쿼리 

 - all 연산자

 :  메인 쿼리의 조건이 서브 쿼리에 모든 조건에 만족해야 함

 - any연산자

 : 메인 쿼리의 조건이 하나만 만족해도 됨

 - exists

 : 서브쿼리의 결과가 있는지 체크하여 있으면 메인쿼리랑 없으면 결과 없음 리턴

* 서브 쿼리 실행

 - from만 메인쿼리가 먼저 실행. 나머지는 서브쿼리가 먼저 실행

* 트랜잭션

 - 명시적 종료 : commit, rollback

 - 묵시적 종료 : exit(commit), 창닫기(rollback)

* 트랜잭션 시작 종료

 - 시작 : DML명령을 시작한 경우

 - 종료 

: 사용자가 commit 도는 rollback을 명시적으로 실행한 경우

  : DDL문장을 실행한 경우

: DCL문장을 실행한 경우

: 사용자가 exit로 프로그램을 종료한 경우

: 하드웨어 고장 또는 시스템 오류시(rollback)

* union vs union all

 : union은 중복이 제거 되며 자동으로 값을 정렬한다.

 : union all 은 중복이 제거 되지 않으며 값도 정렬 되지 않는다.

*제약조건

- 제약 조건 중 not null은 컬럼레벨에만 적용, 테이블 레벨에서는 안되고 나머지는 둘다 가능

*pk 네이밍 규칙

테이블명_컬럼명_pk이름

* 테이블 정보 보기

select * from tab;

* table 삭제

delet만 하면 휴지통에 버리는 거고 + PURGE 를 붙혀야 완전삭제

* 테이블 복구

flashback table 테이블명 to before drop;

* 데이터 삭제

truncate는 ddl이기 때문에 자동 트랜잭션. 그래서 롤백이 안된다.

수행 속도도 truncate가 더 빠름.

delete인 경우는 복사복을 만들고 지움.

* 데이터 저장공간

- block : I/O단위

- extent : 메모리 할당 단위(block들의 집합)

논리적인

block -> extent -> segment(table로 이해하면 쉽다) -> tablespace -> data base;

물리적인

os블록 = block

tablespace = data파일(*.dbf)

* 사용자가 생성한 뷰 목록 보기

select * from user_views;

* 뷰는 수정이 없고 create or repalce 를 이용하여 없으면 만들고 있으면 다시 생성함.

* 시퀀스 정보 보기

select * from user_sequences;

* 실행계획 보기

f10

* 인덱스를 이용하지 않는 경우

not 같은 부정

null 관련

컬럼 변형(to number)

Mssql을 Mysql로 마이그레이션시 삽질한 내용이다

가장 빈번하게 사용한 함수에 대해 정리해보았다.

기본적으로 insert할 때 return 값은 

성공시 null을

실패시 exception을 발생한다.

그리하여 insert할때 값을 얻어오고 싶으면 아래와 같이 추가하면된다.

insert into values (어쩌고 ) 

<selectKey resultClass="int">

SELECT LAST_INSERT_ID();

</selectKey>

위와 같이 하면 인서트 된 마지막 값을 반환한다.

파일이름을 변경하여 변경하는 방법이다.

RESTORE FILELISTONLY FROM DISK='C:\A.bak'

RESTORE DATABASE 스키마이름 FROM DISK='C:\A.bak'

WITH 

   MOVE '논리이름' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\스키마이름 .mdf',

   MOVE '논리이름_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\스키마이름 .ldf'

파랑 : 백업 파일 위치

첫번째 줄 입력하게 하게 원래 백업파일에 논리적인 이름 나오게 된다.

녹색 : 원래 백업파일에 논리적인 이름

빨강 : 복원될 스키마 이름

mysql 5.5이상부터는 기본적으로 utf-8로 한글설정을 하여도 system설정 말고는 utf-8로 변경이 되어 있지 않는다.

workbench에 왼쪽탭에 > instance > status and system variables에

system variables에 character로 검색하면 system 이름 말고는 utf-8로 설정되어 있지 않는다.

[그림 - 캐릭터 셋 설정(변경전)]

이럴 경우 my.ini에 파일을 수정해야 한다.

(설치 위치는 위에 캐릭터 셋 그림에 마지막 행의 dir 위치를 참고 하면 된다.)

아래 그림 파일에서 my-default.ini파일을 그대로 복사하여 my.ini파일로 교체한다.

[그림 - my.ini 설정 위치]

위 만들어진 파일에 아래와 같이 내용을 채운다.

[client]

default-character-set = utf8

[mysqld]

character-set-client-handshake = FALSE

init_connect="SET collation_connection = utf8_general_ci"

init_connect="SET NAMES utf8"

character-set-server = utf8

[mysql]

default-character-set = utf8

[mysqldump]

default-character-set = utf8

[그림 - my.ini 내용]

그리고 mysql을 재시작하여 아래와 같이 utf-8로 설정되어 있다.

[그림 - 캐릭터 셋 설정(변경후)]

mysql의 스토리지 엔진 중(MyISAM, InnoDB) InnoDB에 관련 된 데이터 복구 방법에 대한 설명이다.
본래 InnoDB는 Mysql이 손상되어도 .frm 파일과 ibdata1파일만 존재하여도 복구가 가능하다.(이 방법은 아주 간단하므로 설명 제외)

이번에 설명할 내용은 ibdata1 인 데이터 파일이 손실될 경우에 복구하는 방법에 대해 설명이다.
Mysql은 정상 시작시 파일의 유무 및 다른 파일등을 검사한다.
하지만 파일이 손상되었을 경우 시작하자마자 서비스를 자체적으로 중단한다.
이에 InnoDB는 파일의 검사 등을 하지 않고 강제로 서비스를 시작하여 복구하는 모드가 따로 존재한다.
윈도우 기준으로 Mysql 설치 폴더/my.ini 이하 my.ini 파일에 innodb_force_recovery라는 명령어를 추가 시켜 주고 서비스를 재시작 하면 강제 복구 모드로 설정된다.
예) innodb_force_recovery = 1
숫자는 1에서 6까지 사용 가능하며, 1이 가장 안전하고 좋은 복구 모드이고, 1에서 복구가 안된다면 1~6까지 순차적으로 값을 바꿔가야 한다.
자세한 내용은 http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21875&cat1=14&cat2=422&cat3=442&lang=k 를 참고 하길 바란다.

위와 같이 프로그램을 시작하면 서비스가 정상적으로 시작하게 되며 그후에 dump로 파일을 백업 시킬 수 있다.
덤프 명령어는 Mysql설치폴더/bin에서 mysqldump -u계정명 -p 데이터베이스명 > 백업할파일.sql 로 하면 백업이 되고
반대로 mysqldump -a -u계정명 -p 데이터베이스명 < 백업할파일.sql 이와 같이 하면 복원이 된다.

만약 1이 아닌 복구 모드로 실행시 파일이 전체적으로 백업되지 않았을 경우 복구 모드에 우선순위를 높여 select 명령어로 검사후 csv파일로 저장
저장 후 벌크 인서트로 파일 다시 불러옴
벌크는 http://kr.blog.yahoo.com/freud19390723/79 를 참고

복원이 완료 된 후에는 innodb_force_recovery = 1를 삭제후 프로그램 재시작을 한다.

간단히 설명하자면 파일이 손상된 경우
1. 서비스 시작 중지
2. my.ini파일에 innodb_force_recovery = 1~6 값 설정 후 저장
3. 서비스 시작
4. 서비스 시작시 정상 동작이면 6. 아니면 5.
5. 정상 동작이 아닐경우 다른 복구 모드를 선택하여 서비스 시작
6. 정상 동작일 경우 mysqldump로 파일 백업
7. 높은 우선순위로 변경하여 파일로 저장후 벌크 인서트로 다시 넣음
8. 복구 모드 삭제 후 서비스 재시작

컴퓨터의 불안정한 종료로 인하여 DB파일에 손상을 입을 경우 데이터베이스 옆에 "(주의대상)"이라로 뜰 때가 있음.

1) SQL Server Management Studio 실행

2) EXEC sp_resetstatus 'yourDBname';

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb('yourDBname')

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

위 쿼리를 복사후 주의대상 DB 이름으로 변경후 쿼리를 실행하면 된다.

출처 : http://http://jjun7983.tistory.com/151

querybrowser 에서 User 테이블의 직접 편집해도 된다.

아래와 같이 해도됨

1. mysql 의 데이터베이스 mysql 로 들어가서

User 테이블의 정보를 확인 해 본다.

User 컬럼의 localhost 와 % 의 비밀 번호 정보가 다르게 입력 되어 있을 수 있다.

다를 경우

update user set Password=Password('password') where User='id' and Host='%';

commit;

FLUSH PRIVILEGES;

이런 식으로 재등록 해준다.

2. 권한이 없는 문제일 경우

GRANT ALL PRIVILEGES ON *.*TO 'id'@'%' IDENTIFIED BY 'password' with GRANT OPTION;

FLUSH PRIVILEGES;

이런 식으로 해결 해 주면 됨.