Проблемы с сервером баз данных MySQL (SQLSTATE[HY000] [2002] Connection refused)
Активно используем бд на чтение и запись, сервер баз данных MySQL, выполняется процесс импорта и других работ.
Проблема заключается в том, что частенько вылетает ошибка SQLSTATE[HY000] [2002] Connection refused при разных запросах select, update, insert и тд. Заметил ошибку в последнее время когда размер бд стал больше чем выделенная в конфиге ОЗУ под нее.
Конфиг mysql
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
secure-file-priv = NULL
skip-external-locking
max_allowed_packet = 100M
key_buffer_size = 16M
innodb_buffer_pool_size = 4096M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 160
long_query_time = 1
#expire_logs_days = 1
max_binlog_size = 100M
disable_log_bin
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
Одним из решений наверное было бы увеличить объем ОЗу, но бесконечно же увеличивать его не получится т.к. бд постоянно растет. Есть какие-то другие варианты решения этой проблемы?
Последние логи за сегодня конейнера mysql
2020-12-26 00:43:17+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26 00:43:17+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-26 00:43:17+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26T00:43:18.244214Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
2020-12-26T00:43:18.256839Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-26T00:43:21.418998Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-26T00:43:21.621634Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-12-26T00:43:21.720132Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-26T00:43:21.723062Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-12-26T00:43:21.754033Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-12-26 01:00:11+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26 01:00:11+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-26 01:00:11+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26T01:00:11.889893Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
2020-12-26T01:00:11.904031Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-26T01:00:14.374501Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-26T01:00:14.722195Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-12-26T01:00:14.786147Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-26T01:00:14.789253Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-12-26T01:00:14.822480Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-12-26T06:39:15.054863Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-12-26 06:40:27+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26 06:40:28+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-26 06:40:28+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26T06:40:28.954425Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
2020-12-26T06:40:28.987416Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-26T06:40:33.545273Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-26T06:40:33.843566Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-12-26T06:40:34.007675Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-26T06:40:34.012998Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-12-26T06:40:34.068772Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-12-26T06:55:32.406940Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-12-26 06:55:34+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26 06:55:34+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-26 06:55:34+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26T06:55:35.479370Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
2020-12-26T06:55:35.496525Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-26T06:55:37.154719Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-26T06:55:37.433835Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-12-26T06:55:37.593885Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-26T06:55:37.596152Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-12-26T06:55:37.621147Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-12-26 08:43:41+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26 08:43:41+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-26 08:43:41+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26T08:43:42.364663Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
2020-12-26T08:43:42.383092Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-26T08:43:45.650870Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-26T08:43:45.826187Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-12-26T08:43:45.894372Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-26T08:43:45.897032Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-12-26T08:43:45.923281Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-12-26T08:46:14.992253Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 11 user: 'app'.
2020-12-26T08:46:16.655425Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.
2020-12-26 08:46:20+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26 08:46:20+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-26 08:46:20+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26T08:46:21.417657Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
2020-12-26T08:46:21.434561Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-26T08:46:22.918554Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-26T08:46:23.109405Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-12-26T08:46:23.225924Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-26T08:46:23.229630Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-12-26T08:46:23.269575Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
mbind: Operation not permitted
mbind: Operation not permitted
mbind: Operation not permitted
mbind: Operation not permitted
2020-12-26 09:06:32+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26 09:06:32+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-12-26 09:06:32+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.20-1debian10 started.
2020-12-26T09:06:32.815176Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 1
2020-12-26T09:06:32.833090Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-26T09:06:35.035344Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-26T09:06:35.202616Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-12-26T09:06:35.283163Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-26T09:06:35.285898Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-12-26T09:06:35.325343Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
mbind: Operation not permitted
mysql> show status like "%onn%";
+-------------------------------------------------------+---------------------+
| Variable_name | Value |
+-------------------------------------------------------+---------------------+
| Aborted_connects | 11 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 276 |
| Locked_connects | 0 |
| Max_used_connections | 5 |
| Max_used_connections_time | 2020-12-26 09:45:02 |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 0 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| Mysqlx_connections_rejected | 0 |
| Performance_schema_session_connect_attrs_longest_seen | 113 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 4 |
+-------------------------------------------------------+---------------------+
22 rows in set (0.00 sec)