Configure MySQL Master-Slave Replication on Ubuntu 18.04

MySQL replication is the process of automatically copying data from one database server to one or more others. It lets you maintain identical copies of your database across separate machines, which is useful for distributing read traffic, creating live backups for disaster recovery, and running analytics queries without touching the production database.

In a Master/Slave topology, one server — the master — handles all write operations and propagates changes to one or more slave servers. By default, replication is asynchronous: the master writes change events to a binary log, and slaves pull those events when they are ready. This guide sets up a basic MySQL Master-Slave replication configuration with one master and one slave on Ubuntu 18.04. The same steps apply to MariaDB.

Prerequisites:

• Two Ubuntu 18.04 servers that can communicate over a private network
• The example servers use these private IPs:
  • Master: 192.168.121.190
  • Slave: 192.168.121.236
• If private IPs are unavailable, use public IPs and restrict port 3306 to trusted sources using UFW

Install MySQL 5.7 on both servers. Run the following commands on each:

bashsudo apt-get updatesudo apt-get install mysql-server

Using the same MySQL version on both servers avoids binary log format compatibility issues.

Set Up MySQL Master-Slave Replication: Configure the Master Server

Open the MySQL configuration file on the master:

bashsudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Set or uncomment the following lines:

inibind-address = 192.168.121.190server-id    = 1log_bin      = /var/log/mysql/mysql-bin.log

• bind-address restricts MySQL to listen on the master’s private IP only
• server-id must be a unique integer across all servers in the replication group
• log_bin enables the binary log, which records all database changes that slaves will read and replay

Restart MySQL to apply the changes:

bashsudo systemctl restart mysql

Create a dedicated replication user on the master. Log into the MySQL shell:

bashsudo mysql

Run the following SQL statements. Replace the IP and password with your slave IP and a strong password:

sqlCREATE USER 'replica'@'192.168.121.236' IDENTIFIED BY 'replica_password';GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.121.236';

Now check the master status and record the output:

sqlSHOW MASTER STATUS\G

File: mysql-bin.000001Position: 629

Note the File name and Position values. You will need both when configuring the slave. These numbers will differ on your server.

Configure the MySQL Slave Server

Open the MySQL configuration file on the slave server:

bashsudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Set the following values, using the slave’s private IP and a unique server ID:

inibind-address = 192.168.121.236server-id    = 2log_bin      = /var/log/mysql/mysql-bin.log

Restart MySQL on the slave:

bashsudo systemctl restart mysql

Log into the MySQL shell on the slave:

bashsudo mysql

Stop the slave threads, configure the connection to the master, then start replication:

sqlSTOP SLAVE;CHANGE MASTER TOMASTER_HOST='192.168.121.190',MASTER_USER='replica',MASTER_PASSWORD='replica_password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=629;START SLAVE;

Use the exact MASTER_LOG_FILE and MASTER_LOG_POS values from SHOW MASTER STATUS. A mismatch here is the most common cause of replication failures.

Verify the Replication Is Working

On the master server, create a test database:

sqlsudo mysqlCREATE DATABASE replicatest;

On the slave server, list all databases:

sqlsudo mysqlSHOW DATABASES;

If replicatest appears in the slave’s output, MySQL Master-Slave replication is working correctly. Any write operation performed on the master will now be automatically applied to the slave.

+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || replicatest        || sys                |+--------------------+

Replication is now active on your Ubuntu 18.04 servers. This setup gives you a live read replica for analytics and reporting, and a warm standby for disaster recovery. Leave a comment below if you run into any issues during setup.