Detailed explanation of MySQL master-slave replication and read-write separation

Preface

In actual enterprise applications, mature businesses usually have large amounts of data, and a single MySQL server cannot meet actual needs in terms of security, high availability, and high concurrency. We can deploy master-slave replication on multiple MySQL servers (Master-Slave) to synchronize data, and then improve the concurrent load capacity of the database through read-write separation. 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.

1. Overview

Master-slave replication: The master database (Master) sends update events to the slave database (Slave), which reads the update records and executes them to keep the contents of the slave database consistent with the master database.

1. Replication types supported by MySQL

• Statement-based replication (STATEMENT). The SQL statement executed on the master database executes the same statement on the slave database. MySQL uses statement-based replication by default, which is more efficient.
• Row-based replication (ROW). Copy the changed content instead of executing the command on the slave library.
• Mixed type replication (MIXED). Statement-based replication is used by default. Once it is found that statement-based replication cannot be accurately replicated, row-based replication will be used.

(II) Working process of MySQL master-slave replication

1.Master server saves records to binary log

• The data updates added, deleted, and modified on the MySQL master database will be written to their own In the binary log

2. The slave server copies the log of the master server

• Then MySQL starts a The I/O thread connects to the main database and reads the main database The binary log is backed up to the relay log of the slave server. If it has caught up with the master, it will sleep and wait for the master to generate new events, and the I/O thread will write these events to the relay log.

3. The slave server replays the copied logs

• Then open from the library SQL thread, SQL thread reads data written by I/O thread Relay log, and update the slave database data according to the content of the relay log to make it consistent with the data in the master database

Important: The replication process has a very important limitation, that is, replication is serialized on the Slave, which means that parallel update operations on the Master cannot be performed in parallel on the Slave.

2. Read-write separation

(1) The concept of read-write separation

Read-write separation: Read-write separation means writing only on the master server and reading only on the slave server. The basic principle is to let the master database handle transactional addition, modification, and deletion operations (INSERT, UPDATE, DELETE), while the slave database handles SELECT query operations. Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.

(2) The role of read-write separation

• Because the "write" operation of the database (writing 10,000 data may take 3 minutes) is time-consuming. But the "reading" of the database (reading 10,000 records may only take 5 seconds).
• Therefore, read-write separation solves the problem that database writing affects the efficiency of queries. Note: The database does not necessarily have to be separated in read and write. If the program uses the database more frequently, but there are fewer updates and more queries, it will be considered for use. By using database master-slave synchronization and read-write separation, database pressure can be shared and performance can be improved.

(3) MySQL read-write separation principle

• Read-write separation means writing only on the master server and reading only on the slave server.
• The basic principle is to let the master database handle transactional queries, while the slave database handles select queries.
• Database replication is used to synchronize changes caused by transactional queries on the master database to the slave databases in the cluster.

(4) Common MySQL read-write separation

1) Based on internal implementation of program code

1. Classify routes according to select and insert in the code. This method is also the most widely used in production environments.

2. Advantages and disadvantages:

• The advantage is better performance, because it is implemented in the program code, and no additional equipment is needed for hardware expenditure;
• The disadvantage is that it requires developers to implement it, and operation and maintenance personnel have no idea where to start.

3. Not all applications are suitable for implementing read-write separation in program code. For example, for some large and complex Java applications, implementing read-write separation in program code will require significant code changes.

2) Implementation based on the intermediate proxy layer

1. The proxy is usually located between the client and the server. After receiving the client request, the proxy server forwards it to the backend database after judgment. The following are representative programs:

• MySQL-Proxy: MySQL-Proxy is a MySQL open source project that uses its own Lua script to perform SQL judgment.
• Atlas: It is a data middle-tier project based on the MySQL protocol developed and maintained by the infrastructure team of the Web Platform Department of Qihoo 360. It is based on mysql-proxy version 0.8.2, and optimizes it and adds some new features. 360 uses Atlas to run MySQL business internally, and the number of read and write requests carried by it every day reaches billions. Supports transactions and stored procedures.
• Amoeba: Developed by Chen Siru, who used to work at Alibaba. The program is developed in Java language and Alibaba uses it in production environment. But it does not support transactions and stored procedures.
  

