mysql


サーバーがつながらない。mysqlにつながらない

ある日突然、運営中のwebサービスに繋がらなくなった。
正確にいうと、webサービスには繋がっているが、mysql にアクセスできない。

参考になりそうなページ：https://totutotu.hatenablog.com/entry/2015/12/07/173936
サイトにアクセスしたときに表示された PHP のエラーメッセージ
-----------------------------------------------------------
Warning: mysql_set_charset(): No such file or directory in /var/www/libs/～～～～

Warning: mysql_set_charset(): A link to the server could not be established in /var/www/～～～～～
ConnectError!DB Error: connect failed
-----------------------------------------------------------
前回と同じエラー。
サーバーを再起動したら mysql に繋がらなくなった。

mysqld の状態を確認
-----------------------------------------------------------
service mysqld status
mysqld は停止していますがサブシステムがロックされています
-----------------------------------------------------------
（↑これはrootでない状態で確認した）

サーバーのerror_log
-----------------------------------------------------------
[Sun Sep 12 11:30:15 2021] [error] server reached MaxClients setting, consider raising the MaxClients setting
-----------------------------------------------------------

システムのログを調べる
-----------------------------------------------------------
cat /var/log/messages
-----------------------------------------------------------
サーバー再起動後だったため再起動前の情報が見えなかったので不明

