Wordpress mySQL crashes, tried some things but HELP needed

Question

We are new to Digital Ocean, and moving some Wordpress sites in here. Our mysql keeps crashing, but what really worries me is that I find lots of forum posts about the same issue, but no ‘official’ recommendation… just ‘try this’, ‘could be that’, ‘perhaps its…’.

PLEASE is there any procedure to follow in order to know 1) what´s causing the issue and 2) take proper action?. We won´t be able to stay at DO if we can´t solve this issue.

thks to all for your time.

markjtalbot · Answer

I had a similar problem and was able to finally fix it. Background I tried a bunch of tutorials that recommended tuning my MySQL installation and Apache configuration with no luck. Identifying the Problem It turns out that my site was being subjected to a brute force attack that targeted the WordPress xmlrpc.php file. H/T @sierracircle . In order to check for this, you should view your apache2 access logs and look for suspicious POST requests. In my case, I found a huge number of POST requests to the /xmlrpc.php file coming from the same IP address (about 2 or 3 requests per second). cd /var/log/apache2/ cat access.log Solution In order to fix this, I banned the offending IP address via Iptables: Ban IP the malicious IP address (replace example with whatever IP address you want to ban) : iptables -A INPUT -s 46.166.139.20 -j DROP To view banned IP addresses: iptables -L INPUT -v -n Reference:http://www.cyberciti.biz/faq/linux-howto-check-ip-blocked-against-iptables/

alexdo · Answer

Hello, I will recommend you to examine the MySQL error_log as well, because this is likely to give you a more verbose error and point you in the right direction to see what exactly is causing the issue here. The location of the error_log should be: /var/log/mysqld.log or /var/log/mysql/error.log If you’re unable to locate the error_log in this two locations you can double check your my.cnf file and see if the error_log is saved somewhere else. What you can do is to examine the last logged entries in the log using tail You can use either: tail -f /var/log/mysqld.log and tail -f /var/log/mysql/error.log or tail -n 100 /var/log/mysqld.log and tail -n 100 /var/log/mysql/error.log You can share the output of the log here so we can have a look. You can also create a simple bash script to check if MySQL is running and if not to restart it. #!/bin/bash # Check if MySQL is running sudo service mysql status > /dev/null 2>&1 # Restart the MySQL service if it's not running. if [ $? != 0 ]; then sudo service mysql restart fi Run this script every 5 minutes using a cron job like this one: */5 * * * * /home/user/scripts/monitor.sh > /dev/null 2>&1 Hope that this helps!Regards,Alex

Jonathan Tittle · Answer

The first thing to note would be that DigitalOcean wouldn’t be directly responsible for MySQL taking a dive on you as you’re in full control of your Droplet (i.e. VPS), so configuration, optimization, and so forth are 100% completely up to you. They do not provide managed services, or provide support for issues unless it’s truly server/hardware/network related. So if the SSD is taking a drive, and it’s not do to your own I/O, they can help there. If network speeds are bad, skipping hops, dropping etc - they can help you there. But, if MySQL isn’t working for you, it falls under “third-party” software, and you need to be able to manage it or have a DBA do it for you. That being said, MySQL won’t just crash without a valid reason. So the first thing you’d need to do is check your logs and see if they can give you some insight. You can also use a tool called MySQL Tuner that you run as a root user or a sudo user with root privileges. http://mysqltuner.com/ Simply log-in via SSH and: cd /usr/local/src/ wget https://github.com/major/MySQLTuner-perl/archive/master.zip unzip master.zip cd MySQLTuner-perl-master To run MySQL Tuner, simply: ./mysqltuner.pl It’ll take about 3-4 seconds to run and the output will give you an idea of how your current installation is performing when it comes to reads & writes, slow queries, key buffer, query cache, temp tables & so forth. At the bottom it’ll give you some general recommendations and variables that it suggests tuning. Keep in mind, this is not a magic fix-all tool. It provides suggestions to help, that’s it. It does nothing else for you and will not make changes to MySQL. General suggestions would be to work with both PHP & MySQL at the same time as PHP is what’s passing data to MySQL, which is then choosing to store it. When PHP is called, it’s going to do one of two things, call on a cached set of data or query the database. If you’re not using Object Caching and/or Page Caching, you’re going to hit MySQL every single time you display a page. So I’d look at a few plugins, such as: https://wordpress.org/plugins/w3-total-cache/ https://wordpress.org/plugins/wp-super-cache/ https://wordpress.org/plugins/wp-fastest-cache/ W3TC is probably one of the most recommended but it can be overkill and time-consuming if you do not like a lot of options. WPSC is a bit more simple. Beyond caching with WordPress, you should look to PHP for caching using OPCache which is included in newer versions of PHP by default (if you’re hitting repo’s i.e yum install php or apt-get install php etc). Enabling that forces PHP to turn on it’s own object cache. A basic configuration for OPCache would look something like this (this goes inside of your php.ini file): zend_extension=opcache.so ;opcache.error_log=/var/log/php_opcache_error.log opcache.enable=1 opcache.memory_consumption=64 opcache.interned_strings_buffer=8 opcache.max_wasted_percentage=5 opcache.max_accelerated_files=8000 opcache.revalidate_freq=180 opcache.fast_shutdown=1 opcache.enable_cli=0 opcache.save_comments=1 opcache.enable_file_override=1 opcache.validate_timestamps=1 You can find your PHP ini file by doing a whereis php at the CLI or by creating a PHP info page using: <?php phpinfo(); ?> You can stick that in any web-accessible directory and access it via browser to find out all there is to know about your PHP installation. In regards to MySQL, enabling the query cache at the server level is something I would consider as well, though the query cache itself isn’t going to be a life-saver. You will also need to work with other variables such as the key, join, read and sort buffer, table cache, thread cache, max connections, etc. If query cache is not enabled, you can enable it by adding the following to your my.cnf file. query_cache_limit = 512K query_cache_size = 16M query_cache_type = 1 That’ll get you started with query caching and what that translates in to is: 1). Don’t cache anything smaller that 512K2). The maximum size of the cache is 16M3). Query Cache is Enabled. All that being said, when it comes to configuration, optimization, security etc - a VPS on an unmanaged provider probably is not the best option unless you know how to do it all yourself, or have someone on-hand to do it for you. It’s hard work sometimes and the images that most providers provide as “one-click” installations are just that, “one-click” installations. You still need to be able to manage the server on your own. They just shorten the amount of time it takes to get off the ground and on to a working installation. If options are on the table, I would personally ditch Apache altogether, switch to NGINX, PHP-FPM and MariaDB (a fork of MySQL with better performance). Apache is a resource hog, whereas NGINX is relatively light and simple.

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

Wordpress mySQL crashes, tried some things but HELP needed

Report this

Wordpress mySQL crashes, tried some things but HELP needed