2. Since using MySQL Proxy requires writing a large number of Lua scripts, these Lua scripts are not ready-made and need to be written by yourself, which is very difficult for people who are not familiar with MySQL Proxy built-in variables and MySQL Protocol.

3.Amoeba is a very easy-to-use and highly portable software, so it is widely used as the proxy layer of the database in production environments.

3. MySQL master-slave replication experimental deployment

Required related software package
amoeba-mysql-binary-2.2.0.tar.gz
jdk-6u14-linux-x64.bin
mysql compressed package

(1) Master-slave replication experimental steps and preparation

Experimental steps Step 1: Client client accesses proxy server amoeba
Step 2: Proxy server reads and writes judgment Write operation: write to the master server Step 3: The master server writes the additions, deletions, and modifications to its own binary log Step 4: The slave server synchronizes the master server's binary log to its own relay log Step 5: The slave server replays the relay log to the database Read operation: Directly access the slave server Final result: Reduce the load and play a load balancing role Host operating system IP address Required tools/software/installation packages

1. Turn off firewall and security mechanisms

All four servers must be shut down

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

2. Install the time synchronization service ntp

(1) Primary Server

yum -y install ntp

vim /etc/ntp.conf
##---------Add at the end---------
server 127.127.71.0 #Set the local clock source, pay attention to modify the network segment fudge 127.127.71.0 stratum 8 #Set the time level to 8 (limited to 15)

service ntpd start

(2) Two slave servers

yum -y install ntp ntpdate

service ntpd start

/usr/sbin/ntpdate 192.168.71.12 #Time synchronization, pointing to the Master server IP

crontab -e #Set the scheduled task to synchronize the time every half an hour */30 * * * * /usr/sbin/ntpdate 192.168.71.12

3. MySQL configuration of the main server

vim /etc/my.cnf
server-id = 1
log-bin=master-bin #Add, master server opens binary log log-slave-updates=true #Add, allow slave server to update binary log systemctl restart mysqld

mysql -u root -p
grant replication slave on *.* to 'myslave'@'192.168.71.%' identified by '123'; #Elevate privileges to the slave server #grant authorization #replication replication #Allow the network segment 192.168.71 to use password 123 to replicate all tables and libraries flush privileges;

show master status;
#The File column shows the log name, and the Fosition column shows the offset 

4. MySQL configuration from the server

Slave1 server: 192.168.163.13
Slave2 server: 192.168.163.14

1) Modify the configuration file

vim /etc/my.cnf
#Modify, note that the id is different from the Master, and the ids of the two Slaves must also be different server-id = 2

#Add, open relay log, synchronize log files from the master server to the local relay-log=relay-log-bin

#Add, define the location and name of the relay log file relay-log-index=slave-relay-bin.index

systemctl restart mysqld

slave1

slave2

2) Operate the database from the server

mysql -u root -p123
change master to master_host='192.168.71.12',master_user='myslave',master_password='123',master_log_file='master-bin.000002',master_log_pos=306; #show master status; The output binary file is the same as the position point #Configure synchronization, note that the values ​​of master_log_file and master_log_pos must be consistent with the Master query	 
start slave; #Start synchronization, if there is an error, execute reset slave;
show slave status\G; #Check the slave status // Make sure that both IO and SQL threads are Yes, indicating that the synchronization is normal.
Slave_IO_Running:Yes #Responsible for io communication with the host Slave_SQL_Running:Yes #Responsible for its own slave mysql process 

5. Verify the results

Create a library on the master server and view it on the slave server

4. MySQL read-write separation experiment

