ローカルでmysql立ち上げるとERROR! The server quit without updating PID file (/usr/local/var/mysql/**.local.pid)

Question

前提・実現したいこと

ローカルでmysql.server start したい

ここに質問の内容を詳しく書いてください。
MacOS: Sierra 10.12.6
mysql: mysql Ver 14.14 Distrib 5.6.34, for osx10.12 (x86_64) using EditLine wrapper

発生している問題・エラーメッセージ

ERROR! The server quit without updating PID file (/usr/local/var/mysql/MacBook-Air.local.pid).

試したこと

touch /usr/local/var/mysql/MacBook-Air.local.pid
を実行しても、local.pid ファイルは消えてしまう

mysql をreinstall したり、

以下記事を参考にしたが解決できず

• https://qiita.com/_natsu_no_yuki_/items/ae4c94187093e4ab3cdc

• https://qiita.com/yuki0208/items/1d5554d98e9ec76084bd

• http://mnmandahalf.hatenablog.com/entry/2017/10/03/002827

補足情報（FW/ツールのバージョンなど）

一ヶ月つまっていまたので、質問しました。コメントによるディスカッションにて適宜解決できればと思います。

Answer 1

メッセージ通りです。MYSQLが利用しようとしているPIDが、すでに他のプログラムによって占領されている可能性が高いです。まずは、MYSQLが利用しようとしているPIDを調べてみてください。エラーログなどに表示されていると思います。

それがわかったら、Shift+Ctrl+Escキーで、タスクマネージャーを立ち上げて詳細タブから、PIDを調べてみてください。すでに起動しているPID番号のプログラムがあると思いますので、そのプログラムを終了させるかしてください。

その後にMYSQLを再起動してみるといいと思います。

Comments

[ryuwryyyy]

>エラーログなどに表示されていると思います

お手数ですが、エラーログからPIDを探す方法をご教授願えませんでしょうか。

ググりましたが自力で解決できませんでした。

以下、**.local.errorのログです
```
181124 18:51:41 mysqld_safe mysqld from pid file /usr/local/var/mysql/MacBook-Air.local.pid ended
181206 08:54:01 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
Warning: World-writable config file '/etc/my.cnf' is ignored
2018-12-06 08:54:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-06 08:54:02 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2018-12-06 08:54:02 0 [Note] /usr/local/Cellar/mysql@5.6/5.6.34/bin/mysqld (mysqld 5.6.34) starting as process 49429 ...
2018-12-06 08:54:02 49429 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2018-12-06 08:54:02 49429 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/Cellar/mysql@5.6/5.6.34/bin/mysqld: Table 'mysql.plugin' doesn't exist
2018-12-06 08:54:02 49429 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2018-12-06 08:54:02 49429 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-12-06 08:54:02 49429 [Note] InnoDB: The InnoDB memory heap is disabled
2018-12-06 08:54:02 49429 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-12-06 08:54:02 49429 [Note] InnoDB: Memory barrier is not used
2018-12-06 08:54:02 49429 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-12-06 08:54:02 49429 [Note] InnoDB: Using CPU crc32 instructions
2018-12-06 08:54:02 49429 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-12-06 08:54:02 49429 [Note] InnoDB: Completed initialization of buffer pool
2018-12-06 08:54:02 49429 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: No valid checkpoint found.
InnoDB: If you are attempting downgrade from MySQL 5.7.9 or later,
InnoDB: please refer to http://dev.mysql.com/doc/refman/5.6/en/upgrading-downgrading.html
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/error-creating-innodb.html
2018-12-06 08:54:02 49429 [ERROR] Plugin 'InnoDB' init function returned error.
2018-12-06 08:54:02 49429 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-12-06 08:54:02 49429 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-12-06 08:54:02 49429 [ERROR] Aborting

2018-12-06 08:54:02 49429 [Note] Binlog end
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'partition'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_METRICS'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_CMPMEM'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_CMP'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_LOCKS'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'INNODB_TRX'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'BLACKHOLE'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'ARCHIVE'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'MRG_MYISAM'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'MyISAM'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'MEMORY'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'CSV'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'sha256_password'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'mysql_old_password'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'mysql_native_password'
2018-12-06 08:54:02 49429 [Note] Shutting down plugin 'binlog'
2018-12-06 08:54:02 49429 [Note] /usr/local/Cellar/mysql@5.6/5.6.34/bin/mysqld: Shutdown complete

181206 08:54:02 mysqld_safe mysqld from pid file /usr/local/var/mysql/MacBook-Air.local.pid ended

```

ちなみにPIDは`49429`かと思い、タスクマネージャーで探しましたが、出てきませんでした。

初心者の質問で大変お手数おかけしますが、ご確認よろしくおねがいします。

[FKM]

linux系なら
~#ps aux | grep mysql
で調べられますし、その後気になるpidがあるようでしたら
kill -9 ** (**は干渉しているPID）で
削除できます。

[FKM]

あと、エラーログたどるとこの現象じゃないでしょうか？
https://teratail.com/questions/29340

PID（プロセスID）の問題よりは、initializeの問題の可能性が高いですね。

[ryuwryyyy]

`mysqld --initialize-insecure --user=mysql`

を実行して

```
2018-12-06 18:01:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-06 18:01:24 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2018-12-06 18:01:24 0 [Note] mysqld (mysqld 5.6.42) starting as process 77562 ...
2018-12-06 18:01:24 77562 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2018-12-06 18:01:24 77562 [Warning] One can only use the --user switch if running as root

2018-12-06 18:01:24 77562 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2018-12-06 18:01:24 77562 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2018-12-06 18:01:24 77562 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-12-06 18:01:24 77562 [Note] InnoDB: The InnoDB memory heap is disabled
2018-12-06 18:01:24 77562 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-12-06 18:01:24 77562 [Note] InnoDB: Memory barrier is not used
2018-12-06 18:01:24 77562 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-12-06 18:01:24 77562 [Note] InnoDB: Using CPU crc32 instructions
2018-12-06 18:01:24 77562 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-12-06 18:01:25 77562 [Note] InnoDB: Completed initialization of buffer pool
2018-12-06 18:01:25 77562 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: No valid checkpoint found.
InnoDB: If you are attempting downgrade from MySQL 5.7.9 or later,
InnoDB: please refer to http://dev.mysql.com/doc/refman/5.6/en/upgrading-downgrading.html
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/error-creating-innodb.html
2018-12-06 18:01:25 77562 [ERROR] Plugin 'InnoDB' init function returned error.
2018-12-06 18:01:25 77562 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-12-06 18:01:25 77562 [ERROR] mysqld: unknown option '--initialize-insecure'
2018-12-06 18:01:25 77562 [ERROR] Aborting
```

が出てきます。

```
mysqld: Table 'mysql.plugin' doesn't exist
2018-12-06 18:01:24 77562 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
```
をググってみたのですが解決できませんでした。

>initializeの問題
について具体的にご回答いただけますと助かります。

3時間ほど調べておりましたが解決できず、お手数おかけしますが、ご確認よろしくおねがいします。

[FKM]

使用しているMYSQLのバージョンは何ですか？それによって対応が変わります。
ログを見ると5.6.42だといっているようなので…だとすると
mysqld --initialize-insecure --user=mysql
コマンドは使用できないかもしれません。

かわりに、MYSQLの中身をバックアップで退避してから、
./scripts/mysql_install_db --user=mysql
を実行して、動くかテストしてみてください。

[ryuwryyyy]

`/usr/local/Cellar/mysql@5.6/5.6.42$ mysql_install_db --user=mysql` を実行したところ、

あえて略さずに書きますが、エラーは出ませんでした。
```
Installing MySQL system tables...2018-12-07 09:41:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-07 09:41:53 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-12-07 09:41:53 0 [Note] ./bin/mysqld (mysqld 5.6.42) starting as process 685 ...
2018-12-07 09:41:53 685 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-12-07 09:41:53 685 [Note] InnoDB: The InnoDB memory heap is disabled
2018-12-07 09:41:53 685 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-12-07 09:41:53 685 [Note] InnoDB: Memory barrier is not used
2018-12-07 09:41:53 685 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-12-07 09:41:53 685 [Note] InnoDB: Using CPU crc32 instructions
2018-12-07 09:41:53 685 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-12-07 09:41:53 685 [Note] InnoDB: Completed initialization of buffer pool
2018-12-07 09:41:53 685 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-12-07 09:41:53 685 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2018-12-07 09:41:53 685 [Note] InnoDB: Database physically writes the file full: wait...
2018-12-07 09:41:54 685 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-12-07 09:41:54 685 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-12-07 09:41:54 685 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-12-07 09:41:54 685 [Warning] InnoDB: New log files created, LSN=45781
2018-12-07 09:41:54 685 [Note] InnoDB: Doublewrite buffer not found: creating new
2018-12-07 09:41:54 685 [Note] InnoDB: Doublewrite buffer created
2018-12-07 09:41:54 685 [Note] InnoDB: 128 rollback segment(s) are active.
2018-12-07 09:41:54 685 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-12-07 09:41:54 685 [Note] InnoDB: Foreign key constraint system tables created
2018-12-07 09:41:54 685 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-12-07 09:41:54 685 [Note] InnoDB: Tablespace and datafile system tables created.
2018-12-07 09:41:54 685 [Note] InnoDB: Waiting for purge to start
2018-12-07 09:41:54 685 [Note] InnoDB: 5.6.42 started; log sequence number 0
2018-12-07 09:41:54 685 [Note] RSA private key file not found: /usr/local/Cellar/mysql@5.6/5.6.42/data//private_key.pem. Some authentication plugins will not work.
2018-12-07 09:41:54 685 [Note] RSA public key file not found: /usr/local/Cellar/mysql@5.6/5.6.42/data//public_key.pem. Some authentication plugins will not work.
2018-12-07 09:41:55 685 [Note] Binlog end
2018-12-07 09:41:55 685 [Note] InnoDB: FTS optimize thread exiting.
2018-12-07 09:41:55 685 [Note] InnoDB: Starting shutdown...
2018-12-07 09:41:56 685 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK

Filling help tables...2018-12-07 09:41:57 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-07 09:41:57 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-12-07 09:41:57 0 [Note] ./bin/mysqld (mysqld 5.6.42) starting as process 687 ...
2018-12-07 09:41:57 687 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-12-07 09:41:57 687 [Note] InnoDB: The InnoDB memory heap is disabled
2018-12-07 09:41:57 687 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-12-07 09:41:57 687 [Note] InnoDB: Memory barrier is not used
2018-12-07 09:41:57 687 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-12-07 09:41:57 687 [Note] InnoDB: Using CPU crc32 instructions
2018-12-07 09:41:57 687 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-12-07 09:41:57 687 [Note] InnoDB: Completed initialization of buffer pool
2018-12-07 09:41:57 687 [Note] InnoDB: Highest supported file format is Barracuda.
2018-12-07 09:41:57 687 [Note] InnoDB: 128 rollback segment(s) are active.
2018-12-07 09:41:57 687 [Note] InnoDB: Waiting for purge to start
2018-12-07 09:41:57 687 [Note] InnoDB: 5.6.42 started; log sequence number 1625977
2018-12-07 09:41:57 687 [Note] RSA private key file not found: /usr/local/Cellar/mysql@5.6/5.6.42/data//private_key.pem. Some authentication plugins will not work.
2018-12-07 09:41:57 687 [Note] RSA public key file not found: /usr/local/Cellar/mysql@5.6/5.6.42/data//public_key.pem. Some authentication plugins will not work.
2018-12-07 09:41:57 687 [Note] Binlog end
2018-12-07 09:41:57 687 [Note] InnoDB: FTS optimize thread exiting.
2018-12-07 09:41:57 687 [Note] InnoDB: Starting shutdown...
2018-12-07 09:41:58 687 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h MacBook-Air.local password 'new-password'

Alternatively you can run:

./bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file ./my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as ./my-new.cnf,
please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
```

その後、`mysql.server start`をすると同じエラーでした
`ERROR! The server quit without updating PID file (/usr/local/var/mysql/MacBook-Air.local.pid).`

何度も申し訳ありませんが、ご確認よろしくおねがいします。

[FKM]

これかな？下の方のパスがうんたらって部分だけ試してみてください。
https://qiita.com/furafura_nau/items/7d8f5370899df125d459

[ryuwryyyy]

ご回答有り難うございます。

>パスうんたらが`echo 'export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"' >> ~/.bash_profile`を指しているとして、実行しましたが、

`/usr/local/opt/mysql@5.6/bin/mysql.server start`となり
エラーは変わりませんでした。

お手数ですが、ご確認お願いしますm(_ _)m

[ryuwryyyy]

すみません！
強硬策で、上記の記事の通り全部mysqを消して、reinstallしたら動きました！
```
$ sudo rm -rf /usr/local/mysql
$ sudo rm -rf /Library/StartupItems/MYSQL
$ sudo rm -rf /Library/PreferencePanes/MySQL.prefPane
$ sudo rm -rf /Library/Receipts/mysql-.pkg
$ sudo rm -rf /usr/local/Cellar/mysql*
$ sudo rm -rf /usr/local/bin/mysql*
$ sudo rm -rf /usr/local/var/mysql*
$ sudo rm -rf /usr/local/etc/my.cnf
$ sudo rm -rf /usr/local/share/mysql*
$ sudo rm -rf /usr/local/opt/mysql*

$ brew install mysql56
```

エラーの原因は、
>一度でも5.7を起動してしまうと、同じデータを使って5.6を起動すると次のようなエラーが出るようになります。

Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/ogin.local.pid).
mysqld等は動いていなかったので権限周りと思われますが、うまく解決できませんでした。
（バージョンによってディレクトリ構成が違っていたので、権限以外にも何か影響しているのかもしれません）

だそうです。

お忙しい中、何度もお付き合いいただきありがとうございました。

[FKM]

なるほど、自分も勉強になりました。
そして、そういう現象が起こるってことをよく覚えておきます。

Answer 2

linux系なら
~#ps aux | grep mysql
で調べられますし、その後気になるpidがあるようでしたら
kill -9 **** (****は干渉しているPID）で
削除できます。