๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
Trouble Shooting

AWS RDS MySQL 8 ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„ ์•ˆํ•˜๋„๋ก ์„ค์ •ํ•˜๊ธฐ(Downgrade MySQL 5)

by ์ฃผ๋ฐœ2 2021. 4. 16.
๋ฐ˜์‘ํ˜•

 ์•ˆ๋…•ํ•˜์„ธ์š”~ ์ด์ „์— ์šด์˜ํ•˜๋˜ ๋ธ”๋กœ๊ทธ ๋ฐ GitHub, ๊ณต๋ถ€ ๋‚ด์šฉ์„ ์ •๋ฆฌํ•˜๋Š” Study-GitHub ๊ฐ€ ์žˆ์Šต๋‹ˆ๋‹ค!

 ๋„ค์ด๋ฒ„ ๋ธ”๋กœ๊ทธ

 GitHub

Study-GitHub

 ๐Ÿ”


 

โœ” AWS RDS MySQL8 ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„ ์•ˆํ•˜๋„๋ก ์„ค์ •

 

์•ˆ๋…•ํ•˜์„ธ์š”, ์ด๋ฒˆ์— ์ •๋ฆฌํ•  ๋‚ด์šฉ์€ AWS RDS์— MySQL ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ƒ์„ฑํ–ˆ์„ ๋•Œ, 

๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„์„ ์„ค์ •ํ•˜๋Š” ๋ฐฉ๋ฒ•์— ๋Œ€ํ•ด ์•Œ์•„๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

 

2022.07.08 ์ถ”๊ฐ€)

์•„๋ž˜์™€ ๊ฐ™์€ ๋Œ“๊ธ€์„ ๋‚จ๊ฒจ์ฃผ์…”์„œ ์ฐธ๊ณ ํ•ด๋ณด์‹œ๋ฉด ์ข‹์„ ๋“ฏ ํ•ฉ๋‹ˆ๋‹ค. ๊ณต์œ ํ•ด์ฃผ์…”์„œ ๊ฐ์‚ฌํ•ฉ๋‹ˆ๋‹ค. ๐Ÿ‘ผ

โ€ป ๊ฐ™์€ ๋ฌธ์ œ๋กœ ์ด ๊ธ€์„ ๊ฒ€์ƒ‰ํ•˜๊ฒŒ ๋˜์—ˆ๋Š”๋ฐ์š”. ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน ๋ณ€๊ฒฝ์€ ์•ˆ๋˜์ง€๋งŒ ์ฒ˜์Œ DB๋ฅผ ์ƒ์„ฑ ํ•  ๋•Œ ๋ถ€ํ„ฐ lower_case_table_names = 1์œผ๋กœ ์„ค์ •๋œ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน์„ ์‚ฌ์šฉํ•˜๋ฉด MySQL 8 RDS์—์„œ๋„ ํ…Œ์ด๋ธ” ์กฐํšŒ์‹œ ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„ ์ž˜ ๋ฉ๋‹ˆ๋‹ค.

 

 

โ€ป ๊ฒฐ๋ก ์„ ๋ฏธ๋ฆฌ ๋ง์”€๋“œ๋ฆฌ๋ฉด, MySQL 8๋ฒ„์ „์—๋Š” lower_case_table_names ์˜ ๊ฐ’์„ 0๋งŒ ์ง€์›ํ•œ๋‹ค๊ณ  ํ•ฉ๋‹ˆ๋‹ค.

https://forums.aws.amazon.com/thread.jspa?messageID=918406

0์€, ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„์„ ํ•˜๋„๋ก default ๊ฐ’์ž…๋‹ˆ๋‹ค ..

๋”ฐ๋ผ์„œ ์•„๋ž˜ ๋‚ด์šฉ๋“ค์€ ๋ชจ๋‘ ์‚ฝ์งˆ์˜ ๊ณผ์ •์ด๋ฉฐ, ์ €์˜ ๊ฒฝ์šฐ MySQL 5๋ฒ„์ „๋Œ€๋กœ DB๋ฅผ ๋‹ค์‹œ ์ƒ์„ฑํ–ˆ์Šต๋‹ˆ๋‹ค. ๐Ÿ˜ฅ๐Ÿ˜ฅ


 

 

 

 

 

