MySQL master-slave replication and separation analysis

Working process of master-slave replication:
1. The change of the data of the Master node is recorded as a binary log,
2. When the data on the Master changes, the changes are written to the binary log.
3. Start a slave service thread and pass it to the slave server
4. The Slave node will detect whether the binary log of the Master has changed within a certain time interval. If it has changed, it will start an I/O thread to request the binary event of the Master.
5. At the same time, the Master node starts a dump thread for each I/O thread to send binary events to it and save them to the Slave node's local Relay log. The Slave node will start the sql thread to read the binary log from the Relay log and replay it locally, that is, parse it into sql statements and execute them one by one, so that its data is consistent with that of the Master node, Finally, the I/O thread and sql thread will go to sleep and wait for the next wake-up.

Note:
● the relay log is usually located in the OS cache, so the overhead of the relay log is very small.
● there is a very important limitation in the replication process, that is, the replication is serialized on the Slave, that is, the parallel update operation on the Master cannot be operated in parallel on the Slave

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 current binary log and the local relay log that have been copied on the current slave node

mariadb-relay-bin.00000#: a relay log, which stores binary logs copied from the primary node. It is essentially a binary log

MySQL master-slave replication delay

The master server is highly concurrent, forming a large number of transactions
Network delay
The master-slave hardware device causes cpu master frequency, memory io and hard disk io
It is not synchronous replication, but asynchronous replication. Optimize Mysql parameters from the library. For example, increase innodb_buffer_pool_size to allow more operations to be completed in Mysql memory and reduce disk operations. Use high-performance hosts from the library. Including strong cpu and increased memory. Avoid using virtual virtual hosts and use physical hosts, which improves the i/o complexity. The slave library uses SSD disk network optimization to avoid synchronization across machine rooms

MySQL extension

Read write separation
Replication: each node has the same data set, which expands outward and is one-way replication based on binary logs

What is read write separation

The basic principle of read-write separation is to let the main database handle transactional add, change and DELETE operations (INSERT, UPDATE and DELETE), while the SELECT query operation is processed from the database. Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.

MySQL read-write separation principle

Read write separation is to write only on the master server and read only on the slave server. The basic principle is to let the master database handle transactional operations and the select query from the database. Database replication is used to synchronize changes caused by transactional operations on the master database to the slave database in the cluster. At present, the common MySQL read-write separation can be divided into the following two types:
1) Internal implementation based on program code
In the code, routing is classified according to select and insert. This kind of method is also the most widely used in production environment.
The advantage is better performance, because it is implemented in program code, and there is no need to add additional equipment for hardware expenditure; The disadvantage is that it needs developers to implement it, and the operation and maintenance personnel have no way to start. However, not all applications are suitable for realizing read-write separation in program code. For example, some large and complex Java applications, if reading-write separation is realized in program code, the code will be greatly changed.

2) Implementation based on intermediate agent layer
The proxy is generally located between the client and the server. After receiving the client request, the proxy server forwards it to the back-end database through judgment. There are the following representative programs.

(1)MySQL-Proxy. MySQL proxy is an open source MySQL project, and SQL judgment is performed through its own lua script.
(2)Atlas. It is a data middle tier project based on MySQL protocol developed and maintained by the infrastructure team of Qihoo 360's Web Platform Department. Based on MySQL proxy version 0.8.2, it optimizes it and adds some new features. The MySQL service run by atlas in 360 carries billions of read and write requests every day. Support things and stored procedures.
(3)Amoeba. Developed by Chen Siru, the author once worked for Alibaba. The program is developed by the Java language and Alibaba uses it in the production environment. However, it does not support transactions and stored procedures.

Because a large number of Lua scripts need to be written to use MySQL Proxy, these Luas are not ready-made, but need to be written by themselves. This is very difficult for people who are not familiar with MySQL Proxy built-in variables and MySQL Protocol.
Amoeba is a very easy to use and portable software. Therefore, it is widely used in the agent layer of database in production environment.

Why read write separation

Because the "write" (writing 10000 pieces of data may take 3 minutes) operation of the database is time-consuming.
However, the "read" of the database (it may take only 5 seconds to read 10000 pieces of data).
Therefore, the separation of reading and writing solves the problem that the writing of the database affects the efficiency of query.

