Anvironment Preparation

# Install wget if missing
sudo yum install -y wget

# Download MySQL community repository package
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

# Install the repository RPM package
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

# Install MySQL server, confirm all prompts with 'y'
sudo yum install -y mysql-server

If initial MySQL login fails due to permissions issues on /var/lib/mysql:

# Fix directory ownership
sudo chown -R mysql:mysql /var/lib/mysql/

# Restart MySQL service
sudo systemctl restart mysqld

# Log into MySQL
mysql -u root

Set root password and enable remote access:

USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'SecurePass123!';
FLUSH PRIVILEGES;
EXIT;

# Restart MySQL and grant remote root access
sudo systemctl restart mysqld
mysql -u root -pSecurePass123! -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'SecurePass123!' WITH GRANT OPTION; FLUSH PRIVILEGES;"
sudo systemctl restart mysqld

MySQL Master-Slave Replication Principles

MySQL includes native replication functionality without requiring third-party tools, using binary log (binlog) files instead of direct file copying. When SQL statements execute on the master node, they are transmitted and re-executed on slave nodes to maintain synchronized data.

Slave nodes run two background threads:

1. An I/O thread that requests binlog files from the master and writes the received data to local relay log files
2. A SQL thread that reads the relay log, parses the contained database operations, and executes them locally to match the masters' data state

The master node also runs a log dump thread that sends binlog data to the requesting slave I/O threads.

Replication Setup

Master Node Configuration

Edit the MySQL configuration file /etc/my.cnf:

[mysqld]
server-id = 101
log-bin = mysql-master-bin

Restart the MySQL service to apply changes:

sudo systemctl restart mysqld

Verify master configuration:

# Check server ID configuration
mysql -u root -pSecurePass123! -e "SHOW VARIABLES LIKE '%server_id%';"

# View master binlog status (record file and position values)
mysql -u root -pSecurePass123! -e "SHOW MASTER STATUS;"

Slave Node Configuration

Edit the MySQL configuration file /etc/my.cnf:

[mysqld]
server-id = 102
log-bin = mysql-slave-bin
binlog_do_db = app_db, inventory_db

Restart the MySQL service to apply changes:

sudo systemctl restart mysqld

Configure Slave Replication

If the slave was cloned from the master, you may encounter a fatal error where the slave I/O thread stops with a message about duplicate MySQL server UUIDs. Resolve this by deleting the auto.cnf file in the MySQL data directory:

# Locate MySQL data directory path
cat /etc/my.cnf | grep datadir

# Navigate to data directory (default path is /var/lib/mysql)
cd /var/lib/mysql
sudo rm -f auto.cnf

# Restart MySQL service
sudo systemctl restart mysqld

Configure the slave to connect to the master node:

CHANGE MASTER TO
  MASTER_HOST = '192.168.0.101',
  MASTER_USER = 'root',
  MASTER_PASSWORD = 'SecurePass123!',
  MASTER_LOG_FILE = 'mysql-master-bin.000001',
  MASTER_LOG_POS = 154;

Start replication and verify status:

START SLAVE;
SHOW SLAVE STATUS\G

Verify Replication Effect

Create a test database and table on the master node:

CREATE DATABASE app_db;
USE app_db;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50));
INSERT INTO users (username) VALUES ('demo_user');

Check the replicated data on the slave node:

SHOW DATABASES;
USE app_db;
SELECT * FROM users;