MYSQL 설치 및 사용자 권한 설정, 외부접근 할당

노요셉·2019년 11월 15일

mysql mysql 외부접근

TIL

목록 보기

22/23

설치전

brew가 설치 되어있어야 합니다.
HomeBrew사용법

mysql 설치 ( docker를 사용하는 걸 추천 )

docker로 mysql 올렸으면 패스

도커로 mysql 설치 및 사용법: https://velog.io/@noyo0123/%EB%8F%84%EC%BB%A4%EB%A1%9C-mysql-%EB%9D%84%EC%9B%8C%EB%B3%B4%EC%9E%90
터미널을 켜시고 다음 명령어를 입력합니다.

레퍼런스 :https://github.com/appkr/l5code/issues/4

$ rm -rf /usr/local/var/mysql <-- 기존 mysql 있다면 삭제
$ brew uninstall mysql <-- brew로 설치한 것도 제거
$ brew services list <-- mysql이 started 가 안되었다면 삭제가 잘 된 것임.
$ brew search mysql  <--

==> Formulae
automysqlbackup            mysql-client@5.7           mysql-search-replace
mysql                      mysql-connector-c++        mysql@5.6
mysql++                    mysql-connector-c++@1.1    mysql@5.7
mysql-client               mysql-sandbox              mysqltuner
==> Casks
mysql-connector-python     mysql-utilities            navicat-for-mysql
mysql-shell                mysqlworkbench             sqlpro-for-mysql
$ brew install mysql <--

Updating Homebrew...
==> Auto-updated Homebrew!
Updated 3 taps (homebrew/core, homebrew/cask and homebrew/services).
==> New Formulae
buildozer
==> Updated Formulae
activemq                   google-java-format         libplctag
docker-compose             grin-wallet                libvmaf
docker-compose-completion  gsoap                      nasm
dvc                        gwyddion                   petsc
entr                       jruby                      petsc-complex
exploitdb                  knot-resolver              rom-tools
fastlane                   libphonenumber             swiftformat
==> Updated Casks
axe-edit-iii               glyphs                     screaming-frog-seo-spider
cheatsheet                 jitsi-meet                 silentknight
eset-cyber-security-pro    libreoffice
fing                       lockrattler
==> Deleted Casks
iconping

==> Downloading https://homebrew.bintray.com/bottles/openssl%401.1-1.1.1g.catali
==> Downloading from https://akamai.bintray.com/19/1926679569c6af5337de812d86f4d
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/protobuf-3.12.3.catalina.bo
==> Downloading from https://akamai.bintray.com/3c/3ced83651f35574357814e547e6d8
######################################################################## 100.0%
==> Downloading https://homebrew.bintray.com/bottles/mysql-8.0.19_1.catalina.bot
==> Downloading from https://akamai.bintray.com/e5/e5a5455d254260e9ca9821cb9c5e9
######################################################################## 100.0%
==> Installing dependencies for mysql: openssl@1.1 and protobuf
==> Installing mysql dependency: openssl@1.1
==> Pouring openssl@1.1-1.1.1g.catalina.bottle.tar.gz
==> Caveats
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /usr/local/etc/openssl@1.1/certs

and run
  /usr/local/opt/openssl@1.1/bin/c_rehash

openssl@1.1 is keg-only, which means it was not symlinked into /usr/local,
because macOS provides LibreSSL.

If you need to have openssl@1.1 first in your PATH run:
  echo 'export PATH="/usr/local/opt/openssl@1.1/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl@1.1 you may need to set:
  export LDFLAGS="-L/usr/local/opt/openssl@1.1/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl@1.1/include"

==> Summary
🍺  /usr/local/Cellar/openssl@1.1/1.1.1g: 8,059 files, 18MB
==> Installing mysql dependency: protobuf
==> Pouring protobuf-3.12.3.catalina.bottle.tar.gz
==> Caveats
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/protobuf
==> Summary
🍺  /usr/local/Cellar/protobuf/3.12.3: 267 files, 19.8MB
==> Installing mysql
==> Pouring mysql-8.0.19_1.catalina.bottle.tar.gz
==> /usr/local/Cellar/mysql/8.0.19_1/bin/mysqld --initialize-insecure --user=sol
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql/8.0.19_1: 286 files, 288.8MB
==> `brew cleanup` has not been run in 30 days, running now...
Removing: /Users/solarconnect-yosephnoh/Library/Caches/Homebrew/icu4c--64.2.catalina.bottle.tar.gz... (26.1MB)
Removing: /Users/solarconnect-yosephnoh/Library/Caches/Homebrew/mongodb-community--4.2.5.tgz... (113.8MB)
Removing: /Users/solarconnect-yosephnoh/Library/Caches/Homebrew/tree--1.8.0.catalina.bottle.tar.gz... (49.5KB)
Removing: /Users/solarconnect-yosephnoh/Library/Logs/Homebrew/mongodb-community... (126B)
Removing: /Users/solarconnect-yosephnoh/Library/Logs/Homebrew/icu4c... (64B)
Removing: /Users/solarconnect-yosephnoh/Library/Logs/Homebrew/yarn... (64B)
Pruned 0 symbolic links and 2 directories from /usr/local
==> Caveats
==> openssl@1.1
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /usr/local/etc/openssl@1.1/certs

and run
  /usr/local/opt/openssl@1.1/bin/c_rehash

openssl@1.1 is keg-only, which means it was not symlinked into /usr/local,
because macOS provides LibreSSL.

If you need to have openssl@1.1 first in your PATH run:
  echo 'export PATH="/usr/local/opt/openssl@1.1/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl@1.1 you may need to set:
  export LDFLAGS="-L/usr/local/opt/openssl@1.1/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl@1.1/include"

==> protobuf
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/protobuf
==> mysql
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

