MySQL 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:

• master.info: used to save relevant information when slave connects to master, such as account, password, server address, etc
• relay-log.info: 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 master.info and relay-log.info 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.1 master node configuration

1. Open binary log in the master node

vim /etc/my.cnf [mysqld] log_bin=1

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

vim /etc/my.cnf [mysqld] log_bin=1 server-id=1 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

SHOW MASTER STATUS;

4. Create a user account with copy permission

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass'; #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 [mysqld] 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

[mysqld] 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

CHANGE MASTER TO MASTER_HOST='10.0.0.201', MASTER_USER='tom', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS= 156; START SLAVE [IO_THREAD|SQL_THREAD]; SHOW SLAVE STATUS;

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) mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.201', -> 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; Slave_IO_State: Master_Host: 10.0.0.201 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_Host: 10.0.0.201 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 10.0.0.201:22 10.0.0.1:3435 ESTABLISHED 4329/sshd: root [pr tcp 0 52 10.0.0.201:22 10.0.0.1:7359 ESTABLISHED 4464/sshd: root [pr tcp6 0 0 10.0.0.201:3306 10.0.0.211:49566 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 CHANGE MASTER TO MASTER_HOST='10.0.0.201', MASTER_USER='tom', 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

[mysqld] server-id=2 log-bin read_only=ON relay_log=relay-log relay_log_index=relay-log.index

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 [mysqld] 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 etc.

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] START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos START SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos thread_types: [thread_type [, thread_type] ... ] thread_type: IO_THREAD | SQL_THREAD

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

[mysqld] server-id=18 log_bin 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 read-onl

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

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

auto_increment_offset=2 auto_increment_increment=2

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 /usr/lib64/mysql/plugin/semisync_master.so /usr/lib64/mysql/plugin/semisync_slave.so

1. Install the plug-in

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

2. Modify the master configuration file

#master server configuration [root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf [mysqld] server-id=8 log-bin rpl_semi_sync_master_enabled=ON #To modify this line, you need to install semisync first_ Restart the master.so 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 'semisync_slave.so'; #slave server configuration [root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf [mysqld] server-id=18 rpl_semi_sync_slave_enabled=ON #To modify this line, you need to install semisync first_ Restart the slave.so plug-in, otherwise Unable to start [root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf [mysqld] server-id=28 rpl_semi_sync_slave_enabled=ON #To modify this line, you need to install semisync first_ Restart the slave.so 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 [mysqld] server-id=8 log-bin plugin-load-add = semisync_master # Add plug-in rpl_semi_sync_master_enabled=ON 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 [mysqld] server-id=18 plugin_load_add = semisync_slave rpl_semi_sync_slave_enabled=ON

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-do-db=db2 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

Add new comment

0 comments