Tibor's Musings

MySQL Replication Repairing

MySQL replication stopped due to a disk full situation on MySQL server. Here are notes on how I repaired it.

Due to /opt being full on DB master, the replication crashed. Symptoms, on DB slave:

PCUDSSW1513> sudo tail -100 /var/log/mysqld.log
120524  9:43:48 [ERROR] Got fatal …

MySQL Replication Purging

How to purge old unnecessary MySQL binary log files on DB master, after checking replication status.

On DB master (PCUDSSX1501), did:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000075 |   277790 |              |                  |
+------------------+----------+--------------+------------------+

On DB slave (PCUDSSW1513), did:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: 137.138.198.204 …

MySQL Replication Setup

Notes on how to set up MySQL replication.

Firstly, stop web application nodes and redirect users to "please come back later" web page.

Secondly, on DB node (PCUDSSX1501), switch on log-bin in /etc/my.cnf if not done yet, then perform in one DB client connection:

mysql> TRUNCATE session;
mysql …

MySQL In-Memory Temporary File System

If a MySQL application uses many tables containing TEXT columns, then for various JOIN and GROUP BY operations on these tables, MySQL cannot use in-memory temporary tables and need to resort to using on-disk ones. The in-memory vs on-disk speed difference is considerable in these cases. Introducing a special in-memory …