era-rogu tuikaMySQLが起動しません。

Question

MySQLが起動しなくなりました。

ある日、MySQLが機能しなくなっていたのを確認した為に、./mysql stop →./mysql start
をしましたがエラーが発生して起動されず。
その時のエラーも今回と同じようなものだったと思います。

とりあえずアンインストールして再インストールを図るも同じエラーが、、

なんででしょうか、、、

mysql.serviceJob for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

OS:Linux Mint 32bit
MySQL: mysql-server 5.7.18-0ubuntu0.16.04.1

やってみた事
テラテイルの過去の質問に同じトラブルがあり
そこの回答には

1.解消方法教えてくださいm(__)m

mysql.sockは自動で作成されるのでこちらを削除してください。
*pidファイルも作られてたら削除してください。

と書いてありました。
見てみたところ、mysql.sockもpidも生成されていないんです。←これは間違いでした。/var/lib/mysqlにちゃんとありました。→mysql.sockを消去。改めて./mysql start をするも同じエラーが発生

systemctl status mysql.service を実行していみる。

/etc/init.d$ systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: activating (start-post) (Result: exit-code) since 土 2017-07-08 11:31
  Process: 21779 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 21771 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exi
 Main PID: 21779 (code=exited, status=1/FAILURE);         : 21780 (mysql-systemd
   CGroup: /system.slice/mysql.service
           └─control
             ├─21780 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─21880 sleep 1

 7月 08 11:31:43 DOG2 systemd[1]: Starting MySQL Community Server...
 7月 08 11:31:43 DOG2 systemd[1]: mysql.service: Main process exited, code=exite
lines 1-13/13 (END)

InnoDB: Progress in percent: 2017-07-07T22:36:22.397197Z 0 [ERROR] InnoDB: Trying to access page number 278272 in space 145, space name coreprotect/wgc_block, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2017-07-07T22:36:22.397210Z 0 [ERROR] InnoDB: Server exits.
2017-07-07T22:36:52.597292Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-07-07T22:36:52.597384Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-07-07T22:36:52.798351Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-07-07T22:36:52.801916Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.18-0ubuntu0.16.04.1) starting as process 7733 ...
2017-07-07T22:36:52.808342Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-07-07T22:36:52.808408Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-07-07T22:36:52.808420Z 0 [Note] InnoDB: Uses event mutexes
2017-07-07T22:36:52.808432Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-07-07T22:36:52.808443Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-07-07T22:36:52.808454Z 0 [Note] InnoDB: Using Linux native AIO
2017-07-07T22:36:52.808874Z 0 [Note] InnoDB: Number of pools: 1
2017-07-07T22:36:52.809016Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2017-07-07T22:36:52.811104Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-07-07T22:36:52.827113Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-07-07T22:36:52.829523Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-07-07T22:36:52.843674Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-07-07T22:36:52.845334Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 22488436582
2017-07-07T22:36:52.845444Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 22488445105
2017-07-07T22:36:52.846946Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 22488445105
2017-07-07T22:36:52.848503Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-07-07T22:36:52.848555Z 0 [Note] InnoDB: Starting crash recovery.
2017-07-07T22:36:52.848836Z 0 [Warning] InnoDB: Page 4 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=183176]
2017-07-07T22:36:52.848853Z 0 [Warning] InnoDB: Page 5 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=254575]
2017-07-07T22:36:52.848866Z 0 [Warning] InnoDB: Page 6 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=231687]
2017-07-07T22:36:52.849812Z 0 [Warning] InnoDB: Page 34 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=196927]
2017-07-07T22:36:52.849848Z 0 [Warning] InnoDB: Page 35 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=214280]
2017-07-07T22:36:52.849860Z 0 [Warning] InnoDB: Page 36 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=233064]
2017-07-07T22:36:52.903722Z 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 34 row operations to undo
2017-07-07T22:36:52.903773Z 0 [Note] InnoDB: Trx id counter is 3117056
2017-07-07T22:36:52.903807Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...

Answer 1

データベースが壊れてしまっているのかもしれないので、たぶんログが/var/log/mysqld.logにあるので参照してみてください。
予想通りなら、何らかの理由を吐き出した後、exitした感じのログになっていると思います。

Comments

[scrollman]

