[MySQL] Access denied for user 'root'@'localhost' 에러

---

이 글은 PC 버전 TISTORY에 최적화 되어있습니다.

문제

 Mac OS에서 mysql 로그인 시 Access denied for user 'root'@'localhost' 에러가 발생했습니다. 이 문제는 mysql root 계정의 비밀번호가 틀려서라고 나오네요. 이를 해결할 수 있는 방법을 제시한 이 페이지를 참고하였습니다.

해결

실행 중인 홈브루 서비스를 확인합니다.

~ $ brew services list
# Name    Status  User  Plist
# dnsmasq started root  /Library/LaunchDaemons/homebrew.mxcl.dnsmasq.plist
# mysql   started appkr /Users/appkr/Library/LaunchAgents/homebrew.mxcl.mysql.plist
# php70   stopped

기존에 사용하던 MySQL 데이터 디렉터리를 확인합니다.

~ $ ls -al /usr/local/var/mysql
# total 185M
# drwxr-xr-x  23 appkr admin  782  1  7 18:18 ./
# drwxrwxr-x   7 appkr admin  238 11 14 10:07 ../
# drwxr-x---  32 appkr admin 1.1K 11 20 21:34 myapp/
# drwxr-x---  77 appkr admin 2.6K 11 14 10:07 mysql/
# drwxr-x---  90 appkr admin 3.0K 11 14 10:07 performance_schema/
# drwxr-x--- 108 appkr admin 3.6K 11 14 10:07 sys/
# ...

MySQL 서버를 중지시키고, 기존에 사용하던 데이터 디렉터리와 MySQL 바이너리를 삭제합니다.

만일의 사태에 대비해 삭제하기 전에 데이터를 백업해 두는 것이 좋습니다.

~ $ mysqldump -uroot -p source__database_name > dump_file_name.sql

~ $ brew services stop mysql
# ==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)

~ $ rm -rf /usr/local/var/mysql

~ $ brew uninstall mysql
# Uninstalling /usr/local/Cellar/mysql/5.7.16... (13,511 files, 439M)

잘 삭제되었는지 확인해 볼까요?

~ $ brew services list
# Name    Status  User Plist
# dnsmasq started root /Library/LaunchDaemons/homebrew.mxcl.dnsmasq.plist
# php70   stopped

~ $ brew list | grep mysql

완전히 지워졌으니, 이제 다시 설치를 합니다.

~ $ brew install mysql
# ==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.17.sierra.bottle.tar.gz
# ######################################################################## 100.0%
# ==> Pouring mysql-5.7.17.sierra.bottle.tar.gz
# ==> Using the sandbox
# ==> /usr/local/Cellar/mysql/5.7.17/bin/mysqld --initialize-insecure --user=appkr --basedir=/usr/local/Cellar/mysql/5.7.17 --datadir=/usr/local/var/mysql --tmp
# ==> Caveats
# We've installed your MySQL database without a root password. To secure it run:
#     mysql_secure_installation
# 
# 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/5.7.17: 14,226 files, 444.4M

MySQL 서비스를 시작하고 접속해 봅니다. 현재는 root 사용자의 비밀번호가 없는 상태입니다.

~ $ 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 3
# Server version: 5.7.17 Homebrew
# 
# Copyright (c) 2000, 2016, 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> exit
# Bye

사실 개발 머신에서는 이 상태로 사용해도 아무런 문제가 없습니다. 그럼에도 불구하고 마음의 평화를 위해, 또 편의상 책에서 사용한 homestead 사용자와 같은 비밀번호를 사용하기 위해 root 사용자의 비밀번호를 셋팅해 보겠습니다.

MySQL 쿼리 사용이 익숙하지 않은 사용자들을 위해 mysql_secure_installation라는 도우미 함수를 제공합니다 . 5.7부터라고 알고 있고, 이전 버전에서는 mysql_easy_install(?)였던 걸로 기억합니다.

그냥 계속 엔터를 누르면 VALIDATE PASSWORD PLUGIN이라는 녀석이 설치되는데요. 이 녀석이 활성화되면 우리가 의도한 secret와 같이 짧고 단순한 비밀번호는 쓸 수 없습니다.

사용자의 실수를 방지하기 위해서 이 블록의 콘솔 출력 결과만 한글로 번역합니다. 아래 블록에서 한글로 번역하지 않은 인터렉티브 질문에 대해서는 그냥 엔터를 눌러도 무방합니다.

다시 요약하자면, 실습 중에 root, homstead 사용자에 대해 복잡한 긴 비밀번호를 써도 무방하다면 이 가이드를 따를 필요가 없습니다.

~ $ mysql_secure_installation

# MySQL server의 안전 장치를 활성화합니다.
#
# MySQL에 비밀번호 없이 접속합니다.
#
# 보안을 향상시키기 위해 VALIDATE PASSWORD PLUGIN을 활성화합니다.
# 이 플러그인은 사용자가 강력한 비밀번호를 사용하도록 강제합니다.
# 이 플러그인을 활성화하겠습니까?
#
# 활성화하려면 Y 키를, 그렇지 않으려면 아무키나 누르세요: N
#
# 이제 root 사용자의 비밀번호를 입력해 주세요.
# 새 비밀번호: ******
# 한 번 더 입력해 주세요: ******
#
# 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.
# 
# 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) : Y
#  - Dropping test database...
# Success.
# 
#  - Removing privileges on test database...
# Success.
# 
# 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!

이제 root 사용자의 비밀번호가 설정되었습니다. 실험해 볼까요?

~ $ mysql -uroot
# ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

~ $ mysql -uroot -p
# Enter password: ******
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 9
# Server version: 5.7.17 Homebrew
# 
# Copyright (c) 2000, 2016, 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> exit
# Bye

백업해 놓은 데이터베이스를 복원하려면 다음 명령을 수행하면 됩니다.

~ $ mysql -uroot -p target_table_name < dump_file_name.sql

---

고급 사용자라면 mysql_secure_installation대신 다음 쿼리를 직접 수행하면 더 편리합니다.

~ $ mysql -uroot
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 10
# Server version: 5.7.17 Homebrew
# 
# Copyright (c) 2000, 2016, 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> use mysql;
# Reading table information for completion of table and column names
# You can turn off this feature to get a quicker startup with -A
# 
# Database changed
mysql> SELECT Host, User, authentication_string FROM user;
# +-----------+-----------+-------------------------------------------+
# | Host      | User      | authentication_string                     |
# +-----------+-----------+-------------------------------------------+
# | localhost | root      | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 |
# | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
# +-----------+-----------+-------------------------------------------+
# 2 rows in set (0.00 sec)
# 
mysql> UPDATE user SET authentication_string=PASSWORD('new_password') WHERE Host = 'localhost' AND User = 'root';
# Query OK, 1 row affected, 1 warning (0.00 sec)
# Rows matched: 1  Changed: 1  Warnings: 1
# 
mysql> FLUSH PRIVILEGES;
# Query OK, 0 rows affected (0.00 sec)
# 
mysql> exit
# Bye