When do I need to separate reading and writing

The database does not have to be read-write separated. If the program uses more databases, less updates and more queries, it will be considered. The use of database master-slave synchronization and read-write separation can share the pressure of the database and improve the performance.

Master-slave replication and read-write separation

In the actual production environment, the reading and writing of the database are in the same database server, which can not meet the actual needs. Whether in security, high availability or high concurrency, it can not meet the actual needs. Therefore, master-slave replication is used to synchronize data, and then read-write separation is used to improve the concurrent load capacity of the database. It is somewhat similar to rsync, but the difference is that rsync backs up disk files, while mysql master-slave replication backs up data and statements in the database.

Replication types supported by mysq
(1) STATEMENT: STATEMENT based replication. Execute sql statements on the server and the same statements on the slave server. mysql adopts STATEMENT based replication by default, with high execution efficiency.
(2) ROW: ROW based replication. Copy the changed content instead of executing the command from the server.
(3) MIXED: MIXED type replication. By default, statement based replication is adopted. Once it is found that statement based replication cannot be accurately copied, row based replication will be adopted.

Actual combat project

Various middleware addresses

mysql-proxy: Oracle，MySQL :: Download MySQL Proxy (Archived Versions)

Atlas: Qihoo，Atlas/README_ZH.md at master · Qihoo360/Atlas · GitHub

dbproxy: meituan, https://github.com/Meituan-Dianping/DBProxy

Cetus: Netease Lede, https://github.com/Lede-Inc/cetus

Amoeba: amoeba download | SourceForge.net

Cobar: Alibaba, an upgraded version of Amoeba, GitHub - alibaba/cobar: a proxy for sharding databases and tables

Mycat: Cobar based http://www.mycat.io/ (original website)

Environmental preparation

master server: 192.168.37.100 mysql 5.7

slave server: 192.168.37.101 mysql 5.7

slave server: 192.168.37.102 mysql 5.7

Amoeba server: 192.168.37.105 jdk 1.6 ,amoeba

Client server: 192.168.37.106 mysql

Set up master-slave replication

Build time synchronization

Install time synchronizer on master-slave server

[root@localhost /opt]# yum install ntp -y

-------------------------------------------------------- Configure master server--------------------------------------------------

Modify profile

[root@localhost /opt]# vim /etc/ntp.conf

Open service

[root@localhost /opt]# service ntpd start

-------------------------------------------------------Configure slave server----------------------------------------------------

Open service

[root@localhost ~]# [root@localhost /opt]# service ntpd start
Redirecting to /bin/systemctl start ntpd.service

Perform synchronization

[root@localhost ~]# /usr/sbin/ntpdate 192.168.37.100
 5 Dec 15:04:35 ntpdate[83019]: the NTP socket is in use, exiting

Schedule synchronization every 30 minutes

The two slave servers operate in the same way

Configure master-slave

Open binary

-------------------------------------------------------- Master server-----------------------------------------------------------

Configure mysql master configuration file

[root@localhost /opt]# vim /etc/my.cnf

Restart database

[root@localhost /opt]# systemctl restart mysqld.service

Enter the database and configure replication master-slave

(root@localhost) [(none)]> grant replication slave on *.* to 'myslave'@'192.168.37.%' identified by  '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

<! -- myslave is the name created by yourself. You can fill in the ip from the server or network segment -- >

Refresh

(root@localhost) [(none)]> flush privileges;

see

