While MySQL replication is rock-solid for most use cases, there's a dangerous loophole that many teams overlook: replica servers happily accept write operations by default. This creates potential data consistency issues when application code accidentally writes to replicas.

Consider this all-too-common scenario:

// Buggy application code
$connection = getReadReplicaConnection(); 
// Oops! Developer meant to use getMasterConnection()
$connection->query("UPDATE users SET last_login=NOW() WHERE id=123");

Your replica now contains data that will never sync back to the master, creating a silent data divergence.

MySQL provides several ways to enforce read-only mode:

1. The read_only System Variable

The simplest solution is setting this in your my.cnf:

[mysqld]
read_only = ON

Or dynamically at runtime:

SET GLOBAL read_only = ON;

2. Superuser Protection

For added security, combine with:

SET GLOBAL super_read_only = ON;

This prevents even SUPER users from writing (except replication threads).

Always implement in your MySQL configuration rather than relying on application logic:

# /etc/mysql/conf.d/replica.cnf
[mysqld]
server-id = 2
log_bin = mysql-bin
relay_log = mysql-relay-bin
read_only = ON
super_read_only = ON

If you need temporary write access (for maintenance), use:

SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;
-- Perform maintenance
SET GLOBAL super_read_only = ON;
SET GLOBAL read_only = ON;

Check replica status with:

SHOW VARIABLES LIKE 'read_only';
SHOW VARIABLES LIKE 'super_read_only';

For automated monitoring, add this to your health checks:

SELECT @@read_only, @@super_read_only;

For MySQL 8.0+, create restricted roles:

CREATE ROLE replica_reader;
GRANT SELECT ON *.* TO replica_reader;

Then assign this role to your application users connecting to replicas.

While MySQL's replication system is robust, one often overlooked vulnerability is the ability to write to replica servers. This can create serious data consistency issues when applications accidentally (or intentionally) perform write operations on replicas. The problem compounds when using row-based replication, as conflicting writes can break replication threads.

The most straightforward solution is setting the read_only system variable:

SET GLOBAL read_only = ON;

This prevents all users without the SUPER privilege from making changes to data. However, it's not foolproof as:

• Users with SUPER privilege can still write
• Doesn't prevent DDL operations
• Temporary tables can still be created

MySQL 5.7.8+ introduced a stronger alternative:

SET GLOBAL super_read_only = ON;

This configuration:

• Implies read_only
• Prevents writes even from SUPER users
• Only allows replication threads to apply changes

For production environments, set these in your my.cnf/my.ini:

[mysqld]
read_only = 1
super_read_only = 1

When combined with proper user permissions, this creates multiple layers of protection:

CREATE USER 'app_read'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON db_name.* TO 'app_read'@'%';

Some legitimate operations might need temporary write access:

-- For maintenance
SET GLOBAL super_read_only = OFF;
-- Perform operations
SET GLOBAL super_read_only = ON;

-- Or for specific sessions
SET SESSION read_only = OFF;

Create alerts for unexpected write attempts:

CREATE EVENT check_replica_writes
ON SCHEDULE EVERY 5 MINUTE
DO
BEGIN
    DECLARE write_count INT;
    SELECT COUNT(*) INTO write_count
    FROM performance_schema.events_statements_summary_by_digest
    WHERE DIGEST_TEXT LIKE 'UPDATE%' 
    OR DIGEST_TEXT LIKE 'INSERT%'
    OR DIGEST_TEXT LIKE 'DELETE%';
    
    IF write_count > 0 THEN
        -- Trigger alert
    END IF;
END;

In your connection setup, explicitly mark replicas as read-only:

// Python example
read_conn = mysql.connector.connect(
    host='replica1',
    database='app_db',
    user='app_read',
    password='secure_password',
    read_only=True  # Enforces at connection level
)

This provides client-side enforcement in addition to server-side restrictions.