Since I got very useful and profound help in this board last time, I hope someone can help me with this issue:
Our hoster was complaining that our intranet (which we are developing on Drupal) is making the database running out of space. He attached a screenshot where it says that we are using 24GB (!) on the MySQL server (from 30GB total) (see screenshot below).
They assume it is due to "temp files which haven't been deleted after picture upload." I really do not understand this, since:
1) Pictures are not stored in the database, but in sites/default/files.
2) When I have a look with phpMyAdmin I can see that the Drupal database is only using about 86MB for all tables.
After I explained to them that I am afraid that I do not understand the issue, they sent another screenshot (see below) and saying that "Mysql-bin log show that it used 1GB per file and glow very fast. This make disk full."
Again, I am not a server or database expert, so I hope someone can help with this (i.e. explaining the screenshots and mysql-bin to me so I understand what they are talking about, since sometimes I feel they do not understand the things themselves).
Comments
Open my.cnf (usually located
Open my.cnf (usually located at /etc/mysql) and comment next lines:
...#log-bin
...
#expire_logs_days = 10
...
Then restart MySQL
Thanks for your reply. I will
Thanks for your reply. I will forward your advise to our hoster. Could you nevertheless do me a favor and explain in 1-2 sentences what exactly it means and where the advantages/disadvantages are? I.e. what could be a reason why our webhoster does not have this setting anyways by default?
MySQL basically logs every
MySQL basically logs every change to the data ever done. These are the logs you are seeing. These logs are CRITICAL to having a good backup plan. Typically you do a nightly backup of your database files (not mysqldump). You also should constantly backup and rotate the binlogs. What this allows is very accurate backups. In a failure case you restore the newest backup of the DB files, then you play back all the relevant binlogs. This gets you a very reliable backup scheme.
Thanks jmccaffrey. Now as you
Thanks jmccaffrey. Now as you mention it is critical in terms of backups I am afraid to just tell our hosting provider to turn it off. Do you have any recommendations what a good solution is, i.e. how to have good backups while not running out of disk space because of all these logs? Are we responsible for making backups or is it our hosters responsibility?
Backup strategy
Like mentioned above, just cap the number of days the binlog is stored for. I see ones from Feb, which is just excessive.
You are always responsible for backups! Your hosting provider may have backups as a service, but whose butt is on the line when they don't work? You also don't just need the backup files, you need a backup strategy. If you don't know what to do with the backup files and it takes you 2 days to recover from a crash, you're still in a bad place. You need to practice restoring the backups and make sure your backups aren't going to be corrupted at the same time as your main database.
Ken Winters
Are you paying for shared
Are you paying for shared hosting, a managed VPS/dedicated, or self-managed VPS/dedicated. If it's either of the first two it sounds to me like your host doesn't know about DB backups and logging. You've got binlogs going back to Feb 27th which, assuming you have some sort of nightly backup system in place, is completely unnecessary and a waste of space. This is basically a log of everything that has ever happened on your site since that date. Perhaps you need to figure out if your host is managing backups and if so then work with them to get the log cleanup to match your backup frequency.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
You (or your hoster) can use
You (or your hoster) can use a simple script to clean old bin logs on cron run (even without changing mysql configuration) instead of complaining about running out of space. Example script:
purge_binlogs.sh#!/bin/sh
action()
{
mysql --password=rootpassword -h localhost --port=3306 -u root mysql<<EOFMYSQL
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 24 HOUR);
EOFMYSQL
}
action
In your server cron add this:
*/15 * * * * sh /path/to/purge_binlogs.sh >/dev/null 2>&1HTH ~Grace
What I do on my personal
What I do on my personal sites is run a nightly DB maintenance shell script at 3am. Here's what it does:
This has worked out very well for me. If I make some stupid mistake updating something, I can go back and reload the mysql dumps from the night before, then replay the binary logs in to get me back up to speed from the time of the dump to now.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
Cache tables
I would also recommend using a cache handler module, like Memcache API or CacheRouter. One possible reason for large binary logs is that hundreds of megabytes of worthless cache data is being saved. Each time a Drupal site is updated, the cache is cleared and then resaved, which can add up.
http://httpremix.com
Script
I'm using a slightly modified version of this:
https://support.mayfirst.org/ticket/361
Run from cron, it basically does a PURGE BINARY LOGS while keeping (in my case) the 20 most recent binlogs.