MySQL Replication FAQ: Compatibility, Master‑Slave Behavior, Performance, and High‑Availability

Question 1: Can the replication feature be used across different operating systems? Answer: Yes, the replication feature works on different operating systems.

Question 2: Can the replication feature be used on different hardware architectures? Answer: Yes, it works on both 32‑bit and 64‑bit systems.

Question 3: In master‑slave replication, must the slave always stay connected to the master? Answer: No. The slave can disconnect; when it reconnects it will catch up using the binary log. The binary log must not be purged before the slave has read the remaining events.

Question 4: How can you know how far the slave is lagging behind the master? Answer: Run SHOW SLAVE STATUS and check the Seconds_Behind_Master column.

Question 5: Can the master be forced to stop updates until the slave catches up? Answer: Yes. Follow these steps: a. On the master execute:

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

b. On the slave execute (using the values from the previous output): SELECT MASTER_POS_WAIT('log_name', log_pos); (this blocks until the slave reaches the specified position). c. On the master resume updates with: UNLOCK TABLES; Question 6: What should be considered when setting up bidirectional replication? Answer: MySQL does not support any lock protocol between master and slave, so distributed updates are not atomic. Conflicting updates can cause data inconsistency unless the application ensures safe ordering or resolves conflicts. Performance gains are limited; each server performs the same amount of work as a single server, with only reduced lock contention, which may be offset by network latency.

Question 7: How can replication improve system performance? Answer: Designate one server as the master for writes and add as many slaves as needed for read scaling. Use options such as --skip-innodb, enable low_priority_updates, and set delay_key_write=ALL on slaves. For read‑heavy workloads, a one‑master‑many‑slaves topology can increase throughput until network bandwidth or master write capacity becomes a bottleneck. Performance can be estimated with the formula R = 1200 - 2 * W (R = reads per second, W = writes per second) and extended calculations involving the number of slaves N to predict maximum write capacity.

Question 8: How can replication be used to provide high availability? Answer: High‑availability solutions require monitoring tools, custom scripts, or middleware to handle failover from master to slave. MySQL Router can provide automatic failover; manual failover can be achieved by reconfiguring the application or updating DNS to point to a new MySQL instance.

Question 9: How to prevent GRANT and REVOKE statements from being replicated to slaves? Answer: Start the server with the option --replicate-wild-ignore-table=mysql.% to ignore replication of tables in the mysql database.

Question 10: How to know the timestamp of the last statement replicated on the slave? Answer: The slave’s SQL thread updates its time using the event timestamp. The Time column in SHOW PROCESSLIST shows the seconds elapsed since the last replicated event, allowing you to infer the last replication time.