MySQL Replication Using Binary Log File Position (MySQL 8.4 LTS)

Updated October 2026 for MySQL 8.4 LTS on AlmaLinux 9 and Ubuntu 24.04.

Binary log file position replication is the classic way to copy one MySQL server to another: you note the source’s current binlog file and byte offset, load a copy of the data on the replica, and tell the replica to start reading from that exact spot. This guide does that on MySQL 8.4 LTS. If you’re following an older 5.7 tutorial, stop. CHANGE MASTER TO, START SLAVE and SHOW MASTER STATUS no longer exist in 8.4 and return a syntax error.

For a brand-new pair of servers I’d use GTID-based replication instead, and there’s a short section on it at the end. File/position is still worth knowing, because it’s what you’ll find on a lot of older setups and it’s how the binlog actually works underneath.

Which MySQL version this covers

As of October 2026, Oracle’s download page lists two LTS lines, 8.4 and 9.7, plus the 26.7 Innovation release. The commands here are written for 8.4 LTS, which is what many teams standardized on. The 9.7 manual documents the same CHANGE REPLICATION SOURCE TO, GET_SOURCE_PUBLIC_KEY and SHOW BINARY LOG STATUS statements.

Here’s what changed since the 5.7 version of this post:

Two defaults also matter. Binary logging is on by default, and new accounts use caching_sha2_password, which needs either an encrypted connection or RSA key exchange for the replica to log in. That second one is the most common reason a fresh 8.4 replica fails to connect.

A few terms you’ll see below:

• Source: the server you copy from (formerly “master”).
• Replica: the server that receives the changes (formerly “slave”).
• Binary log: the source’s record of every change, written as numbered files in the data directory (/var/lib/mysql by default). Run SHOW VARIABLES LIKE 'log_bin_basename'; to see exactly where yours are.
• Relay log: the replica’s local copy of events pulled from the source, which the applier thread then executes.

1. Install MySQL 8.4 LTS on both servers

AlmaLinux 9

Use Oracle’s MySQL 8.4 repository package. It enables the 8.4 LTS series by default. The official guide also has you disable the distro’s mysql module so AppStream packages don’t get in the way.

sudo dnf install https://dev.mysql.com/get/mysql84-community-release-el9-4.noarch.rpm
sudo dnf module disable mysql
sudo dnf install mysql-community-server
sudo systemctl enable --now mysqld
sudo grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p

Log in with the temporary password and set a real one right away:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Use-A-Strong-Password-1!';

The config file is /etc/my.cnf.

Ubuntu 24.04

Ubuntu’s own mysql-server package is 8.0, so add Oracle’s APT repository and pick mysql-8.4-lts when the configuration dialog asks for a server version. You set the root password during the install.

wget https://dev.mysql.com/get/mysql-apt-config_0.8.40-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.40-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

The service is called mysql on Ubuntu, not mysqld, and the server config lives in /etc/mysql/mysql.conf.d/mysqld.cnf. Check that file for a bind-address = 127.0.0.1 line. If it’s there, the replica won’t be able to reach the source.

2. Configure the source

Add this to the [mysqld] section of the source’s config file:

[mysqld]
server_id=1
log_bin=mysql-bin
bind_address=0.0.0.0

server_id must be unique across every server in the topology. The default is 1, so the replica is the one that has to change. log_bin is already on in 8.4; setting a base name just keeps the file names stable if the hostname ever changes (otherwise they’re named binlog.000001 and so on). For a durable setup with InnoDB, the manual also recommends sync_binlog=1 and innodb_flush_log_at_trx_commit=1, which are the defaults.

Restart MySQL (sudo systemctl restart mysqld on AlmaLinux, sudo systemctl restart mysql on Ubuntu), then open port 3306 to the replica only. Replace 10.0.0.20 with your replica’s address:

# AlmaLinux 9 (firewalld)
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.20/32" service name="mysql" accept'
sudo firewall-cmd --reload

# Ubuntu 24.04 (ufw)
sudo ufw allow from 10.0.0.20 to any port 3306 proto tcp

Replication traffic and the replica’s credentials shouldn’t cross the public internet unencrypted. Run it over a private network between the two servers if you have one.

3. Create the replication user

On the source:

CREATE USER 'repl'@'10.0.0.20' IDENTIFIED BY 'Another-Strong-Password-2!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.20';

