Automate /var/log/mysql from filling up with logs?

[Endda]

So I logged into my website today and noticed that my storage space of 60GB was like 45% full. This seemed odd because I only have three websites only there and the biggest one is 2 or 3GB while the other two take up 1GB or less.

After doing some research, I noticed that the /var/log/mysql folder was filled with 100MB files that were eating up like 15GB of my storage. I continued doing research here and was lead to this thread

• Mariadb bin log eat my disk space - part2

Which suggested I could just wipe out the whole folder because it’s just logs and unless I’m troubleshooting something immediately, there’s no need to have them stored for so long

---

So my question is, how can I automate this server so I don’t have to do this manually? It seems like it would be smart to keep some recent log files in the folder (I’m okay with storing 1GB of logs, or a few hundred MB or whatever).

But unless I notice something wrong, I shouldn’t need any of the older log files in this folder. Is there a recommended way of keeping this folder in check with EasyEngine?

If not, what would be the best Linux style way of keeping this folder clean? Maybe setting up a cronjob to wipe it clean once a month? Or is there something built into MariaDB, like a setting or something, that prevents the logs folder from reaching a certain size?

[bredpit]

I don’t know any EE special way.

But… I am not an expert but you can try and setup a cron to clean up the folder every now and then.

If I were you I would worry about how the chunks got there. You should check if any of your websites got hacked: sitecheck.sucuri.net

Then you should be aware that it is possible for apps (WordPress or w/e) write enormous amounts of data. Even if you do something, such as deleting, cleaning, updating tables or w/e, everything will be in a log. And the logs will consume a lot of space. Idk how often these logs get cleaned up automatically or at all, but a cron should help.

Also, if you set a plugin to keep logs for 1 year, it will consume a big amount of database space. So try to set your plugins not to keep logs older that a month or w/e.

Try to check the dates when the logs got written and start from there.

These are all just suggestions, just trying to help. Cheers

[Endda]

No, none of my sites got hacked, but I did notice that AMP for WP did just get taken down for a security flaw. Still, I use Wordfence and all sites on this server are safe.

I will be keeping an eye on that folder to see how quickly it grows and will setup a cronjob if there isn’t some sort of MariaDB option to limit the size of the logs it collects or something.

Thanks

[marty]

[General note: Cron is already being used to trigger log rotation for log files (which is why you see files such as syslog, syslog.1, syslog.2.gz etc, which are also deleted beyond a certain age).]

Does it sound right that there would be a lot of database updating going on for this server (not reads, only stuff that changes data is logged)? If not, you might want to look into that.

Otherwise, it does sound like a good solution here is (as virtubox recommended in the link you posted), to disable binary logging for MySQL (explained here) if you don’t use db replication (which you probably don’t).

[Endda]

I have three websites on this server. One website is tutorial blog that I use to write a single tutorial once a day on. A second website on the server hasn’t been updated in a year or two.

The other website though, is a Woocommerce external link website that I use to post application sales for Android. I schedule about 10-12 per day (some of these just get re-used as I keep expired sales in a category that people can’t see).

I can’t say how many new posts I put up on that one each day and how many get rescheduled, but it does currently have 6,943 “products” setup in Woocommerce (again, as external links).

I’m not sure what db replication is, so I definitely don’t use it. I will, again, be keeping my eye on it to see how it goes. If it fills up quickly then I’ll download a log file or two and see if I can see any patterns of what is going on.

But if there isn’t, then you suggest I just disable binary logging altogether instead of using a cronjob to clear it out every X days? There wouldn’t be any need to view these logs for troubleshooting purposes in the future at all?

[bredpit]

Maybe I am wrong but I think your WooCommerce site is to be blamed. 7k of products, uhhh. A small change that can affect all of them can be a huge database impact. Which is normal, I think. A good reason to upgrade your storage space.
Disabling binary logs is not suggested in some configurations, like master-slave. If it doesn’t hold anything important then you might want to disable it. If I were you I would just upgrade my storage, because it is the safest way in my opinion. Disabling binary logs looks safe in your situations, but I am not an expert which is why I’d go with storage upgrade.
As I said in my first post:

Idk how often these logs get cleaned up automatically or at all, but a cron should help.

If the files gets too big too fast, then think about a cron that deletes the log files more often than automatic cleanup. PS. I really suggest inspecting those logs.

[Endda]

Yea, something looks to be strange as I’m filling up a 100MB mariadb-bin.000001, mariadb-bin.000002, mariadb-bin.000003, etc log file every two hours

I just downloaded and renamed one mariadb-bin.000001.conf (as it defaults to opening it up in Wordpad), and I’m seeing a ton of activity from the one site that doesn’t get anything added to it.

I’m seeing a lot of jibberish like. . .

