and mysql, keeps tumbling down............

christophera · August 8, 2016, 1:27pm

A couple of weeks ago mysql/mariadb crashed in the middle of the night… then again a few evenings later, then again, and again, now going down once or twice every day.

Why?

Looking at the logs without a lot of understanding, it looks like it might be going down and restarting(?) itself regularly, lots of crashed tables… due to the db crashing, or causing the crash?

I ran a mysql general log and have the last request before the last crash as a wp plugin hitting the db -


10100 QuerySELECT option_value FROM wp_options WHERE option_name = 'mepr_vat_
enabled' LIMIT 1
/usr/libexec/mysqld, Version: 5.5.47-MariaDB-log (MariaDB Server). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

.

Would that mean that plugin (mepr is memberpress plugin) caused the problem, or it just happened then? And is that “started with” in there mysql trying to reboot itself?

Here’s the end of the mysql general log file: http://pastebin.com/5NvJyqAr

The end of the mariadb log: http://pastebin.com/Z8UxMGfc

and my my.cnf: http://pastebin.com/nupmUkKu

I’m on an 8GB / 4 cpu virtual server (CentOS Linux 7.2.1511) over at digitalocean and am using Virtualmin GPL 5.04, MySQL version 5.5.47, PHP 5.4.16

Any help or thoughts on the crashing and what to do about it appreciated!

Chris

christophera · August 8, 2016, 5:35pm

Crashed again…

This time I got this in the log of what mysql was doing… not the same queries as before, so I’m guessing it’s not a particular query bringing it down…


5757 QuerySELECT option_value FROM wp_options WHERE option_name = '_ba_eas_role_slugs' LIMIT 1
 5764 QuerySELECT option_value FROM wp_options WHERE option_name = '_site_transient_timeout_icwp_1e54fca87b678bc286d8787be60022a1' LIMIT 1
 5785 QuerySELECT option_value FROM wp_options WHERE option_name = 'PO_ignore_protocol' LIMIT 1
 5784 QuerySELECT option_value FROM wp_options WHERE option_name = 'PO_ignore_protocol' LIMIT 1
 5784 QuerySELECT option_value FROM wp_options WHERE option_name = 'PO_ignore_arguments' LIMIT 1
 5785 QuerySELECT option_value FROM wp_options WHERE option_name = 'PO_ignore_arguments' LIMIT 1
 5761 QuerySHOW TABLES LIKE 'wp_icwp_wpsf_statistics'
 5763 QuerySHOW TABLES LIKE 'wp_icwp_wpsf_statistics'
 5760 QuerySHOW TABLES LIKE 'wp_icwp_wpsf_statistics'
 5691 QuerySHOW TABLES LIKE 'wp_icwp_wpsf_statistics'
/usr/libexec/mysqld, Version: 5.5.47-MariaDB-log (MariaDB Server). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

christophera · August 8, 2016, 5:37pm

I do get a lot of these in the mariadb.log, is this normal? I don’t know the ips -


150325  4:04:14 [Warning] IP address '61.240.144.67' could not be resolved: Name or service not known
150326  7:02:23 [Warning] IP address '118.123.119.169' could not be resolved: Name or service not known
150326  7:02:23 [Warning] IP address '118.123.119.169' could not be resolved: Name or service not known
150326  7:44:15 [Warning] IP address '222.186.21.194' could not be resolved: Name or service not known
150326  7:44:15 [Warning] IP address '222.186.21.194' could not be resolved: Name or service not known
150326 15:56:53 [Warning] IP address '222.186.15.208' could not be resolved: Name or service not known
150326 15:56:53 [Warning] IP address '222.186.15.208' could not be resolved: Name or service not known

Eric · August 9, 2016, 2:40am

Howdy,

Well, just to check a common issue we see – 8GB is normally plenty of RAM, but can you run the command “dmesg | tail -50”, and just make sure you don’t see any references to oomkiller, or processes being killed off due to lack of resources?

