binlogs filling up disk

Problem: binlogs fill up storage space
Solution: manage your binlogs

Ever notice your disk utilization fly off the handle due to binlogs? There are a few commands and settings you need to be aware of.

Important SQL Commands

RESET MASTER

Removes all the binary logs listed in the index file, resets the index, and creates a new binary log file. Basically, it starts over the logging from scratch. This command is not safe for Replication environments.

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

Removes binary logs older than a certain date. This command is safe in Replication environments.

Important Settings in /etc/my.cnf

To limit the size of the individual binlog files:

max_binlog_size=200M

To automatically clean log files older than a certain number of days:

expire_logs_days = 14

How to resolve "Host 'host_name' is blocked"

Problem: "Host 'host_name' is blocked"

Solution:

1. increase your max_connect_errors setting in /etc/my.cnf
2. investigate and resolve the reason for your connection failures.

The way that I discovered this MySQL "feature" is by observing a strange phenomenon. Periodically, during peak traffic hours, our servers would lose connection to the database.

The database would continue running, the firewall rules were clear, but for some unknown reason, MySQL would simply refuse the connection.

Apparently, according to the MySQL Documentation for max_connect_errors:

"If there are more than this number of interrupted connections from a host, that host is blocked from further connections. "

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connect_errors

The default value for max_connect_errors is 10, that means that your application will get BANNED after 10 connection failures. Well, a good question to ask is: what exactly is meant by connect errors?

It seems that connections that take more than connect-timeout seconds to complete are considered as connect errors,

therefore setting the connect-timeout setting too low will cause an increase in connection failures, and therefore cause your application to get banned.