Mysql always oocasional refused connection

Question

Hi , after I deploy my laravel app , I occasional encounter mysql refused connection error.

I check mysql status

systemctl status mysqld

it shows failed.
I have to restart everytime in order to make it back to normal, it very annoying.
I search around digitalocean community it looks like it’s comman issues mostly its memory issue.
I want to check my root cause of this crash , so I want to access mysql error log from here
https://www.digitalocean.com/community/tutorials/how-to-access-mysql-error-logs
i run the command sudo less /var/log/mysql/error.log but it return /var/log/mysql/error.log: No such file or directory
any ideas where is error log for centos 8 ? thanks

Answer 1

bobbyiliev October 18, 2020

Hi there @stupidboy,

It is possible that the MySQL error logging is not enabled, in this case, you would need to enable the error logging.

You can check the location of your log file by first accessing MySQL:

mysql -u root -p

And then run the following query:

SHOW VARIABLES LIKE 'log_error';

Also besides that, what I could suggest is checking the following log for more information:

tail -1000 /var/log/messages

Regards,
Bobby

Reply Report

stupidboy October 19, 2020

@bobbyiliev luckiy I had found the error message

2020-10-19T04:32:24.763340Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.21) starting as process 312648
2020-10-19T04:32:24.882908Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-19T04:32:24.919565Z 0 [ERROR] [MY-012681] [InnoDB] mmap(137363456 bytes) failed; errno 12
2020-10-19T04:32:24.919762Z 1 [ERROR] [MY-012956] [InnoDB] Cannot allocate memory for the buffer pool
2020-10-19T04:32:24.919856Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2020-10-19T04:32:24.919978Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-10-19T04:32:24.920325Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-10-19T04:32:24.920509Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-10-19T04:32:24.922440Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.21)  Source distribution.

it means I do not have enough memory for my droplet I have to increase it ?thanks very much

Reply Report

stupidboy October 19, 2020
@bobbyiliev after I checked what you suggested

tail -1000 /var/log/messages

the same timestampt of mysql crashed , its out of memory , anything I can do beside to upgrade my droplet ,or maybe to increase some meory limit of mysql ? thanks

Oct 19 04:32:23 centos-s-1vcpu-1gb-sgp1-01 kernel: oom_reaper: reaped process 311921 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB Oct 19 04:32:23 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Main process exited, code=killed, status=9/KILL Oct 19 04:32:23 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Failed with result 'signal'. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Service RestartSec=100ms expired, scheduling restart. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Scheduled restart job, restart counter is at 5. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: Stopped MySQL 8.0 database server. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: Starting MySQL 8.0 database server... Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 mysql-check-socket[312585]: Socket file /var/lib/mysql/mysql.sock exists. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 mysql-check-socket[312585]: No process is using /var/lib/mysql/mysql.sock, which means it is a garbage, so it will be removed automatically. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE Oct 19 04:32:25 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Failed with result 'exit-code'. Oct 19 04:32:25 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: Failed to start MySQL 8.0 database server. Oct 19 04:32:25 centos-s-1vcpu-1gb-sgp1-01 dnf[312530]: Metadata cache created. Oct 19 04:32:25 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: Started dnf makecache. O
Reply Report
- bobbyiliev October 19, 2020
  
  Hi there @stupidboy,
  
  One thing that you could try is to follow the steps from this answer here on how to tweak your MySQL/MariaDB:
  
  https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability
  
  Also, I think that the way to go would be to make sure that other services like PHP for example are not using too much memory as well.
  
  You could also add a swap file so that you do have some buffer in case that your server runs out of RAM:
  
  https://www.digitalocean.com/community/tutorials/how-to-add-swap-space-on-ubuntu-20-04
  
  Lastly, if none of the above helps, you should consider adding extra RAM.
  
  Hope that this helps!
  Regards,
  Bobby
  
  How To Add Swap Space on Ubuntu 20.04
  by Brian Boucheron
  One way to guard against out-of-memory errors in applications is to add some swap space to your server. In this guide, we will cover how to add a swap file to an Ubuntu 20.04 server.
  
  Reply Report
  
  stupidboy October 20, 2020
  
  @bobbyiliev hi bro , now the situation is I cant restart mysql at all I had posted here can you please take a look.
  https://www.digitalocean.com/community/questions/centos-8-mysql-keeps-stoped-status
  
  Reply Report
  
  bobbyiliev October 22, 2020
  
  Hi there @stupidboy,
  
  The error that you are seeing there is saying the following:
  
  [ERROR] [MY-012956] [InnoDB] Cannot allocate memory for the buffer pool
  
  This means that you’ve specified a too high value for the InnoDB buffer pool size and there is not enough RAM on the server.
  
  Make sure to reduce it so that MySQL could allocate enough RAM and start as normal.
  
  Regards,
  Bobby
  
  Reply Report
  
  stupidboy October 23, 2020
  
  Hi @bobbyiliev I had never specify the high value for the innoDB buffer pool I just set it up and runing , and ther e any way I can reduce it ? or your website have any course on this I am really not a infra guy thanks
  
  Reply Report
  
  bobbyiliev October 24, 2020
  
  Hi there @stupidboy,
  
  Did this improve after adding the swap file? It should give you 2GB of extra RAM buffer.
  
  Regarding the InnoDB buffer pool size, you can take a look at this answer on how to use the MySQL tuner script which will suggest the best MySQL values for your server:
  
  https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability
  
  Regards,
  Bobby
  
  Reply Report

Answer 2

stupidboy October 19, 2020

@bobbyiliev luckiy I had found the error message

