MySQL Replication on AWS EC2 with Docker: Comprehensive Guide

Introduction

This guide explains setting up MySQL replication between an AWS EC2 master instance and a replica within a Docker container on another EC2 instance. It includes integrated troubleshooting steps for a smooth setup experience.

Prerequisites

Two AWS EC2 instances running Ubuntu.
SSH access to both EC2 instances.

Step 1: Setting Up the Master MySQL Server

Install MySQL: SSH into your master EC2 instance and install MySQL:
```
 sudo apt-get update
 sudo apt-get install mysql-server
```
Configure MySQL for Replication: Edit the MySQL configuration:
```
 sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
- Set the bind-address to 0.0.0.0 to allow connections from any IP:
```
  bind-address = 0.0.0.0
```
- Configure server ID and binary logging:
```
  server-id = 1
  log_bin = /var/log/mysql/mysql-bin.log
```
- Restart MySQL to apply changes:
```
  sudo systemctl restart mysql
```

Create a Replication User: Log into MySQL:

 sudo mysql -u root -p

Create a user specifically for replication:

 CREATE USER 'replicator'@'%' IDENTIFIED BY 'yourpassword';
 GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
 FLUSH PRIVILEGES;

Get Master Status: Note the File and Position for replication setup:
```
 SHOW MASTER STATUS;
```

Step 2: Setting Up the Slave EC2 Instance with Docker

Install Docker: SSH into the slave instance and install Docker:

 sudo apt-get update
 sudo apt-get install docker.io
 sudo systemctl start docker
 sudo systemctl enable docker

Run MySQL Container: Pull and run the MySQL Docker image:

 docker pull mysql:8.0
 docker run --name mysql-replica -e MYSQL_ROOT_PASSWORD=yourpassword -d mysql:8.0

Step 3: Configuring the MySQL Replica

Access MySQL Inside Docker:

 docker exec -it mysql-replica mysql -u root -p

Set Up the Replica:

If using MySQL 8+, alter the replication user's authentication method due to caching_sha2_password issues:

  ALTER USER 'replicator'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'yourpassword';
  FLUSH PRIVILEGES;

Configure the replica with the master's details:

  CHANGE MASTER TO
    MASTER_HOST='master_public_ip',
    MASTER_USER='replicator',
    MASTER_PASSWORD='yourpassword',
    MASTER_LOG_FILE='noted_file_from_master',
    MASTER_LOG_POS=noted_position;

Address a server ID conflict if both servers have the same ID:

  STOP SLAVE;
  SET GLOBAL server_id = 2;
  START SLAVE;

Start the slave and check the status:
```
  START SLAVE;
  SHOW SLAVE STATUS\G;
```

Step 4: Testing Replication

Create Test Data on Master:

Create a database and table, then insert data on the master:

  CREATE DATABASE test_replication;
  USE test_replication;
  CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255));
  INSERT INTO test_table (data) VALUES ('Hello, replication!');

Verify Replication on Slave:
- In the MySQL shell inside Docker, check for the replicated data:
```
  SHOW DATABASES;
  USE test_replication;
  SELECT * FROM test_table;
```

Troubleshooting Common Issues

Skipping Transactions: If a specific transaction is causing issues on the slave:

  STOP SLAVE;
  SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
  START SLAVE;

Conclusion

You've now successfully set up and tested MySQL replication between an AWS EC2 master instance and a replica within a Docker container. This setup enhances your database's reliability and scalability. Remember to monitor and maintain your replication setup for optimal performance.

Anique's Blog