An article to understand MySQL master-slave replication and read-write separation

introduction

The business in an enterprise usually has a large amount of data, and a single database cannot meet the actual needs in terms of data storage, security, and high concurrency. Therefore, it is necessary to configure multiple master-slave data servers to achieve master-slave replication, increase data reliability, separate reads and writes, and reduce database pressure and table and row locking problems caused by the storage engine.

1. MySQL master-slave replication

In an actual production environment, reading and writing the database are all done in the same database server, which cannot meet actual needs. Whether in terms of security, high availability or high concurrency, it is completely unable to meet actual needs. Therefore, data is synchronized through master-slave replication, and the concurrent load capacity of the database is improved 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. MySQL replication type

(1) 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.

(2) Row-based replication (ROW)

Copy the changes instead of executing the command again on the slave server.

(3) Mixed type assistance (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.

2. The principle of MySQL master-slave replication

(1) 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.

(2) The slave node will detect whether the master's binary log has changed at a certain time interval. If it has changed, it will start an I/O thread to request the master's binary events.

(3) 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.

Note: Relay logs are usually located in the OS cache, so the overhead of relay logs is very small. 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.

3. MySQL master-slave replication delay

①The master server has high concurrency, resulting in a large number of transactions

②Network delay

③Master-slave hardware devices cause

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.

Use high-performance hosts for slave libraries. Including powerful CPU and larger 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. MySQL read-write separation

The basic principle of read-write separation is to let the master database handle transactional add, modify, and delete 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.

The database does not necessarily have to be separated into 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.

1. Common MySQL read-write separation

(1) 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 does not require additional equipment as 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, for some large and complex Java applications, implementing read-write separation in program code requires significant code changes.

(2) 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.

①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, the author 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.

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. 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 highly portable software. Therefore, it is widely used in the database proxy layer in production environments.

2. 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 operations, while the slave database handles select queries. Database replication is used to synchronize changes resulting from transactional operations on a master database to slave databases in a cluster.

3. MySQL master-slave replication and read-write separation configuration steps

1. Build the environment

2. Solve the needs

The client accesses the proxy server; the 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 master server's binary log to its own relay log; the slave server replays the relay log into the database; if the client reads, the proxy server directly accesses the slave server; this reduces the load and plays a role in load balancing.

3. Preparation stage (closing the firewall and controlling access mechanism)

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
[root@localhost ~]# setenforce 0

4. Build MySQL master-slave replication

(1) MySQL master-slave server time synchronization configuration steps

①Master server (192.168.32.128) configuration

[root@localhost ~]# yum -y install ntp #Install ntp
[root@localhost ~]# vim /etc/ntp.conf #Configure ntp
#Add the following content in line 25 server 127.127.32.0 #Set the local as the clock source, pay attention to modify the network segment fudge 127.127.32.0 stratum 8 #Set the time level to 8 (limited to 15)
[root@localhost ~]# systemctl restart ntpd #Restart ntp service②Slave1 server (192.168.32.133) and Slave2 server (192.168.32.134) configuration[root@localhost ~]# yum -y install ntp ntpdate #Install service, ntpdate is used to synchronize time[root@localhost ~]# systemctl start ntpd #Start service[root@localhost ~]# /usr/sbin/ntpdate 192.168.32.128 #Perform time synchronization, point to the Master server IP
[root@localhost ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.32.128 #Write a scheduled task to synchronize the time every half an hour

(2) Configure the Master server (192.168.32.128)

[root@localhost ~]# vim /etc/my.cnf
server-id = 1 #Note that the id cannot be the same log-bin=master-bin #Add, the master server opens the binary log log-slave-updates=true #Add, allow the slave server to update the binary log [root@localhost ~]# systemctl restart mysqld #Restart the service to make the configuration effective [root@localhost ~]# mysql -uroot -p123456 #Log in to the database program mysql> grant replication slave on *.* to 'myslave'@'192.168.32.%' identified by '123456'; #Authorize the slave server mysql> flush privileges;
mysql> show master status; #The File column shows the log name, and the Fosition column shows the offset 

(3) Slave1 server (192.168.32.133) and Slave2 server (192.168.32.134) configuration

①Slave1 server (192.168.32.133) configuration

[root@localhost ~]# 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, turn on the relay log, and 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 filemysql> change master to master_host='192.168.32.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; #Configure synchronization, note that the values ​​of master_log_file and master_log_pos must be consistent with those of the Mastermysql> start slave; #Start synchronization, if there is an error, execute reset slave;
mysql> show slave status\G #Check the slave status (make sure both IO and SQL threads are Yes, indicating normal synchronization)
Slave_IO_Running: Yes #Responsible for io communication with the host Slave_SQL_Running: Yes #Responsible for its own slave mysql process 

②Slave2 server (192.168.32.134) configuration

[root@localhost ~]# vim /etc/my.cnf
server-id = 3 #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, turn on the relay log, and 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 filemysql> change master to master_host='192.168.32.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; #Configure synchronization, note that the values ​​of master_log_file and master_log_pos must be consistent with those of the Mastermysql> start slave; #Start synchronization, if there is an error, execute reset slave;
mysql> show slave status\G #Check the slave status (make sure both IO and SQL threads are Yes, indicating normal synchronization)
Slave_IO_Running: Yes #Responsible for io communication with the host Slave_SQL_Running: Yes #Responsible for its own slave mysql process 

The possible reasons for Slave_IO_Running: No are: the network is not connected; there is a problem with the my.cnf configuration; the password, file name, and pos offset are incorrect; the firewall is not turned off.

(4) Verify the master-slave replication effect

①Enter execution on the primary server and create a database named "mysql01"

②Verify from two servers

5. Steps to 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. Through Amoeba, it can complete the functions of high availability, load balancing, and data slicing of multiple data sources.

(1) Configure the Amoeba server and install the Java environment (because Amoeba is developed based on jdk1.5, the official recommendation is to use jdk1.5 or 1.6, and higher versions are not recommended.)

[root@localhost ~]# cd /opt/
[root@localhost opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin
[root@localhost opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@localhost opt]# cd /usr/local/
[root@localhost local]# chmod +x jdk-6u14-linux-x64.bin
: : : : : : : : : : : : : : : 

(2) Install and configure Amoeba

[root@localhost ~]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# /usr/local/amoeba/bin/amoeba #Displaying amoeba start|stop indicates successful installation

(3) Configure Amowba server read-write separation and two slaves to balance read and write loads

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

The Master server (192.168.32.128), Slave1 server (192.168.32.133) and Slave2 server (192.168.32.134) are all configured:

mysql> grant all on *.* to test@'192.168.32.%' identified by '123.com';

②Edit the amoeba.xml configuration file in the Amoeba server

[root@localhost ~]# cd /usr/local/amoeba/conf/
[root@localhost conf]# cp amoeba.xml amoeba.xml.bak #Backup [root@localhost conf]# vim amoeba.xml #Modify amoeba configuration file #30 lines to modify <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

[root@localhost conf]# cp dbServers.xml dbServers.xml.bak #Backup [root@localhost conf]# vim dbServers.xml #Modify the database configuration file #23 line comment out effect: 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>
#Uncomment lines 28-30<!-- mysql password -->
<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.32.128</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.132.133</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.32.134</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

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

[root@localhost conf]# netstat -antulp | grep 8066

6. Test read-write separation

(1) Client configuration

[root@localhost ~]# yum install -y mariadb-server mariadb #Use YUM to quickly install the MySQL virtual client [root@localhost ~]# systemctl start mariadb.service
[root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.32.135 -P8066 #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--slave server

(2) Create a table on the Master server and synchronize it to the two slave servers

mysql> use xm;
mysql> create table test (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

mysql> stop slave; #Close synchronizationmysql> use xm;

①Configure on slave1

mysql> insert into test values('1','zhangsan','this_is_slave1');

②Configure on slave2

mysql> insert into test values('2','lisi','this_is_slave2');

(4) Return to the main server and insert other content

mysql> insert into test values('3','wangwu','this_is_master');

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

mysql> use xm;
mysql> select * from test; #The client will read data from slave1 and slave2 respectively, and only the data added on the two slave servers will be displayed, not the data added on the master server

(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.

mysql>insert into test values('4','kanglong','this_is_client'); #This data is only available on the primary server

(7) Execute start slave on both slave servers to synchronize the data added on the master server.

mysql> start slave;

Summarize

1. The principle of master-slave replication: The master server adds, deletes, modifies, and updates data, and after the transaction is committed, it is written to the binary file. The I/O thread of the slave server requests to detect the binary file of the master server. When there is new data, the master server will use the dump thread to send the binary file to the memory of the slave service I/O thread and write it to the relay log. The SQL thread will listen to the relay log file, read the relay log file when there is new data, and replay the data to the slave server database.

2. The working process of master-slave replication is based on logs: master binary log and slave relay log; request method: I/O thread, dump thread, SQL thread.

3. Read-write separation means writing only on the master server and reading only on the slave server.

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!