Amoeba Server (192.168.153.30)
Install the Java environment. Because Amoeba is developed based on jdk1.5, the official recommendation is to use jdk1.5 or 1.6. Higher versions are not recommended.

1) Create a jdk java environment

cd /opt/
#Upload jdk-6u14-linux-x64.bin and amoeba-mysql-binary-2.2.0.tar.gz to the /opt directory cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
//Press yes, press enter

mv jdk1.6.0_14/ /usr/1ocal/jdk1.6

vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=SCLASSPATH:$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

source /etc/profile
java -version

2) Install Amoeba software

mkdir /usr/local/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
//If amoeba start|stop is displayed, it means the installation is successful 

3) Authorize on the mysql of the master and slave servers

Master server: 192.168.71.12
Slave1 server: 192.168.71.13
Slave2 server: 192.168.71.14

First, open permissions on the mysql of Master, Slave1, and Slave2 for Amoeba to access

grant all on *.* to 'test'@'192.168.163.%' identified by 'abc123'; 

4) Configure amoeba service on amoeba

cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak #Backup the configuration file vim amoeba.xml #Modify the amoeba configuration file <property name="user">amoeba</property> #Modify line 30 <property name="password">abc123</property> #Modify the password for the client to connect to the amoeba server on line 32 <property.name="defaultPool">master</property> #Modify the default pool on line 115 <property name="writePool">master</property> #Remove comments on line 117 and set master as the write pool <property name="readPool">slaves</property> #slaves as the read pool 

cp dbServers.xml dbServers.xml.bak
vim dbServers.xml #Modify the database configuration file<!-- <property name="schema"> test</property> --> #Line 23, comment out the purpose: enter the test library by default in case there is no test library in mysql, an error will be reported<property name="user">test</property> #Line 26, modify the user and password for privilege escalation on the master and slave servers<property.name-"password">123</property> #Lines 28-30, remove the comments<dbServer name= "master" parent="abstractServer"> #Line 45, modify, set the name of the master server to Master

<property name= "ipAddress">192.168.71.12</property> #48 line, modify, set the address of the master server <dbServer name="slave1" parent="abstractServer"> #52 line, modify, set the name of the slave server slave1

<property.name="ipAddress">192.168.71.13</property> #55 line, modify, set the address of slave server 1 <dbServer name="slave2 " parent="abstractserver"> #59 line, copy and paste the above 6 lines, set the name and address of slave server 2 slave2 <property, name="ipAddress">192.168.71.14</property>

<dbServer name="slaves" virtual="true"> #65 line, modify <property name="poolNames">slave1,slave2</property> #71 line, modify /usr/local/amoeba/bin/amoeba start& #Start Amoeba software, press ctrl+c to return netstat -anpt | grep java #Check whether port 8066 is open, the default port is TCP 8066

5) Test read-write separation

Testing on the client server

yum install -y mysql mysql-server #Quickly install mysql, or you can choose to compile and install mysql -u amoeba -pabc123 -h 192.168.71.20 -P8066 

This table can be seen on both the master and slave servers.

Access MySQL through the amoeba server proxy. After connecting to MySQL through the client, the data written will only be recorded by the master service and then synchronized to the slave server.

stop slave; #Close synchronous writing of data use school;
//On slave1:
insert into test1 values('1','slave1');

//On slave2:
insert into test1 values('2','slave2');

Insert data on the primary server

insert into test1 values('3','master');

Insert data into the table in the client

//On the client server:
use school;
select * from test1; //The client will read data from slave1 and s1ave2 respectively, and only the data added on the two slave servers will be displayed, and no data added on the master server will be displayed insert into test1 values('4','climet'); //This data is only available on the master server 

View data on the primary server

Start two slave servers at the same time and view the table records

This is the end of this article about MySQL master-slave replication and read-write separation. For more relevant MySQL master-slave replication and read-write separation content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!