ターミナルから mysqld のログを調べる
-----------------------------------------------------------
cat /var/log/mysqld.log
-----------------------------------------------------------

 
怪しいそうな部分だけ抜粋
-------------------------------------------------------------------------
2021-09-12 10:14:41 1623 [Note] - '::' resolves to '::';
2021-09-12 10:14:41 1623 [Note] Server socket created on IP: '::'.
2021-09-12 10:14:41 1623 [Note] Event Scheduler: Loaded 0 events
2021-09-12 10:14:41 1623 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:32:49 3983 [Note] Plugin 'FEDERATED' is disabled. ← おそらく起動開始がここ。disabled = 無効
2021-09-12 11:32:49 3983 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:32:49 3983 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:32:49 3983 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:32:49 3983 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:32:49 3983 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:32:49 3983 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:32:49 3983 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:32:49 3983 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:32:49 3983 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:32:50 3983 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:32:50 3983 [Note] InnoDB: The log sequence numbers 209069373861 and 209069373861 in ibdata files do not match the log sequence number 209069557708 in the ib_logfiles!
2021-09-12 11:32:50 3983 [Note] InnoDB: Database was not shutdown normally!
2021-09-12 11:32:50 3983 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:32:50 3983 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:32:50 3983 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:32:50 3983 [Note] InnoDB: from the doublewrite buffer...
2021-09-12 11:32:50 3983 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:32:50 3983 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:32:50 3983 [Note] InnoDB: 5.6.36 started; log sequence number 209069557708
2021-09-12 11:32:50 3983 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:32:50 3983 [Note] IPv6 is available.
2021-09-12 11:32:50 3983 [Note] - '::' resolves to '::';
2021-09-12 11:32:50 3983 [Note] Server socket created on IP: '::'.
2021-09-12 11:32:50 3983 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:32:50 3983 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:33:06 4025 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:33:06 4025 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:33:06 4025 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:33:06 4025 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:33:06 4025 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:33:06 4025 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:33:06 4025 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:33:06 4025 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:33:06 4025 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:33:07 4025 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:33:07 4025 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:33:07 4025 [Note] InnoDB: The log sequence numbers 209069373861 and 209069373861 in ibdata files do not match the log sequence number 209069557927 in the ib_logfiles!
2021-09-12 11:33:07 4025 [Note] InnoDB: Database was not shutdown normally!
2021-09-12 11:33:07 4025 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:33:07 4025 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:33:07 4025 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:33:07 4025 [Note] InnoDB: from the doublewrite buffer...
2021-09-12 11:33:07 4025 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:33:07 4025 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:33:07 4025 [Note] InnoDB: 5.6.36 started; log sequence number 209069557927
2021-09-12 11:33:07 4025 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:33:07 4025 [Note] IPv6 is available.
2021-09-12 11:33:07 4025 [Note] - '::' resolves to '::';
2021-09-12 11:33:07 4025 [Note] Server socket created on IP: '::'.
2021-09-12 11:33:07 4025 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:33:07 4025 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:33:12 4068 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:33:12 4068 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:33:12 4068 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:33:12 4068 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:33:12 4068 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:33:12 4068 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:33:12 4068 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:33:12 4068 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:33:12 4068 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:33:12 4068 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:33:12 4068 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:33:12 4068 [Note] InnoDB: The log sequence numbers 209069373861 and 209069373861 in ibdata files do not match the log sequence number 209069557937 in the ib_logfiles!
2021-09-12 11:33:12 4068 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:33:12 4068 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:33:12 4068 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:33:13 4068 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:33:13 4068 [Note] InnoDB: from the doublewrite buffer...
2021-09-12 11:33:13 4068 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:33:13 4068 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:33:13 4068 [Note] InnoDB: 5.6.36 started; log sequence number 209069557937
2021-09-12 11:33:13 4068 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:33:13 4068 [Note] IPv6 is available.
2021-09-12 11:33:13 4068 [Note] - '::' resolves to '::';
2021-09-12 11:33:13 4068 [Note] Server socket created on IP: '::'.
2021-09-12 11:33:13 4068 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:33:13 4068 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:33:17 4110 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:33:17 4110 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:33:17 4110 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:33:17 4110 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:33:17 4110 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:33:17 4110 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:33:17 4110 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:33:17 4110 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:33:17 4110 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:33:17 4110 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:33:17 4110 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:33:17 4110 [Note] InnoDB: Log scan progressed past the checkpoint lsn 209069557937
2021-09-12 11:33:17 4110 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:33:17 4110 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:33:17 4110 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:33:18 4110 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:33:18 4110 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 209069557947
2021-09-12 11:33:18 4110 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:33:18 4110 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:33:18 4110 [Note] InnoDB: 5.6.36 started; log sequence number 209069557947
2021-09-12 11:33:19 4110 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:33:19 4110 [Note] IPv6 is available.
2021-09-12 11:33:19 4110 [Note] - '::' resolves to '::';
2021-09-12 11:33:19 4110 [Note] Server socket created on IP: '::'.
2021-09-12 11:33:19 4110 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:33:19 4110 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:33:22 4152 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:33:22 4152 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:33:22 4152 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:33:22 4152 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:33:22 4152 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:33:22 4152 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:33:22 4152 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:33:23 4152 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:33:23 4152 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:33:23 4152 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:33:23 4152 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:33:23 4152 [Note] InnoDB: The log sequence numbers 209069373861 and 209069373861 in ibdata files do not match the log sequence number 209069557957 in the ib_logfiles!
2021-09-12 11:33:23 4152 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:33:23 4152 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:33:23 4152 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:33:23 4152 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:33:23 4152 [Note] InnoDB: from the doublewrite buffer...
2021-09-12 11:33:24 4152 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:33:24 4152 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:33:24 4152 [Note] InnoDB: 5.6.36 started; log sequence number 209069557957
2021-09-12 11:33:24 4152 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:33:24 4152 [Note] IPv6 is available.
2021-09-12 11:33:24 4152 [Note] - '::' resolves to '::';
2021-09-12 11:33:24 4152 [Note] Server socket created on IP: '::'.
2021-09-12 11:33:24 4152 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:33:24 4152 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:33:28 4201 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:33:28 4201 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:33:28 4201 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:33:28 4201 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:33:28 4201 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:33:28 4201 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:33:28 4201 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:33:28 4201 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:33:28 4201 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:33:28 4201 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:33:28 4201 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:33:28 4201 [Note] InnoDB: Log scan progressed past the checkpoint lsn 209069557957
2021-09-12 11:33:28 4201 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:33:28 4201 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:33:28 4201 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:33:28 4201 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:33:28 4201 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 209069558157
2021-09-12 11:33:29 4201 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2021-09-12 11:33:29 4201 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:33:29 4201 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:33:29 4201 [Note] InnoDB: 5.6.36 started; log sequence number 209069558157
2021-09-12 11:33:29 4201 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:33:29 4201 [Note] IPv6 is available.
2021-09-12 11:33:29 4201 [Note] - '::' resolves to '::';
2021-09-12 11:33:29 4201 [Note] Server socket created on IP: '::'.
2021-09-12 11:33:29 4201 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:33:29 4201 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:33:37 4265 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:33:37 4265 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:33:37 4265 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:33:37 4265 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:33:37 4265 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:33:37 4265 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:33:37 4265 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:33:37 4265 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:33:37 4265 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:33:37 4265 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:33:37 4265 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:33:37 4265 [Note] InnoDB: The log sequence numbers 209069373861 and 209069373861 in ibdata files do not match the log sequence number 209069558563 in the ib_logfiles!
2021-09-12 11:33:37 4265 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:33:37 4265 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:33:37 4265 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:33:37 4265 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:33:37 4265 [Note] InnoDB: from the doublewrite buffer...
2021-09-12 11:33:38 4265 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:33:38 4265 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:33:38 4265 [Note] InnoDB: 5.6.36 started; log sequence number 209069558563
2021-09-12 11:33:38 4265 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:33:38 4265 [Note] IPv6 is available.
2021-09-12 11:33:38 4265 [Note] - '::' resolves to '::';
2021-09-12 11:33:38 4265 [Note] Server socket created on IP: '::'.
2021-09-12 11:33:38 4265 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:33:38 4265 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:36:14 4355 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:36:14 4355 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:36:14 4355 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:36:14 4355 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:36:14 4355 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:36:14 4355 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:36:14 4355 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:36:14 4355 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:36:14 4355 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:36:14 4355 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:36:14 4355 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:36:14 4355 [Note] InnoDB: The log sequence numbers 209069373861 and 209069373861 in ibdata files do not match the log sequence number 209069568015 in the ib_logfiles!
2021-09-12 11:36:14 4355 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:36:14 4355 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:36:14 4355 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:36:14 4355 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:36:14 4355 [Note] InnoDB: from the doublewrite buffer...
2021-09-12 11:36:14 4355 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:36:14 4355 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:36:14 4355 [Note] InnoDB: 5.6.36 started; log sequence number 209069568015
2021-09-12 11:36:14 4355 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:36:14 4355 [Note] IPv6 is available.
2021-09-12 11:36:14 4355 [Note] - '::' resolves to '::';
2021-09-12 11:36:14 4355 [Note] Server socket created on IP: '::'.
2021-09-12 11:36:14 4355 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:36:14 4355 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:36:21 4404 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:36:21 4404 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:36:21 4404 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:36:21 4404 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:36:21 4404 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:36:21 4404 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:36:21 4404 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:36:21 4404 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:36:21 4404 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:36:21 4404 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:36:21 4404 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:36:21 4404 [Note] InnoDB: The log sequence numbers 209069373861 and 209069373861 in ibdata files do not match the log sequence number 209069568235 in the ib_logfiles!
2021-09-12 11:36:21 4404 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:36:21 4404 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:36:21 4404 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:36:21 4404 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:36:21 4404 [Note] InnoDB: from the doublewrite buffer...
2021-09-12 11:36:21 4404 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:36:21 4404 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:36:21 4404 [Note] InnoDB: 5.6.36 started; log sequence number 209069568235
2021-09-12 11:36:21 4404 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:36:21 4404 [Note] IPv6 is available.
2021-09-12 11:36:21 4404 [Note] - '::' resolves to '::';
2021-09-12 11:36:21 4404 [Note] Server socket created on IP: '::'.
2021-09-12 11:36:21 4404 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:36:21 4404 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:36:25 4449 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:36:25 4449 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:36:25 4449 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:36:25 4449 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:36:25 4449 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:36:25 4449 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:36:25 4449 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:36:25 4449 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:36:25 4449 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:36:25 4449 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:36:25 4449 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:36:25 4449 [Note] InnoDB: Log scan progressed past the checkpoint lsn 209069568235
2021-09-12 11:36:25 4449 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:36:25 4449 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:36:25 4449 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:36:25 4449 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:36:25 4449 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 209069568440
2021-09-12 11:36:25 4449 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2021-09-12 11:36:26 4449 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:36:26 4449 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:36:26 4449 [Note] InnoDB: 5.6.36 started; log sequence number 209069568440
2021-09-12 11:36:26 4449 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:36:26 4449 [Note] IPv6 is available.
2021-09-12 11:36:26 4449 [Note] - '::' resolves to '::';
2021-09-12 11:36:26 4449 [Note] Server socket created on IP: '::'.
2021-09-12 11:36:26 4449 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:36:26 4449 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:36:28 4503 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:36:28 4503 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:36:28 4503 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:36:28 4503 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:36:28 4503 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:36:28 4503 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:36:28 4503 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:36:28 4503 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:36:28 4503 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:36:28 4503 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:36:28 4503 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:36:28 4503 [Note] InnoDB: Log scan progressed past the checkpoint lsn 209069568235
2021-09-12 11:36:28 4503 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:36:28 4503 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:36:28 4503 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:36:28 4503 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:36:28 4503 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 209069568450
2021-09-12 11:36:28 4503 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2021-09-12 11:36:29 4503 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:36:29 4503 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:36:29 4503 [Note] InnoDB: 5.6.36 started; log sequence number 209069568450
2021-09-12 11:36:29 4503 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:36:29 4503 [Note] IPv6 is available.
2021-09-12 11:36:29 4503 [Note] - '::' resolves to '::';
2021-09-12 11:36:29 4503 [Note] Server socket created on IP: '::'.
2021-09-12 11:36:29 4503 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:36:29 4503 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:36:32 4551 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:36:32 4551 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:36:32 4551 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:36:32 4551 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:36:32 4551 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:36:32 4551 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:36:32 4551 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:36:32 4551 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:36:32 4551 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:36:32 4551 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:36:32 4551 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:36:32 4551 [Note] InnoDB: Log scan progressed past the checkpoint lsn 209069568450
2021-09-12 11:36:32 4551 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:36:32 4551 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:36:32 4551 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:36:32 4551 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:36:32 4551 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 209069568460
2021-09-12 11:36:32 4551 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:36:32 4551 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:36:32 4551 [Note] InnoDB: 5.6.36 started; log sequence number 209069568460
2021-09-12 11:36:32 4551 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:36:32 4551 [Note] IPv6 is available.
2021-09-12 11:36:32 4551 [Note] - '::' resolves to '::';
2021-09-12 11:36:32 4551 [Note] Server socket created on IP: '::'.
2021-09-12 11:36:32 4551 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:36:32 4551 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:36:35 4601 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:36:35 4601 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:36:35 4601 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:36:35 4601 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:36:35 4601 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:36:35 4601 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:36:35 4601 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:36:35 4601 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:36:35 4601 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:36:35 4601 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:36:35 4601 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:36:35 4601 [Note] InnoDB: Log scan progressed past the checkpoint lsn 209069568460
2021-09-12 11:36:35 4601 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:36:35 4601 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:36:35 4601 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:36:35 4601 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:36:35 4601 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 209069568470
2021-09-12 11:36:36 4601 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:36:36 4601 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:36:36 4601 [Note] InnoDB: 5.6.36 started; log sequence number 209069568470
2021-09-12 11:36:36 4601 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:36:36 4601 [Note] IPv6 is available.
2021-09-12 11:36:36 4601 [Note] - '::' resolves to '::';
2021-09-12 11:36:36 4601 [Note] Server socket created on IP: '::'.
2021-09-12 11:36:36 4601 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:36:36 4601 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:36:39 4641 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:36:39 4641 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:36:39 4641 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:36:39 4641 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:36:39 4641 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:36:39 4641 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:36:39 4641 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:36:39 4641 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:36:39 4641 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:36:39 4641 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:36:39 4641 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:36:39 4641 [Note] InnoDB: Log scan progressed past the checkpoint lsn 209069568470
2021-09-12 11:36:39 4641 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:36:39 4641 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:36:39 4641 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:36:39 4641 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:36:39 4641 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 209069568480
2021-09-12 11:36:39 4641 [Note] InnoDB: 128 rollback segment(s) are active.
2021-09-12 11:36:39 4641 [Note] InnoDB: Waiting for purge to start
2021-09-12 11:36:39 4641 [Note] InnoDB: 5.6.36 started; log sequence number 209069568480
2021-09-12 11:36:39 4641 [Note] Server hostname (bind-address): '*'; port: 3306
2021-09-12 11:36:39 4641 [Note] IPv6 is available.
2021-09-12 11:36:39 4641 [Note] - '::' resolves to '::';
2021-09-12 11:36:39 4641 [Note] Server socket created on IP: '::'.
2021-09-12 11:36:39 4641 [Note] Event Scheduler: Loaded 0 events
2021-09-12 11:36:39 4641 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