๐ŸŽต ๋จผ์ € ๋‹ค์Œ ๋ช…๋ น์–ด๋ฅผ ํ†ตํ•ด ํ˜„์žฌ ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„ ์„ค์ •๊ฐ’์„ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค.

 

SHOW VARIABLES LIKE 'lower_case_table_names';

  • lower_case_table_names๊ฐ€  0 ์ผ ๊ฒฝ์šฐ ๋Œ€์†Œ๋ฌธ์ž๋ฅผ ๊ตฌ๋ถ„ํ•ฉ๋‹ˆ๋‹ค.
  • lower_case_table_names๊ฐ€ 1์ผ ๊ฒฝ์šฐ ๋Œ€์†Œ๋ฌธ์ž๋ฅผ ๊ตฌ๋ถ„ํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

๋”ฐ๋ผ์„œ ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„์„ ํ•˜์ง€์•Š๋„๋ก ์„ค์ •ํ•˜๋ ค๋ฉด ์œ„์˜ ๊ฐ’์„ 1๋กœ ๋ณ€๊ฒฝํ•ด์ฃผ์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

 

๋งŒ์•ฝ ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„์„ ํ•˜๋„๋ก ์„ค์ •์ด ๋˜์–ด์žˆ์œผ๋ฉด, ์œ„์™€ ๊ฐ™์ด ๋Œ€์†Œ๋ฌธ์ž๊ฐ€ ํ‹€๋ฆด๊ฒฝ์šฐ ์กฐํšŒ๊ฐ€ ๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

 

 

 

 

 

AWS RDS > ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค > DB ์‹๋ณ„์ž๋ฅผ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

๊ตฌ์„ฑ > ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน์˜ DB๋ฅผ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

ํŒŒ๋ผ๋ฏธํ„ฐ์— lower ๋กœ ๊ฒ€์ƒ‰์„ ํ•œ ๋’ค lower_case_table_names๋ฅผ ์ฒดํฌํ•˜๊ณ  ํŒŒ๋ผ๋ฏธํ„ฐ ํŽธ์ง‘์„ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

 

๋‹ค์Œ๊ณผ ๊ฐ™์ด ๊ฐ’์— 1์„ ์ž‘์„ฑํ•˜๊ณ  ๋ณ€๊ฒฝ ์‚ฌํ•ญ ์ €์žฅ์„ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

The parameter value for lower_case_table_names can't be changed for parameter group one-line-rds, because it is associated with one or more MySQL 8.0 DB instances. (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterCombination; Request ID: 06273803-ee8c-4926-9851-ef8ae76c8f59; Proxy: null)

 

 

์ €์žฅ์„ ํ•˜๋ฉด ์œ„์™€ ๊ฐ™์ด lower_case_table_names๋Š” ๋ณ€๊ฒฝํ•  ์ˆ˜ ์—†๋‹ค๋Š” ์˜ค๋ฅ˜๊ฐ€ ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค... ๐Ÿ˜ข

 

 

 

 

 

๐ŸŽ— ํ•ด๊ฒฐ์ฑ…์€ ?

lower_case_table_names๋Š” ๋ณ€๊ฒฝํ•  ์ˆ˜ ์—†๋Š” ๋ถ€๋ถ„์ด์–ด์„œ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน์„ ์ƒˆ๋กœ ์ƒ์„ฑํ•˜๊ณ  ์„ค์ •์„ ํ•ด์•ผํ•ฉ๋‹ˆ๋‹ค.. ๐Ÿคฌ

 

 

 

 

ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน์—์„œ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน ์ƒ์„ฑ์„ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

๊ทธ๋ฃน ์ด๋ฆ„ ๋ฐ ์„ค๋ช… ์„ ์ž‘์„ฑํ•˜๊ณ  ์ƒ์„ฑ์„ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

์ƒ์„ฑ๋œ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน์„ ์ฒดํฌํ•˜๊ณ , ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน ์ž‘์—… > ํŽธ์ง‘ ์„ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

๊ทธ ํ›„ ํŒŒ๋ผ๋ฏธํ„ฐ์—์„œ ๊ฒ€์ƒ‰ ํ›„ ๋‹ค์Œ ๋ถ€๋ถ„์„ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.