$ brew cask install mysqlworkbench <--
==> Downloading https://cdn.mysql.com/Downloads/MySQLGUITools/mysql-workbench-co
######################################################################## 100.0%
==> Verifying SHA-256 checksum for Cask 'mysqlworkbench'.
==> Installing Cask mysqlworkbench
==> Moving App 'MySQLWorkbench.app' to '/Applications/MySQLWorkbench.app'.
🍺  mysqlworkbench was successfully installed!

https://gist.github.com/jonjack/37ce2d0a28fc53d85d7c18de8065d555

설치후 익명상태로 mysql 명령을 사용하여 쓸 수 있지만, 보안에 취약합니다.

완전히 삭제후 다시 설치했기 때문에 다음과 같이 실행 됩니다.

$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 Homebrew

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 서비스를 시작하고 접속해 봅니다. 현재는 root 사용자의 비밀번호가 없는 상태입니다.
이제 MySQL 설정하기 위해서

"mysql_secure_installation" 명령어를 입력해주세요.

레퍼런스에 나온 설명보면서 설정

레퍼런스 : https://whitepaek.tistory.com/16

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: no

패스워드를 입력하라고 하니 입력합니다.

New password:

Re-enter new password:
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.


Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

"Remove anonymous users? (Press y|Y for Yes. any other key for No)"

사용자 설정을 묻는 질문입니다.

Yes 또는 No를 입력해주세요.

Yes - 접속하는 경우 "mysql -uroot"처럼 -u 옵션 필요

No - 접속하는 경우 "mysql"처럼 -u 옵션 불필요

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

"Remove test database and access to it? (Press y|Y for Yes, any other key for No)"

Test 데이터베이스를 설정하는 질문입니다.

Yes - Test 데이터베이스 제거

No - Test 데이터베이스 유지

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

"Reload privilege tables now? (Press y|Y for Yes, any other key for No)"

변경된 권한을 테이블에 적용하는 설정에 대한 질문입니다.

Yes - 적용시킨다.

No - 적용시키지 않는다.

*저는 "Yes"로 설정하였습니다.

(해당 질문은 무조건 "Yes" 하시기 바랍니다.)

레퍼런스 : OS X에서 MySQL을 잘못 설치했을 때 다시 설치하는 방법 - appkr

일단은 빠르게 설치합니다.

mysql 접속 (도커에 mysql 올림)

터미널을 켜서
mysql -u root -p 명령어를 입력합니다.
위에 root비밀번호를 입력한 비밀번호로 접속할 수 있습니다.

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.18 Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

참고 m1칩에서 mysql 도커로 올렸을때

https://hub.docker.com/r/mysql/mysql-server/

Downloading a MySQL Server Docker Image

docker pull mysql/mysql-server:tag

docker pull mysql/mysql-server:8.0.15 (확인)

Starting a MySQL Server Instance

docker run --name=mysql1 -d mysql/mysql-server:tag

도커 컨테이너 확인

docker ps

안나오면 터미널 껏다 다시 켜보세요.

docker logs mysql1 2>&1 | grep GENERATED
GENERATED ROOT PASSWORD: Axegh3kAJyDLaRuBemecis&EShOs

Connecting to MySQL Server from within the Container

docker exec -it mysql1 mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

아마 여기까지 왔으면 컨테이너 들어가서는 mysql client로 접근할 수 있지만,

host os에서 workbench나 mysql client로 접속할 수 없을 것임.
저는 그랬음.

root 계정 외부 접속하게 하기.

기존 root 날리기 호스트가 localhost로 되어있어서 외부 접속이 안됌.
DROP USER 'root'@'localhost';

id: root이고, password가 local이고, 외부 접근 권합 부여한 계정 생성
CREATE USER 'root'@'%' IDENTIFIED BY 'local';

'root'@'%' 계정에게 모든 데이터베이스, 모든 테이블에 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

변경된 내용을 메모리에 반영
FLUSH PRIVILEGES;

workbench 설치

$ brew cask install mysqlworkbench <--
==> Downloading https://cdn.mysql.com/Downloads/MySQLGUITools/mysql-workbench-co
######################################################################## 100.0%
==> Verifying SHA-256 checksum for Cask 'mysqlworkbench'.
==> Installing Cask mysqlworkbench
==> Moving App 'MySQLWorkbench.app' to '/Applications/MySQLWorkbench.app'.
🍺  mysqlworkbench was successfully installed!

workbench

DB connection 생성방법
https://gmlwjd9405.github.io/2018/05/09/mysql-workbench-guide.html

testDB 추가하기

https://three-pleasure.tistory.com/131

mysql 사용자 추가 (mysql 설치된 상태에요!!)

mysql > create user 사용자ID; // 사용자 추가

출처: https://nickjoit.tistory.com/144 [nickjoIT]

권한 에러 ERROR 1044 (42000)

ERROR 1044 (42000): Access denied for user

mysql> show grants for peter0618@localhost
    -> ;
+-----------------------------------------------+
| Grants for peter0618@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'peter0618'@'localhost' |
+-----------------------------------------------+
1 row in set (0.00 sec)

레퍼런스 보고 달라진 계정

mysql> show grants for peter0618@localhost
    -> ;
+--------------------------------------------------------------------------+
| Grants for peter0618@localhost                                           |
+--------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'peter0618'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 콘솔에서 한글 입력 안될때

mysql> show variables like 'c%';

파라미터 그룹에서 character 검색
하단 목록 utf8mb4로 설정
character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server

https://wjjeong.tistory.com/12

mysql 설정파일을 수정하기로 했음.
리눅스에 vi ( 에디터 설치 )
sudo apt-get install vim 수퍼유저라면 #이 붙어있다면 sudo 빼면됩니다.

https://calvinjmkim.tistory.com/23