Replication Concepts

• Facilitates data transfer from primary (master) server to replica (slave) server.
• Primary server stores the source data, while the replica server acts as the destination.
• Primary and replica servers are hosted on separate machines for redundancy.
• Replication occurs asynchronously by default, allowing flexibility in timing.
• Replica server doesn’t require a continuous connection to the primary server.
• Offers selection options for replication: all databases, specific selections, or individual tables.
• Enhances system availability and ensures fault tolerance through data redundancy.

Replication Methods

there are two type of replication method
1.Traditional – Binary log file position based replication
2. GTID : global transaction identifier

Binary log file position based replication

• The primary server’s binary log contains events for replication, which need to be replicated to the replica server.
• Replication involves syncing the positions of events in the binary log between the primary and replica servers.
• To replicate events, the binary log files and their positions must be synchronized between the primary and replica servers.

GTID global transaction identifier

• GTID (Global Transaction Identifier) replication eliminates the need to work with binary logs and positions within files.
• It ensures consistency between the primary and replica servers by guaranteeing that transactions committed on the primary are also applied to the replica.
• GTID replication is the recommended method for replication due to its simplicity and reliability.

Replication Formats

Each binary log format—Statement-Based Replication (SBR), Row-Based Replication (RBR), and Mixed-Based Replication (MBR)—offers distinct advantages and disadvantages:

• Statement-Based Replication (SBR):
  • Advantages: Efficient for simple queries, reduces data transferred over the network, and minimizes storage space usage.
  • Disadvantages: May lead to inconsistencies due to non-deterministic queries or schema changes between primary and replica.
• Row-Based Replication (RBR):
  • Advantages: Guarantees exact data replication, immune to inconsistencies from non-deterministic queries or schema changes, and ideal for complex queries.
  • Disadvantages: Requires more network bandwidth and storage space due to transmitting actual row changes.
• Mixed-Based Replication (MBR):
  • Advantages: Offers flexibility by dynamically selecting between SBR and RBR based on the type of query, combining efficiency and accuracy.
  • Disadvantages: Complexity in managing different replication formats, potential inconsistency issues when switching between formats.

Statement-Based Replication (SBR)

binlog_format = STATEMENT

• Replication Process:
  • Entire SQL statements are logged in the binary file and replicated to the replica server.
  • The replica executes these SQL statements to mirror the changes made on the primary server.
• Data Loss Concerns:
  • Due to the replication of SQL statements, there’s a risk of data loss or inconsistencies, especially with non-deterministic behavior or functions.
• Storage Efficiency:
  • STATEMENT format consumes less storage space for log files compared to other formats, as it logs SQL statements rather than row changes.
• Limitations:
  • Not all statements are replicated accurately, particularly those involving non-deterministic behavior, posing challenges for maintaining consistency between the primary and replica databases.
  • if these SQL statement that use user defined function and stored procedure that calculate some filed , calculate some values , and beforehand those values cannot be determined
    which make this SQL Statement labelled as non-deterministic
    by default non-deterministic are not safe to replicate
• In general, if the goal is to ensure that the data on both the primary and replica servers are identical at all times then this format may not guarantee this level of consistency.

Row-Based Replication (RBR):

binlog_format = ROW

• Replication Mechanism:
  • Only the modified rows are replicated to the replica server.
  • Source server records events in the binary log detailing the changes made to individual table rows.
• Complete Data Replication:
  • All changes, regardless of complexity, are replicated to the replica server.
  • This approach consumes more storage space due to logging every row change.
• Limitation:
  • You can’t see which SQL statements were changed on the primary server, which SQL statements were received on the replica, or which statements were executed on the replica.

Mixed-Based Replication (MBR)

binlog_format = MIXED

• Hybrid Approach:
  • MIXED format combines the benefits of both STATEMENT and ROW formats.
  • It offers a balanced solution for data integrity and performance.
• Replication Mechanism:
  • Events in the binary log detail both SQL statements and individual table row changes.
  • This allows for flexibility in replication methods, optimizing performance and ensuring data accuracy.
• Recommended Format:
  • MIXED format is typically recommended in various scenarios due to its ability to provide both efficient replication and reliable data integrity.

General Replication Setup Requirements

• Binary logging must be enabled on the primary server. You have to set up the following parameter:
  • log-bin
  • log-bin-index
• You must have a unique server ID for both primary and replica servers by setting up the following parameter:
  • server-id
• A dedicated user for replication must be created.
  • you can called the user replicator , replicaadmin
  • should have proper permissions
  • GRANT REPLICATION SLAVE ON *.* to [username]
• The binary log file format must be defined.
  • binlog_format = STATEMENT | ROW | MIXED

setup primary server

• suggested to create separate option file called replication.cnf
• Enable binary Logging
• Setup Unique server ID
• Create Dedicated replication User
  • Create user replicator IDENTIFIED BY 'password '
  • Grant Replication SLAVE ON *.* to replicator

