Anagura's.....

たまに書く

brewでいれたmysqlが壊れる件

講義でmysqlを使うのですが,ローカルなmacbrewでinstallするまでは良いのですが 定期的に mysql -u root -p すると正しいパスワードをいれても解決しない問題が出てきました.

具体的には

❯ mysql -u root                                                           
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

な感じですね. brew uninstall mysqlしても解決せず.四苦八苦していると

yukiyamashina.com

なblog記事が

この手順通りにやってもrm出来てないところがあって,そこは臨機応変にした. 以下作業ログ.

/v/empty ❯❯❯ sudo rm -rf /usr/local/var/mysql
/v/empty ❯❯❯ brew uninstall mysql
Uninstalling /usr/local/Cellar/mysql/5.7.16... (13,511 files, 439M)
/v/empty ❯❯❯ brew cleanup
Warning: Skipping boost: most recent version 1.63.0 not installed
Warning: Skipping source-highlight: most recent version 3.1.8_4 not installed
/v/empty ❯❯❯ brew update
Already up-to-date.
/v/empty ❯❯❯ brew install mysql
==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.16.el_capitan.bot
Already downloaded: /Users/e155730/Library/Caches/Homebrew/mysql-5.7.16.el_capitan.bottle.1.tar.gz
==> Pouring mysql-5.7.16.el_capitan.bottle.1.tar.gz
==> Using the sandbox
==> /usr/local/Cellar/mysql/5.7.16/bin/mysqld --initialize-insecure --user=e1557
==> 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.16: 13,511 files, 439M
/v/empty ❯❯❯ mysqld --initialize  --explicit_defaults_for_timestamp
2016-12-30T08:35:33.113558Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2016-12-30T08:35:33.113992Z 0 [ERROR] Aborting

/v/empty ❯❯❯                                                                  ⏎
/v/empty ❯❯❯ sudo rm -rf /usr/local/var/mysql                                 ⏎
/v/empty ❯❯❯ mysqld --initialize  --explicit_defaults_for_timestamp
2016-12-30T08:36:03.921480Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2016-12-30T08:36:04.125426Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-12-30T08:36:04.157283Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-12-30T08:36:04.217534Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 003ca31c-ce6b-11e6-ab71-0e9de9b7da21.
2016-12-30T08:36:04.231309Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-12-30T08:36:04.827665Z 0 [Warning] CA certificate ca.pem is self signed.
2016-12-30T08:36:04.886195Z 1 [Note] A temporary password is generated for root@localhost: J/nhD5rss3yx

なのだが,どうもここの root@localhost: 以下に表示されるpasswordを覚えておく必要があるらしい

今回は

2016-12-30T08:36:04.886195Z 1 [Note] A temporary password is generated for root@localhost: J/nhD5rss3yx

であるので J/nhD5rss3yx ですね

/v/empty ❯❯❯ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
/v/empty ❯❯❯ mysql_secure_installation                                        ⏎

Securing the MySQL server deployment.

Enter password for user root: 
Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
/v/empty ❯❯❯ mysql.server start                                               ⏎
Starting MySQL
. SUCCESS! 
/v/empty ❯❯❯ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: NO)

でさっきのパスワードをこれをした後に入力

/v/empty ❯❯❯ mysql_secure_installation                                        ⏎

Securing the MySQL server deployment.

Enter password for user root:  (ここで入力する)

The existing password for the user account root has expired. Please set a new password.

New password: 
Sorry, you can't use an empty password here.

New password: 

Re-enter new password: 

VALIDATE PASSWORD PLUGIN 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 plugin?

Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

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.


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) : n

 ... skipping.
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! 
/v/empty ❯❯❯ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
/v/empty ❯❯❯ mysql -u root ^p                                                 ⏎
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
/v/empty ❯❯❯ mysql -u root -p                                                 ⏎
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.16 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
exit
^C
mysql> exit;
Bye

な感じで解決した


追記(2017/01/03)

かくとぅふ氏からコメントが

上のlogに追記しておくと人によってはクリーンインストールした際にrootでのログインの際にpassを設定していない場合にうまくログインできない場合があるみたいです. その場合passを変更しなきゃいけないのですが, http://qiita.com/ahaha0807_alg/items/7323a5d2dd365308cb94 を参考にすると幸せになれるみたいです.ほとんどがpasswordでの記述で自分のところのelcapitanではずっと ERROR 1054 (42S22): Unknown column 'Password' in 'field list' みたいなエラーはいていたんで.もしpass変更の際にひっかかるようであればこちらも参考にするとよいかと.

なるほど.パスワードカラムを変更する場合もある様です