Incident Library

IL-0319 Oregon State University Open Source Lab · 2015-07-30database

Dropping databases on the MySQL replica mysql2 also dropped them on mysql1, as the pair actually ran master-master

The OSU Open Source Lab ran two MySQL 5.5 servers, mysql1 and mysql2. From July 15th, replication on mysql2 kept failing on a couple of session tables, and after several restarts the team decided to reload mysql2 from scratch. Documentation and staff knowledge described the pair as master-slave, but it was configured as master-master. When the reload began on July 30th, the team stopped slave replication on mysql2 and dropped its databases one by one; mysql2 still replicated its statements to mysql1, so the drops removed the databases on mysql1 too. After a few minutes the team noticed mysql1 losing databases, stopped the script and began restoring.

The restore was slow for three reasons. To save disk space, the lab's main resource limit, backups were taken with mysql-zrm under heavy compression, which made restores CPU-bound. A couple of databases failed to restore on indexing and foreign keys, and each failure forced the team to parse the roughly 200GB backup file again to restore that database separately and then extract the rest. And the backups were taken from mysql2, which had been out of date with mysql1 since the replication failures. Binary logs from the 17th through the 30th let most data be recovered, but some data written between the 15th and the 17th was lost. The lab also said the outage lasted too long and that it did not give enough progress reports during it.

Severity
–
Scope
Databases on the mysql1 and mysql2 MySQL cluster were dropped and unavailable until restored; some data written between July 15th and 17th was lost
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

Symptoms

On July 30th, a few minutes after the OSU Open Source Lab started reloading the MySQL server mysql2 to fix its replication errors, the team saw that databases were being dropped on mysql1 as well. The script was stopped, and the MySQL service behind mysql1-vip stayed down through a long restore.

The reload followed two weeks of replication trouble on mysql2. On July 15th replication errors on a couple of session tables paused replication, a large number of statements had to be skipped, and replication was restarted. On July 16th the same tables failed again in far greater number, a ticket was opened, and replication was restarted several times. In the week of the 20th the team decided to reload mysql2 completely and to look at other replication methods, mainly row-based replication.

For the reload, replication on mysql2 was stopped on purpose at 4:00PM PDT and a script dropped the databases on mysql2 one at a time with a short delay between drops. Documentation and what the staff knew said mysql1 and mysql2 were a master-slave pair, but both MySQL 5.5 servers were configured for master-master replication. Stopping slave replication on mysql2 did not stop mysql2 from sending its statements to mysql1, so each drop ran on both servers. The delays in the script are what let the team notice after a few minutes.

The restore then took far longer and more manual work than expected. Backups made with mysql-zrm were heavily compressed, so restoring was limited by CPU rather than disk IO. A couple of databases failed to restore because of indexing and foreign keys, and each failure meant parsing the whole backup file of around 200GB to pull out the bad database and then the databases not yet restored. Several test databases and databases that had grown past 100GB slowed the restore further.

Symptom Data lossService outageReplication lag

Root cause

Root cause Operator error

Component DatabaseDatabase replicationDatabase backups and snapshots
Contributing factor Unverified assumptionUntested backup
Trigger
On July 30th at 4:00PM PDT the team stopped slave replication on mysql2 and began dropping its databases one at a time to reload it and fix replication errors.

Detection

The team watching the reload noticed after a few minutes that mysql1 was also dropping databases; the delays between drops in the script gave them that time.

Remediation

Action items

Lessons

The lab had stretched its resources to host as many projects as possible, and that came with tradeoffs such as heavily compressed backups to save disk and less redundancy than wanted; the heavy compression made this restore CPU-bound. The lab concluded that it needs to change how it plans, monitors and manages resources, and that the length of the outage and the lack of progress reports were not acceptable.

Sources

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database, Database replication, Database backups and snapshots among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.