(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      603 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

-------------------------------------------------------Slave server-----------------------------------------------------------

Configure mysql master configuration file

[root@localhost ~]# vim /etc/my.cnf

server-id = 2
#Modify. Note that the id is different from that of the Master, and the IDs of the two Slave should also be different
relay-log=relay-log-bin
#Add, enable the relay log, and synchronize the log file records from the primary server to the local server
relay-log-index=slave-relay-bin.index
#Add and define the location and name of relay log files, which are generally in the same directory as relay log

Restart MySQL service

[root@localhost ~]# systemctl restart mysqld.service

Enter database

Template

mysql>help change master to


.....
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;
.....

change master to master_host='192.168.37.100',master_user='myslave',master_password='123456',master_r_log_file='mysql-bin.000005',master_log_pos=603; 
Query OK, 0 rows affected, 2 warnings (0.01 sec)

The IP address of the primary server, the user name is configured during replication, and the password is also a binary file. It corresponds to the primary server

(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      603 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

open

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

View status

mysql> show slave status\G;

<!-- The configuration of the second slave server is the same. The only difference is that the master configuration file server ID is set to 3 -- >

The second slave server also succeeded

Verify master-slave synchronization

Create a library on the master server

(root@localhost) [(none)]> create database xiaob;

Create a table

create table bb (id int primary key,name varchar(20),Gender varchar(2));
Query OK, 0 rows affected (0.01 sec)

From the server

mysql> show tables;
+-----------------+
| Tables_in_xiaob |
+-----------------+
| bb              |
+-----------------+
1 row in set (0.00 sec)

Master slave synchronization has been successful!!!

Build Amoeba to realize read-write separation

Install jdk

Because Amoeba is developed based on jdk1.5, it is officially recommended to use jdk1.5 or 1.6, and the higher version is not recommended.

First download jdk1.6 and the middleware amoeba

Copy jdk to / usr/local directory

[root@localhost /opt]# cp jdk-6u14-linux-x64.bin /usr/local/

Give execution permission

[root@localhost /opt]# chmod +x /usr/local/jdk-6u14-linux-x64.bin

Install jdk, press enter all the way to the end, and finally enter yes for automatic installation

[root@localhost /usr/local]# ./jdk-6u14-linux-x64.bin

Change your name

[root@localhost /usr/local]# mv jdk1.6.0_14/ jdk1.6

Add environment variable

[root@localhost /usr/local]# vim /etc/profile

export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

Refresh

[root@localhost /usr/local]# source /etc/profile

Java environment installation completed!!!

[root@localhost /usr/local]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

Install amoeba Middleware

Create a new directory

[root@localhost /usr/local]# mkdir amoeba

decompression

[root@localhost /usr/local]# cd /opt/
[root@localhost /opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin  rh
[root@localhost /opt]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/

Give permission

[root@localhost /opt]# chmod -R 755 /usr/local/amoeba/

Install amoeba ， amoeba start|stop indicates that the installation is successful

[root@localhost /usr/local/amoeba/bin]# /usr/local/amoeba/bin/amoeba
amoeba start|stop

Configure Amoeba read-write separation and two Slave read-write load balancing

First, open the permission to Amoeba on the mysql of Master, Slave1 and Slave2

(root@localhost) [(none)]> grant all on *.* to test@'192.168.37.%' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

Refresh

(root@localhost) [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Modify amoeba configuration

[root@localhost /usr/local/amoeba]# cd conf/

It is best to back up the amoeba.xml file

[root@localhost /usr/local/amoeba/conf]# cp amoeba.xml amoeba.xml.bak

Global configuration

[root@localhost /usr/local/amoeba/conf]# vim amoeba.xml

to configure dbServers.xml file, you'd better back up a copy first

[root@localhost /usr/local/amoeba/conf]# cp dbServers.xml dbServers.xml.bak

[root@localhost /usr/local/amoeba/conf]# vim dbServers.xml

Open amoeba

[root@localhost /usr/local/amoeba/conf]# amoeba start &

<!-- This command is started from the previous console, and the startup information will be output. After checking that there is no error information, it will be interrupted and run in the background -- >:

View Java port number

[root@localhost /usr/local/amoeba/conf]# netstat -ntap |grep java
tcp6       0      0 127.0.0.1:46361         :::*                    LISTEN      53022/java          
tcp6       0      0 :::8066                 :::*                    LISTEN      53022/java          
tcp6       0      0 192.168.37.105:43478    192.168.37.102:3306     ESTABLISHED 53022/java          
tcp6       0      0 192.168.37.105:54060    192.168.37.100:3306     ESTABLISHED 53022/java          
tcp6       0      0 192.168.37.105:37016    192.168.37.101:3306     ESTABLISHED 53022/java

When connecting amoeba on the client side, the password is the password set by the global configuration

[root@localhost /usr/local/mysql]# mysql -u amoeba -p -h 192.168.37.105 -P8066

So come in. The table is on the primary server

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bank               |
| bbs                |
| hellodb            |
| jianghu            |
| mysql              |
| performance_schema |
| sys                |
| usr                |
| xb                 |
| xiaob              |
+--------------------+
11 rows in set (0.01 sec)

Verify read / write separation

First, turn off the master and slave

mysql> stop slave;

Add data from the first server

mysql> insert into bb values(1,'dav','m');

Add data from the second server

mysql> insert into bb values(2,'xiaoli','female');
Query OK, 1 row affected (0.00 sec)

polling

mysql> select * from bb;
+----+------+--------+
| id | name | Gender |
+----+------+--------+
|  1 | dav  | m      |
+----+------+--------+
1 row in set (0.00 sec)

mysql> select * from bb;
+----+--------+--------+
| id | name   | Gender |
+----+--------+--------+
|  2 | xiaoli | female     |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> select * from bb;
+----+------+--------+
| id | name | Gender |
+----+------+--------+
|  1 | dav  | m      |
+----+------+--------+
1 row in set (0.01 sec)

mysql> select * from bb;
+----+--------+--------+
| id | name   | Gender |
+----+--------+--------+
|  2 | xiaoli | female     |
+----+--------+--------+
1 row in set (0.01 sec)

Verify that write all is on

mysql> set global general_log=1;

mysql> show variables like 'general%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | ON                                  |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)

Review the bb table again

see information

Read write separation is successful!!!

Possible problems

Causes of inconsistency between master and slave

1. The binlog format of the master library is Statement. After synchronization to the slave library, the master and slave may be inconsistent.

2. If set sql_log_bin=0 is executed before the master database changes, the master database will not record binlog, and the slave database will not be able to change this part of data.

3. The slave node is not set to read-only, and the data is written incorrectly

4. Unexpected downtime of the master database or slave database may cause damage to binlog or relaylog files, resulting in inconsistency between the master and slave databases

5. The versions of the master and slave instances are inconsistent, especially when the higher version is the master and the lower version is the slave, the functions supported on the master database are never supported

6. This function may not be supported on the database

7. MySQL bug

Repair method of master-slave inconsistency

Re implement from library

Although this is also a solution, the recovery time of this scheme is relatively slow, and sometimes the slave database also undertakes part of the query operation, so it cannot be rebuilt rashly.

Using the percona toolkit tool to assist

PT toolkit contains two tools, Pt table checksum and Pt table sync, which are mainly used to detect whether the master and slave are consistent and repair data inconsistencies. This scheme has the advantages of fast repair speed and no need to stop master-slave assistance. The disadvantage is that it requires knowledge accumulation and time to learn and test, especially in the production environment. It should be used carefully

Manually rebuild inconsistent tables

In the slave database, it is found that some tables are inconsistent with the data of the master database, and the data volume of these tables is relatively large. It is unrealistic to compare the data manually, and it is slow to redo the whole database. At this time, you can redo only these tables to repair the inconsistency between the master and slave. The disadvantage of this scheme is that you need to temporarily stop copying from the library during import, but it is also acceptable

Avoid master-slave inconsistency

1. The binlog of the main database is in ROW format

2. The master-slave instance database version is consistent

3. The master database controls the account permissions and cannot execute set sql_log_bin=0

4. Read only is enabled from the library, and manual writing is not allowed

5. Conduct master-slave consistency inspection regularly

Posted by connex on Sun, 05 Dec 2021 14:00:44 -0800

Programmer Group

MySQL master-slave replication and separation analysis

MySQL master-slave replication

Master slave replication architecture and principle

Function of replication

Replication schema

Principle of master-slave replication

Master slave replication related threads

Files related to copy function:

MySQL master-slave replication delay

MySQL extension

What is read write separation

Why read write separation

Master-slave replication and read-write separation

Actual combat project

Various middleware addresses

Environmental preparation

Set up master-slave replication

Build time synchronization

Configure master-slave

Build Amoeba to realize read-write separation

Possible problems

Causes of inconsistency between master and slave

Repair method of master-slave inconsistency

Avoid master-slave inconsistency

Hot Keywords