MySQL master-slave replication

1, Introduction to master-slave replication

1.1 what is master-slave replication

Copy the binlog log of the master server to the slave server for execution to achieve the consistent state of the master-slave data, which is called master-slave replication. In a word, what the master database does is what the slave database does.

1.2 why should we be the master and slave

  • Pave the way for server load balancing / read-write separation and improve access speed

    #1. What is read write separation
     With the master-slave data consistency as the main premise, we can separate read and write operations Master Responsible for the load of write operations,
    In other words, all write operations are in progress Master The read operation is allocated to Slave On.
    #2. The role of read-write separation
     First answer: read write separation can greatly improve the efficiency of reading.
    In general Internet applications, after some data investigation, draw a conclusion and read/The ratio of writing is about 10:1,
    In other words, there are very few write operations, and a large number of data operations focus on read operations (such as some applications, such as the website of fund net worth prediction).
    The data is updated by the administrator, that is, there are few updated users, and the number of query users will be very large.)
    At this time, we can create one master and many slaves. Because there are few write operations, one master library is responsible, and a large number of read operations are allocated to
     Multiple slave libraries, so that the pressure of the largest proportion of read operations is balanced, so the read efficiency will be greatly improved,
    In addition, there is another reason: write operations involve locks, whether row locks, table locks or block locks,
    These are things that reduce the execution efficiency of the system. Our separation is to concentrate write operations on one node,
    The read operation has its other functions N On the other hand, it effectively improves the reading efficiency and ensures the performance and high availability of the system.
    #3. Specific practice
     Scheme I:
    It is to write from the main library and read from the library
    Scheme II:
    The master library is responsible for writing and some reading. The slave library is only responsible for reading, and it is the main force of reading operation
     That is, when the master server is busy, some query requests will be automatically sent to the slave server to reduce the workload of the master server.
  • Remote backup of data is realized through replication to ensure data security

  • Improve the availability of database system

1.3 master slave replication architecture

One master and one slave replication architecture

One master multi slave replication architecture

Cascade replication, one master and one slave, one master and many slave, dual master mode.

1.4 master slave replication principle

Master slave replication related threads

  • Master node:
    • dump Thread: start a dump Thread for each Slave I/O Thread to send binary log events
  • Slave node:
    • I/O Thread: request binary log events from the Master and save them in the relay log
    • SQL Thread: read the log events from the relay log and complete the replay locally

Files related to copy function:

  • used to save relevant information when slave connects to master, such as account, password, server address, etc
  • saves the correspondence between the copied current binary log and the local relay log on the current slave node
  • mysql-relay-bin.00000#: relay log, which saves binary logs copied from the primary node. It is essentially binary logs

MySQL 8.0 cancels the and files

The final log is as follows:

[root@rocky8 ~]# file /var/lib/mysql/rocky8-relay-bin.000001
/var/lib/mysql/rocky8-relay-bin.000001: MySQL replication log, server id 1 MySQL V5+, server version 8.0.26

2, Master slave replication implementation

2.1 master node configuration

1. Open binary log in the master node

vim /etc/my.cnf

2. Set a globally unique ID number for the current node

vim /etc/my.cnf
log-basename=master  #Optional, set the log name in datadir to ensure that it does not depend on the host name
server-id Value range of
1 to 4294967295 (>= MariaDB 10.2.2),The default value is 1
0 to 4294967295 (<= MariaDB 10.2.1),The default value is 0. If the slave node is 0, all master Will reject this
slave Connection of

3. View the file and location of the binary log of the master node and start copying


4. Create a user account with copy permission


#MySQL 8.0 is implemented in two steps
mysql> create user tom@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to tom@'10.0.0.%';

Specific steps

1.Modify profile
vim /etc/my.cnf

log_bin=/data/mysql/logs/mysql-bin # Binary log storage directory and prefix
server-id=1 # Specify a unique id number

2.Create directory and authorize     
[root@rocky8 ~]# mkdir -p /data/mysql/logs
[root@rocky8 ~]# chown -R mysql.mysql /data/mysql 