Yes, the privilege is still called REPLICATION SLAVE in 8.4. This account uses caching_sha2_password, which you’ll account for when you point the replica at the source in step 6.

4. Get the binary log coordinates

If the source has no data yet

Lock writes, read the position, then release the lock, all in one session. If that session closes early, the lock goes with it.

FLUSH TABLES WITH READ LOCK;
SHOW BINARY LOG STATUS\G
UNLOCK TABLES;

Write down File (for example mysql-bin.000001) and Position. Skip to step 5.

If the source already has data

Take a dump that records the coordinates for you. With --source-data=2, mysqldump writes the CHANGE REPLICATION SOURCE TO line into the dump as a comment, and --single-transaction holds the global read lock only for a moment at the start instead of for the whole dump.

mysqldump -uroot -p --all-databases --single-transaction --source-data=2 \
  --routines --events --triggers --flush-privileges > source-dump.sql

grep -m1 "CHANGE REPLICATION SOURCE TO" source-dump.sql

The grep returns something like -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000003', SOURCE_LOG_POS=1573;. Those are your coordinates.

--single-transaction only gives a consistent snapshot for InnoDB tables. If you still have MyISAM tables, drop that flag and mysqldump will lock all tables for the length of the dump. On large databases, mysqldump is slow to restore; a physical copy (MySQL Enterprise Backup, Percona XtraBackup or the clone plugin) is faster, but that’s a different walkthrough.

5. Configure the replica

On the replica, add to [mysqld]:

[mysqld]
server_id=2
log_bin=mysql-bin
relay_log=relay-bin
read_only=ON

read_only stops ordinary application accounts from writing to the replica by accident. Replication threads and admin accounts still get through. Restart MySQL.

If you took a dump in step 4, copy it over and load it:

scp source-dump.sql user@10.0.0.20:/tmp/
mysql -uroot -p < /tmp/source-dump.sql

An --all-databases dump includes the mysql system schema, and --flush-privileges reloads the grant tables at the end, so after the import the replica’s MySQL accounts and passwords match the source’s. Log in with the source’s root password from then on.

6. Point the replica at the source and start it

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.10',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='Another-Strong-Password-2!',
  SOURCE_LOG_FILE='mysql-bin.000003',
  SOURCE_LOG_POS=1573,
  GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

GET_SOURCE_PUBLIC_KEY=1 lets the replica fetch the source’s RSA key so caching_sha2_password can exchange the password over an unencrypted link. Leave it out and the replica fails to authenticate. The better option is to encrypt the connection: replace that line with SOURCE_SSL=1. MySQL generates self-signed certificates on first start, so this encrypts traffic out of the box, though it doesn’t verify the source’s identity unless you add your own CA and the SOURCE_SSL_VERIFY_SERVER_CERT option.

7. Check that it’s working

SHOW REPLICA STATUS\G

The fields that matter:

• Replica_IO_Running: Yes means the replica is connected and pulling binlog events.
• Replica_SQL_Running: Yes means it’s applying them.
• Seconds_Behind_Source should drop to 0 or near it once it catches up.
• Last_IO_Error and Last_SQL_Error should be empty. An authentication error here almost always means the caching_sha2_password setting from step 6 is missing.

Then do a real test. Create a throwaway database on the source, insert a row, and confirm it shows up on the replica within a second or two.

Why I’d use GTIDs on a new build

With file/position replication, the replica only knows “file X, byte Y.” Promote a replica or repoint one to a new source and you have to work out the matching coordinates by hand. GTIDs give every transaction a global ID, so the replica tracks what it has already applied and asks the new source for the rest.

For new servers, add these to [mysqld] on both the source and the replica before you load any data:

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON

Then on the replica, drop the file and position options and use auto-positioning:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.10',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='Another-Strong-Password-2!',
  SOURCE_AUTO_POSITION=1,
  GET_SOURCE_PUBLIC_KEY=1;

START REPLICA;

If you seed the replica with mysqldump when GTIDs are on, dump your application databases with --databases rather than --all-databases. The manual warns against loading a dump that includes system tables into a server with gtid_mode=ON. mysqldump adds the source’s GTID set to the dump automatically. Switching an existing file/position setup to GTIDs is a separate procedure covered in the MySQL 8.4 GTID setup guide.

The full reference for everything above is the MySQL 8.4 binary log file position replication chapter.

Don’t want to run replication yourself? Our managed servers team can handle the server side for you.