2021-09-12 11:36:42 4689 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 11:36:42 4689 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:36:42 4689 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:36:42 4689 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 11:36:42 4689 [Note] InnoDB: Memory barrier is not used
2021-09-12 11:36:42 4689 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 11:36:42 4689 [Note] InnoDB: Using Linux native AIO
2021-09-12 11:36:42 4689 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 11:36:42 4689 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2021-09-12 11:36:42 4689 [Note] InnoDB: Completed initialization of buffer pool
2021-09-12 11:36:42 4689 [Note] InnoDB: Highest supported file format is Barracuda.
2021-09-12 11:36:42 4689 [Note] InnoDB: Log scan progressed past the checkpoint lsn 209069568470
2021-09-12 11:36:42 4689 [Note] InnoDB: InnoDB: Database was not shutdown normally!
2021-09-12 11:36:42 4689 [Note] InnoDB: Starting crash recovery.
2021-09-12 11:36:42 4689 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-09-12 11:36:42 4689 [Note] InnoDB: Restoring possible half-written data pages
2021-09-12 11:36:42 4689 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 209069568490 ← この後に mysqld が停止してログ無しみたい

2021-09-12 11:47:07 1622 [Note] Plugin 'FEDERATED' is disabled. ←サーバー手動再起動後
2021-09-12 11:47:07 1622 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 11:47:07 1622 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 11:47:07 1622 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-------------------------------------------------------------------------
「InnoDB: Database was not shutdown normally!」というログが短時間のうちに複数あり、再起動を繰り返しているのが分かる。
これ以前を解析していけばシャットダウンの原因が分かるはず。

