mysql - 중요 테이블 백업파일 aws s3에 업로드하기

 

 

맥북기준

 

서버는 aws 리눅스 ec2. (빈스톡은 설정에 따라 인스턴스가 바뀌니까 이 방법 ㄴㄴ) 

 

mysql 주요 테이블의 dump를 압축하여 s3로 업로드하는 스크립트를 짜고, 이걸 젠킨스 잡에서 주기적으로 실행시키는 걸 해볼 거다

 

참고로 테이블 스키마(구조)만 export하고싶으면

https://stackoverflow.com/questions/6175473/mysql-export-schema-without-data

MySql export schema without data

--no-data 옵션은 데이터를 뺀 스키마만 export 할 때 쓴다. 

 

2. 로컬에서 테스트해보기 

터미널을 열고

# 특정 database 덤프

mysqldump -u[아이디] -p[패스워드] [database명] > 경로/파일명.sql

 

# 특정 table 덤프

mysqldump -u[아이디] -p[패스워드] [database명] [table] > 경로/파일명.sql

 

3. 서비스중인 db에 락걸리는 걸 방지하기 위한 옵션 설명 

mysqldump를 사용하면 자동으로 table lock이 걸린다.

그럴 경우  –single-transaction 옵션으로  lock를 안 걸리게 할 수 있다. mysqldump 도중에도 trasaction (insert, update, delete)이 허용된다.

mysqldump -h 호스트 -u 유저 -p패스워드 –single-transaction  DB명 > 파일명

(스냅샷과 다르게 백업이 완벽하게 되진 않음 유의)

 

4. Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109) 에러 발생 시 

참고 링크 

https://jay-ji.tistory.com/62

[MySQL] Unknown table 'COLUMN_STATISTICS' in information_schema (1109) 에러

 

5. 이제 로컬 말고 AWS 서버에 들어가서 해볼 거임. ec2에 접속해서 aws cli를 설치해준다. 

 

6. 서버 컴에다가 다음 스크립트를 저장한다. 밑에 설명 있음.

stamp=`date "+%Y-%m-%d_%H:%M:%S-back-up"`

tables=(table_one table_two table_three)

mkdir ~/$stamp

cd ~

for table in ${tables[@]}; do
        mysqldump -h 호스트명 -u 유저네임 -p'비번' store ${table} --single-transaction > ~/$stamp/${table}.sql
done



tar cvfz - ./$stamp | aws s3 cp - s3://아마존s3경로/$stamp.tar.gz

rm -r $stamp/

 

 

stamp=`date "+%Y-%m-%d_%H:%M:%S-back-up"`
 -> 임시폴더명을 담은 변수.

tables=(table_one table_two table_three)
-> 백업할 테이블 목록을 담은 변수 

mkdir ~/$stamp

-> 임시폴더 생성

cd ~

for table in ${tables[@]}; do
        mysqldump -h 호스트명 -u 유저네임 -p'비번' store ${table} --single-transaction > ~/$stamp/${table}.sql
done

-> stamp 임시폴더 안에 테이블명.sql 이름의 덤프파일을 하나씩 만든다


tar cvfz - ./$stamp | aws s3 cp - s3://아마존s3경로/$stamp.tar.gz

-> tar로 압축 후 아마존 s3로 전송한다. stamp에 할당한 날짜대로 "날짜.tar.gz" 압축파일이 s3에 업로드 될 것이고, s3에서 다운받은 다음에 열어보면 table_one.sql , table_two.sql ,  table_three.sql이 들어있을 거임. 

rm -r $stamp/

-> 서버컴 내 임시폴더 삭제

 

 

7. 부록) 서버에 연결해놓은 젠킨스를 통해서 스크립트 원격으로 실행시키기 

 

 * 젠킨스 잡을 실행할 때, 백업할 테이블들을 젠킨스 파라미터를 통해 지정할 거면 위아래 sed문들을 추가한다. 스크립트 안 tables 변수를 파라미터대로 바꿨다가 원복하는 거....필요없으면 중간에 실행문만 날리기 

sed -i "5s/[파라미터를 통해 백업할 테이블명 나열. ex)table1 table2 table3]/${tables}/" [백업스크립트파일명]

bash [백업스크립트파일명]

sed -i "5s/${tables}/[스크립트안에서 tables변수가 원래 가지고 있던 디폴트 테이블명 나열. ex)table1 table2 table3]/" [백업스크립트파일명]

 

 

* s3에 백업해놓은 거 주기적으로 삭제하고 싶으면 참고 - S3 수명주기 

https://aws.amazon.com/ko/premiumsupport/knowledge-center/s3-empty-bucket-lifecycle-rule