[MySQL/Linux] 덤프 파일 생성 스케줄링 및 복원

환경

centOS7

mysql  Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL) 

 

 

---

 

✔ Mysql 덤프 생성 스케줄링 처리

 

매일 데이터를 백업 시켜둬야 하기 때문에 리눅스의 crontab 을 활용해 백업본을 매일 만들도록 한다.

방식은 아래와 같다.

 

1. sh 파일 생성 

 

위치는 아무곳이나 상관 없다. 원하는 위치에 쉘파일을 생성한다. 

$ vi db_backup.sh

 

vi 편집기로 아래와 같이 작성하고 저장(:wq) 한다. 

매일 백업본을 만들 예정이라 패턴을 날짜로 설정해 주었다. 

#!/bin/bash
DB_BACKUP="/root/dump/mydb/" # 백업파일 위치
db="mydbtest"

# 생성할 파일 패턴
mysqldump --login-path=설정명 --routines $db | gzip > "$DB_BACKUP/$db-$(date +%Y-%m-%d).gz";

 

이때 mysql 의 login path 는 따로 미리 설정을 해줘야 한다.

$ mysql_config_editor set --login-path=설정명 --host=IP주소 --user=아이디 --port=포트 --password 
Enter password: 비밀번호

생성하고 나서 확인 하려면 아래 명령어를 입력하면 결과를 확인할 수 있다.

$ mysql_config_editor print --all


[설정명]
user = root
password = *****
host = IP주소 혹은 호스트명
port = 3306

 

---

 

2. crontab 등록

# 목록 확인
$ crontab -l

# 크롭탭 편집 
$ crontab -e 

# 순서대로 시간, 실행할 파일이 있는 경로를 입력하고 저장한다. 
00 01 * * * /root/dump/db_backup.sh

위와 같이 설정을 해두면 매일 새벽 1시에 db_backup.sh 파일이 실행되어 설정한 위치에 백업본이 저장 될 것이다.

 

 

 

 

---

 

 

✔ Mysql 덤프 복원

 

테스트를 위해 다른 위치에 있는 데이터베이스에 데이터를 그대로 가져와야 한다. 

이때 만들어둔 덤프 파일을 활용하면 편리하다.

 

 

1. 덤프 파일 압축 해제

$ gzip -d mydbtest_2021-04-14.gz

덤프 파일이 있는 위치에서 명령어를 입력해 압축을 풀어준다.

 

 

2. 데이터베이스 복원

$ mysql -u 계정명 -p 데이터베이스명 < mydbtest-2021-04-14

압축 해제한 덤프 파일 위치에서 위의 명령어를 실행하면 된다. 

Enter password: 가 뜨면 비밀번호를 입력해주면 끝!

 

위 명령어는 전체 데이터베이스 스키마와 데이터를 생성해주는 방식이다. 

 

일단 원하는 데이터베이스가 먼저 만들어져있어야 한다는 점 잊으면 안된다.

그리고 해당 데이터베이스에 대한 권한도 부여되어 있어야한다. 

 

3. 실행 로그 확인

# mysql 로그 위치 
$ vi /var/log/mysqld.log

# 바이너리 로그 위치 
$ vi /var/lib/mysql

 

---

 

특정 테이블만 따로 덤프파일에서 import 하고 싶을 경우가 있다. 

모든 스키마를 import 하기에는 시간이 너무 오래걸리기도 하고 뭐 이런저런 이유가 있을 것이다. 

그럴 땐 아래와 같이 실행하면 된다.

 

 

1. 테이블 덤프 파일 생성

# 실패
$ sed -n -e '/DROP TABLE.*t_user/,/UNLOCK TABLES/p' mydbtest-2021-04-14 > usertable.dump
$ sed -n -e '/^DROP TABLE IF EXISTS \`t_user\`;/,/^UNLOCK TABLES;/p' mydbtest-2021-04-14 > usertable.dump


# 성공!!!!
$ sed -n -e '/-- Table structure for table `t_user`/,/UNLOCK TABLES/p' mydbtest-2021-04-14 > usertable.sql

여러 방법을 해봤는데 맨 아래 명령어로 성공했다. 

 

2. import

$ mysql -u 계정명 -p 데이터베이스명 < usertable.sql

 

import 할때 drop 쿼리문이 먼저 실행 되는데, 만약 이미 테이블이 생성되어 있는 경우 drop 되고 insert 된다.

굳이 테이블을 삭제하지 않아도 된다는 점. 

그렇지만 만약 foreign key 가 걸려있는 경우에는 에러가 발생한다. 

에러구문은 친절하게 나오니까 보면서 처리하면 된다. 

 

 

 

 

 

 

[참고] 

stackoverflow.com/questions/1013852/can-i-restore-a-single-table-from-a-full-mysql-mysqldump-file

Can I restore a single table from a full mysql mysqldump file?