Scribd
Upload
50 views7 pages

MySQL 8.4 Replication Setup Guide

This document outlines the steps for setting up binary log file position-based replication between a source MySQL server and a replica server, both running MySQL version 8.4. It includes detailed instructions for installation, configuration, user creation, data snapshotting, and testing replication. Key configurations involve unique server IDs, binary logging, and ensuring the replica server is set to read-only to prevent write operations.

Uploaded by

Vignesh M
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
50 views7 pages

MySQL 8.4 Replication Setup Guide

This document outlines the steps for setting up binary log file position-based replication between a source MySQL server and a replica server, both running MySQL version 8.4. It includes detailed instructions for installation, configuration, user creation, data snapshotting, and testing replication. Key configurations involve unique server IDs, binary logging, and ensuring the replica server is set to read-only to prevent write operations.

Uploaded by

Vignesh M
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Setting Up Binary Log File Position Based Replication:

SOURCE SERVER IP:[Link]

REPLICA SERVER IP:[Link]

MYSQL VERSION :8.4

Installation document link:

[Link]
mysql-84-activity-7270113700815003649-
z0OG?utm_source=share&utm_medium=member_desktop

Step1) Install MySQL 8.4 On Source server:

wget [Link]

sudo yum localinstall [Link]

yum repolist enabled | grep mysql.*-community

sudo yum install mysql-community-server

systemctl start mysqld

systemctl enable mysqld

systemctl status mysqld

sudo grep 'temporary password' /var/log/[Link]

mysql_secure_installation

Step2) Install MySQL 8.4 On Replica server:

wget [Link]

sudo yum localinstall [Link]

yum repolist enabled | grep mysql.*-community

sudo yum install mysql-community-server

systemctl start mysqld

systemctl enable mysqld

systemctl status mysqld

sudo grep 'temporary password' /var/log/[Link]

mysql_secure_installation

Step3) Setting the Replication Source server Configuration:

Each server within a replication topology must be configured with a unique server ID, which you
can specify using the server_id system variable. This server ID is used to identify individual
servers within the replication topology, and must be a positive integer between 1 and (232)−1.
The default server_id value is 1
Binary logging is required on the source because the binary log is the basis for replicating
changes from the source to its replicas. Binary logging is enabled by default (the log_bin system
variable is set to ON). The --log-bin option tells the server what base name to use for binary log
files. It is recommended that you specify this option to give the binary log files a non-default
base name, so that if the host name changes, you can easily continue to use the same binary
log file names

For the greatest possible durability and consistency in a replication setup using InnoDB with
transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the
source's [Link] file.

#Edit mysql configuration file(/etc/[Link]):

server-id=1

log-bin=/var/lib/mysql/[Link]

innodb_flush_log_at_trx_commit=1

sync_binlog=1

save&exit

Step4) Restart the MySQL service on the source server:

systemctl restart mysqld

systemctl status mysqld

Step5) Setting the Replica server Configuration:

Each replica must have a unique server ID, as specified by the server_id system variable. If you
are setting up multiple replicas, each one must have a unique server_id value that differs from
that of the source and from any of the other replicas. If the replica's server ID is not already set,
or the current value conflicts with the value that you have chosen for the source or another
replica, you must change it.
The relay log contains a record of events that affect the data or structure of a database.

#Edit mysql configuration file(/etc/[Link]):

server-id=2

relay-log = /var/lib/mysql/[Link]

log_bin = /var/lib/mysql/[Link]

save&exit

Step6) Restart the MySQL service on the replica server:

systemctl restart mysqld

systemctl status mysqld

Step7) Creating a User for Replication on the source server:

CREATE USER 'rep_user'@'[Link]' IDENTIFIED BY 'Rep_user@2025';

GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'[Link]';

Step8) Creating a admin User for Backup on the source server:

CREATE USER 'admin_user'@'[Link]' IDENTIFIED BY 'Admin_user@2025';

GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'[Link]';


Step9) Create sample database on the source and load some data on the source server:

-- Create a database

CREATE DATABASE SampleDB;

-- Use the newly created database

USE SampleDB;

-- Create tables

CREATE TABLE Employees (EmployeeID INT AUTO_INCREMENT PRIMARY KEY,FirstName


VARCHAR(50),LastName VARCHAR(50),Department VARCHAR(50),Salary DECIMAL(10, 2));

