어제 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)
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
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)
스키마 백업
mysql> exit
Bye
C:\Users\PC\Documents\sql_ws>mysqldump -d -u root -p zerobase snl_show > snl.sql
Enter password: *******
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)
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)
📋실습
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 INTO police_station VALUES('서울특별시경찰청','서울시 종로구 사직로8길 31');
INSERT INTO police_station VALUES('서울중부경찰서','서울특별시 중구 수표로 27');
INSERT INTO police_station VALUES('서울종로경찰서','서울특별시 종로구 율곡로 46');
INSERT INTO police_station VALUES('서울남대문경찰서','서울특별시 중구 한강대로 410');
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)