A detailed tutorial on master-slave replication and read-write separation of MySQL database

Preface

In an actual production environment, if both reading and writing to a MySQL database are performed in one database service, it cannot meet actual needs in terms of security, high availability, and high concurrency. Generally speaking, data is synchronized through master-slave replication, and then read-write separation is used to improve the concurrent load capacity of the database.

1. MySQL master-slave replication

1. Supported replication types

Statement-based replication (statement): execute SQL statements on the server and execute the same statements on the slave server. MySQL uses statement-based replication by default, which has high execution efficiency. Row-based replication (row): copy the changed content instead of executing the command on the slave server. Mixed replication (mixed): execute SQL statements on the server and execute the same statements on the slave server. MySQL uses statement-based replication by default, which has high execution efficiency.

2. The working process of master-slave replication is based on logs

master binary log

Slave relay log

3. Request method

I/O Threads

dump thread

SQL Thread

4. The principle of master-slave replication

①The Master node records data changes in a binary log. When the data on the Master node changes, the changes are written to the binary log.

②The slave node will detect whether the binary log of the master has changed at a certain time interval.

If a change occurs, an I/O thread is started to request the binary event of the Master

③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 in the local relay log of the Slave node.

④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 to make its data consistent with that of the master node. Finally, the I/O thread and SQL thread will enter a sleep state and wait to be awakened next time.

The replication process has an 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.

Relay logs are usually located in the OS cache, so the overhead of relay logs is very small.

5. In what scenarios are MySQL cluster and master-slave replication suitable?

Clustering and master-slave replication are designed to cope with high concurrency and large traffic. If the traffic and concurrency of a website are too large, a small number of database servers cannot handle it, which will cause slow website access. Data writing will cause the data table or record to be locked. Locking means that other access threads cannot read or write temporarily and have to wait until the writing is completed before continuing, which will affect the reading speed of other users. Using master-slave replication can solve this problem by allowing some servers to specialize in reading and some to specialize in writing.

6. Why use master-slave replication and read-write separation?

Master-slave replication and read-write separation are generally used together. The purpose is very simple, which is to improve the concurrent performance of the database. You can imagine that if it is a single machine, and both reading and writing are done on one MySQL, the performance will definitely not be high. If there are three MySQL servers, one master is only responsible for write operations, and two slaves are only responsible for read operations, wouldn't the performance be greatly improved?

Therefore, master-slave replication and read-write separation are to enable the database to support greater concurrency.

As the business volume expands, if MySQL is deployed on a single machine, the I/O frequency will be too high. Using master-slave replication and read-write separation can improve the availability of the database

7. Purpose and conditions

MySQL master-slave replication purposes:

Real-time disaster recovery for failover

Read-write separation, providing query services

Backup to avoid affecting services

Necessary conditions:

The main database opens binlog log (set log-bin parameters)

The master and slave server-ids are different

The slave server can connect to the master

8. Problems with MySQL master-slave replication

After the main database goes down, data may be lost

The slave database has only one SQL Thread, the master database has a lot of write pressure, and replication is likely to be delayed

Solution

Semi-synchronous replication - solving the problem of data loss

Parallel replication - solving the problem of slave replication delay

9.MySQL master-slave replication delay

①High concurrency of the master server, resulting in a large number of transactions ②Network delay ③Master-slave hardware devices - CPU main frequency, memory io, hard disk io
④It is not synchronous replication, but asynchronous replication

• Optimize Mysql parameters from the database. For example, increase innodb_buffer_pool_size to allow more operations to be completed in MySQL memory and reduce disk operations.
• The slave library uses a high-performance host, including a powerful CPU and large memory. Avoid using virtual cloud hosts and use physical hosts, which improves i/o performance
• Use SSD disks from the library
• Network optimization to avoid synchronization across computer rooms

2. Master-slave replication

3. Read-write separation

1. Principle

①Write only on the master server and read only on the slave server

②The master database handles transactional queries, and the slave database handles select queries

③Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster

2. Why do we need to separate reading and writing?

Because the "write" operation of the database (writing 10,000 data may take 3 minutes) is relatively time-consuming, but the "read" operation of the database (reading 10,000 data may only take 5 seconds), so the read-write separation solves the problem that the writing of the database affects the efficiency of the query.

3. When should read and write be separated?

The database does not necessarily have to be separated from the read and write. If the program uses the database more frequently, but updates less and queries more frequently, we will consider using the database master-slave synchronization. Then, through read-write separation, the database pressure can be shared and performance can be improved. 4. Master-slave replication and read-write separation In the actual production environment, the reading and writing of the database are all in the same database server, which cannot meet the actual needs.

5. Currently more common MySQL read-write separation

Divided into the following two types

① Based on internal implementation of program code