CREATE TABLE Departments (DepartmentID INT AUTO_INCREMENT PRIMARY


KEY,DepartmentName VARCHAR(50),ManagerID INT);

-- Insert sample data into Employees table

INSERT INTO Employees (FirstName, LastName, Department, Salary) VALUES('John', 'Doe', 'HR',
60000.00),('Jane', 'Smith', 'IT', 75000.00),('Michael', 'Brown', 'Finance', 50000.00),('Emily', 'Davis',
'HR', 58000.00),('Robert', 'Johnson', 'IT', 80000.00);

-- Insert sample data into Departments table

INSERT INTO Departments (DepartmentName, ManagerID) VALUES('HR', 1),('IT', 2),('Finance', 3);

-- Query the tables to verify data

SELECT * FROM Employees;

SELECT * FROM Departments;

Step10) Obtaining the Replication Source Binary Log Coordinates on the source server:

Flush all tables and block write statements by executing the FLUSH TABLES WITH READ
LOCK statement:
FLUSH TABLES WITH READ LOCK;

Use the SHOW BINARY LOG STATUS statement to determine the current binary log file
name and position:

SHOW BINARY LOG STATUS\G;

The File column shows the name of the log file and the Position column shows the position
within the file. In this example, the binary log file is mysql-bin.000004 and the position is 2867.
Record these values. You need them later when you are setting up the replica. They represent
the replication coordinates at which the replica should begin processing new updates from the
source.

Step11) Creating a Source Server Data Snapshot Using mysqldump on the Replica Server:

mysqldump -u admin_user -p -h [Link] --all-databases > source_data_snapshot.sql

Step12) Release the lock on source server:

UNLOCK TABLES;

Step13) Restoring the Source Server Data Snapshot Using mysql on the Replica Server:

mysql -u root -p < source_data_snapshot.sql

Step14) Setting the Source Configuration on the Replica:


Execute a CHANGE REPLICATION SOURCE TO statement on the replica to set the source
configuration.

CHANGE REPLICATION SOURCE TO


SOURCE_HOST='source_host_name',SOURCE_USER='replication_user_name',SOURCE_PASS
WORD='replication_password',SOURCE_LOG_FILE='recorded_log_file_name',SOURCE_LOG_P
OS=recorded_log_position,SOURCE_SSL = {0|1};

CHANGE REPLICATION SOURCE TO


SOURCE_HOST='[Link]',SOURCE_USER='rep_user',SOURCE_PASSWORD='Rep_user@2
025',SOURCE_LOG_FILE='mysql-bin.000004', SOURCE_LOG_POS=2867,SOURCE_SSL=1;

View the replica status by issuing SHOW REPLICA STATUS statement:

SHOW REPLICA STATUS\G;

Start the replication threads by issuing a START REPLICA statement:

START REPLICA;

Test the Replication:

ON THE SOURCE SERVER:

USE SampleDB;

CREATE TABLE Persons (PersonID int,LastName varchar(255),FirstName varchar(255),Address


varchar(255),City varchar(255));

INSERT INTO Persons (PersonID, LastName, FirstName, Address, City) VALUES(1, 'Smith', 'John',
'123 Elm Street', 'New York'),(2, 'Johnson', 'Mary', '456 Oak Avenue', 'Los Angeles'),(3, 'Brown',
'James', '789 Pine Road', 'Chicago'),(4, 'Taylor', 'Patricia', '101 Maple Lane', 'Houston'),(5,
'Anderson', 'Robert', '202 Birch Blvd', 'Phoenix');

SELECT * FROM Persons;

ON THE REPLICATION SERVER:

USE SampleDB;

SHOW TABLES;

SELECT * FROM Persons;

Enable below parameters to prevent the write operations on the replica server:

If the read_only system variable is enabled, the server permits no client updates except from
users who have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege). This
variable is disabled by default.

If super_read_only is enabled, the server prohibits client updates, even from users who have
the CONNECTION_ADMIN or SUPER privilege.

SET @@GLOBAL.read_only = ON;

SET @@GLOBAL.super_read_only=ON;

Common questions

Powered by AI