もう少しログをさかのぼったらこんな記述も見つけた。これも mysql が停止したタイミングもログと思われる
-------------------------------------------------------------------------
2021-09-12 09:01:57 1810 [Note] Plugin 'FEDERATED' is disabled.
2021-09-12 09:01:57 1810 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-09-12 09:01:57 1810 [Note] InnoDB: The InnoDB memory heap is disabled
2021-09-12 09:01:57 1810 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-09-12 09:01:57 1810 [Note] InnoDB: Memory barrier is not used
2021-09-12 09:01:57 1810 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-09-12 09:01:57 1810 [Note] InnoDB: Using Linux native AIO
2021-09-12 09:01:57 1810 [Note] InnoDB: Using CPU crc32 instructions
2021-09-12 09:01:57 1810 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2021-09-12 09:01:57 1810 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2021-09-12 09:01:57 1810 [ERROR] Plugin 'InnoDB' init function returned error.
2021-09-12 09:01:57 1810 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2021-09-12 09:01:57 1810 [ERROR] Unknown/unsupported storage engine: InnoDB
2021-09-12 09:01:57 1810 [ERROR] Aborting

2021-09-12 09:01:57 1810 [Note] Binlog end
2021-09-12 09:01:57 1810 [Note] Shutting down plugin 'partition'
2021-09-12 09:01:57 1810 [Note] Shutting down plugin 'ARCHIVE'
2021-09-12 09:01:57 1810 [Note] Shutting down plugin 'BLACKHOLE'
2021-09-12 09:01:57 1810 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
-------------------------------------------------------------------------