(MySQL ๊ธฐ๋ณธ ์„ค์ • ๋ฐ ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„ ์„ค์ • ์ž…๋‹ˆ๋‹ค.)

  • time_zone ๊ฒ€์ƒ‰ ํ›„ time_zone ํŒŒ๋ผ๋ฏธํ„ฐ๋ฅผ Asia/Seoul ๋กœ ๋ณ€๊ฒฝํ•ฉ๋‹ˆ๋‹ค.
  • char ๊ฒ€์ƒ‰ ํ›„ character_set ~ ๋‚˜์˜ค๋Š” ํ•ญ๋ชฉ์˜ ๊ฐ’์„ ๋ชจ๋‘ utf8mb4 ๋กœ ๋ณ€๊ฒฝํ•ฉ๋‹ˆ๋‹ค.
  • collation ์œผ๋กœ ๊ฒ€์ƒ‰ ํ›„ collation_connection, collation_server์˜ ๊ฐ’์„ utf8_mb4_general_ci๋กœ ๋ณ€๊ฒฝํ•ฉ๋‹ˆ๋‹ค.
  • lower_case ๊ฒ€์ƒ‰ ํ›„ lower_case_table_names ์˜ ๊ฐ’์„ 1 ๋กœ ๋ณ€๊ฒฝํ•ฉ๋‹ˆ๋‹ค. (๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„ X)

 

 

 

 

์œ„ ์„ค์ •์ด ๋๋‚ฌ์œผ๋ฉด RDS DB์˜ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน์„ ๋ณ€๊ฒฝํ•ด์ฃผ์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค > DB ์‹๋ณ„์ž > ์ˆ˜์ • ์„ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

์•„๋ž˜๋กœ ๋‚ด๋ ค์™€์„œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์˜ต์…˜์—์„œ DB ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน์„ ์œ„์—์„œ ์ƒ์„ฑํ•œ ๊ทธ๋ฃน์œผ๋กœ ์ˆ˜์ •ํ•ฉ๋‹ˆ๋‹ค.

๊ทธ ํ›„ ๊ฐ€์žฅ ์•„๋ž˜์˜ ๊ณ„์† ๋ฒ„ํŠผ์„ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

 

 

 

๊ทธ๋Ÿผ ์œ„์™€ ๊ฐ™์ด ํ˜„์žฌ ๊ฐ’ / ์ƒˆ ๊ฐ’์˜ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน์ด ๋‚˜์˜ต๋‹ˆ๋‹ค.

DB ์ธ์Šคํ„ด์Šค ์ˆ˜์ •์„ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค ! ๐Ÿ˜

 

 

 

 

๐Ÿคฌ MySQL 8.0 DB์—์„œ๋Š” lower_case_table_names์˜ parameter value(1)์„ ์„ค์ •ํ•  ์ˆ˜ ์—†๋‚˜๋ด…๋‹ˆ๋‹ค...

 

 

 

 

https://forums.aws.amazon.com/thread.jspa?messageID=918406

์Œ,, ์ฐพ์•„๋ณด๋‹ˆ MySQL 8์€ lower_case_table_names์˜ ๊ฐ’์„ 0๋งŒ ์ง€์›ํ•œ๋‹ค๊ณ  ํ•ฉ๋‹ˆ๋‹ค...

 

 

 

๋”ฐ๋ผ์„œ ์ €๋Š” MySQL 5๋ฒ„์ „๋Œ€๋กœ DB๋ฅผ ๋‹ค์‹œ ์ƒ์„ฑํ•ด์ฃผ์—ˆ์Šต๋‹ˆ๋‹ค........ ๐Ÿคฌ๐Ÿคฌ๐Ÿคฌ

 

 

 

MySQL 5๋ฒ„์ „๋Œ€๋ฅผ ์ƒ์„ฑํ•œ ํ›„ ์œ„์—์„œ ์„ค์ •ํ•œ ํŒŒ๋ผ๋ฏธํ„ฐ ๊ทธ๋ฃน(lower_case_table_names = 1) ์œผ๋กœ ์„ค์ •ํ•˜์‹œ๋ฉด ๋ฉ๋‹ˆ๋‹ค.

 

 

 

๋ฐ˜์‘ํ˜•

๋Œ“๊ธ€