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
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 15764 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
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 known150326 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
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
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
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.
Visit me at coderinthebox.com
...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".
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
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
Ok... now I am apparently under attack, server load up around 50+, if I turn mysql off, the load comes back down.
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.
Yes, that is correct. that is the "magic" setting to make MySQL to listen to localhost only.
Visit me at coderinthebox.com
Been stable for two days now - that was definitely the fix :)
Thank you both for your help!
Chris
make sure you enable to firewall as well. That will keep miscreants out of your machine as well.