[2021.07.30] 인턴 +151 How to extract data stored in mysql?(Not Using WorkBench)

[2021.07.30] 인턴 +151  How to extract data stored in mysql?(Not Using WorkBench) 

 

 

 

이 게시글은, 데이터 베이스에 저장된 데이터를 csv 파일로 export 하는 방법이다.

인턴기간 내에, DB에 저장된 데이터를 추출하여, 백업할 일은 없을줄 알았지만 우연히, ELK(AWS-EC2)에 있는 mysql 서버가 이상해서, 데이터 추출 후 새로운 서버에 백업하려고 한다. 아래에 Mysql 서버에 대한 문제점을 게시해보겠다.

 

 

mysql 서버 문제점

- Linux(Ubuntu20.04) 에서는 mysql이 잘 들어가짐

- 그러나, 사용자를 만든 후, 권한을 부여한 후에 flush를 했지만, workbench로는 원격접속을 못하는 상황임 (당연히 port도 열어논 상태)

 

 

* 필요한 프로그렘

- 파일 질라 (FTP)

- Git Bash (ssh 접속프로그램)

 

 

 

---------------------------------------------------------------------------------------------------------------------

 DB에 저장된 데이터들을 CSV로 추출하는 방법

sudo mysql -u root -p

 

mysql> use DATA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

에러발생1 

mysql> TABLE air_quality INTO OUTFILE '/home/ubuntu/backup/airbackup.csv' FIELDS TERMINATED BY ",";

-> 해당 Query 적용 시 아래와 같은 에러 발생

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

 

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

-> 위의 명령어를 사용하여, 출력된 경로(PATH)를 OUTFILE 뒤에 붙여주면 됨.

 

 

에러발생2

mysql> TABLE air_quality INTO OUTFILE '/var/lib/mysql-files/airbackup.csv' FIELDS TERMINATED BY ",";

-> 해당 Query 적용 시 아래와 같은 에러 발생 

ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql-files/airbackup.csv' (OS errno 13 - Permission denied)

해결방법

sudo chmod 777 /var/lib/mysql-files/  
sudo chown -R ubuntu:ubuntu /var/lib/mysql-files/

-> 이 방법으로 위의 에러 해결 . 

 

 

 

 

★★★★★ 최종 성공 ★★★★★

mysql> TABLE air_quality INTO OUTFILE '/var/lib/mysql-files/airbackup2.csv' FIELDS TERMINATED BY ",";
Query OK, 129199 rows affected (0.44 sec)

mysql> TABLE blood_pressure_sugar INTO OUTFILE '/var/lib/mysql-files/blood_pressure_sugar.csv' FIELDS TERMINATED BY ",";
Query OK, 1000000 rows affected (1.36 sec)

mysql> TABLE blood_test INTO OUTFILE '/var/lib/mysql-files/blood_test.csv' FIELDS TERMINATED BY ",";
Query OK, 1000000 rows affected (1.55 sec)

mysql> TABLE waist INTO OUTFILE '/var/lib/mysql-files/waist.csv' FIELDS TERMINATED BY ",";
Query OK, 960 rows affected (0.01 sec)

 

+ 파일 질라 에러 (Linux -> Local PC로 데이터 보낼 때)

상태: /var/lib/mysql-files/airbackup.csv 다운로드 시작
명령: get "airbackup.csv" "C:\Users\injekim97\Desktop\0730 AWS-ELK BACK UP SERVER\airbackup.csv"
오류: /var/lib/mysql-files/airbackup.csv: open for read: permission denied
오류: 파일 전송 실패

 

sudo chown -R ubuntu:ubuntu /var/lib/mysql-files/

->  위의 명령어를 사용했음에도 불구하면, 계속 위의 명령어 쳐주면 됨

* 파일 한번 보낼 때마다, 해당 명령어를 사용