Ensuring MySQL Data Safety: Configurations, Replication, and Backup Strategies

Sharing practical MySQL data‑safety techniques, the author outlines four key areas: single‑node security, cluster security, backup security, and future development.

1. Single‑Node Security

Two main parameters protect data on a standalone server:

Double Write – a mechanism that writes each InnoDB page to a mirror file before the final write, allowing recovery if a crash corrupts a page.

innodb_flush_log_at_trx_commit – controls when redo logs are flushed to disk (values 0, 1, 2) and thus determines data loss risk during server or OS crashes.

When set to 1, every transaction is flushed to the redo log, guaranteeing no loss; value 2 flushes to OS cache, still safe if the OS remains intact; value 0 risks loss.

2. Cluster Security

To avoid total data loss when a single node fails, replication or clustering spreads data across multiple nodes.

2.1 Master‑Slave Replication

Key parameters:

sync_binlog – 0 writes to cache, 1 forces disk sync; affects data loss on power failure.

binlog_format – can be STATEMENT, MIXED, or ROW (ROW is preferred despite larger log size).

sync‑master‑info and sync‑relay‑log‑info – ensure replication positions survive crashes.

relay_log_recovery – when enabled, the slave rebuilds the relay log from the last known position.

2.2 Semi‑Synchronous Replication

In semi‑sync mode the master waits for at least one slave to acknowledge receipt of the binlog and write it to its redo log before committing. Important features include:

Slave reports its semi‑sync status to the master.

Master transaction commit may be blocked until acknowledgment.

If the master times out, it falls back to asynchronous replication.

The parameter rpl_semi_sync_master_wait_point defines whether the master waits before or after committing, influencing data‑loss scenarios during master failure.

2.3 MySQL Galera Cluster

Galera provides true multi‑master clustering with synchronous replication, ensuring that writes are applied on a majority of nodes before committing, which greatly reduces the chance of data loss.

3. Backup Security

Backup is divided into data backup and binlog backup.

3.1 Data Backup

Two approaches:

Cold backup – taken when the server is stopped.

Hot backup – taken while the server runs.

Backup tools:

Logical:

mysqldump

,

mysqldumper

,

mysqlpump

,

SELECT … INTO OUTFILE

.

Physical: InnoDB

ibbackup

, Percona XtraBackup.

Physical backups are preferred for faster restoration and lower impact on production.

Backup storage should be remote whenever possible to avoid loss if the local server fails.

3.2 Binlog Backup

Since data backups capture a point‑in‑time snapshot, binlogs must be retained (ideally off‑site) to replay changes that occur between backups.

4. Development Evolution

As a service grows, it typically moves from a single‑node deployment to master‑slave replication and eventually to a clustered architecture, ensuring continuous availability and protection against node failures.