How to configure automatic failover for MySQL master-slave

You can configure automatic failover for a MySQL master-slave setup with tools such as Keepalived, MySQL Utilities, or MySQL InnoDB Cluster. This guide walks you through prerequisites, replication setup, failover tool configuration, testing, and best practices. Keepalived manages a virtual IP for high availability, while MySQL Utilities and InnoDB Cluster promote a new primary. The steps assume you know basic MySQL commands and have at least two servers ready. You will learn to protect data consistency and keep availability high when a master goes down. Follow along to build a failover plan you can trust.

Prerequisites for MySQL replication

Server and network requirements

You need at least two servers before you build any failover plan. One machine acts as the master and handles all write traffic. The other machine acts as the slave and receives copies of every change. Give each host a static IP address, a resolvable hostname, and a reliable network link between them. A dropped connection is one of the most common triggers for failover, so treat the network as part of your design, not an afterthought.

Failover can fire for three main reasons. The network between nodes can go down, the master machine can die, or someone can shut down MySQL itself. Each event looks different to your tooling, and each one demands a clear response. You can also run a master-to-master layout, where each master replicates to its own slave. That design adds data redundancy, yet your failover solution must still promote the correct node when each master owns a separate slave. Test this topology carefully, because a wrong promotion breaks consistency across the pair.

MySQL installation and version compatibility

Install the same MySQL version on every node. Mixed versions can break mysql replication in subtle ways, especially when the binary log format changes between releases. Check the version on each host before you configure anything, and upgrade mismatched servers first. On Ubuntu or Debian, the main configuration file lives at /etc/mysql/mysql.conf.d/mysqld.cnf, and you will edit it on the master later.

Plan your monitoring before you touch replication settings. You need a way to detect a dead primary quickly, and you need a way to confirm the replica is healthy and caught up. Decide which tool will watch the cluster, how often it checks, and who gets alerted. A solid plan here prevents surprises during a real outage. With servers ready and versions matched, you can move on to configuring replication itself.

Configure MySQL master-slave replication

Set up the master server

Start on the machine you chose as the master. On Ubuntu or Debian, open /etc/mysql/mysql.conf.d/mysqld.cnf and set a unique server ID, turn on binary logging, and name the log file. These settings let the primary record every change for shipping to each replica.

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_do_db = appdb

Restart the service after you save the file. Next, create a dedicated user for MySQL replication and grant it the REPLICATION SLAVE privilege. Lock the tables briefly, read the current binary log position, and note the file name and offset. That spot marks where each slave starts copying data.

Configure the slave and start replication

Move to the slave host and edit its own configuration file. Assign a different server ID, then restart the service. Point the replica at the master with the CHANGE MASTER TO statement, using the log file and position you recorded.

CHANGE MASTER TO
  MASTER_HOST='192.0.2.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='secret',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;

Run SHOW SLAVE STATUS and confirm both Slave_IO_Running and Slave_SQL_Running read Yes. Oracle Utilities can automate this step when you configure master to feed several slaves at once. Test MySQL replication by writing a row on the primary and reading it on the replica. Verify consistency with a checksum on both sides before you trust the link. A clean replica now mirrors the primary, ready for failover tooling.

Configure automatic failover for MySQL

You now have mysql replication working. The next step involves adding a mechanism that handles primary crashes automatically. Two main approaches dominate production setups. Keepalived moves a virtual IP between hosts. MySQL Utilities and InnoDB Cluster promote a slave to master directly. You can combine these tools for extra data redundancy and reliability.

Use Keepalived with a virtual IP

Keepalived runs on both servers and uses VRRP to share a floating IP address. The master normally holds this virtual IP. When the master fails, keepalived moves the IP to the slave. Applications connect to this single address and never see the switch. This design provides high availability with minimal changes to your application stack.

The keepalived configuration file stays at /etc/keepalived/keepalived.conf. On the master, define a health check script and a VRRP instance. The following example shows the syntax from a working deployment.