In the code, routes are classified according to select and insert. This method is also the most widely used in production environments. The advantage is that the performance is better because it is implemented in the program code, and no additional equipment is required for hardware expenses. The disadvantage is that it requires developers to implement it, and operation and maintenance personnel have no idea where to start. However, not all applications are suitable for implementing read-write separation in program code. For example, some large and complex Java applications require a large code change if read-write separation is implemented in the program code.

② Implementation based on the intermediate proxy layer

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: (1) MySQL-Proxy. MySQL-Proxy is a MySQL open source project that performs SQL judgment through its own Lua script.
(2) Atlas 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 the mysql-proxy 0.8.2 version, and has been optimized to add 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 (3) Amoeba, developed by Chen Siru, who once worked 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.

Since using MySQL Proxy requires writing a lot of Lua scripts, these Lua scripts are not ready-made, but 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. Amoeba is a very easy-to-use and highly portable software. Therefore, it is widely used in the database proxy layer in production environments.

IV. Case Implementation

1. Case environment

This case environment is built using the stage server. The topology diagram is as follows

2. Experimental ideas (solving needs)

Client access proxy server

Proxy server writes to the master server

The master server writes additions, deletions, and modifications to its own binary log

The slave server synchronizes the binary log of the master server to its own relay log

Replay relay logs from the server into the database

The client reads, then the proxy server directly accesses the slave server

Reduce the load and play a load balancing role

3. Preparation

• In addition to the client, you need to compile and install MySQL from source code first
• All firewalls and access control mechanisms need to be turned off

systemctl stop firewalld
systemctl disable firewalld
#Turn off the firewall (and disable it at startup)
setenforce 0
#Disable the security access control mechanism

4. Build MySQL master-slave replication

①Mysql master-slave server time synchronization

Master Server Settings

#Install NTP
yum -y install ntp
#Configure NTP
vim /etc/ntp.conf
#Add the following content to the last line server 127.127.126.0
fudge 127.127.126.0 stratum 8
#Set the local clock source, pay attention to modify the network segment #Set the time level to 8 (limited to 15)
#Restart service service ntpd restart

From the server settings

yum -y install ntp ntpdate
#Install service, ntpdate is used to synchronize time service ntpd start
#Start service /usr/sbin/ntpdate 192.168.126.11
#Perform time synchronization and point to the Master server IP
crontab -e
#Write a scheduled task to synchronize time every half hour*/30 * * * * /usr/sbin/ntpdate 192.168.126.11

②Configure MySQL Master server

vim /etc/my.cnf
#Configure the following content server-id = 1
log-bin=master-bin
#Add, master server opens binary log log-slave-updates=true
#Add, allow updating binary log from server systemctl restart mysqld
#Restart the service to make the configuration take effect mysql -uroot -p123123
#Log in to the database program and grant authorization to the slave server GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.126.%' IDENTIFIED BY '123123';
FLUSH PRIVILEGES;
show master status;
quit
#The File column shows the log name, and the Fosition column shows the offset 

③Configure the slave server

vim /etc/my.cnf
server-id = 2
#Modify, note that the id is different from the Master, and the ids of the two Slaves must also be different relay-log=relay-log-bin
#Add, open relay log, synchronize log file records from the master server to the local relay-log-index=slave-relay-bin.index
#Add, define the location and name of the relay log file systemctl restart mysqld
mysql -uroot -p123123
change master to master_host='192.168.126.11' , master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=604;
#Configure synchronization, note that the values ​​of master_log_file and master_log_pos must be consistent with those of the Master start slave;
#Start synchronization, if there is an error, execute reset slave;
show slave status\G
#Check the Slave status // Make sure 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 your own slave mysql process

slave1:

slave2: Same configuration as slave1, but different ids

Generally, there are several possibilities for Slave_IO_Running: No:

• Network is not working
• There is a problem with my.cnf configuration
• The password, file name, pos offset are incorrect
• The firewall is not turned off

④Verify the master-slave replication effect

5. Build MySQL read-write separation

• This software is dedicated to the distributed database front-end proxy layer of MySQL. It mainly acts as SQL routing when the application layer accesses MySQL, and has load balancing, high availability, SQL filtering, read-write separation, routing to the target database, and concurrent requests to multiple databases.
• Amoeba can achieve high availability, load balancing, and data slicing for multiple data sources

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

cd /opt/
#In FinalShell, drag in the software package amoeba-mysql-binary-2.2.0.tar.gz
jdk-6u14-linux-x64.bin
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
#Press and hold the Enter key until you reach the bottom. When prompted, enter YES and press Enter. mv jdk1.6.0_14/ /usr/local/jdk1.6
#Rename vim /etc/profile
#Edit the global configuration file and add the following configuration in the last line 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

#Output defines the Java working directory #Output the specified Java type #Add java to the path environment variable #Output defines the amoeba working directory #Add the path environment variable source /etc/profile
#Execute the modified global configuration file java -version
#Check the java version information to check whether the installation is successful