3.restart mysql

4.Log in to the database and view the current binary log location
mysql> show master status;
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| mysql-bin.000003 |      156 |              |                  |                   |
5.Create account
mysql> create user tom@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.*  to tom@'10.0.0.%';

2.2 slave node configuration

1. Start relay log

server_id=# #Set a global unique ID number for the current node
log-bin # It is recommended that the slave node also enable binary logs
read_only=ON #The setting database is read-only, which is invalid for super user
relay_log=relay-log #The file path of the relay log. The default value is hostname relay bin
relay_log_index=relay-log.index  #The default value is hostname-relay-bin.index

2. Connect to the main server with a user account with replication permission and start the replication thread



Specific operation
1. Install a database of the same version as the primary node
2. Modify slave node configuration
3. Start the slave node
4. Execute the change master to command

mysql> show slave status;
Empty set, 1 warning (0.01 sec)

    -> MASTER_USER='tom', 
    -> MASTER_PASSWORD='123456', 
    -> MASTER_LOG_FILE='mysql-bin.000003', 
    -> MASTER_LOG_POS= 156;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

5. Check the status again

mysql> show slave status;
                  Master_User: tom
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 156
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No

As you can see, Slave_IO_Running: No , Slave_SQL_Running: No is turned on
Seconds_Behind_Master: NULL
5. Start thread

mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_User: tom
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 156
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes # Already turned on
            Slave_SQL_Running: Yes # Already turned on
			. . . 
			  Seconds_Behind_Master: 0 # The data has been displayed

6. View status

