Setting Up MySQL Replication

mysql_replication.md

Setting Up MySQL Replication

Overview

We’ll configure one MySQL instance as the source database and another as its replica. Replication allows data synchronization between these separate databases.

Step 1: Configure the source

1. Obviously install MySQL on 2 servers.
2. Enable Binary Logging

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following line in that or equivilent file:
log-bin = /var/log/mysql/mysql-bin.log

3. Restart services with susdo systemctl restart mysql
4. Create a unique server id for the source.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following line in that or equivilent file:
server-id = 1

Step 2: Set up the replica

1. Edit replica config file

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following lines in that or equivilent file:
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log-bin = /var/log/mysql/mysql-bin.log

2. Restart with sudo systemctl restart mysql
3. On the source create a user run:

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

4. Lock the master database to take a snapshot:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

5. Dump the master database:

mysqldump -u root -p --all-databases --master-data > master_dump.sql

6. Unlock the master db with UNLOCK TABLES;
7. Copy the dump file to the replica:

scp master_dump.sql user@repllica_server:/path/to/dump/

Step 3: Import data to replica

1. On the replica import the data: mysql -u root -p < master_dump.sql
2. Configure replication on the replica:

CHANGE MASTER TO
    MASTER_HOST = 'source_server_ip',
    MASTER_USER = 'replication_user',
    MASTER_PASSWORD = 'your_password',
    MASTER_LOG_FILE = 'master_bin_log_file',
    MASTER_LOG_POS = master_bin_log_position;
START SLAVE;

3. Check the replica status with SHOW SLAVE STATUS\G
4. Ensure that Slave_IO_Running and Slave_SQL_Running are both Yes.

That's it!

NOTE: Make sure to update the firewall if one is running.

Replication with docker containers

1. docker-compose.yml

version: '3'
services:
  mysql-master:
    image: percona:ps-8.0
    container_name: mysql-master
    restart: unless-stopped
    env_file: ./master/.env.master
    cap_add:
      - all
    volumes:
      - ./master/data:/var/lib/mysql
      - ./master/my.cnf:/etc/my.cnf
    environment:
      - TZ=${TZ}
      - MYSQL_USER=${MYSQL_USER}
      - MYSQL_PASSWORD=${MYSQL_PASSWORD}
      - MYSQL_ROOT_PASSWORD=${MYSQL_PASSWORD}
    networks:
      default:
        aliases:
          - mysql

  mysql-slave:
    image: percona:ps-8.0
    container_name: mysql-slave
    restart: unless-stopped
    env_file: ./slave/.env.slave
    cap_add:
      - all
    volumes:
      - ./slave/data:/var/lib/mysql
      - ./slave/my.cnf:/etc/my.cnf
    environment:
      - TZ=${TZ}
      - MYSQL_USER=${MYSQL_USER}
      - MYSQL_PASSWORD=${MYSQL_PASSWORD}
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    networks:
      default:
        aliases:
          - mysql

2. Environment variables

• In the above configuration, replace ${TZ}, ${MYSQL_USER}, ${MYSQL_PASSWORD}, and ${MYSQL_ROOT_PASSWORD} with your desired values.
• Ensure you have .env.master and .env.slave files with the necessary environment variables

3. Run the Containers:

• Use docker-compose up -d to start the master and slave containers.
• The master container will be named mysql-master, and the slave container will be named mysql-slave.

4. Configure Replication:

• Enter the master container using docker-compose exec mysql-master bash.
• Log in to MySQL using the root user created earlier.
• Create a user for replication and grant replication privileges.
• Dump the master database and import it into the slave database.
• Start the replication process.