ˬ˜Ù[£ ’ ’CD Ù[¡ ) I mariadb-bin.000156Ù[¢ & o “CD Ù[ Ô

But then I see stuff like. . .

“appstoreapps_com INSERT INTO wp_wfconfig (name, val, autoload) values (‘scanFileProcessing’, ‘a:2:{i:0;s:82:“wp-content/themes/applay/inc/option-tree/assets/images/colorpicker/color_wheel.png”;i:1;i:1540948243;}’, ‘yes’) ON DUPLICATE KEY UPDATE val = ‘a:2:{i:0;s:82:“wp-content/themes/applay/inc/option-tree/assets/images/colorpicker/color_wheel.png”;i:1;i:1540948243;}’, autoload”

“appstoreapps_com INSERT INTO wp_wfconfig (name, val, autoload) values (‘scanFileProcessing’, ‘a:2:{i:0;s:86:“wp-content/plugins/oiopub-direct/uploads/nBRQNS_iTunesIcon.no_transparency.125x125.png”;i:1;i:1540948507;}’, ‘yes’) ON DUPLICATE KEY UPDATE val = ‘a:2:{i:0;s:86:“wp-content/plugins/oiopub-direct/uploads/nBRQNS_iTunesIcon.no_transparency.125x125.png”;i:1;i:1540948507;}’, autoload”

I would say 90% of this 100MB file is stuff like that. wp_wfconfig is Wordfence, so I’m guessing that’s a scan. . .but it’s as if the scans aren’t stopping or something.

[Endda]

I have since disabled Wordfence on that website, cleared out the logs folder again, and created a thread in the Wordfence WordPress plugin page.

Maybe they will be able to help me figure out what is going on there.

[bredpit]

There you go. I don’t know if you remember but I told you in another topic:

502 bad gateway on the /wp-admin panel, goes away after a refresh

The Bruiser plugin is a much safer option, for me. Wordfence is messing too much with the website. It is heavier too. From my experience neither the Wordfence, ithemesSecurity Pro or w/e is necessary. All you need is a server-side firewall and a light plugin to protect you from spam and protect your login page.

I can say that I had incompatibility problems with Wordfence on more than one occasion, and I really felt its weight impact on my website. You should try other options, like bruiser or cerber security (I never tried cerber but I inspect how it works and I am satisfied) WordPress has excellent security guides and you can start from here if you like: https://codex.wordpress.org/Brute_Force_Attacks

[Endda]

I’ll be switching to Ninja Firewall before I I try Bruiser

But those errors were happening on two different websites. . .so the two issues are unrelated in the first place

[bredpit]

Ninja Firewall, looks good. But, seems like it works above WP, meaning that if it gets stuck, wp will not load. Pass. Maybe I’m wrong.

What I do is: I am on google cloud so I have a fw working on top of everything. Only http and https ports free. Got sftp working for the sake of filezilla. Using WPS hide login - which grants me to completely avoid brute force attacks on wp-admin or wp-login page. Bruiser to do this:

Screenshot

and

Screenshot

and

Screenshot

• with some function php codes to disable and remove lost password fields and sending emails. block long requests and bad requests like eval, base64, etc etc. I also use extra security question on login instead of captcha.

All this without impact on my site with intrusive security plugins.

I have multiple levels of security on my login page which never even got touched because of wps hide login. The only hole is a ddos attack, but this can be mitigated if using cloudflare or similar. or you simply use ssh keys.

[virtubox]

Hello,

if you do not use MySQL or MariaDB replication, you can safely comments all lines related to binlog in /etc/mysql/my.cnf and delete binlog files in /var/log/mysql/

[Endda]

Using WPS hide login - which grants me to completely avoid brute force attacks on wp-admin or wp-login page.

I don’t think this is true. I asked about this over on /r/WordPress and they said it was only good against certain scripts/bots

[Endda]

Hey, thank you for the suggestion in that thread long ago. That is what I ended up doing. I still don’t know what DB replication is or how it can be beneficial, so since I doubt I use it, I just stopped collecting those logs altogether.

There’s a completely different command (I believe) I can use. . .

• tail -f /var/log/syslog | grep mysql

. . .to troubleshoot current database errors. So the ones that were filling up that log/mysql folder are useless to me.

Thanks again (and again). I see you all over this board helping so many people. I know that I, personally, have learned so much about EE and how it works from you and hope you continue as I know I have much more to learn.

[virtubox]

Thanks again (and again). I see you all over this board helping so many people. I know that I, personally, have learned so much about EE and how it works from you and hope you continue as I know I have much more t

Yes, errors related to MySQL will be displayed in the syslog (/var/log/syslog). You can use :

tail -n 500/var/log/syslog | grep mysql

To look for mysql logs in the last 500 lines of /var/log/syslog.

About DB replication, it’s only related to all architecture where a database is replicated accross multiple MySQL servers. Database replication require to enable binlog, because it use it to sync data accross MySQL nodes.

[bredpit]

Maybe true, but,statistics talks for itself. I used to have at least 2k (sometimes up to 10k) attacks on my login, every day. Since I started using wps hide, I only receive user enumeration attacks and comment attacks (0 to 30 attempts daily) which Bruiser successfully blocks

This can only mean that bots, scripts or w/e have no clue where my login page is. I do have limit login attempts too. So it’s wps as first level, bruiser as 2nd, limit login attempts 3rd, security question 4th. With 2 factor auth it would be complete. But I don’t want to use 2auth.