SQL - [AWS, SQL File, Backup, Restore]

솔비·2024년 1월 22일

sql study note

📚 SQL. 관계형 데이터베이스

목록 보기

7/19

AWS MySQL 실습

윈도우의 경우 환경변수에 Path를 등록한 후
cmd 창에서 mySQL 명령어를 사용할 수 있다.

참고링크 | https://hoho325.tistory.com/163

접속

mysql -h "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p

+password 입력

Database (zerobase) 를 생성하고 확인하세요.
(DEFAULT CHARACTER SET utf8mb4)

CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4;

여담으로 MB4는 이모티콘까지 대응 가능한 거라나 뭐라나

사용자(zero) 를 추가하고 확인하세요.

CREATE USER 'zero'@'%' IDENTIFIED BY '1234';

user TABLE이 있는 mysql로 이동 후 user 목록확인

mysql> USE mysql;
Database changed
mysql> SHOW TABLES;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| rds_configuration                                    |
| rds_global_status_history                            |
| rds_global_status_history_old                        |
| rds_heartbeat2                                       |
| rds_history                                          |
| rds_replication_status                               |
| rds_reserved_users                                   |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
44 rows in set (0.15 sec)

mysql> SELECT host, user FROM user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | admin            |
| %         | zero             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | rdsadmin         |
+-----------+------------------+
6 rows in set (0.15 sec)

사용자(zero)에게 외부에서 zerobase 에 접근해서 사용하기 위한 권한을 부여하세요.

#현재 권한 확인
mysql> SHOW GRANTS FOR 'zero'@'%';
+----------------------------------+
| Grants for zero@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `zero`@`%` |
+----------------------------------+
1 row in set (0.15 sec)

#권한부여
mysql> GRANT ALL ON zerobase.* TO 'zero'@'%';
Query OK, 0 rows affected (0.15 sec)

#부여된 권한 확인
mysql> SHOW GRANTS FOR 'zero'@'%';
+----------------------------------------------------+
| Grants for zero@%                                  |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `zero`@`%`                   |
| GRANT ALL PRIVILEGES ON `zerobase`.* TO `zero`@`%` |
+----------------------------------------------------+
2 rows in set (0.15 sec)

현재 접속을 종료하고 AWS RDS (database-1)의 zerobase 에 zero 로 접속하세요.

# 접속종료
mysql> EXIT
Bye

#AWS RDS 접속
C:\Users\solbi>mysql -h "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.35 Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#zerobase 접속
mysql> USE zerobase;
Database changed

🧷 현재 접속 종료
EXIT

SQL File

sql 확장자를 가진 파일
sql 파일 내용으로는 sql 쿼리들이 모여 있음
sql 파일 안에 실행 여러 쿼리를 다 작성한 다음
sql 파일을 실행해서 한꺼번에 실행!하는 용도
특징을 이용해서 backup, restore 하고 있음

sql_ws 파일 생성 후 VS CODE 실행

# 파일을 생성할 경로로 이동
C:\Users\solbi>CD OneDrive
C:\Users\solbi\OneDrive>CD Documents

# sql_ws 폴더생성
C:\Users\solbi\OneDrive\Documents>mkdir sql_ws;

# sql_ws 폴더이동
C:\Users\solbi\OneDrive\Documents>CD sql_ws

#VScode 실행
C:\Users\solbi\OneDrive\Documents\sql_ws>code .

🧷 폴더생성
MKDIR foder_name

파일생성 (확장자 .sql)

터미널로 mysql의 zerobase로 접속

mysql -u root -p zerobase
Enter password: ****

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zerobase           |
+--------------------+
5 rows in set (0.05 sec)

VS CODE에서는 터미널을 바로 실행 시킬 수 있음

TEST01.sql 파일에 police_station 테이블생성

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| police_station     |
| snl_show           |
| test1              |
| test2              |
+--------------------+
5 rows in set (0.01 sec)

mysql> desc police_station;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(16)  | YES  |     | NULL    |       |
| address | varchar(128) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

SQL File 실행

mysql> source TEST01.sql
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| police_station     |
| snl_show           |
| test1              |
| test2              |
+--------------------+
5 rows in set (0.01 sec)

TEST02.sql 파일에 crime_status 테이블생성
SQL File 실행

mysql> source TEST02.sql
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| crime_status       |
| police_station     |
| snl_show           |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> desc police_station;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(16)  | YES  |     | NULL    |       |
| address | varchar(128) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year           | year        | YES  |     | NULL    |       |
| police_station | varchar(8)  | YES  |     | NULL    |       |
| crime_type     | varchar(16) | YES  |     | NULL    |       |
| status_type    | char(2)     | YES  |     | NULL    |       |
| case_number    | int         | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

🧷 cmd에서 sql접속
mysql -u root -p db.name
🧷 SQL 파일실행

Database Backup

🧷 데이터베이스 백업방법

# cmd에서 경로까지 이동
C:\Users\solbi>cd OneDrive
C:\Users\solbi\OneDrive>cd Documents
C:\Users\solbi\OneDrive\Documents>cd sql_ws