短い時間で解決策を調べたけど不明。

とりあえずバッファプールへのメモリ割り当てを上げておく。
参考ページ：https://tech.librastudio.co.jp/entry/index.php/2018/05/22/post-1891/

現状を確認。128Mであることが分かる
--------------------------------------------------------
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
--------------------------------------------------------

my.cnf を編集（my.conf でない）
--------------------------------------------------------
vi my.cnf
--------------------------------------------------------

--------------------------------------------------------
[mysqld]
...
innodb_buffer_pool_size=512M
--------------------------------------------------------

mysql を再起動
--------------------------------------------------------
/etc/init.d/mysqld restart
--------------------------------------------------------

変更できていることを確認
--------------------------------------------------------
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
--------------------------------------------------------

ここも後で見る
MySQLパフォーマンスチューニング -my.cnfの見直し-

カテゴリー：mysql, サーバートラブル・つながらない

サーバーを再起動したら mysql に繋がらなくなった。

サーバーを再起動したら mysql に繋がらなくなった。

サイトにアクセスしたときに表示された PHP のエラーメッセージ
-----------------------------------------------------------
Warning: mysql_set_charset(): No such file or directory in /var/www/libs/～～～～

Warning: mysql_set_charset(): A link to the server could not be established in /var/www/～～～～～
ConnectError!DB Error: connect failed
-----------------------------------------------------------

