MySQLをいれた際に遭遇したエラー

初めに

maysqlを起動しようとすると

$ mysql.server start
Starting MySQL
.. ERROR! The server quit without updating PID file (/usr/local/var/mysql/XXXXXXXX.local.pid).

という内容のエラーが出ました。

考えられる事象

home brew で本当に mysql@5.7のパッケージが正常インストールを完了しているか、想定しているパッケージのmysqlが正常に起動しているか、競合している設定が優先されていないか、この辺りを確認していく必要がありそうです。

検証する

まずはmysqlの確認を行なってみます。

$ which mysql
/usr/local/bin/mysql

$ brew info mysql
mysql: stable 8.0.12 (bottled)
Open source relational database management system
https://dev.mysql.com/doc/refman/8.0/en/
Conflicts with:
 mariadb (because mysql, mariadb, and percona install the same binaries.)
 mariadb-connector-c (because both install plugins)
 mysql-cluster (because mysql, mariadb, and percona install the same binaries.)
 mysql-connector-c (because both install MySQL client libraries)
 percona-server (because mysql, mariadb, and percona install the same binaries.)
Not installed
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/mysql.rb
==> Dependencies
Build: cmake ✘
Required: openssl :チェックマーク大:
==> Requirements
Required: macOS >= 10.10 :チェックマーク大:
==> Options
--with-embedded
       Build the embedded server
--with-local-infile
       Build with local infile loading support
--with-memcached
       Build with InnoDB Memcached plugin
==> 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

A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

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
==> Analytics
install: 63,641 (30 days), 209,644 (90 days), 885,601 (365 days)
install_on_request: 60,047 (30 days), 193,795 (90 days), 770,018 (365 days)
build_error: 0 (30 days)

$ brew doctor
No check available by the name: mysql
~MacBook-puro:~$ brew doctor
Please note that these warnings are just used to help the Homebrew maintainers
with debugging if you file an issue. If everything you use Homebrew for is
working fine: please don't worry or file an issue; just ignore this. Thanks!

Warning: "config" scripts exist outside your system or Homebrew directories.
｀./configure｀ scripts often look for *-config scripts to determine if
software packages are installed, and what additional flags to use when
compiling and linking.

Having additional scripts in your path can confuse software installed via
Homebrew if the config script overrides a system or Homebrew provided
script of the same name. We found the following "config" scripts:
 /Users/~/.pyenv/shims/libdynd-config
 /Users/~/.pyenv/shims/python3.5m-config
 /Users/~/.pyenv/shims/icu-config
 /Users/~/.pyenv/shims/libpng16-config
 /Users/~/.pyenv/shims/python3.5-config
 /Users/~/.pyenv/shims/python-config
 /Users/~/.pyenv/shims/python3-config

Warning: You have unlinked kegs in your Cellar
Leaving kegs unlinked can lead to build-trouble and cause brews that depend on
those kegs to fail to run properly once built. Run ｀brew link｀ on these:
 git

違うmysqlを参照している可能性があります。

mysqlの削除

$ brew uninstall mysql # mysqlをアンインストールする
$ brew uninstall mysql@5.7 # もしこのバージョンも入っているようならいったん削除
$ sudo rm -rf /usr/local/var/mysql/ # mysqlの持っていたdbを全削除
$ rm -rf /tmp/mysql.sock #ソケットも削除

削除されているかの確認

$ brew list| grep mysql

こちらを実行して何も表示されなければOKです。

もう一度インストールしましょう。

$ brew install mysql@5.7 # mysql5.7を導入
$ brew link mysql@5.7 --force # フォースリンク

インストールログで成功しているか確認しましょう

$　brew install mysql@5.7
==> Downloading https://homebrew.bintray.com/bottles/[e-mail]_sierra.bottle.tar.gz
Already downloaded: /Users/〜/Library/Caches/Homebrew/downloads/ccf66b0[e-mail]_sierra.bottle.tar.gz
==> Pouring [e-mail]_sierra.bottle.tar.gz
==> /usr/local/Cellar/mysql@5.7/5.7.24/bin/mysqld --initialize-insecure --user=kanekoharuaki --basedir=/usr/local/Cellar/mysql@5.7/5.7.24 --datadir=/u
==> 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

A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

mysql@5.7 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

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

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

For pkg-config to find mysql@5.7 you may need to set:
 export PKG_CONFIG_PATH="/usr/local/opt/mysql@5.7/lib/pkgconfig"


To have launchd start mysql@5.7 now and restart at login:
 brew services start mysql@5.7
Or, if you don't want/need a background service you can just run:
 /usr/local/opt/mysql@5.7/bin/mysql.server start
==> Summary
:ビール:  /usr/local/Cellar/mysql@5.7/5.7.24: 317 files, 234.3MB

$　brew link mysql@5.7 --force
Linking /usr/local/Cellar/mysql@5.7/5.7.24... 87 symlinks created

If you need to have this software first in your PATH instead consider running:
 echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile

このようになっていれば大丈夫です。

PATHの指定

今回は複数のmysqlが干渉している可能性がありますので、こちらのコマンドを打ってターミナルを再起動してください。

$　echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile

違うmysqlを優先して起動出来ない場合

serverを立ち上げても先ほどと同じエラーが出る方は競合している可能性が高いです。

$　mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf 
/etc/mysql/my.cnf
/usr/local/etc/my.cnf
 ~/.my.cnf

私の場合は4つの候補が出てきまして、実際に存在していたのは

$　ls /usr/local/etc/my.cnf
/usr/local/etc/my.cnf

$　ls /etc/my.cnf
/etc/my.cnf

2つ存在していましたのでいらない方を

$　mv /etc/my.cnf my.cnf.old

してserver を起動してみましょう。

＄　mysql.server start
Starting MySQL
SUCCESS!
〜MacBook-puro:〜$ 2018-10-30T12:41:13.6NZ mysqld_safe A mysqld process already exists

起動出来ました！

終わりに

起動出来ない理由には様々な原因があると思います。
参考に以下のqiita記事も載せておきます。
https://qiita.com/_natsu_no_yuki_/items/ae4c94187093e4ab3cdc
https://qiita.com/sato11/items/ba887a5655217f60f2a2

私はこのエラーでだいぶ手こずってしまったので、誰かの役に立てば嬉しいです。