ちょっと調べてみます

[scrollman]

/var/log/mysqld.logはありませんでしたが

/var/log/mysqlはありました。

[scrollman]

InnoDB: Progress in percent: 2017-07-07T22:36:22.397197Z 0 [ERROR] InnoDB: Trying to access page number 278272 in space 145, space name coreprotect/wgc_block, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2017-07-07T22:36:22.397210Z 0 [ERROR] InnoDB: Server exits.
2017-07-07T22:36:52.597292Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-07-07T22:36:52.597384Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-07-07T22:36:52.798351Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-07-07T22:36:52.801916Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.18-0ubuntu0.16.04.1) starting as process 7733 ...
2017-07-07T22:36:52.808342Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-07-07T22:36:52.808408Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-07-07T22:36:52.808420Z 0 [Note] InnoDB: Uses event mutexes
2017-07-07T22:36:52.808432Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-07-07T22:36:52.808443Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-07-07T22:36:52.808454Z 0 [Note] InnoDB: Using Linux native AIO
2017-07-07T22:36:52.808874Z 0 [Note] InnoDB: Number of pools: 1
2017-07-07T22:36:52.809016Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2017-07-07T22:36:52.811104Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-07-07T22:36:52.827113Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-07-07T22:36:52.829523Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-07-07T22:36:52.843674Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-07-07T22:36:52.845334Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 22488436582
2017-07-07T22:36:52.845444Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 22488445105
2017-07-07T22:36:52.846946Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 22488445105
2017-07-07T22:36:52.848503Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-07-07T22:36:52.848555Z 0 [Note] InnoDB: Starting crash recovery.
2017-07-07T22:36:52.848836Z 0 [Warning] InnoDB: Page 4 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=183176]
2017-07-07T22:36:52.848853Z 0 [Warning] InnoDB: Page 5 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=254575]
2017-07-07T22:36:52.848866Z 0 [Warning] InnoDB: Page 6 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=231687]
2017-07-07T22:36:52.849812Z 0 [Warning] InnoDB: Page 34 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=196927]
2017-07-07T22:36:52.849848Z 0 [Warning] InnoDB: Page 35 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=214280]
2017-07-07T22:36:52.849860Z 0 [Warning] InnoDB: Page 36 in the doublewrite buffer is not within space bounds: page [page id: space=145, page number=233064]
2017-07-07T22:36:52.903722Z 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 34 row operations to undo
2017-07-07T22:36:52.903773Z 0 [Note] InnoDB: Trx id counter is 3117056
2017-07-07T22:36:52.903807Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...

[scrollman]

というログでした

[yoorwm]

InnoDBが壊れているみたいですね。そのまま
「If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.」で検索してみた感じ、
http://artisan.hatenablog.com/entry/2014/11/22/224337
ここらへん参考にすると良いかと思います。

[scrollman]

参考にしてみました。

最初は値を１にして、MySQLの起動を行う。ダメだったら、この値を１ずつ足していき、６までMySQLが起動するまで試す。
sudo vim /etc/mysql/my.cnf
[mysqld]
innodb_force_recovery = 3

という記述があったのですが、

my.cnfの中にそのような部分は見当たりませんでした。
my.cnfの中はこのようになってました
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[yoorwm]

あまり見ない形式ですね。ひょっとして、書かれている/etc/mysql/conf.d /etc/mysql/mysql.conf.d/の内容を読み込むようになっているのでしょうか？

[scrollman]

読み込むようになっていたようです。
my.cnfの設定をリカバリーモードにして起動。
破損箇所が大きかったのか、少ししかエクスポートできませんでした。
データベースのあるフォルダ諸共アンインストール→インストール→インポートで無事MySQL復旧しました
ありがとうございます。

Answer 2

過去の質問 を参考にされては？

Comments

[scrollman]

>1.解消方法教えてくださいm(__)m
mysql.sockは自動で作成されるのでこちらを削除してください。
*pidファイルも作られてたら削除してください。

が、過去の質問の回答だと思うのですが、
.sockもpidも作られいないのです。

[scrollman]

と思っていたのは勘違いで、
/var/lib/mysqlにちゃんとありました。

[scrollman]

mysql.sockを発見したので消去。

./mysql start しても同じエラーが発生しました。