Mysql Error
•
can't delete from a CTE on MySQL 8+, as you might have been able to do on SQL Server.
Mysql 은 with 로 생성한 cte 를 update 하거나 delete 할 수 없다.
-- 아래와 같이 하면 error 발생한다.
DELETE
FROM cwd_group
WHERE id IN
(SELECT DISTINCT a.id ext_id
FROM cwd_group a
JOIN cwd_group b ON a.group_name=b.group_name
JOIN cwd_directory d ON d.id=a.directory_id
WHERE a.directory_id != b.directory_id
AND directory_name = 'My JIRA Server');
SQL
복사
→ sub query 를 하나 더 넣고 그 결과를 임시 테이블로 만든 후에 실행한다.
DELETE
FROM cwd_group
WHERE id IN
(SELECT ext_id
FROM
(SELECT DISTINCT a.id ext_id
FROM cwd_group a
JOIN cwd_group b ON a.group_name=b.group_name
JOIN cwd_directory d ON d.id=a.directory_id
WHERE a.directory_id != b.directory_id
AND directory_name = 'My JIRA Server') tmp) ;
SQL
복사
→ 만약, 자기 테이블의 서브 쿼리 결과를 받아서 하게 되면 또 아래와 같은 에러가 발생한다.
SQL Error [1093] [HY000]: You can't specify target table for update in FROM clause
→ 단일 테이블일 경우 아래와 같이 sub query 의 결과를 임시 테이블로 만들어서 한 번 더 감싸도록 서브 쿼리를 작성하면 됨.
-- 아래의 코드를
UPDATE tbl1
set name = concat(name, 'aa')
where id in (select id from tbl1 where name is not null);
-- 다음과 같이 변경한다.
UPDATE tbl1
set name = concat(name, 'aa')
where id in (
select tbl1_alias.nid
from (
select id nid
from tbl1
where name is not null
) tbl1_alias
);
SQL
복사
delete 사용시 주의할 점
Delete문은 위험한 명령어 이므로 꼭 트랜잭션 안에서만 사용해줘야 한다.
특히 delete 문구에 실수로 where절을 쓰지 않고 실행을 하게 되면 테이블에 있는 칼럼이 모두 삭제가 되는 상황이 발생
한다. 따라서 delete를 하기 앞서 select 문으로 자신이 바꿔야 할 데이터를 조회한 뒤, 꼭 트랜젝션 안에서 delete 문을 실행하도록 하자.
•
트랜잭션?
하나의 처리를 여러 단계로 다루는 기능을 트랜잭션(Transaction)이라고 한다. 하나의 처리를 Commit이라는 명령어를 수행하기 전에 마지막으로 한번 더 확인할 수 있는 기회를 줌으로써 좀더 안정적인 데이터베이스 작업을 가능하게 한다.
◦
begin tran; — 트랜잭션의 실행 결과를 데이터베이스에 반영
— 트랜잭션 시작
◦
rollback tran; — 실행 결과를 반영하기 전으로 되돌리는 것
— 트랜잭션 이전 상태로 되돌리기
◦
commit tran;
— 트랜잭션 완료
-- delete + 트랜잭션
select * from book where rate <= 3;
begin tran;
delete from book where rate <= 3;
select * from book where rate <= 3
-- 삭제 됐는지 확인, 적용은 안된 상태
rollback tran; -- delete 취소
commit tran; -- 적용
SQL
복사
바이너리 로그로 데이터 복구하기
MySQL에는 바이너리 로그라는 것이 존재한다. 바이너리 로그에는 MySQL에서 데이터베이스에서 테이블 생성, 변경 작업, 데이터 추가, 삭제, 변경 등의 ‘이벤트’ 가 저장되어 있다. 단, SELECT 와 같이 데이터베이스를 변경하지 않는 명령에 대한 이벤트는 저장하지 않는다.
크게 두 가지로 활용된다. 첫번째는 데이터베이스 복구이다. 말했듯, 바이너리 로그에는 데이터베이스를 변경하는 모든 이벤트가 저장되어있어 데이터베이스를 특정 시점으로 복구하는데 사용할 수 있다.
두번째로는 데이터베이스 레플리케이션이다. 레플리카가 소스의 바이너리 로그를 읽어가 레플리카에 릴레이 로그로 저장한다음 복제가 진행된다.
•
PIT(point in time) 복구
mysql 바이너리 로그를 활용하여 특정 시점으로 데이터를 복구할 수 있다. 이를 PIT 복구라고 한다.
•
바이너리 로그 확인
-- 우선, 다음 쿼리로 바이너리 로그 목록을 조회해본다.
show binary logs;
SQL
복사
◦
현재 작성중인 바이너리 로 파일을 닫고 새로운 바이너리 로그 파일을 열자. (복구 시점을 확실히 확인하기 위한 작업이다.)
flush logs;
show binary logs; -- 새 바이너리 로그 파일이 열린 것을 확인할 수 있다.
SQL
복사
•
바이너리 로그를 텍스트로 변환
바이너리 로그는 이름 그대로 이벤트 정보를 이진 데이터로 저장한다. 따라서 그냥 파일을 열게되면 우리가 읽을 수 없다. 이를 텍스트로 변환할 필요가 있다. 이때 사용되는 유틸리티가 mysqlbinlog 이다. mysqlbinlog 는 MySQL 에서 기본 제공되므로 곧바로 사용할 수 있다.
바이너리 로그의 일반적인 저장 경로는 /var/lib/mysql 이다. 아래 명령을 통해 단일 바이너리 로그 파일을 텍스트 파일로 변환할 수 있다. 리다이렉션을 사용하여 표준 출력을 파일로 리다이렉트할 수 있다.
mysqlbinlog /var/lib/mysql/binlog.000001 > binlog.000001.sql
SQL
복사
또는 아래와 같이 와일드카드를 사용하여 모든 바이너리 로그를 단일 텍스트 파일로 변환할 수 있다.
mysqlbinlog /var/lib/mysql/binlog.0* > binlog.sql
SQL
복사
아래의 방법처럼 여러 바이너리 로그를 전달하여 하나의 바이너리 로그로 만드는 방법도 있다.
mysqlbinlog /var/lib/mysql/binlog.000001 /var/lib/mysql/binlog.000002 > recover.sql
SQL
복사
mysql transaction
mysql 에서 트랜잭션은 데이터베이스의 상태를 바꾸는 일종의 작업 단위다.
insert, delete, update 등 명령문을 통해 데이터의 상태를 바꿀 때마다 내부적으로 자동으로 commit 을 실행하여 변경된 내용을 데이터베이스에 반영하는 것이다.
1. 활성(Active)
: 트랜잭션이 정상적으로 실행중인 상태를 의미한다.
트랜잭션이 시작되면, 해당 트랜잭션의 상태는 활동(Active)상태가 된다.
해당 상태는 설계자가 설계한 대로 연산들이 정상적으로 실행중인 상태를 의미한다.
•
작업 성공시,
2-1. 부분 완료(Partially Committed)
: 트랜잭션의 마지막까지 실행되었지만, Commit 연산이 실행되기 직전의 상태
설계된 작업대로 작업이 성공하였다고 하여 무조건 반영하는 것이 아니라, 설계자의 최종 승인(Commit)이 있을 때 까지 실제 데이터베이스에 작업 내용을 반영하지 않고 기다리고 있는 상태이다.
2-2. 완료(Committed)
: 트랜잭션이 성공이 종료되어 Commit 연산을 실행한 후의 상태
•
작업 실패시,
2-1. 실패(Failed)
: 트랜잭션 실행에 오류가 발생하여 중단된 상태.
2-2. 철회(Aborted)
: 트랜잭션이 비정상적으로 종료되어 Rollback 연산을 수행한 상태.
Commit
Commit이란, 모든 작업들을 정상 처리하겠다고 확정하는 명령어로서, 해당 처리 과정을 DB에 영구 저장
하겠다는 의미이며, Commit을 수행하면 하나의 트랜잭션 과정이 종료
되는 것이다.
Commit을 수행하면 이전 데이터가 완전히 반영되어 UPDATE된다.
Roll-back
Roll-back은 작업 중 문제가 발생되어 트랜잭션의 처리 과정에서 발생한 변경사항을 취소 하는 명령어이다.
해당 명령을 트랜잭션에게 하달하면, 트랜잭션은 시작되기 이전의 상태로 되돌아간다.
이것은 마지막 Commit을 완료한 시점으로 돌아간다는 말과 상통한다. 즉, Rollback은 Commit하여 저장한 예전 상태를 복구하는 것이다
mysql은 내부적으로 자동 commit 이 되므로 수동으로 바꾼 뒤 트랜잭션을 진행해야 한다.
-- transaction 시작 알리기
begin tran;
select * from 테이블1; -- 변경 전 상태 확인
insert into 테이블1 values('','','',....); -- 데이터 수정
select * from 테이블1; -- 변경 되기 전 수정 상태 확인
commit tran; -- 트랜잭션을 db에 적용
select * from 테이블1; -- 최종 수정된 상태 조회
-- rollback 예시
begin tran;
insert into 테이블1 values('','','',...); -- 데이터 수정
select * from 테이블1 -- 변경 전 수정 상태 확인
rollback tran; -- 트랜잭션 취소하고 begin tran 실행 전 상태로 롤백
select * from 테이블1; -- 조회
SQL
복사