SQL - SQL file ๋ฌธ์ œ

Jungminยท2022๋…„ 11์›” 10์ผ
1

SQL

๋ชฉ๋ก ๋ณด๊ธฐ
9/17

๐Ÿ“‚AWS RDS (database-1)์˜ zerobase ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋ฐฑ์—…ํ•˜๊ธฐ.

โ—AWS RDS ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋ฐฑ์—… ์‹œ ์•„๋ž˜ ์˜ต์…˜ ์ถ”๊ฐ€.
mysqldump --set-gtid-purged=OFF -h <hostname> -P <port> -u <user๋ช…> -p <database name> <file name>.sql

$ mysqldump --set-gtid-purged=OFF -h "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com" -P 3306 -u 
admin -p zerobase > backup_zerobase.sql;

๐Ÿ“‚Database (zerodb) ์ƒ์„ฑํ•˜๊ธฐ

+default character set utf8mb4

mysql> create database zerodb default character set utf8mb4;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zerobase           |
| zerodb             |
+--------------------+

๐Ÿ“‚์ƒ์„ฑํ•œ ์‚ฌ์šฉ์ž (zero@%)์— zerodb์˜ ๋ชจ๋“ ๊ถŒํ•œ ๋ถ€์—ฌํ•˜๊ธฐ

mysql> grant all on zerodb.* to 'zero'@'%';
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`@`%`   |
+----------------------------------------------------+

๐Ÿ“‚์•ž์„œ ๋ฐฑ์—…ํ•œ zerobase๋ฐฑ์—… ํŒŒ์ผ์„ zerodb์—์„œ ์‹คํ–‰

mysql> source backup_zerobase.sql;
mysql> show tables;
+------------------+
| Tables_in_zerodb |
+------------------+
| celeb |
| crime_status |
| oil_price |
| police_station |
| snl_show |
+------------------+

๐Ÿ“‚police_stationํ…Œ์ด๋ธ”์— ๋ฐ์ดํ„ฐ insertํ•˜๊ธฐ ์œ„ํ•œ ํŒŒ์ผ ์ƒ์„ฑ

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');

๐Ÿ“‚SQLํŒŒ์ผ ์‹คํ–‰ํ•˜์—ฌ AWS RDS zerobase์˜ police_station ํ…Œ์ด๋ธ”์— ๋ฐ์ดํ„ฐ insertํ•˜๊ณ  ํ™•์ธ.

mysql> source insert.sql;
mysql> select * from police_station;

๐Ÿ“‚AWS RDS zerobase์˜ police_stationํ…Œ์ด๋ธ”์„ sqlํŒŒ์ผ๋กœ ๋ฐฑ์—….

>mysqldump --set-gtid-purged=OFF -h "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com" -P 3306 -u 
admin -p zerobase police_station > backup_police.sql;
profile
๋ฐ์ดํ„ฐ๋ถ„์„ ์Šคํ„ฐ๋””๋…ธํŠธ๐Ÿงโœ๏ธ

0๊ฐœ์˜ ๋Œ“๊ธ€