The notices you’re seeing regarding the IP addresses definitely shouldn’t be causing it to crash.

That said, is MySQL available to the outside world? It’s normally only listening on localhost, if it’s available to the outside world and doesn’t need to be, you may want to block that off to ensure that it’s not being hammered by bots.

-Eric

christophera · August 9, 2016, 4:23am

ah… oom meaning ‘out of memory’… makes sense… at the end of dmesg | tail -50, I get:

[ 8241.695406] Out of memory: Kill process 15574 (mysqld) score 38 or sacrifice child
[ 8241.696498] Killed process 15574 (mysqld) total-vm:2989492kB, anon-rss:310968kB, file-rss:0kB
[ 8245.492515] hrtimer: interrupt took 5691894 ns

in my virtualmin MySQL server options, I find:

MySQL server listening address

is set at “any”. I only have mysql for my own sites on my own server, so that should be set to ‘localhost’ if I am understanding you correctly?

Doesn’t look like it’s being hammered by other ips though, so I’m not sure if that’s the problem. Should I give mysql more memory to use?

Currently the rest of the settings on the MySQL server options page are at their default settings.

Query cache size in bytes Default 32
Maximum packet size Default 1
MyISAM sort buffer size Default 64

just wonder why it’s suddenly running out of memory though when it’s been fine for the last year, I’m not getting any big surges in traffic right now…

Thanks for your awesome assistance Eric. When no one else has a clue you are always there! I volunteer on a help forum myself - I know how time consuming it can be to help others!

Chris

coderinthebox · August 9, 2016, 10:19am

Your server is being attacked, only allow localhost to connect to Maria/MySql. One failed login that was not terminated stays in memory. Since brute force attacks can range from 1000 attacks per 10 minutes, resources starts to dwindle. MariaDB/MySql also have a maximum concurrent connection causing your database to fail.

christophera · August 9, 2016, 2:54pm

…and would that be the ‘MySQL server listening address’ setting, or something else? I don’t seem to be able to set ‘MySQL server listening address’ to “localhost”.

Eric · August 9, 2016, 5:03pm

Howdy,

Can you paste the contents of your /etc/my.cnf file?

Also, you may be seeing RAM issues, but maybe we should try setting it to localhost first. If you’re seeing memory issues, we can tell MySQL to use up less RAM.

-Eric

christophera · August 9, 2016, 5:42pm

Can do… but where do I set it to listen only to localhost? In the “MySQL server listening address”, it does not accept “localhost”. Do I enter my server’s ip? Or am I in the wrong setting?


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

innodb_file_per_table = 1

thread_concurrency = 8

query_cache_size = 32M

thread_cache_size = 8

myisam_sort_buffer_size = 64M

read_rnd_buffer_size = 8M

read_buffer_size = 2M

sort_buffer_size = 2M

table_open_cache = 512

max_allowed_packet = 1M

key_buffer_size = 384M
[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

christophera · August 9, 2016, 9:09pm

Ok… now I am apparently under attack, server load up around 50+, if I turn mysql off, the load comes back down.

christophera · August 9, 2016, 9:17pm

Been doing some research and added:

bind-address = 127.0.0.1

to the [mysqld] section in my.cnf

and restarted mysql.

Load is back down to a normal range for several minutes now, continuing to monitor.

If this was an attack, its the second one the last ten days for me. The first one was a standard ddos attack that lasted several days. Had to leave cloudflare in “under attack” mode for several days until it let up.

coderinthebox · August 10, 2016, 3:47am

Yes, that is correct. that is the “magic” setting to make MySQL to listen to localhost only.

christophera · August 12, 2016, 1:07am

Been stable for two days now - that was definitely the fix

Thank you both for your help!

Chris

hescominsoon · August 17, 2016, 11:35am

make sure you enable to firewall as well. That will keep miscreants out of your machine as well.