Mysql: What to do if a disc full happens on master OR slave and ther is no space left to binary logs

Question

Recently I configured master-slave replication for my production with out considering all the user cases. I am started on all the possible use cases at hard ware level again to ensure we will be ready to handle all the failure.

I dint see any better approach for the below scenarios.

There is no space left on master to write binary logs
There is no space left on slave to write really logs

Thanks a lot for any help on this. Regards, Uday

Solution

Basically you want to detect this before it happens.

I use monit with a filesystem check to send an email and try some basic steps like so;

 check filesystem rootfs with path /dev/hda1
IF SPACE USAGE > 90 % THEN alert
IF SPACE USAGE > 95 % THEN 
EXEC '/run/script/with/logrotate/and/empty/tmp/dir/while/you/read/alert/email'

---

The outcome of a Disk FULL condition on mysql is highly dependent on how you configured MySQL and the underlying partition structure. The default on CentOS packaged version is to write the following

logs -> /var/log/mysqld.log
binlogs -> /var/lib/mysql/mysql-bin.00NNNN
data -> /var/lib/mysql/

However, unfortunately the default filesystem layout is to put /var/ on a single parition like so;

/dev/cciss/c0d0p2 on /var type ext3 (rw)

hence the result of disk full is usually a pretty sh*tty outcome with loss of data.

The MySQL server procedure for dealing with a Disk Full condition is described in the manual here;
http://dev.mysql.com/doc/refman/5.0/en/full-disk.html

According to the docs, if you free up enough space that it should continue;

When a disk-full condition occurs, MySQL does the following:

It checks once every minute to see whether there is 

enough space to write the current row. If there is enough space, it continues as if nothing had happened.

Every 10 minutes it writes an entry to the log file,

warning about the disk-full condition.

To alleviate the problem, you can take the following actions:

To continue, you only have to free enough disk space to insert all records.

However my experience is that by then, too much crashy stuff has happened and the mysqld needs to be restarted with loss of pending transactions.

Regarding the order of operations, the mysql manual for version 5.1 has this to say about what happens to transaction during failures to write the transacion or the binlog;

Binary logging is done immediately after a statement completes but before any locks are released or any commit is done. This ensures that the log is logged in execution order.

Updates to nontransactional tables are stored in the binary log immediately after execution. In MySQL 5.1.22 and earlier versions of MySQL 5.1, an UPDATE statement using a stored function that modified a nontransactional table was not logged if it failed, and an INSERT ... ON DUPLICATE KEY UPDATE statement that encountered a duplicate key constraint—but did not actually change any data—was not logged. Beginning with MySQL 5.1.23, both of these statements are written to the binary log. (Bug #23333)

Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that the modifications to those tables are replicated.

Comments

[Uday]

This is good Tom.. How what I should do with some thing which already happened. How about the transaction that is committed in the DB but not logged in the binary log. Please post me some thing that deals with this situation.IS there any way to work around ?

[gokva]

it depends on how you configured mysql file partitions. In the ideal scenario you would configure Mysql to write to separate partitions for the DataDir, the LogDir and the Binary log location, so the DISK FULL has fairly predictable consequences. However if you write all your mysql data/log/binary logs to the same partition, then the outcome is dependent on at what point in the transaction the DISK FULL condition happened.