2017年02月06日
このエントリーをはてなブックマークに追加
お客さんから連絡がありました。
Mysqlでデータを修正していたら、何かの作業のときにMySQLが落ちて、ログインできなくなりました。と。

そのときには、よくあるようなトラブルだろうと考えていました。ありがちなところでは、メモリ不足でプロセスが落とされるとか。インポートデータが大きすぎて、PHPなどの容量制限にかかっているとか、Apacheのタイムアウトとか。そしてそれらの負荷によって反応が遅くなるとか。

お客さんの作業内容を聞くと、他環境からのDBのインポートを行った。それに伴ってユーザーデータもインポートしている。

そのように言われました。
うーん。作業内容には特に問題となるようなところは無いなーそう思っていました。

サービスの再起動ではエラーは特に出ずに、反応が無くなります。しかしプロセスは safe のものが実行されているようでした。

# ps -ef |grep mysql
出てきたプロセスを落とします。

# kill -9 [PID]

これで mysql 関連のプロセスが無くなったことを確認します。

エラーログの確認。なお再起動を繰り返しているようで、以下の行が何回も表示されます。

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2017-03-22 22:55:15 26917 [Note] Plugin 'FEDERATED' is disabled.
2017-03-22 22:55:15 26917 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-22 22:55:15 26917 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-22 22:55:15 26917 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-22 22:55:15 26917 [Note] InnoDB: Memory barrier is not used
2017-03-22 22:55:15 26917 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-22 22:55:15 26917 [Note] InnoDB: Using Linux native AIO
2017-03-22 22:55:15 26917 [Note] InnoDB: Using CPU crc32 instructions
2017-03-22 22:55:15 26917 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-03-22 22:55:15 26917 [Note] InnoDB: Completed initialization of buffer pool
2017-03-22 22:55:15 26917 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-22 22:55:15 26917 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-22 22:55:15 26917 [Note] InnoDB: Waiting for purge to start
2017-03-22 22:55:15 26917 [Note] InnoDB: 5.6.35 started; log sequence number 1737079
2017-03-22 22:55:15 26917 [Note] Server hostname (bind-address): '*'; port: 3306
2017-03-22 22:55:15 26917 [Note] IPv6 is available.
2017-03-22 22:55:15 26917 [Note] - '::' resolves to '::';
2017-03-22 22:55:15 26917 [Note] Server socket created on IP: '::'.
13:55:15 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68108 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x22257c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ffd8cf78828 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8e3535]
/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x6676f4]
/lib64/libpthread.so.0[0x34eae0f7e0]
/usr/sbin/mysqld(_Z9get_fieldP11st_mem_rootP5Field+0x5a)[0x77805a]
/usr/sbin/mysqld[0x681782]
/usr/sbin/mysqld(_Z10acl_reloadP3THD+0x421)[0x682de1]
/usr/sbin/mysqld(_Z8acl_initb+0x120)[0x6830a0]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0xa16)[0x5a2c56]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x34eaa1ed1d]
/usr/sbin/mysqld[0x593d09]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 0
Status: NOT_KILLED



以下のコマンドで、認証情報をスキップして起動。
# /usr/bin/mysqld_safe --user=root --skip-grant-tables

これでは問題無く起動しました。
で、結局の原因ですが以下の通りでした。

他のDBからデータをインポートするとき、なんとテーブルの初期化部分から実行していたようです。しかも違うバージョンのを!
ということで、僕の管理していたMysqlは、異なるバージョンの user テーブルをインポートされてしまい、システムがおかしくなってしまったようです。

以下の方法で修復しました。
既存テーブルの削除
# rm -rf /var/lib/mysql/mysql/user.MYD
# rm -rf /var/lib/mysql/mysql/user.MYI
# rm -rf /var/lib/mysql/mysql/user.frm

DB実行ユーザーでログイン
# su - mysql

初期化コマンドの実行
-bash-4.1$ mysql_install_db

WARNING: Could not write to config file /usr/my.cnf: 許可がありません

Installing MySQL system tables...2017-03-23 16:57:29 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-03-23 16:57:29 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2017-03-23 16:57:29 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 25327 ...
2017-03-23 16:57:29 25327 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2017-03-23 16:57:29 25327 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2017-03-23 16:57:29 25327 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-23 16:57:29 25327 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-23 16:57:29 25327 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-23 16:57:29 25327 [Note] InnoDB: Memory barrier is not used
2017-03-23 16:57:29 25327 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-23 16:57:29 25327 [Note] InnoDB: Using Linux native AIO
2017-03-23 16:57:29 25327 [Note] InnoDB: Using CPU crc32 instructions
2017-03-23 16:57:29 25327 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-03-23 16:57:29 25327 [Note] InnoDB: Completed initialization of buffer pool
2017-03-23 16:57:29 25327 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-23 16:57:29 25327 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-23 16:57:29 25327 [Note] InnoDB: Waiting for purge to start
2017-03-23 16:57:29 25327 [Note] InnoDB: 5.6.35 started; log sequence number 1619621
2017-03-23 16:57:29 25327 [Note] Binlog end
2017-03-23 16:57:29 25327 [Note] InnoDB: FTS optimize thread exiting.
2017-03-23 16:57:29 25327 [Note] InnoDB: Starting shutdown...
2017-03-23 16:57:31 25327 [Note] InnoDB: Shutdown completed; log sequence number 1619631
OK

Filling help tables...2017-03-23 16:57:31 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-03-23 16:57:31 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2017-03-23 16:57:31 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 25350 ...
2017-03-23 16:57:31 25350 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2017-03-23 16:57:31 25350 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2017-03-23 16:57:31 25350 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-23 16:57:31 25350 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-23 16:57:31 25350 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-23 16:57:31 25350 [Note] InnoDB: Memory barrier is not used
2017-03-23 16:57:31 25350 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-23 16:57:31 25350 [Note] InnoDB: Using Linux native AIO
2017-03-23 16:57:31 25350 [Note] InnoDB: Using CPU crc32 instructions
2017-03-23 16:57:31 25350 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-03-23 16:57:31 25350 [Note] InnoDB: Completed initialization of buffer pool
2017-03-23 16:57:31 25350 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-23 16:57:31 25350 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-23 16:57:31 25350 [Note] InnoDB: Waiting for purge to start
2017-03-23 16:57:31 25350 [Note] InnoDB: 5.6.35 started; log sequence number 1619631
2017-03-23 16:57:31 25350 [Note] Binlog end
2017-03-23 16:57:31 25350 [Note] InnoDB: FTS optimize thread exiting.
2017-03-23 16:57:31 25350 [Note] InnoDB: Starting shutdown...
2017-03-23 16:57:33 25350 [Note] InnoDB: Shutdown completed; log sequence number 1619641
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:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h log-server password 'new-password'

Alternatively you can run:

/usr/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 /usr ; /usr/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: Could not copy config file template /usr/share/mysql/my-default.cnf to
/usr/my.cnf, may not have access rights to do so.
You may want to copy the file manually, or create your own,
it will then be used by default by the server when you start it.

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

-bash-4.1$ exit

パスワード再設定
# /usr/bin/mysqladmin -u root password 'new-password'

以上で user テーブルが初期化されました。
もちろん既存のユーザーはすべて削除されるので注意してね!


stock_value at 17:36│Comments(0)TrackBack(0)技術:2017年 

トラックバックURL

この記事にコメントする

名前:
URL:
  情報を記憶: 評価: 顔