[root@rocky8 my.cnf.d]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0            ESTABLISHED 4329/sshd: root [pr 
tcp        0     52            ESTABLISHED 4464/sshd: root [pr 
tcp6       0      0        ESTABLISHED 5261/mysqld  

You can see that the slave node maintains a continuous connection with the master node.

2.3 when the business of the master node runs for a period of time, the implementation idea of loading the slave node in the later stage

Master node: first make a full backup of the data of the master node, and then start a new binary log
Slave node: restore the data of full backup first, and then synchronize the new binary log after full backup on the master node

1. Full backup of primary node

[root@rocky8 ~]# mysqldump -uroot -p123 -A -F --single-transaction --master-data=1 > /opt/full_backup.sql

2. Copy the full backup sql file to the slave node, and then add the following in the change master to field:

vim /root/full_backup.sql

MASTER_PASSWORD='123456', # Manually added

MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=156; #Automatically generated

3. Configure the configuration file of the slave node




4. Restart the slave node database and execute sql statements

mysql> source /root/full_backup.sql

6. Start IO and sql threads

mysql> start slave;

2.3 error in synchronization solution

You can ignore the replication events of several master servers in the slave server. This is a global variable, or specify the ID of the skip event
Note: in MariaDB10.3 on CentOS version 8.1 and above, there will be no conflict when the master and slave nodes create a library and table with the same name at the same time, and there will be a conflict when creating a primary key record
Mode 1

#System variable that specifies the number of skip replication events on the slave node
mysql> SET GLOBAL sql_slave_skip_counter = N;
mysql> stop slave; # Stop first
mysql> start slave; # At startup

Mode 2

#Server option, read-only system variable, specifies the ID of the skip event, which needs to be specified in the configuration file
slave_skip_errors=1007|ALL (all Indicates that all errors are ignored ID)

2.4 if the slave node writes the wrong synchronization information with the master server, it needs to be cleared first and then written again

Execute on slave node

mysql> reset slave all #Clear all master server synchronization information set on the slave server, such as HOST, PORT, USER and PASSWORD 

2.5 during master-slave replication, you can specify the START SLAVE statement to specify the execution to a specific point

START SLAVE [thread_types]
    [thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD

3, Cascaded replication implementation

The following configuration needs to be enabled on the intermediate slave server to realize that the intermediate slave node can update the binary log of the master in the local database and update the binary log of the local machine at the same time, so as to realize cascade replication

log_slave_updates      #A required option for cascading replication intermediate nodes, MySQL 8.0. This is the default value and can not be added manually. Others
 The version is not enabled by default

Steps: 1. In the existing master-slave mode, you only need to turn on the log in the intermediate node_ slave_ updates
2. Start transaction full backup from the data of the node to the third node
3. The configuration of the third node can be synchronized with that of the second node

4, Primary replication

Master replication: both nodes can update data and are master-slave to each other
Easy problems: inconsistent data; Therefore, use with caution
Key considerations: automatic growth id
Configure a node to use an odd id

auto_increment_offset=1   #Start point
auto_increment_increment=2 #Growth range

The other node uses an even id


Configuration steps for primary replication:
(1) Each node uses a unique server_id
(2) Both binary log and relay log are started
(3) Create a user account with copy permission
(4) The value range of the auto growth id field is defined as parity
(5) Both specify each other as the master node and start the replication thread

5, Semi synchronous

By default, MySQL's replication function is asynchronous. Asynchronous replication can provide the best performance. The master database ends by sending binlog logs to the slave database, and does not verify whether the slave database has received them. This means that when the master server or slave server fails, the slave server may not receive the binlog log sent by the master server, which will cause the data inconsistency between the master server and the slave server, and even the loss of data during recovery
The message notification mechanism of full synchronization may wait for a long time, so semi synchronization is adopted. As long as one slave node completes synchronization, a successful message will be returned immediately.
CentOS8 implements semi synchronous replication in MySQL 8.0
It is supported by plug-ins

#View plug-in files
[root@centos8 ~]#rpm -ql mysql-server |grep semisync

1. Install the plug-in

#Master server configuration:
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME ''; #Permanently installed plug

2. Modify the master configuration file

#master server configuration
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
rpl_semi_sync_master_enabled=ON #To modify this line, you need to install semisync first_ Restart the plug-in. No
 Cannot start
rpl_semi_sync_master_timeout=3000   #If the settings cannot be synchronized within 3s, the success information will also be returned to the client

3. Modify master configuration file of slave node

Install the plug-in first
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME '';

#slave server configuration
[root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
rpl_semi_sync_slave_enabled=ON #To modify this line, you need to install semisync first_ Restart the plug-in, otherwise
 Unable to start

[root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
rpl_semi_sync_slave_enabled=ON #To modify this line, you need to install semisync first_ Restart the plug-in, otherwise
 Unable to start

#Note: if master-slave replication has been implemented, stop slave is required; start slave;
mysql> stop slave;
mysql> start slave;

CentOS 8 implements semi synchronous replication on Mariadb-10.3.11
There is no need to install plug-ins, just specify the following

#In the master implementation, enable the semi synchronization function
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
plugin-load-add = semisync_master # Add plug-in
rpl_semi_sync_master_timeout=3000   #If the settings cannot be synchronized within 3s, the success information will also be returned to the client

There is no need to install plug-ins on all slave nodes. The configuration is as follows:

[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
plugin_load_add = semisync_slave

6, Copy filter

Let the slave node copy only the specified database, or the specified table of the specified database
Replication filters can be implemented in two ways:
(1) Server option: the primary server only logs events related to a specific database to the binary log
Disadvantages: binary based Restore cannot be implemented; Not recommended
Advantages: you only need to configure it once on the master node

vim /etc/my.cnf
binlog-do-db=db1 #The database white list does not support specifying multiple values at the same time. If you want to implement multiple databases, you need to implement multiple rows
binlog-ignore-db= #Database blacklist list

(2) SQL from server_ When thread events in the relay log, only events related to a specific database (specific table) are read and applied locally
Disadvantages: it will waste network and disk IO, which should be configured on all slave nodes
Advantages: binary backup and restore are not affected
Copy filter related variables from the server

Tags: Database MySQL SQL

Posted on Fri, 19 Nov 2021 18:28:24 -0500 by ravira