MARIADB Master-slave Replication Architecture Learning notes

copy function:

Data distribution
Load balancing: Read operations for read-intensive applications
Backup
High Availability and failover
MySQL Upgrade test

There are two threads on the slave server:

I/O thread: Request binary log information from master and save to the trunk log

SQL Thread: Reads log information from relay log and completes replay locally

Start a dump thread on the primary server for each I/O thread from the server, sending it a binary log events

Process diagram:

　　

Add: Operations on database modifications must first be applied to disk files to be written to the disk binary log, so data from the server will inevitably lag behind the primary server

with the development of the site, when the master node has too much from the server, will give the main node too much pressure, can be dedicated to use a slave server from the primary node to replicate the binary day The master node from the node to the other slave node

Process diagram:

Supplement: The first slave server does not need to store data, it is only responsible for passing the binary log, so this database engine from the server uses blackhole when replaying the relay log, it will put all the data into/dev/null (this is equivalent to a cosmic black hole)

Binary log format: We recommend using the mixed mode if you can use row mode

Master-Slave configuration process:

Main: 1. Enable binary logging, 2. Set a unique Server-id in the current cluster (the above two steps are modified in the configuration file):

　　　　

3. Create an account with replication permissions (REPLICATION SLAVE, REPLICATION CLIENT):

Grant replication Slave,replication Client on * * to ' repl ' @ '% ' identified by ' replpass ';

Flush privileges;

From: 1. Enable the relay log, 2. Set a unique Server-id in the current cluster (the above two steps are modified in the configuration file):

　　　　

3. Connect to the primary server using the user account with copy rights and start the replication thread:

Change Master to master_host= ' 192.168.238.224 ', master_user= ' repl ', master_password= ' Replpass ', master_log_file= ' On.000003 ', master_log_pos=4;

Use the help change Master to view command assistance

Start slave; Turn on I/O and SQL threads

show slave status; View the synchronization status, where the seconds_behind_master can see if the master node is behind and how much is behind

View whether the From node is read only show global variables like ' read_only ';

Log_slave_updates = 1 (the event that allows the repository to replay it is also logged to its own binary log, so that it is both a slave node and a master node)

Sync_binlog =1 MySQL each time in the commit memory will synchronize the in-memory binary logs to disk, ensuring that the server crashes without losing events (strongly built on the master node)

Innodb_support_xa default is True, Binlog the order in which transactions are logged may not match the actual, resulting in slave inconsistencies if closed　　　　

If you have a new join from the server, how to fit into the architecture:

1. Make a full backup of the master node and record the binary log file and location

2. Restore this full backup from the node and start with the recorded binary log file and location when initiating replication

Question: How can I provide high availability of the master node?

MARIADB Master-slave Replication Architecture Learning notes