Copy this configuration to the slave. Change the state to BACKUP and lower the priority to 101. Restart the keepalived service on both machines. The service checks mysql every two seconds. When the master dies, the health check fails. The backup host takes over the virtual IP within seconds. You gain failover capability without modifying your application configuration.

Use MySQL Utilities or InnoDB Cluster

MySQL Utilities provides two command-line tools for handling primary failover. The mysqlrpladmin command performs a controlled switchover. The mysqlfailover command monitors the master and triggers an automated failover when the master goes down.

Run mysqlfailover --master=root@utils1:3306 --discover-slaves-login=root --rediscover in monitoring mode. It checks the master every few seconds. Upon failure, it identifies the best slave to promote. The tool applies any missing events from other slaves before promotion. You can also limit eligible slaves or run custom pre- and post-failover scripts.

MySQL InnoDB Cluster takes a different path. It uses Group Replication, which provides synchronous replication. The following table compares Group Replication to traditional asynchronous replication.

Choose Group Replication when you need strong consistency and can accept higher write latency. Choose traditional replication with Keepalived or MySQL Utilities when you want simpler setup and lower overhead. Test your failover configuration thoroughly. Simulate a master crash and verify that the virtual IP moves and the slave promotes correctly.

Test failover and best practices

Simulate master failure

You must prove your setup works before a real outage hits. Stop the MySQL service on the master host and watch what happens. Keepalived detects the dead process through its health check script. The virtual IP moves to the slave within seconds. Your application reconnects to the new address without manual steps.

Run this drill during a maintenance window. Confirm the slave promotes to primary and accepts writes. Check that the old master rejoins as a replica when it recovers. Keepalived handles the IP shift, but your promotion script must reset replication on the new primary. Verify that monitoring replication health tools report a clean state after the switch.

Avoid split-brain and data loss

Split-brain happens when two nodes both think they are primary. Both accept writes, and your data diverges. MySQL InnoDB Cluster prevents this with quorum. A write commits only when a majority of nodes acknowledge it. This Paxos-based consensus is the core mechanism that stops split-brain during network partitions.

Even-numbered clusters are unsafe. A 50/50 split leaves neither side with a majority, so both refuse writes. This protects consistency at the cost of availability.

Follow these rules to protect your data. Confirm the master is truly dead before you fail over. Fail over only once, and never promote an inconsistent slave. Write only to the primary. After failover, update your application to point at the new master. Test your configuration in staging first.

Automatic failover rests on three pillars: working replication, a failover tool, and repeated testing. Pick your tool by topology. Keepalived suits simple virtual IP failover. MySQL Utilities or InnoDB Cluster handles topology-aware promotion.

Next, add monitoring, schedule regular failover drills, and review MySQL logs after every test. Run each drill in staging before production. That habit protects your data and keeps availability high.

FAQ

How long does automatic failover take?

Keepalived checks the primary every two seconds and moves the virtual IP once the health check fails, so the switch completes within seconds. MySQL Utilities and InnoDB Cluster promote a new primary on a similar timescale. Actual duration depends on your monitoring interval and network conditions.

Can I run failover without a virtual IP?

Yes. MySQL Utilities and InnoDB Cluster promote a replica directly, so your application must reconnect to the new host. A virtual IP keeps the address stable and spares you that reconfiguration. Choose the approach that matches how much change your application can tolerate.

What happens to the old master after failover?

The old master rejoins as a replica once it recovers. Your promotion script points it back to the new primary with CHANGE MASTER TO. Verify that replication resumes cleanly and that no writes landed on the demoted node during the outage.

Does automatic failover risk losing writes?

It can, with asynchronous replication. A primary crash before replicas fetch recent binary log events leaves those changes behind. InnoDB Cluster avoids this through synchronous Group Replication and quorum-based commits. Traditional setups accept a small window of potential loss.

How many nodes do I need for safe failover?

Three nodes give you a quorum of two and tolerate one failure. Even-numbered clusters are unsafe because a 50/50 split leaves neither side with a majority. Keepalived pairs work for simple virtual IP failover, but quorum-based tools need an odd count.