# 백업
C:\Users\solbi\OneDrive\Documents\sql_ws>mysqldump -u root -p zerobase > zerobase.sql
Enter password: ****

# 리스트확인 (맥의경우 ls)
C:\Users\solbi\OneDrive\Documents\sql_ws>dir
 Volume in drive C has no label.
 Volume Serial Number is C05F-E84E

 Directory of C:\Users\solbi\OneDrive\Documents\sql_ws

2024-01-22  오후 02:56    <DIR>          .
2024-01-22  오후 01:34    <DIR>          ..
2024-01-22  오후 01:42                83 TEST01.sql
2024-01-22  오후 02:20               160 TEST02.sql
2024-01-22  오후 02:56             6,377 zerobase.sql
               3 File(s)          6,620 bytes
               2 Dir(s)  387,658,420,224 bytes free

C:\Users\solbi\OneDrive\Documents\sql_ws>code .

Database Restore

데이터베이스를 백업한 sql file을 실행하여 그 시점으로 복구하거나 이전할 수 있음
SQL File 실행 방법과 동일
(따로 복구하는 방법이 있는게 아니고 sql file을 실행하면 복구한것)

#AWS RDS 접속
mysql -h "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p
Enter password: ************



mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zerobase           |
+--------------------+
5 rows in set (0.15 sec)

#복구하고자 하는 데이터베이스 사용
mysql> use zerobase;
Database changed

#SQL File 실행
mysql> source zerobase.sql

Table Backup

🧷 테이블 백업방법

테이블 백업

C:\Users\solbi\OneDrive\Documents\sql_ws>mysql -u root -p zerobase celeb > celeb.sql

파일 생성확인

Table Restore

AWS 접속 후 zerobase 접속

mysql -h "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p

mysql> use zerobase;
Database changed

celeb테이블 백업

mysql> use zerobase
Database changed
mysql> source celeb.sql

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| crime_status       |
| police_station     |
| snl_show           |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.15 sec)

📌 ERROR 1064 (42000): You have an error in your SQL syntax;
ㅋ.. 해결방법 : https://velog.io/@jaam_mini/SQL-Advanced-DatabaseTable-Backup-Restore

Table Schema Backup

🧷 테이블 스키마 백업방법

snl_show 테이블 백업

C:\Users\solbi\OneDrive\Documents\sql_ws>mysqldump -d -u root -p zerobase snl_show > snl.sql
Enter password: ****

연습문제

AWS RDS (database-1) 의 zerobase 데이터베이스를 백업하세요.
⛔ 주의> AWS RDS database 를 백업할 경우 다음의 옵션을 추가합니다.

mysqldump --set-gtid-purged=OFF -h "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p[비번] zerobase > backup_zerobase.sql

AWS RDS (database-1) 에 admin 계정으로 로그인하세요.

mysql -h "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p
Enter password: ************

Database (zerodb) 를 생성하세요.
(DEFAULT CHRACTER SET utf8mb4)

mysql> CREATE DATABASE zerodb DEFAULT CHARACTER SET utf8mb4;

앞서 생성한 사용자 (zero@%) 에게 zerodb 의 모든 권한을 부여하세요

mysql> GRANT ALL ON zerodb.* TO 'zero'@'%';

앞서 백업한 zerobase 백업 파일을 zerodb 에서 실행하세요.

mysql> USE zerodb;
Database changed
mysql> SOURCE backup_zerobase.sql

SQL 파일을 실행하여 AWS RDS (database-1) zerobase 의 police_station 테이블에 데이터를 INSERT 하고 확인하세요.

mysql> use zerobase;
Database changed
mysql> source INSERT.sql

⛔ 꼭 저장 후 source 할것 !!

AWS RDS (database-1) zerobase 의 police_station 테이블을 SQL 파일로 백업하세요.

mysqldump --set-gtid-purged=OFF -h "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p[비밀번호] zerobase police_station > backup_police.sql

SQL 파일을 실행하여 AWS RDS (database-1) zerodb 의 police_station 테이블을 zerobase 와 동일하게 만들고 확인하세요

# AWS RDS 접속
mysql -h "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p
Enter password: ************

#zerodb의 table 확인
mysql> use zerodb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_zerodb |
+------------------+
| celeb            |
| crime_status     |
| police_station   |
| snl_show         |
| test1            |
| test2            |
+------------------+

# 테이블 데이터 확인
mysql> select * from police_station;
Empty set (0.15 sec)

# police_station에 데이터를 insert하는 쿼리가 담긴
# INSERT.sql 파일 실행
mysql> source INSERT.sql

# 변경된 테이블 데이터 확인
mysql> select * from police_station;
+--------------------------+-----------------------------------------+
| name                     | address                                 |
+--------------------------+-----------------------------------------+
| 서울특별시경찰청         | 서울시 종로구 사직로8길 31              |
| 서울중부경찰서           | 서울특별시 중구 수표로 27               |
| 서울종로경찰서           | 서울특별시 종로구 율곡로 46             |
| 서울남대문경찰서         | 서울특별시 중구 한강대로 410            |
+--------------------------+-----------------------------------------+