SQL - (Advanced) Database/Table Backup Restore + 예제

jaam._.mini·2023년 12월 28일
0

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

목록 보기
13/23
post-thumbnail

  • 어제 drop으로 날렸던 celeb 만들기

    mysql> create table celeb(
    -> ID int not null auto_increment primary key,
    -> NAME varchar(32) not null default '',
    -> BIRTHDAY date,
    -> AGE int,
    -> SEX CHAR,
    -> JOB_TITLE varchar(32),
    -> AGENCY varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| crime_status       |
| oil_price          |
| person             |
| police_station     |
| refueling          |
+--------------------+
6 rows in set (0.00 sec)

mysql> select * from celeb;
Empty set (0.00 sec)

mysql> DESC CELEB;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| ID        | int         | NO   | PRI | NULL    | auto_increment |
| NAME      | varchar(32) | NO   |     |         |                |
| BIRTHDAY  | date        | YES  |     | NULL    |                |
| AGE       | int         | YES  |     | NULL    |                |
| SEX       | char(1)     | YES  |     | NULL    |                |
| JOB_TITLE | varchar(32) | YES  |     | NULL    |                |
| AGENCY    | varchar(32) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> INSERT INTO CELEB VALUES(1,'아이유','1993-05-16',29,'F','가수,텔런트','EDAM엔터테이먼트');        ;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO CELEB VALUES(2,'이미주','1994-09-23',28,'F','가수','울림엔터테이먼트');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO CELEB VALUES(3,'송강','1994-04-23',28,'M','텔런트','나무엑터스');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO CELEB VALUES(4,'강동원','1981-01-18',41,'M','영화배우, 텔런트','YG엔터테이먼트');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO CELEB VALUES(5,'유재석','1972-08-14',50,'M','MC, 개그맨','안테나');
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO CELEB VALUES(6,'차승원','1970-06-07',48,'M','영화배우, 모델','YG엔터테이먼트');
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO CELEB VALUES(7,'이수현','1999-05-04',23,'F','가수','YG엔터테이먼트');
Query OK, 1 row affected (0.00 sec)

mysql> select * from celeb;
+----+--------+------------+------+------+------------------+------------------+
| ID | NAME   | BIRTHDAY   | AGE  | SEX  | JOB_TITLE        | AGENCY           |
+----+--------+------------+------+------+------------------+------------------+
|  1 | 아이유 | 1993-05-16 |   29 | F    | 가수,텔런트      | EDAM엔터테이먼트 |
|  2 | 이미주 | 1994-09-23 |   28 | F    | 가수             | 울림엔터테이먼트 |
|  3 | 송강   | 1994-04-23 |   28 | M    | 텔런트           | 나무엑터스       |
|  4 | 강동원 | 1981-01-18 |   41 | M    | 영화배우, 텔런트 | YG엔터테이먼트   |
|  5 | 유재석 | 1972-08-14 |   50 | M    | MC, 개그맨       | 안테나           |
|  6 | 차승원 | 1970-06-07 |   48 | M    | 영화배우, 모델   | YG엔터테이먼트   |
|  7 | 이수현 | 1999-05-04 |   23 | F    | 가수             | YG엔터테이먼트   |
+----+--------+------------+------+------+------------------+------------------+
7 rows in set (0.00 sec)

  • 어제 drop으로 날렸던 snl_show 만들기

    mysql> CREATE TABLE SNL_SHOW
    -> (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> SEASON INT NOT NULL,
    -> EPISODE INT NOT NULL,
    -> BROADCAST_DATE DATE,
    -> HOST VARCHAR(32) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC SNL_SHOW;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| ID             | int         | NO   | PRI | NULL    | auto_increment |
| SEASON         | int         | NO   |     | NULL    |                |
| EPISODE        | int         | NO   |     | NULL    |                |
| BROADCAST_DATE | date        | YES  |     | NULL    |                |
| HOST           | varchar(32) | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> insert into SNL_SHOW values (1,8,7, '2020-09-05', '강동원');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SNL_SHOW values (2,8,8, '2020-09-12', '유재석');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SNL_SHOW values (3,8,9,'2020-09-19','차승원');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SNL_SHOW values (4,8,10,'2020-09-26','이수현');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SNL_SHOW values (5,9,1,'2021-09-04','이병헌');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SNL_SHOW values (6,9,2,'2021-09-11', '하지원');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SNL_SHOW values (7,9,3,'2021-09-18','제시');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SNL_SHOW values (8,9,4,'2021-09-25','조정석');
Query OK, 1 row affected (0.00 sec)

mysql> insert into SNL_SHOW values(9,9,5,'2021-10-02','조여정');
Query OK, 1 row affected (0.00 sec)

mysql> select * from snl_show;
+----+--------+---------+----------------+--------+
| ID | SEASON | EPISODE | BROADCAST_DATE | HOST   |
+----+--------+---------+----------------+--------+
|  1 |      8 |       7 | 2020-09-05     | 강동원 |
|  2 |      8 |       8 | 2020-09-12     | 유재석 |
|  3 |      8 |       9 | 2020-09-19     | 차승원 |
|  4 |      8 |      10 | 2020-09-26     | 이수현 |
|  5 |      9 |       1 | 2021-09-04     | 이병헌 |
|  6 |      9 |       2 | 2021-09-11     | 하지원 |
|  7 |      9 |       3 | 2021-09-18     | 제시   |
|  8 |      9 |       4 | 2021-09-25     | 조정석 |
|  9 |      9 |       5 | 2021-10-02     | 조여정 |
+----+--------+---------+----------------+--------+
9 rows in set (0.00 sec)

  1. database 'zerobase' 생성 & 위 데이터들 백업/저장

    C:\Users\PC\Documents\sql_ws> mysqldump -u root -p zerobase > zerobase.sql
Enter password: *******

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zerobase           |
| zerodb             |
+--------------------+
6 rows in set (0.16 sec)

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

  2. database 'zerobase' - table 'celeb'

  • 백업

    C:\Users\PC\Documents\sql_ws> mysqldump -u root -p zerobase celeb > celeb.sql
Enter password: *******

  • AWS RDS 에 옮기는 작업

    mysql> drop table celeb;
Query OK, 0 rows affected (0.16 sec)

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| crime_status       |
| oil_price          |
| person             |
| police_station     |
| refueling          |
| snl_show           |
| test1              |
| test2              |
+--------------------+
8 rows in set (0.15 sec)

mysql> source celeb.sql
Query OK, 0 rows affected (0.15 sec)

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

mysql> desc celeb;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| ID        | int         | NO   | PRI | NULL    | auto_increment |
| NAME      | varchar(32) | NO   |     |         |                |
| BIRTHDAY  | date        | YES  |     | NULL    |                |
| AGE       | int         | YES  |     | NULL    |                |
| SEX       | char(1)     | YES  |     | NULL    |                |
| JOB_TITLE | varchar(32) | YES  |     | NULL    |                |
| AGENCY    | varchar(32) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
7 rows in set (0.15 sec)

mysql> select * from celeb;
+----+--------+------------+------+------+------------------+------------------+
| ID | NAME   | BIRTHDAY   | AGE  | SEX  | JOB_TITLE        | AGENCY           |
+----+--------+------------+------+------+------------------+------------------+
|  1 | 아이유 | 1993-05-16 |   29 | F    | 가수,텔런트      | EDAM엔터테이먼트 |
|  2 | 이미주 | 1994-09-23 |   28 | F    | 가수             | 울림엔터테이먼트 |
|  3 | 송강   | 1994-04-23 |   28 | M    | 텔런트           | 나무엑터스       |
|  4 | 강동원 | 1981-01-18 |   41 | M    | 영화배우, 텔런트 | YG엔터테이먼트   |
|  5 | 유재석 | 1972-08-14 |   50 | M    | MC, 개그맨       | 안테나           |
|  6 | 차승원 | 1970-06-07 |   48 | M    | 영화배우, 모델   | YG엔터테이먼트   |
|  7 | 이수현 | 1999-05-04 |   23 | F    | 가수             | YG엔터테이먼트   |
+----+--------+------------+------+------+------------------+------------------+
7 rows in set (0.15 sec)
  1. database 'zerobase' - table 'snl_show'

  • 스키마 백업

    mysql> exit
Bye

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

  1. database 'zerobase' - table 'police_station' 생성

    mysql> create database zerobase default character set utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;

mysql> use zerobase
Database changed
mysql> source test01.sql
Query OK, 0 rows affected (0.01 sec)

ysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| police_station     |
+--------------------+
1 row in set (0.00 sec)

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

  2. database 'zerobase' - table 'crime_status' 생성
    💡 mysql> -u root -p zerobase < test02.sql → zerobase(DB)에 test02.sql 파일을 바로 넣어서 실행해줘
    ❗위 명령어가 정상 작동하지 않아, source 를 이용

    mysql> use zerobase
Database changed
mysql> source test02.sql 
Query OK, 0 rows affected (0.01 sec)

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

📋실습

  1. AWS RDS (database-1)의 zerobase 데이터베이스 백업하기
C:\Users\PC\Documents\sql_ws> mysqldump --set-gtid-purged=OFF -h "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p94woals!! zerobase > backup_zerobase.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.
C:\Users\PC\Documents\sql_ws> mysql -h "database-1.cj22sogoe8oa.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 55
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.

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

mysql> create database zerodb default character set utf8mb4;
Query OK, 1 row affected (0.14 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zerobase           |
| zerodb             |
+--------------------+
6 rows in set (0.14 sec)
mysql> grant all on zerodb.* 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`@`%` |
| GRANT ALL PRIVILEGES ON `zerodb`.* TO `zero`@`%`   |
+----------------------------------------------------+
3 rows in set (0.15 sec)
mysql> use zerodb;
Database changed

mysql> show tables;
Empty set (0.15 sec)

mysql> source backup_zerobase.sql;
Query OK, 0 rows affected (0.15 sec)

mysql> show tables;
+------------------+
| Tables_in_zerodb |
+------------------+
| celeb            |
| crime_status     |
| oil_price        |
| person           |
| police_station   |
| refueling        |
| snl_show         |
| test1            |
| test2            |
+------------------+
9 rows in set (0.15 sec)
  • insert.sql 파일 직접 만들고
  • 데이터 입력
  INSERT INTO police_station VALUES('서울특별시경찰청','서울시 종로구 사직로8길 31');
  INSERT INTO police_station VALUES('서울중부경찰서','서울특별시 중구 수표로 27');
  INSERT INTO police_station VALUES('서울종로경찰서','서울특별시 종로구 율곡로 46');
  INSERT INTO police_station VALUES('서울남대문경찰서','서울특별시 중구 한강대로 410');
  • table 'zerobase' 내 'police_station'에 'insert.sql'쿼리문을 넣어 작동시켜주려 함
  mysql> use zerobase;
  Database changed
  mysql> show tables;
  +--------------------+
  | Tables_in_zerobase |
  +--------------------+
  | celeb              |
  | crime_status       |
  | oil_price          |
  | person             |
  | police_station     |
  | refueling          |
  | snl_show           |
  | test1              |
  | test2              |
  +--------------------+
  9 rows in set (0.14 sec)

  mysql> select * from police_station;  
  Empty set (0.14 sec)

  mysql> source insert.sql;
  Query OK, 1 row affected, 3 warnings (0.15 sec)

  Query OK, 1 row affected, 4 warnings (0.15 sec)

  Query OK, 1 row affected, 4 warnings (0.15 sec)

  Query OK, 1 row affected, 4 warnings (0.15 sec)

  mysql> select * from police_station;
  +-----------------------+----------------------------------+
  | name                  | adress                           |
  +-----------------------+----------------------------------+
  | ?쒖슱?밸퀎?쒓꼍李곗껌 | ?쒖슱??醫낅줈援??ъ쭅濡?湲?31    |
  | ?쒖슱以묐?寃쎌같?     | ?쒖슱?밸퀎??以묎뎄 ?섑몴濡?27    |
  | ?쒖슱醫낅줈寃쎌같?    | ?쒖슱?밸퀎??醫낅줈援??④끝濡?46  |
  | ?쒖슱?⑤?臾멸꼍李곗꽌 | ?쒖슱?밸퀎??以묎뎄 ?쒓컯??줈 410 |
  +-----------------------+----------------------------------+
  4 rows in set (0.15 sec)

🙄 응? 한글이 왜 저래....?

mysql 실행 시 --default-character-set utf8mb4 옵션을 추가해서 실행한 다음 source insert.sql 다시 실행

mysql> exit
Bye

C:\Users\PC\Documents\sql_ws> mysqldump --set-gtid-purged=OFF -h "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p zerobase police_station > backup_police.sql
Enter password: *********

9.

mysql> use zerodb
Database changed
mysql> show tables;
+------------------+
| Tables_in_zerodb |
+------------------+
| celeb            |
| crime_status     |
| oil_price        |
| person           |
| police_station   |
| refueling        |
| snl_show         |
| test1            |
| test2            |
+------------------+
9 rows in set (0.15 sec)

mysql> select * from police_station;
Empty set (0.16 sec)

mysql> source insert.sql;
Query OK, 1 row affected (0.15 sec)

Query OK, 1 row affected (0.16 sec)

Query OK, 1 row affected (0.15 sec)

Query OK, 1 row affected (0.15 sec)

mysql> select * from police_station;
+--------------------------+-----------------------------------------+
| name                     | adress                                  |
+--------------------------+-----------------------------------------+
| 서울특별시경찰청         | 서울시 종로구 사직로8길 31              |
| 서울중부경찰서           | 서울특별시 중구 수표로 27               |
| 서울종로경찰서           | 서울특별시 종로구 율곡로 46             |
| 서울남대문경찰서         | 서울특별시 중구 한강대로 410            |
+--------------------------+-----------------------------------------+
4 rows in set (0.15 sec)
profile
jaam._.mini
비전공자의 데이터 공부법
이전 포스트

SQL - (Advanced) Database/Table Backup Restore + 예제

다음 포스트

SQL - (Advanced) Python + MySQL

0개의 댓글