2020-10-19T04:32:24.763340Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.21) starting as process 312648
2020-10-19T04:32:24.882908Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-19T04:32:24.919565Z 0 [ERROR] [MY-012681] [InnoDB] mmap(137363456 bytes) failed; errno 12
2020-10-19T04:32:24.919762Z 1 [ERROR] [MY-012956] [InnoDB] Cannot allocate memory for the buffer pool
2020-10-19T04:32:24.919856Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2020-10-19T04:32:24.919978Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-10-19T04:32:24.920325Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-10-19T04:32:24.920509Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-10-19T04:32:24.922440Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.21)  Source distribution.

it means I do not have enough memory for my droplet I have to increase it ?thanks very much

Reply Report

stupidboy October 19, 2020
@bobbyiliev after I checked what you suggested

tail -1000 /var/log/messages

the same timestampt of mysql crashed , its out of memory , anything I can do beside to upgrade my droplet ,or maybe to increase some meory limit of mysql ? thanks

Oct 19 04:32:23 centos-s-1vcpu-1gb-sgp1-01 kernel: oom_reaper: reaped process 311921 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB Oct 19 04:32:23 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Main process exited, code=killed, status=9/KILL Oct 19 04:32:23 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Failed with result 'signal'. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Service RestartSec=100ms expired, scheduling restart. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Scheduled restart job, restart counter is at 5. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: Stopped MySQL 8.0 database server. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: Starting MySQL 8.0 database server... Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 mysql-check-socket[312585]: Socket file /var/lib/mysql/mysql.sock exists. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 mysql-check-socket[312585]: No process is using /var/lib/mysql/mysql.sock, which means it is a garbage, so it will be removed automatically. Oct 19 04:32:24 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE Oct 19 04:32:25 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: mysqld.service: Failed with result 'exit-code'. Oct 19 04:32:25 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: Failed to start MySQL 8.0 database server. Oct 19 04:32:25 centos-s-1vcpu-1gb-sgp1-01 dnf[312530]: Metadata cache created. Oct 19 04:32:25 centos-s-1vcpu-1gb-sgp1-01 systemd[1]: Started dnf makecache. O
Reply Report
- bobbyiliev October 19, 2020
  
  Hi there @stupidboy,
  
  One thing that you could try is to follow the steps from this answer here on how to tweak your MySQL/MariaDB:
  
  https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability
  
  Also, I think that the way to go would be to make sure that other services like PHP for example are not using too much memory as well.
  
  You could also add a swap file so that you do have some buffer in case that your server runs out of RAM:
  
  https://www.digitalocean.com/community/tutorials/how-to-add-swap-space-on-ubuntu-20-04
  
  Lastly, if none of the above helps, you should consider adding extra RAM.
  
  Hope that this helps!
  Regards,
  Bobby
  
  How To Add Swap Space on Ubuntu 20.04
  by Brian Boucheron
  One way to guard against out-of-memory errors in applications is to add some swap space to your server. In this guide, we will cover how to add a swap file to an Ubuntu 20.04 server.
  
  Reply Report
  
  stupidboy October 20, 2020
  
  @bobbyiliev hi bro , now the situation is I cant restart mysql at all I had posted here can you please take a look.
  https://www.digitalocean.com/community/questions/centos-8-mysql-keeps-stoped-status
  
  Reply Report
  
  bobbyiliev October 22, 2020
  
  Hi there @stupidboy,
  
  The error that you are seeing there is saying the following:
  
  [ERROR] [MY-012956] [InnoDB] Cannot allocate memory for the buffer pool
  
  This means that you’ve specified a too high value for the InnoDB buffer pool size and there is not enough RAM on the server.
  
  Make sure to reduce it so that MySQL could allocate enough RAM and start as normal.
  
  Regards,
  Bobby
  
  Reply Report
  
  stupidboy October 23, 2020
  
  Hi @bobbyiliev I had never specify the high value for the innoDB buffer pool I just set it up and runing , and ther e any way I can reduce it ? or your website have any course on this I am really not a infra guy thanks
  
  Reply Report
  
  bobbyiliev October 24, 2020
  
  Hi there @stupidboy,
  
  Did this improve after adding the swap file? It should give you 2GB of extra RAM buffer.
  
  Regarding the InnoDB buffer pool size, you can take a look at this answer on how to use the MySQL tuner script which will suggest the best MySQL values for your server:
  
  https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability
  
  Regards,
  Bobby
  
  Reply Report

Answer 3

pistle2020 October 19, 2020

Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces. If the server was started with the skipnetworking system variable enabled, no TCP/IP connections are accepted. If the server was started with the bindaddress system variable set to 127.0.0.1, it listens for TCP/IP connections only locally on the loopback interface and does not accept remote connections.

Check to make sure that there is no firewall blocking access to MySQL. Your firewall may be configured on the basis of the application being executed, or the port number used by MySQL for communication (3306 by default). Under Linux or Unix, check your IP tables (or similar) configuration to ensure that the port has not been blocked. Under Windows, applications such as ZoneAlarm or Windows Firewall may need to be configured not to block the MySQL port.

Reply Report

Related

Question

Mysql always oocasional refused connection

Related

Leave a comment

Looking for something else?

Sign up for our newsletter

Related

Question

Mysql always oocasional refused connection

Related

Leave a comment

Related

question

How to connect to a database on DATABASE CLUSTERS

question

Need help to troubleshoot mysqlnd_ms setup on PHP72 with php-fpm

question

Hi, I am not familiar with droplets and linux whats the easiest way to access my files

question

Connect to DB installed into dedicated VPS (private networking)

Looking for something else?