IT Talks/Database

λΉ„λ°€λ²ˆν˜Έ μž…λ ₯ 없이 MySQL μ ‘μ†ν•˜κΈ°

OkOJJ 2023. 2. 9. 15:43

MySQL 버전이 점점 μ˜¬λΌκ°€λ©΄μ„œ μ–Έμ œλΆ€ν„°μΈκ°€ mysql λͺ…λ Ήμ–΄λ‘œ μ ‘μ†μ‹œ λΉ„λ°€λ²ˆν˜Έλ₯Ό 같이 μž…λ ₯ν•˜λ©΄ μ•„λž˜μ™€ 같이 μ•ˆμ „ν•˜μ§€ μ•Šλ‹€λŠ” κ²½κ³ κ°€ λ‚˜μ˜¨λ‹€.

$ mysql -hlocalhost -udb_user -pdb_password
mysql: [Warning] Using a password on the command line interface can be insecure.

μ‰˜ 슀크립트둜 DB μ ‘μ†ν•˜λŠ” μž‘μ—…μ„ ν•˜λŠ” 경우 μœ„μ™€ 같이 λͺ…령어에 λΉ„λ°€λ²ˆν˜ΈκΉŒμ§€ 같이 μž…λ ₯ν•˜λŠ” κ²½μš°κ°€ λ§Žμ€λ° 싀행은 λ˜μ§€λ§Œ κ²½κ³ κ°€ λ‚˜μ˜€λŠ”κ²Œ 맀우 κ±°μŠ¬λ¦°λ‹€.

이런 κ²½μš°λŠ” μ•„λž˜ 2가지 λ°©λ²•μœΌλ‘œ 해결이 κ°€λŠ₯ν•˜λ‹€.

 

방법1)

계정 home path (λ¦¬λˆ…μŠ€ κΈ°μ€€ /home/userid λ“±) 에 .my.cnf νŒŒμΌμ„ μƒμ„±ν•˜κ³  μ•„λž˜μ™€ 같이 λ‚΄μš©μ„ μž…λ ₯ν•œλ‹€.

[client]
host=localhost
user=db_user
password=db_password

μƒμ„±ν•œ 후에 mysql λͺ…λ Ήλ§Œ μ‹€ν–‰ν•˜λ©΄ λ°”λ‘œ 접속이 κ°€λŠ₯ν•˜λ‹€.

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47904
Server version: 8.0.23 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>

μ΄λ ‡κ²Œ ν•˜λ©΄ κ°„νŽΈν•˜κ²Œ 접속을 ν•  수 μžˆμœΌλ‚˜, μ§€μ •λœ DB ν•œκ³³λ§Œ 접속이 κ°€λŠ₯ν•˜κ³ , λ‹€λ₯Έ DB (host κΈ°μ€€) 에 μ ‘μ†ν•˜λ €λ©΄ 기쑴처럼 mysql 에 -h μ˜΅μ…˜μ„ μ‚¬μš©ν•΄μ„œ 접속을 ν•΄μ•Ό ν•œλ‹€.

DBλ₯Ό μ—¬λŸ¬κ°œ μ‚¬μš©ν•˜λŠ” 경우 μ•„λž˜ λ°©λ²•μœΌλ‘œ μ‚¬μš©ν•˜λ©΄ λœλ‹€.

 

방법2)

mysql_config_editor λͺ…령을 μ‚¬μš©ν•˜μ—¬ DB λ³„λ‘œ profile 을 μƒμ„±ν•œλ‹€.

$ mysql_config_editor set --login-path=dev --host=localhost --user=root --password

μœ„μ™€ 같이 μ‹€ν–‰ν•˜λ©΄ λΉ„λ°€λ²ˆν˜Έ μž…λ ₯ ν”„λ‘¬ν”„νŠΈκ°€ λ‚˜μ˜€κ³ , λΉ„λ°€λ²ˆν˜Έλ₯Ό μž…λ ₯ν•˜λ©΄ μ™„λ£Œλœλ‹€.
계정 home path 에 보면 .mylogin.cnf 파일이 μƒμ„±λ˜μ–΄ μžˆμ„κ²ƒμ΄λ‹€. (λ°”μ΄λ„ˆλ¦¬ ν˜•μ‹μ΄λΌ λ‚΄μš©μ€ 보기 어렀움)

--login-path 의 값을 λ‹€λ₯΄κ²Œ ν•˜μ—¬ μ—¬λŸ¬ DB 정보λ₯Ό 등둝해 두고, mysql μ ‘μ†μ‹œ μ•„λž˜μ™€ 같이 μ ‘μ†ν•œλ‹€.

$ mysql --login-path=dev
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9759
Server version: 8.0.23 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_config_editor print --all
[dev]
user = "user1"
password = *****
host = "host1"
[stg]
user = "user2"
password = *****
host = "host2"
[prod]
user = "user3"
password = *****
host = "host3"

λ“±λ‘λœ 정보 μ‚­μ œ

mysql_config_editor remove --login-path=dev

λ“±λ‘λœ λΉ„λ°€λ²ˆν˜ΈλŠ” μ•”ν˜ΈνšŒ λ˜μ–΄μ„œ .mylogin.cnf 에 μ €μž₯λ˜μ–΄ λ°”λ‘œ 확인은 μ–΄λ ΅μ§€λ§Œ, μ•„λž˜ λͺ…λ Ήμ–΄λ₯Ό 톡해 λΉ„λ°€λ²ˆν˜Έ 확인이 κ°€λŠ₯ν•˜λ‹€.

$ my_print_defaults --show dev
--user=user1
--password=password1
--host=host1
728x90