MySQL 데이터베이스 백업(dump) & 복구(restore)

Jiumn·2025년 1월 2일
0

MySQL

목록 보기
6/9

데이터베이스 생성하기

create database 데이터베이스 이름
  • '~ access denined' 에러 발생: 원인은 현재 접속한 유저 정보가 데이터베이스를 생성할 권한을 갖고 있지 않아서였다. 권한이 있는 계정을 전달 받아 dbeaver에서 새로 데이터베이스를 연결하고 명령어를 실행하자 새로운 데이터베이스가 생성됐다.

    ※ 주의사항: 데이터베이스를 생성하고 난 후 데이터를 이관하고 나면 데이터베이스 이름을 변경할 수 없다. (RENAME 명령어가 안 먹힘) MySQL 8.0 버전에서 변경된 사항이라고 한다. 내 경우 데이터를 이관하고 나서 데이터베이스 명에 오타가 있는 것을 발견하고 수정하려고 했으나 이미 늦어버렸다. (...) 데이터베이스를 새로 생성하고 다시 복구를 해야 한다고 한다.

  • 이후 소스코드에서 연결할 MySQL 유저에 권한을 부여해야 한다.

GRANT ALL PRIVILEGES ON DB명.* TO 사용자 계정@'%'

특정 사용자 계정에 특정 DB의 모든 테이블에 모든 권한을 모든 접속 주소에서 허용한다는 의미다.

이제 해당 유저로 로그인하면 새로운 DB가 추가된 것을 확인할 수 있다.

MySQL dump (백업)

MySQL DB를 dump 하는 방법은 mysqldump 명령어를 사용하거나 dbeaver와 같은 DBMS 클라이언트 도구 내에서 하는 방법이 있다. 이 포스팅에서는 dbeaver로 이용한다.

새로 생성한 데이터베이스 위를 우클릭해서 도구 > dump database 를 선택한다.

dump 할 테이블 목록이 뜨고 여기서 dump 하려는 테이블만 선택해주면 된다.

다음 단계에서 dump 옵션을 선택해야 하는데, 테이블 lock을 걸지 않은 상태에서 데이터 정합성을 보존할 수 있는 Online backup in single tranasaction 을 선택했다. (InnoDB만 지원 가능, mysqldump 명령어에서는 --single-transaction 옵션을 추가하면 사용 가능)

※ 입사 초기 dump를 하면 테이블 lock이 걸리는지 모르고 운영 시간대에 했다가 서버가 멈춘 적이 있었다. (...) 그래서 보통 dump는 새벽 시간대에 배치로 돌린다고 한다.

  • Settings에 보면 옵션들이 있는데(캡처 이미지는 디폴트 옵션)

  • Remove DEFINER: dump에 포함된 sql 문에 뷰, 프로시저, 트리거 등이 있는 경우 DEFINER가 포함되는데 여기에 지정된 계정이 복구하려는 계정과 일치하지 않으면 복구 중간에 ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 오류가 발생해서 멈춘다. (이 에러는 해당 계정이 all privileges 권한을 가지고 있을때도 발생한다.)

    미리 설정해서 DEFINER를 제거해주자.

    만약 설정하지 않았다면 dump를 받은 후 생성된 sql 파일을 열어 일일이 지워주는 수밖에 없다.

  • disable keys: 데이터를 삽입할 때마다 인덱스를 업데이트하는 것을 무시해서 삽입 속도를 빠르게 해준다. 이 옵션은 MyISAM 테이블에 대해서만 효과가 있다. InnoDB 에서는 무시된다.

  • -e, --extended-insert: mysqldump는 테이블의 데이터를 INSERT 문장 형태로 출력하게 되는데, 이 옵션을 사용하면 확장 형태의 INSERT 문장으로 데이터를 덤프하게 된다. 이런 형태의 덤프는 다시 로드하는 작업을 빠르게 해줄 수 있다.

    확장 형태의 INSERT 문장이라는 것은 "INSERT INTO tab VALUES ('1','2'),('2','3'),('3','4');"와 같은 형태의 INSERT 문장을 의미한다.

MySQL RESTORE (데이터 복구)

  1. GTID 관련 설정 에러
  • --set-gtid-purged=OFF 추가 후 dump 필요
  • 만약 dump 당시에 해당 옵션을 설정하지 않았다면 sql dump 파일을 직접 열어서 GTID 관련 문구를 모두 삭제해줘야 함 (vscode 에서 대용량 파일 열리지 않아서 vim으로 해결함)
  1. ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci' 에러

    ALTER DATABASE `데이터베이스명`
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_general_ci;
profile
Back-End Wep Developer. 꾸준함이 능력이다. Node.js, React.js를 주로 다룹니다.

0개의 댓글