ローカルホストのMySQL接続ができない ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/my sql.sock' (2) ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (110) ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (4) ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: NO) ERROR 1040 (08004): Too many connections ERROR 3032 (HY000): The server is currently in offline mode etc.. 8/87
エラーコードに注目する ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/my sql.sock' (2) ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (110) ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (4) ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: NO) ERROR 1040 (08004): Too many connections ERROR 3032 (HY000): The server is currently in offline mode etc.. mysql コマンドラインクライアントが接続に失敗した時の出力のフォーマットは、 MySQLのエラーコード + SQLSTATE + エラーメッセージ + OSのエラーコード SQLSTATEは切り分けには重要でないので以降割愛 ‐ 9/87
エラーコードに注目する ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/my sql.sock' (2) MySQLエラーコード: 2002 エラーメッセージ: Can't connect to local MySQL server through socket '/var/ lib/mysql/mysql.sock' OSエラーコード: 2 Linuxでは 2 = ENOENT = No such file or directory ‐ 10/87
エラーコードに注目する ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) MySQLエラーコード: 2003 エラーメッセージ: Can't connect to MySQL server on '127.0.0.1' OSエラーコード: 111 Linuxでは 111 = ECONNREFUSED = Connection refused ‐ 11/87
エラーコードに注目する ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (110) MySQLエラーコード: 2003 エラーメッセージ: Can't connect to MySQL server on '127.0.0.1' OSエラーコード: 110 Linuxでは 110 = ETIMEDOUT = Connection timed out ‐ 12/87
エラーコードに注目する ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (4) MySQLエラーコード: 2003 エラーメッセージ: Can't connect to MySQL server on '127.0.0.1' OSエラーコード: 4 Linuxでは 4 = EINTR = Interrupted system call ‐ 13/87
エラーコードに注目する ERROR 3032 (HY000): The server is currently in offline mode MySQLエラーコード: 3032 エラーメッセージ: The server is currently in offline mode OSエラーコード: なし 16/87
SHOW PROCESSLISTから読み解くもの mysql> SHOW PROCESSLIST; .. 4 rows in set (0.00 sec) 取り敢えず一発目はログが流れても構わないので最後の n rows in set を見ましょ う それがほぼ （レプリケーション関連のスレッドやINSERT Delayedスレッド、イベントスケジューラーなどを含むが大体は数が小さいので無視して構わない） 現在の接続中（接続 シーケンス中のクライアントコネクションも含むが、 max_connections とかは接続 シーケンス中のものも含むので問題ないはず）のコネクションの数になる 23/87
SHOW PROCESSLISTから読み解くもの mysql> SHOW PROCESSLIST; .. 4 rows in set (0.00 sec) n rows in set を見た時に、 max_connections の値に対して余裕があるか？ ‐ 数回叩いても平常時と比べて非常に高い/低い値をさまよっていないか？ ‐ 24/87
SHOW PROCESSLISTから更に読み解くもの mysql> pager grep -v Sleep mysql> SHOW PROCESSLIST; .. 4 rows in set (0.00 sec) ざっとスリープ以外の出力結果を見た時に、 バックグラウンドスレッド以外で Time が異様に大きいクエリーはないか？ ‐ Info には実行中のクエリーが表示されるが、似たようなクエリーが並んでいないか？ ‐ 26/87
SHOW ENGINE INNODB STATUSのTRANSACTIONSセクション mysql> SHOW ENGINE INNODB STATUS\G .. ------------ TRANSACTIONS ------------ Trx id counter 22588 Purge done for trx's n:o < 22588 undo n:o < 0 state: running but idle History list length 678 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 2, OS thread handle 0x7f6bd0094700, query id 13 localhost root init show engine innodb status ---TRANSACTION 22275, ACTIVE 1397 sec rollback ROLLING BACK 542497 lock struct(s), heap size 64420280, 100542496 row lock(s), undo log entries 52 636267 MySQL thread id 1, OS thread handle 0x7f6bd00d5700, query id 8 localhost root init ROLLBACK -------- FILE I/O -------- .. 37/87
マスターのバイナリログ末尾が欠損すると mysql> SHOW SLAVE STATUS\G .. Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'C lient requested master to start replication from impossible position; the first event 'my sql-bin.003583' at 45997491, the last event read from './mysql-bin.003583' at 4, the last byte read from './mysql-bin.003583' at 4.'.. 52/87
リレーログ - master_info - relay_log_info - ストレージエンジン間の整合性 が崩れると mysql> SHOW SLAVE STATUS\G .. Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Delete_rows event on table test.t; Can't find record i n 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql -bin.000002, end_log_pos 333 .. 56/87
雑なエラーログの読み取り方(全景) 190618 2:06:02 - 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=2097152 max_used_connections=11 max_threads=500 thread_count=10 connection_count=10 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5133883 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x72b8550 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 = 0x7f3f608e6e68 thread_stack 0x30000 /tools/mysql/bin/mysqld(my_print_stacktrace+0x39)[0x798989] /tools/mysql/bin/mysqld(handle_segfault+0x380)[0x4fde40] /lib64/libpthread.so.0[0x3ca3a0f710] /tools/mysql/bin/mysqld(hp_movelink+0xb)[0x918a5b] /tools/mysql/bin/mysqld(hp_write_key+0x22e)[0x91ba0e] /tools/mysql/bin/mysqld(heap_write+0x9b)[0x91bdcb] /tools/mysql/bin/mysqld(_ZN7ha_heap9write_rowEPh+0x43)[0x917d13] /tools/mysql/bin/mysqld(_ZN7handler12ha_write_rowEPh+0x5c)[0x66db2c] /tools/mysql/bin/mysqld[0x5a41e4] /tools/mysql/bin/mysqld[0x59283b] /tools/mysql/bin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x86)[0x597ec6] /tools/mysql/bin/mysqld[0x59d6dd] /tools/mysql/bin/mysqld(_ZN4JOIN4execEv+0x4ef)[0x5b139f] /tools/mysql/bin/mysqld(_ZN18st_select_lex_unit4execEv+0x1f1)[0x5deea1] /tools/mysql/bin/mysqld(_Z21mysql_derived_fillingP3THDP3LEXP10TABLE_LIST+0x168)[0x559b88] /tools/mysql/bin/mysqld(_Z20mysql_handle_derivedP3LEXPFbP3THDS0_P10TABLE_LISTE+0x69)[0x559989] /tools/mysql/bin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x125)[0x541955] /tools/mysql/bin/mysqld[0x5715c5] /tools/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x226a)[0x576c4a] /tools/mysql/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x15a)[0x5790ba] /tools/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1321)[0x57ac31] /tools/mysql/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x60f0b7] /tools/mysql/bin/mysqld(handle_one_connection+0x54)[0x60f124] /lib64/libpthread.so.0[0x3ca3a079d1] /lib64/libc.so.6(clone+0x6d)[0x3ca32e88fd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f3f20004c00): SELECT dst_ip, SUM(total_stream) AS total_stream, SUM(up_stream) AS up_stream, SUM(down_stream) AS down_stream FROM ( SELECT dst_ip, SUM(oct_upstream + oct_downstream) AS total_stream, SUM(oct_upstream) AS up_stream, SUM(oct_downstream) AS down_stream FROM t_monitor_user_app_if_zone_critical_60min_0 _1560556800 WHERE timestamp >= 1560708343 AND timestamp <= 1560794743 AND src_user = 0 GROUP BY dst_ip UNION ALL SELECT dst_ip, SUM(oct_upstream + oct_downstream) AS total_stream, SUM(oct_upstream) AS up_stream, SUM(oct_downstream) AS down_stream FROM t_monitor_user_app_if_zone_critical_60min_0_1560643200 WHERE timestamp >= 1560708343 AND timestamp <= 1560794743 AND src_user = 0 GROUP BY dst_ip UNION ALL SELECT dst_ip, SUM(oct_upstream + oct_downstream) AS total_stream, SUM(oct_upstream) AS up_stream, SUM(oct_downstream) AS down_stream FROM t_monitor_user_app_if_zone_critical_60min_0_1560729600 WHERE timestamp >= 1560708343 AND timestam p <= 1560794743 AND src_user = 0 GROUP BY dst_ip UNION AL Connection ID (thread ID): 350 Status: NOT_KILLED https://bugs.mysql.com/bug.php?id=95932 71/87
雑なエラーログの読み取り方(2) クエリーが直接の原因の場合、当該のクエリーがここに現れることも（現れないこ とも）ある ここに表示されていない, 表示が切り詰められているけど全部知りたい場合はコアファイル解析 ‐ Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f3f20004c00): SELECT dst_ip, SUM(total_stream) AS total_stream, SUM(up_stream) AS up_s tream, SUM(down_stream) AS down_stream FROM ( SELECT dst_ip, SUM(oct_upstream + oct_downstream ) AS total_stream, SUM(oct_upstream) AS up_stream, SUM(oct_downstream) AS down_stream FROM t_mon itor_user_app_if_zone_critical_60min_0_1560556800 WHERE timestamp >= 1560708343 AND timestamp <= 1560794743 AND src_user = 0 GROUP BY dst_ip UNION ALL SELECT dst_ip, SUM(oct_upstream + oct _downstream) AS total_stream, SUM(oct_upstream) AS up_stream, SUM(oct_downstream) AS down_stream FROM t_monitor_user_app_if_zone_critical_60min_0_1560643200 WHERE timestamp >= 1560708343 AND ti mestamp <= 1560794743 AND src_user = 0 GROUP BY dst_ip UNION ALL SELECT dst_ip, SUM(oct_ups tream + oct_downstream) AS total_stream, SUM(oct_upstream) AS up_stream, SUM(oct_downstream) AS d own_stream FROM t_monitor_user_app_if_zone_critical_60min_0_1560729600 WHERE timestamp >= 156070 8343 AND timestamp <= 1560794743 AND src_user = 0 GROUP BY dst_ip UNION AL Connection ID (thread ID): 350 Status: NOT_KILLED 73/87
雑なエラーログの読み取り方(3) これは単なる「ハードコードされた決まり文句」なのであまり真に受けない 計算式が今更MyISAMなのも古いままで更新されていない以外に深い意味はない（はず） ‐ 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=2097152 max_used_connections=11 max_threads=500 thread_count=10 connection_count=10 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5133883 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 74/87
yoku0825
takasehideki
chumajin
k1low
poteboy
pkshadeck
onlinesalon
minorun365
oldbigbuddha
route06
rilmayer
lemiorhan
okamototakuyasr2
nonsquared
hatefulcrawdad
jonyablonski
marktimemedia
mthomps
chriscoyier
dougneiner
chrisshort
thekraken
kneath
skipperchong
jrom