-setup Binary log file format
– binlog_format = MIXED

Configure Primary Server

we have two MySQL server with following details

1- create sperate configuration file for replication
we will create replication configuration file for better management
sudo mkdir /etc/percona
sudo touch /etc/percona/replication.cnf

2 – Enable binary Logging
before proceeding we will create separate directory go bin-log

sudo mkdir bin-log
sudo chown -R mysql:mysql bin-log/

in the replicator.cnf file we will enable binlog and bin-log & bin-log-index
use vi or nano to edit the replication.cnf file
at top of file make sure to mention [mysqld]

add the below option file and mention the below option

log-bin = /bin-log
log-bin-index = /bin-log
binlog_expire_logs_seconds  = 432000

move the binlog file on data dir to new directory

cp binlog.000001 /bin-log/
cp binlog.index /bin-log/

3- Setup Unique server ID

add the option server-id = 1 in cnf file

4- setup Binary log file format

add format for binlog-format to be MIXED

binlog-format  =  MIXED

5- add include option to point to replactor.cnf file

in my.cnf file , we will add !includedir /etc/percona/ to tell mysql also read option in this directory

sudo vi /etc/my.cnf

!includedir /etc/percona/

6- Create Dedicated replication User

now we will work on creating a user

Create user replicator IDENTIFIED BY 'P@ssword1 ';
Grant Replication SLAVE ON *.* to replicator;
show grants for replicator;

flush privileges;

7- restart MySQL

sudo systemctl restart mysqld
sudo systemct	status mysqld

8 – verify configured options are implemented

first verify bin-log is enabled with MIXED mode
show global variables like '%bin%';

verify that server id is set to 1
show global variables like 'server%';

9- verify if the primary server is actually setup as master

now we will verify if the primary server is configured as master by login to MySQL and running the below command .

show master status ;

the command will show what is the current file master server is processing and the position

any changes in the database it will change the position

Configure Secondary Server

we have installed perocna MySQL 8 we need to install xtrabackup

you can get the version from perocna site
https://www.percona.com/downloads

and use wget to download package directory on the OS

wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/redhat/8/x86_64/Percona-XtraBackup-8.0.35-30-r6beb4b49-el8-x86_64-bundle.tar?_gl=1*1tunal5*_gcl_au*MTA0Mzk1MDYyOS4xNzE1NjkwMzk5


tar -xf Percona-XtraBackup-8.0.35-30-r6beb4b49-el8-x86_64-bundle.tar?_gl=1*1tunal5*_gcl_au*MTA0Mzk1MDYyOS4xNzE1NjkwMzk5

sudo yum localinstall percona-xtrabackup-80-8.0.35-30.1.el8.x86_64.rpm -y

verify server-id

login to MySQL and use below command to check which id is assinged

show global variables like 'server%';

server id must changed so go to my.cnf file and add option to change server id to 2
sudo vi /etc/my.cnf
server-id = 2
sudo systemctl restart mysqld

show global variables like 'server%';

Hot Backup on Primary for Replication

now we need to take backup from primary and restore this on replica

now we will create directory for backup and then take hot backup

mkdir hotbackup

xtrabackup  -uroot -p --backup --target-dir=/hotbackup

now we have to check binlog file and binlog position , this information is taken by backup and it stored in file called xtrabackup_binlog_info

cat xtrabackup_binlog_info

master binlog file is 000005 and position is 157

Copy Hot Backup from Primary to Replica

now we have the backup already taken on primary server now we need to copy to secondary and restore it there .
we will use the command scp that will copy file to secondary using port 22

scp -r  /hotbackup  [email protected]:hotbackup/

Restore Hot Backup on Replica

now we have hot backup copied from master to replica now it is time to restore this
to restore we need to first stop MySQL services

sudo systemctl stop mysqld
sudo systemctl status mysqld

remove every thing in DATA DIR

rm -rf /mysqldata/mysql/*

copy content from hotbackup we moved from master server to replica DATA DIR

cp -r  cp -r backup backup-my.cnf binlog.000005 binlog.index ib_buffer_pool ibdata1 mysql mysql.ibd performance_schema sys undo_001 undo_002 /mysqldata/mysql/

redo the ownership and change it to MySQL user

chown -R mysql:mysql /mysqldata/mysql

now start mysqld and check the status

sudo systemctl start  mysqld
sudo systemctl status mysqld

Replica Server Configuration

below are steps to configure replica
1 – enable relay log
2 – set unique ID
3 – Skip replica to auto start
4 – replica should be read only

1 – enable relay log

first setup a directory for relay log inside DATA DIR and assignee ownership to mysql user

sudo mkdir /mysqldata/mysql/relylog

sudo chown -R mysql:mysql /mysqldata/mysql/