②Install and configure Amoeba

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 

③Configure Amowba read-write separation, and two slaves read and write load balancing. Open permissions for amoeba to access on the mysql of master, slave1, and slave2

④Edit the amoeba.xml configuration file in the host amoeba

cd /usr/local/amoeba/conf/
cp amoeba.xml amoeba.xml.bak
vim amoeba.xml
#Modify the amoeba configuration file #Modify line 30 <property name="user">amoeba</property>
#Modify line 32 <property name="password">123123</property>
#115 line modification <property name="defaultPool">master</property>
#117 Remove the comment <property name="writePool">master</property>
<property name="readPool">slaves</property>

⑤Edit the dbServers.xml configuration file

cp dbServers.xml dbServers.xml.bak
vim dbServers.xml
#Modify the database configuration file #23 line comment out purpose: enter the test library by default in case there is no test library in mysql, an error will be reported <!-- <property name="schema">test</property> -->
#26 line modification <property name="user">test</property>
#28-30 lines uncomment <property name="password">123.com</property>
#Modify line 45 and set the name of the master server to Master
<dbServer name="master" parent="abstractServer">
#Modify line 48 and set the address of the primary server <property name="ipAddress">192.168.126.11</property>
#Modify line 52 and set the slave server name to slave1
<dbServer name="slave1" parent="abstractServer">
#Modify line 55 and set the address of slave server 1 <property name="ipAddress">192.168.126.13</property>
#58 Copy and paste the above 6 lines to set the name and address of slave server 2 <dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.184.14</property>
#65 line modification <dbServer name="slaves" virtual="true">
#71 Modify <property name="poolNames">slave1,slave2</property>

⑥After confirming that the configuration is correct, you can start the Amoeba software. Its default port is tcp 8066

/usr/local/amoeba/bin/amoeba start&
#Start Amoeba software, press ctrl+c to return to netstat -anpt | grep java
#Check whether port 8066 is open. The default port is TCP 8066

⑦Test

1. Go to the client and quickly install the MySQL virtual client, then access MySQL through the proxy

yum -y install mysql
#Use YUM to quickly install the MySQL virtual client mysql -u amoeba -p123123 -h 192.168.126.12 -P8066
#Access MySQL through the proxy, the IP address points to amoba
#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

2. Create a table on the Master and synchronize it to the two slave servers

use club;
create table puxin (id int(10),name varchar(10),address varchar(20));

3. Then turn off the slave function of the slave server, synchronize the table from the master server, and manually insert the data content

stop slave;
#Turn off synchronization use club;
insert into puxin values('1','wangyi','this_is_slave1');
#slave2
insert into puxin values('2','wanger','this_is_slave2');

4. Return to the main server and insert other content

insert into pucin values('3','wangwu','this_is_master');

5. Test the read operation and go to the client host to query the results

use club;
select * from puxin;

6. A statement is inserted on the client, but it cannot be queried on the client. Finally, the content of this statement can only be viewed on the Master, indicating that the write operation is on the Master server.

insert into puxin values('4','liuliu','this_is_client');

7. Then execute start slave on the two slave servers; the data added on the master server can be synchronized

Summarize

This verifies that MySQL read-write separation has been achieved. Currently, all write operations are performed on the Master server to avoid data asynchrony.

All read operations are distributed to the slave server to share the pressure of the database.

1. How to check whether the master-slave synchronization status is successful

Enter the command show slave status\G in the slave server to view the master-slave information, which contains the status information of the IO thread, as well as the IP address, port, and transaction start number of the master server.

When slave_io_running and slave_sql_running are both displayed as yes, it means that the master-slave synchronization status is successful.

2. If I/O and SQL are not yes, how do you troubleshoot?

• First, rule out network problems and use the ping command to check whether the slave service can communicate with the master server.
• Also check whether the firewall and core protection are turned off.
• Then check whether the slave in the slave server is turned on
• Are the server-ids of the two slave servers the same? This is why only one can be connected.
• The values ​​of master_log_file and master_log_pos should be consistent with those in the Master query.

3. What information can be seen in show slave status (more important)

• IO thread status information
• IP address, port, and transaction start location of the master server
• The most recent error message and error location, etc.

4. What are the possible reasons for slow master-slave replication (delay)?

• The main server is overloaded and occupied by multiple sleeping or zombie threads, causing the system to be overloaded.
• The slave database hardware is worse than the master database, resulting in replication delays
• Master-slave replication is single-threaded. If the master database writes too much concurrently, it will not be able to be transmitted to the slave database in time, which will cause delays.
• Too many slow SQL statements
• Network latency

The above is the detailed content of the intensive tutorial on master-slave replication and read-write separation of MySQL database. For more information about master-slave replication and read-write separation of MySQL database, please pay attention to other related articles on 123WORDPRESS.COM!