Creating a test database and loading sample data is critically important to MySQL replication configuration as it helps verify the correctness and operational integrity of the replication setup. By using test data, operations can simulate real-world loads without affecting production data. This step also helps identify and resolve potential configuration issues or errors in the replication process, ensuring reliability before applying settings to a production environment. It serves as a proactive measure to catch and mitigate failures, providing confidence that the replication will perform as expected when handling actual business data .

Setting up binary log file position based replication involves several key steps: (1) Install MySQL 8.4 on both Source and Replica servers; (2) Configure the Source server with a unique server ID and enable binary logging; (3) Restart the MySQL service on the Source server; (4) Configure the Replica server with a unique server ID and relay log settings; (5) Restart the MySQL service on the Replica server; (6) Create a replication user on the Source server and grant necessary privileges; (7) Create a test database and load data on the Source server; (8) Obtain replication coordinates by flushing tables with read lock and recording the binary log's name and position; (9) Create a data snapshot using mysqldump; (10) Restore the data snapshot on the Replica server; (11) Set source configuration on the Replica server using the obtained replication coordinates; (12) Start the replication process on the Replica server .

Each server within a MySQL replication topology must have a unique server ID to identify individual servers. This uniqueness prevents conflicts and ensures that replication can be properly tracked and maintained across different servers. A positive integer between 1 and (2^32)-1 is used for the server_id system variable. Having unique server IDs is crucial because this identifier distinguishes servers from each other in the event of multiple replicas being part of the replication topology .

To effectively manage MySQL replication, a user needs the REPLICATION SLAVE privilege. This privilege allows the user to read the binary log files from the source server necessary for replication. In the setup process, the user 'rep_user' is created at the source server with the statement: CREATE USER 'rep_user'@'172.31.46.145' IDENTIFIED BY 'Rep_user@2025';, followed by granting replication privileges: GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'172.31.46.145' .

Replication coordinates in MySQL are determined by executing the SHOW BINARY LOG STATUS command on the source server. This provides the current binary log file's name and position, which represent the precise point from which replication should begin processing updates. These coordinates ensure that the replica starts replicating from a consistent state, aligning the slave's data with the master's changes. The coordinates are later used in the CHANGE REPLICATION SOURCE TO command issued on the replica to configure replication .

Using 'mysqldump' for data synchronization in MySQL replication offers several pros and cons. Pros include providing a consistent and comprehensive backup strategy that captures both schema and data, is database-agnostic, and is straightforward to initiate. 'mysqldump' can be integrated into scripts, making it versatile and accessible. However, cons include performance drawbacks, as large datasets can require significant time and resources to dump and restore. It's also limited by the server's memory due to its on-platform operation. Thus, while effective for ensuring initial data consistency, it might not be suitable for very large or live systems without downtime .

Synchronizing data between the source and replica servers involves creating a data snapshot using 'mysqldump' and restoring it on the replica server. Initially, a FLUSH TABLES WITH READ LOCK statement is executed to acquire a consistent view and prevent table updates. The current binary log coordinates are recorded for replication alignment. A data snapshot is then generated with mysqldump, which captures both the structure and data from the source server. This dump is subsequently restored on the replica server using 'mysql < source_data_snapshot.sql', effectively synchronizing the data prior to initiating replication .

Failing to properly configure server IDs and binary logging in a MySQL replication setup can lead to major issues, including replication conflicts, data inconsistency, and replication failures. Without unique server IDs, servers within the replication topology could overwrite or misinterpret data changes, leading to erroneous replication. Analogously, improper binary log configuration can result in an inability to track data changes accurately, undermining replication integrity. Additionally, using default log-bin names without configuration could cause issues if server hostnames change, disrupting the data continuity in a replication topology .

Setting 'read_only' and 'super_read_only' variables ensures that no write operations can be performed on the replica server, preventing data inconsistency due to unintended changes from client operations. The 'read_only' variable restricts updates only from users with the CONNECTION_ADMIN privilege. 'super_read_only' enforces a stricter policy, disallowing updates even from users with privileges, thus ensuring that replicas remain exact copies of the source .

Binary logging is indispensable for MySQL replication as it allows recording of all changes to the database, providing the transaction log required for data replication across servers. It's recommended to configure the log-bin option with a specific base name for binary log files to ensure consistency even if the host name changes. Additionally, for optimal durability and consistency, especially when using InnoDB with transactions, it's advised to set innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the configuration .

You might also like