ターミナルから mysql にログインすると次のエラー
-----------------------------------------------------------
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
-----------------------------------------------------------

socketファイルというものが無いのが原因のよう。

ターミナルから mysql を再起動するとsocketファイルが作られるらしいので、再起動して解決。
-----------------------------------------------------------
sudo /etc/init.d/mysqld restart
-----------------------------------------------------------

参考サイト

https://qiita.com/kanohisa/items/564035efd74d9c75bdcb

カテゴリー：CentOS, mysql, サーバートラブル・つながらない

phpmyadminにログインできない

phpmyadminにログインできない

ログインすると以下の画面

mysql_error

Error during session start; please check your PHP and/or webserver log file and configure your PHP installation properly. Also ensure that cookies are enabled in your browser.

session_write_close(): write failed: No space left on device (28)

session_write_close(): Failed to write session data (files). Please verify that the current setting of session.save_path is correct (/var/lib/php/session)

セッション開始中にエラーが発生しました。 PHPやWebサーバーのログファイルをチェックして、PHPのインストールを正しく設定してください。また、ブラウザでクッキーが有効になっていることを確認してください。

ターミナルから実行
--------------------------------
df -h コマンドを実行する。
--------------------------------

tarminal

ファイル容量がいっぱいなので不要なファイルを削除する

参考サイト

https://qiita.com/myzkyy/items/53e985cf028e3c3edfe5

カテゴリー：mysql

php + mysql のデータマイニングを高速化したときの覚書

mysql のデータベース内に入っているデータをphpを使ってデータマイニングした際、時間がかかったのでしたこと。

何も対策せずにすると35日ほどかかった。

もう一度する必要でてきたのでそのときにしたこと。

ブラウザを複数立ち上げて同時に処理していった。

このとき同じブラウザ（IE）でやると、それぞれのブラウザで処理が倍になってトータル時間が変わらなかった。

なので、IE、chrome、opera の三つで同時に行った。

単純に三倍速くはならなかったけど、2.5倍ほど早くなったように思う。

カテゴリー：mysql, PHP, データマイニング

【mysql】Row size too large (> 8126) のエラー対処法

mysql で update を実行した際、以下のエラーが出た

-------------------------------------------------
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
-------------------------------------------------

ROW_FORMATが「Compact」のときに、8126バイト以上のレコードを入力しようとすると出るエラーらしい。

ターミナルの mysql から現在の ROW_FORMAT を確認

-------------------------------------------------
use データベース名 ← データベースを選択

