SQL - .sql, Backup, Restore

Bean·2024년 2월 17일

.sql DataBackUp DataRestore TableBackup TableRestore sql_ws

Data

목록 보기

9/14

.sql 이란?

sql 쿼리들이 모여있는 파일로 실행할 여러개의 쿼리들을 한 번에 실행해준다
이러한 특징을 이용해 데이터베이스와 테이블을 백업하거나 리스토어 할 수 있다

SQL 파일 실행

로그인 이후(mysql에 접속)

mysql> source /path/filename.sql
 mysql> \. /path/filename.sql -- source 대신 \. 사용가능
 --
 mysql> \. filename.sql -- 현재 폴더에 파일이 있다면 path 생략가능
 --
 mysql> source filename.sql --mysql을 접속한 위치와 파일의 위치가 같다면 절대경로 생략 가능

---- (예제)vscode를 통해 .sql파일을 만들고 create table 쿼리문(tableA)을 작성했다고 가정
mysql -u root -p databasename -- -p에서 한칸 띄우면 해당 db로 바로 접속 가능
mysql> source filename.sql
Query OK -- sql 파일 안에 있는 테이블이 정상적으로 배포(?) 되었다면 해당 문구가 뜬다
use databasename
desc tableA; -- .sql파일에 만들어놨던 테이블이 지정한 db->table안에 들어가있음

외부에서 바로 실행

mysql -u username -p databasename < filename.sql

---- (예제)vscode를 통해 test.sql파일을 만들고 create table 쿼리문(tableB)을 작성했다고 가정
mysql -u root -p databaseA < test.sql
mysql -u root -p
use databaseA
desc tableB -- .sql파일에 만들어놨던 테이블이 지정한 db->table안에 들어가있음

mysql 경로찾기

내가 만든 database 경로를 확인할 수 있다
경로를 알아두면 터미널을 통해 SQL 워크스페이스를 만들 때 편리하다
```
 mysql -u root -p --p바로 뒤에 password
 show variables like 'datadir';
```

Database Backup

#문법(local)
mysqldump -u root(<-username) -p dbname > dbnamebackup.sql #특정 Database backup
mysqldump -u root(<-username) -p dbname --all-database > dbnamebackup.sql  #모든 Database backup

#문법(remote)
mysqldump --set-gtid-purged=off -h "AWS RSD end point" -P 3306 -u admin -p DBname backupfile.sql

AWS RDS에서 작업을 할 때(remote모드로 연결 할 때) --set-gtid-purged=off <-해당 옵션을 써야한다.
- 정확한 이유가 궁금해 멘토님께 질문드렸는데 아래와 같은 답변이 달렸다
  
  --set-gtid-purged=OFF 옵션은 MySQL에서 데이터를 백업할 때 사용하는 설정입니다. MySQL에서는 각 데이터 변경 사항(트랜잭션)을 고유한 번호로 추적할 수 있는데, 이를 GTID(글로벌 트랜잭션 식별자)라고 합니다. GTID를 사용하면 데이터 복제나 백업에서 데이터의 일관성을 유지하는 데 도움이 됩니다.
  Master 서버에서 Slave 서버로 복제할 때 사용하는 옵션으로 보시면될 것 같아요.
  Master에서 사용하면 Slave에서도 사용해야합니다.
백업 요청 시 경로는 백업 할 파일의 상위폴더가 좋다
- 예를들어) 'sql_ws > filename.sql' 이런 파일구조를 가지고 있다면, sql_ws 위치에서 백업작업을 실행하기
Database Backup 예제

mysqldump -u root -p dbname > dbnamebackup.sql -- dbname데이터를 .sql로 백업
ls -- dbnamebackup.sql 이라는 dbname의 백업파일이 생성된 것을 확인
code . -- vscode를 통해 확인해보면 sql_ws 안에 dbnamebackup.sql 백업 파일이 생성되어 있다

Database Restore

데이터베이스를 백업한 SQL File을 실행하여 그 시점으로 복구하거나 이전할 수 있다

Database Restore 예제

# AWS RDS(database-1) 서비스가 사용 가능한 상태에서 접속
mysql -h "database-1.c~~~rds.amazonaws.com" -P 3306 -u admin -p
# 백업파일을 재배치할 데이터베이스로 이동
mysql> use mydb;
# 백업한 파일을 해당 데이터베이스에서 실행, AWS RDS(database-1)의 dbname(위 예제) Database를 복원
mysql> source dbnamebackup.sql
query OK, 0 rows affected (0.01 sec) --쿼리가 성공적으로 실행중임을 보여줌
query OK, 0 rows affected (0.02 sec)
...
# 결과확인
mysql> show tables;
# Database Backup 과정부터 이렇게 local에서 만든 database를 remote에 정상적으로 restore할 수 있다(상태가 같아짐)

Table Backup

Table 단위로도 Backup할 수 있다

-- 문법
mysqldump -u root -p dbname tablename > backup.sql

-Table Backup 예제

# 아래 코드 실행 후 sql_ws안에 back.sql 생성되었는지 확인
sql_ws% mysqldump -u root -p  dbname tablename > backup.sql

Table Restore

테이블을 백업한 SQL File을 실행하여 해당 테이블을 복구하거나 이전할 수 있다

Table Restore 예제

# AWS RDS(database-1)의 dbname에서 myTable 삭제
# 위에서 DB restore하며 myTable도 같이 재배치 되어 임의로 삭제하고 예제 진행
sql_ws% mysql -h "database-1.c5y2s46i0az1.us-east-2.rds.amazonaws.com" -P 3306 -u admin -p dbname --db바로 실행
mysql> drop table myTable;
# AWS RDS(database-1)의 dbname에서 myTable의 backup.sql 복구
mysql> source backup.sql

아래와 같은 에러가 발생하는 경우는?

ERROR: 
ASCII '\0' appeared in the statement, but this is not allowed unless option -- binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: '?-'.
Bye

local에서 backup할 때 mysqldump를 제대로 기입하지 않았거나 테이블명이 오타가 있었음
그럼에도 back.sql이 생성되었는데 삭제 후 local 접속 -> Table Backup -> remote 접속(aws)
-> Table Restore 정상적으로 쿼리가 작동했다

Table Schema Backup

데이터를 제외하고 테이블 생성 쿼리만 백업할 수 있다

-- 문법
-- 여기서 -d는 데이터만 빼고 Only Table Schema copy
mysqldump -d -u username -p DBname Tablename > backup.sql  -- 특정 Table Schema Backup
mysqldump -d -u username -p DBname > backup.sql  -- 모든 Table Schema Backup

Table Schema Backup 예제
```
-- Local Database-> myDB - mytable의 Table Schema 백업
sql_ws % mysqldump -d -u root -p myDB mytable > tablebackup.sql
```
위처럼 데이터가 들어가야 되는 부분에 데이터만 빠지고 backup된 것을 확인할 수 있다