Testing Safe vs. Forceful MySQL Shutdown in a Semi‑Synchronous Replication Setup

Background

During a discussion in a MySQL community group about preferred ways to stop MySQL, a poll showed most users chose service mysqld stop (systemctl), while a minority used kill -9 . The author, a senior DBA, decided to verify the poll results by conducting his own tests.

Environment Introduction

A MySQL 5.7 semi‑synchronous master‑slave topology was built with the following configuration:

master  ip: 192.168.168.11  port: 6666  version: 5.7.26
slave   ip: 192.168.168.12  port: 6666  version: 5.7.26

1. Master Configuration

mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 1000000    |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+

2. Slave Configuration

mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 1000000    |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+

Test Demonstration

1. Safe Shutdown Scenario

Steps:

Stop slave replication ( stop slave; ).

Issue an update on the master that waits for semi‑sync ACK.

Run shutdown; on the master.

Result: The update transaction is committed after the shutdown, but the slave does not receive the change, leading to a temporary inconsistency that can be resolved by restarting the master and re‑enabling semi‑sync.

2. Forceful Shutdown Scenario

Steps:

Stop slave replication.

Issue an update on the master that waits for ACK.

Send kill -9 to the MySQL process.

Result: The client receives “Lost connection to MySQL server during query”, the update is rolled back on the master, and the slave remains consistent because the transaction never committed.

Shutdown Process Overview

Based on MySQL 5.7 documentation, when the server receives a SIGINT (or a shutdown command), it performs the following steps:

Stop accepting new connections (close TCP/IP ports and sockets).

Handle existing connections: idle connections are terminated; active transactions are rolled back (or partially applied for non‑transactional engines).

Stop the semi‑sync ACK receiver thread.

Flush table caches and close storage engines (InnoDB flushes its buffer pool, writes LSN, and stops internal threads).

Terminate the server process.

Log excerpts show the “Stopping ack receiver thread” and the sequence of plugin shutdowns, confirming the behavior observed in the tests.

Conclusion

The experiments reveal that the seemingly dangerous kill -9 can actually preserve data consistency in this semi‑synchronous setup, while the graceful shutdown may temporarily leave the slave out‑of‑sync if the master’s pending transaction is forced to commit before the ACK is received. In production, both methods should be used with caution, especially on MySQL versions prior to 5.7 where kill‑9 is riskier.