SHOW TABLE STATUS LIKE 'テーブル名'\G
-------------------------------------------------

出力結果
*************************** 1. row ***************************
Name: テーブル名
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 35155
Avg_row_length: 4583
Data_length: 161136640
Max_data_length: 0
Index_length: 0
Data_free: 6291456
Auto_increment: 46319
Create_time: 2018-03-09 16:18:39
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

Row_format の Compact の部分を、DYNAMIC か COMPRESSED に変更すれば消えるらしい。で、変更するには innodb_file_format が Barracuda になっていないといけない。

innodb_file_format を確認

-------------------------------------------------
mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';
-------------------------------------------------

出力結果
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)

innodb_file_format が Antelope なので Barracuda に変更する必要がある。

my.cnfを編集
-------------------------------------------------
vim /etc/my.cnf
-------------------------------------------------

[mysqld]以下に追記
-------------------------------------------------
[mysqld]
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
-------------------------------------------------

service mysqld restart で再起動

変更を確認
mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)

↑一度 mysqld を再起動しても反映されていない時があった。必ず反映していることを確認すること。

ここまでで、Row_format を Compact → DYNAMIC （or COMPRESSED）に変更する下準備ができたことになる。

再度確認
-------------------------------------------------
use データベース名 ← データベースを選択

SHOW TABLE STATUS LIKE 'テーブル名'\G
-------------------------------------------------

まだ Row_format は Compact のまま

今回は ROW_FORMAT を DYNAMIC にするので以下を実行
-------------------------------------------------
mysql> ALTER TABLE `テーブル名` ROW_FORMAT=DYNAMIC;
-------------------------------------------------

無事に変更されていることを確認する
-------------------------------------------------
use データベース名 ← データベースを選択

SHOW TABLE STATUS LIKE 'テーブル名'\G
-------------------------------------------------

カテゴリー：mysql

phpmyadminでデータベースをエクスポートしたらphpの実行時間オーバー

phpmyadminからデータベースをエクスポートしたらphpの実行時間オーバーでできなかった。

解決方法１
ターミナルでサーバーにログインしてダウンロードする

phpの実行時間オーバーなので php を使わずにエクスポートする。

////////////////////////////////////////
mysqldump -u root -p database_name > ダウンロード後のファイル名.sql
////////////////////////////////////////

上記は mysql はログインせずに実行する。ダウンロード先はカレントディレクトリになるので、不都合ならパスを指定する。

解決方法２
php の実行時間を延長させる。

カテゴリー：mysql

【mysql】データベースの照合順序を変更する

データベースの照合順序を変更する
---------------------------------------------------------
ALTER DATABASE データベース名 COLLATE utf8mb4_general_ci
---------------------------------------------------------

テーブルの照合順序を変更する
---------------------------------------------------------
ALTER TABLE テーブル名 COLLATE utf8mb4_general_ci
---------------------------------------------------------

カテゴリー：mysql

さくらVPSで外部ホストからmysqlに接続する

さくらVPSで外部ホストからmysqlに接続するには、接続を許可するユーザーを作ればいい。

参考サイト
MySQLに外部ホストから接続できるように設定する

接続を許可するユーザーを作るSQL
-------------------------------------------------
grant all privileges on 接続を許可するDB名.* to new_user_name@"接続元のホスト" identified by 'password' with grant option;
-------------------------------------------------

grant → 付与。許諾する。mysqlで権限を付与する命令。

all privileges → 全ての権限を与える。

privileges → プリビレッジ。権限の意。

カテゴリー：mysql, unix, さくらインターネット

sourceを使ってsqlファイルをインポートする

sourceを使ってsqlファイルをインポートするスクリプト

-------------------------------------------------
use データベース名;
source /var/www/html/file.sql;
-------------------------------------------------

カテゴリー：mysql

【PHP】mysqlでselectしたデータの出力時に使う変数名に変数を使う

$get_datas['カラム名'];

↓↓↓

$get_datas[$culumn_name];

変数名をそのまま記述すればOK。可変変数は不要。

カテゴリー：mysql, PHP

1 / 612345...最後 »