MySQL Master slave replication is separated from reading and writing

summary

• In enterprise applications , Mature businesses usually have a large amount of data
• A single MySQL In security 、 High availability and high concurrency can not meet the actual needs
• Configure multiple master-slave database servers to realize read-write separation

1. Master slave replication principle

1. Generate two threads from the library , One I/O Threads , One SQL Threads ;
2. i/o Thread to request main library Of binlog, And will get binlog The log says relay log（ relay logs ） In file ;
3. The main library will generate a log dump Threads , Used to give to the slave i/o Thread transfer binlog;
4. xSQL Threads , Will read relay log Log in file , And parse it into concrete operation , To achieve master-slave operation consistency , And the final data is consistent ;

2. The principle of separation of reading and writing

Read / write separation , The basic principle is to make the primary database deal with transaction 、 Change 、 Delete operation （INSERT、UPDATE、DELETE）, And processing from the database SELECT Query operation . Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster .

3. Why do we do read-write separation

1. Because the database “ Write ”（ Write 10000 This piece of data may be 3 minute ） The operation is time-consuming .
2. But the database “ read ”（ read 10000 A piece of data may only 5 Second ）.
3. So read write separation , The solution is , Database writing , Affect the efficiency of the query .

4. When to separate reading and writing

1. The database does not have to be read-write separated , If the program uses more databases , And less updates , If there are many queries, we will consider using .
2. Using database master-slave synchronization , Then, the database pressure can be shared through read-write separation , Improve performance .

5. Master slave replication is separated from reading and writing

In the actual production environment , Read and write to the database in the same database server , It can't meet the actual needs . Whether it's in security 、 High availability or high concurrency can not meet the actual needs . therefore , Synchronize data through master-slave replication , Then through read-write separation to improve the concurrent load capacity of the database . It's kind of like rsync, But here's the difference rsync Is to back up disk files , and mysql Master-slave replication is the replication of data in the database 、 Statement for backup .

6.MySQL Supported replication types

1. STATEMENT： Statement based replication . Execute on server sql sentence , Execute the same statement on the slave server ,mysql Statement based replication is used by default , High execution efficiency .
   （
2. ROW： Line based replication . Copy the changes , Instead of executing the command on the slave server .
3. MIXED： Hybrid type of replication . Statement based replication is used by default , Once it is found that statement based cannot be accurately copied , You'll use row based replication .

7. The working process of master-slave replication

1. Before each transaction updates the data ,Master In binary log （Binary log） Record these changes . After writing the binary log ,Master Notify the storage engine to commit the transaction .
2. Slave take Master Copy the log to it （Relay log）. First slave Start a worker thread （I/O）,I/O The thread is in Master Open a normal connection on , And then start Binlog dump process.Binlog dump process from Master Read events in binary log of , If you've caught up with Master, It will sleep and wait Master Create new events ,I/O Threads write these events to the relay log .
3. SQL slave thread（SQL From thread ） The last step in the process ,SQL Thread reads events from relay log , And replays the events and updates Slave data , Make it relate to Master The data in is consistent , As long as the thread and I/O Threads are consistent , The relay log is usually located at OS In cache , So the overhead of relay log is very small .
   There is an important limitation to the replication process , That is, to copy in Slave It's serial , in other words Master The parallel update operation on cannot be in Slave Up parallel operation .

Replication framework

common MySQL The separation of reading and writing can be divided into the following two types ：

1. Based on the internal implementation of program code
   In the code according to select、insert Route classification , This kind of method is also the most widely used method in production environment .
   The advantage is better performance , Because it is implemented in program code , There is no need to add additional equipment for hardware expenses ; The disadvantage is that it needs developers to implement , Operation and maintenance personnel have no way to start .
   However, not all applications are suitable for reading and writing separation in program code , Like some large and complex Java application , If the separation of reading and writing is realized in the program code, the code will be greatly changed .
2. Based on the intermediate agent layer
   The proxy is usually 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 procedures .
   （1）MySQL-Proxy.MySQL-Proxy by MySQL Open source project , Through its own lua The script goes on SQL Judge .
   （2）Atlas. It's from Qihoo. 360 Of Web Platform Department infrastructure team development and maintenance based on MySQL Data middle tier project of the protocol . It's in mysql-proxy 0.8.2 Based on version , It's optimized , Added some new features .360 For internal use Atlas Running mysql Business , It carries billions of read and write requests every day . Support things and stored procedures .
3. Amoeba. Developed by Chen Siru , The author worked for Alibaba . The procedure consists of Java Language development , Alibaba uses it in the production environment . But it does not support transactions and stored procedures .

Due to the use MySQL Proxy Need to write a lot of Lua Script , these Lua Not ready-made , But you need to write it yourself . This is not familiar with MySQL Proxy Built in variables and MySQL Protocol It's very difficult for people .

Amoeba Is a very easy to use 、 Highly portable software . Therefore, it is widely used in the agent layer of database in production environment .

8.Amoeba The configuration file

• Master profile ： $AMOEBA_HOME/conf/amoeba.xml, Used to configure Amoeba Basic parameters of the service , Such as Amoeba The host address 、 port 、 authentication 、 The user name used for the connection 、 password 、 Number of threads 、 Timeout time 、 Location of other configuration files, etc .
• Database service profile ： $AMOEBA_HOME/conf/dbServers.xml, For storage and configuration Amoeba Information about the database server being represented , Such as : host IP、 port 、 user name 、 Password etc. .
• Shred the rules configuration file ： $AMOEBA_HOME/conf/rule.xml, Used to configure the sharding rules .
• Database function configuration file ： $AMOEBA_HOME/conf/functionMap.xml, The processing method used to configure the database functions ,Amoeba The methods in this configuration file are used to parse the database functions .
• Shred the rule function configuration file ： $AMOEBA_HOME/conf/ruleFunctionMap.xml, To configure the handling of the user-defined functions used in the sharding rule .
• Access the rules configuration file ： $AMOEBA_HOME/conf/access_list.conf, Used to authorize or disable certain servers IP visit Amoeba.
• Log specification profile ： $AMOEBA_HOME/conf/log4j.xml, Used to configure Amoeba The level and manner of the output log .

9. Actual operation

1. Environment configuration

master The server ： 192.168.254.10 mysql5.7
slave1 The server ： 192.168.254.20 mysql5.7
slave2 The server ： 192.168.254.30 mysql5.7
Amoeba The server ： 192.168.254.40 jdk1.6、Amoeba
client The server ： 192.168.254.50 mysql

2. Initial environmental preparation

systemctl stop firewalld
setenforce 0

build mysql Master slave copy

Set up time synchronization

[[email protected] ~]#yum install ntp -y

Modify the configuration file

# Insert... At the end of the line after opening the file 
vim /etc/ntp.conf
server 127.127.254.0 # Set the local clock source 
fudge 127.127.254.0 stratum 8 # Set the time level to 8 Restriction on 15 within 

Opening service

service ntpd start

4. Configure slave （192.168.254.20、192.168.254.30）

 Configure slave
[[email protected] ~]# yum install ntp -y
[[email protected] ~]# yum install ntpdate -y
# Install synchronization service 
[[email protected] ~]# service ntpd start
# Opening service 
Redirecting to /bin/systemctl start ntpd.service
[[email protected] ~]# /usr/sbin/ntpdate 192.168.254.10
# Perform synchronization 
23 Dec 11:18:18 ntpdate[2534]: the NTP socket is in use, exiting
[[email protected] ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.254.10

slave2:192.168.254.30 Same as above

5. Configure the primary server （192.168.254.10）

[[email protected] ~]#vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin=master-bin
# Turn on binary log 
binlog_format=MIXED
# Binary log format 
log-slave-updates=true
# Turn on synchronization from the server 
log-bin=master-bin
binlog_format=MIXED
log-slave-updates=true

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.254.%' IDENTIFIED BY '123123';
# Authorize the slave server , Allow all users of this network segment , send myslave user 123123 Log in with a password and use any table in the library 
FLUSH PRIVILEGES;
# Save the user and permission configuration to memory 
show master status;
# see master The binary log being used by the server and the current execution binary log location 
#File The column displays the log name ,Fosition Column shows the offset 

6. From the server mysql To configure （192.168.254.20、192.168.254.30）

vim /etc/my.cnf
server-id = 2
# modify , Be careful id And Master Different , Two Slave Of id It has to be different
relay-log=relay-log-bin
# add to , Turn on relay log , Synchronize log records from the primary server to the local
relay-log-index=slave-relay-bin.index
# add to , Define the location and name of the relay log file

systemctl restart mysqld
mysql -u root -p
change master to master_host='192.168.254.10' , master_user='myslave',master_password='123123',master_log_file='master-bin.000003',master_log_pos=604;
# Configuration synchronization , Be careful master_log_file and master_log_pos The value of should be the same as Master Consistency of query , Here is an example , Everyone's is different 
start slave;
# Start the synchronization , If an error is reported, execute reset slave;
show slave status\G
# see Slave state 
// Make sure IO and SQL Threads are Yes, It means that the synchronization is normal .
Slave_IO_Running: Yes
# Responsible for communication with the host io signal communication 
Slave_SQL_Running: Yes
# Be responsible for your own slave mysql process 

commonly Slave_IO_Running: No The possibility of ：

1、 The Internet is not working

2、my.cnf Configuration problem

3、 password 、file file name 、pos The offset is not right

4、 The firewall is not closed

Verify the master-slave replication effect

Execute... On the primary server

create database db_test;

Go and see from the server

show databases;

server1:

server2:

build MySQL Read / write separation

Amoeba（192.168.254.40）

1. install Java Environmental Science

 because Amoeba Based on jdk1.5 Developed , Therefore, it is officially recommended to use jdk1.5 or 1.6 edition , Higher versions are not recommended .
take jdk-6u14-linux-x64.bin and amoeba-mysql-binary-2.2.0.tar.gz.0 Upload to /opt Under the table of contents .
cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
# Copy the executable to /usr/local Under the table of contents 
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
# Use chmod +x Empower them 
./jdk-6u14-linux-x64.bin
# Execute the document , Press the space to the last line , Press yes, Press enter
mv jdk1.6.0_14/ /usr/local/jdk1.6
# take jdk Directory move to /usr/local Directory and renamed jdk1.6
vim /etc/profile
# Edit global profile 
# Add... 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
source /etc/profile
# Execute global configuration file 
java -version
# see java Version information 

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 displayed amoeba start|stop Description installation successful

3. To configure Amoeba Read / write separation , Two Slave Read load balancing

Now? Master、slave1、slave2 Of mysql Open permissions to Amoeba visit

grant all on *.* to test@'192.168.254.%' identified by '123.com';

amoeba Server configuration amoeba service

cd /usr/local/amoeba/conf/
cp amoeba.xml amoeba.xml.bak
vim amoeba.xml
# modify amoeba The configuration file 

30 modify

<property name="user">amoeba</property>

32 modify

<property name="password">123123</property>

115 modify

<property name="defaultPool">master</property>

117 Remove annotations –

<property name="writePool">master</property>
<property name="readPool">slaves</property>

---

cp dbServers.xml dbServers.xml.bak
vim dbServers.xml # Modify the database configuration file

23 Comment out
effect ： Default entry test library in case mysql There is no test library , Will report a mistake

<!-- mysql schema
<property name="schema">test</property>
-->

26 modify

<!-- mysql user -->
<property name="user">test</property>

28-30 Remove annotations

<property name="password">123.com</property>

45 modify , Set the name of the primary server Master

<dbServer name="master" parent="abstractServer">

48 modify , Set the address of the primary server

<property name="ipAddress">192.168.184.10</property>

52 modify , Set the name of the slave server slave1

<dbServer name="slave1" parent="abstractServer">

55 modify , Set up slave server 1 The address of

<property name="ipAddress">192.168.184.30</property>

58 Copy above 6 Line paste , Set up slave server 2 The name of slave2 And address

<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.184.40</property>

65 modify

<dbServer name="slaves" virtual="true">

71 modify

<property name="poolNames">slave1,slave2</property>
/usr/local/amoeba/bin/amoeba start& # start-up Amoeba Software , Press ctrl+c return
netstat -anpt | grep java # see 8066 Whether the port is open , The default port is TCP 8066

3、 Test read-write separation

Test on the client server ：
Use yum Fast installation MySQL Virtual client

yum install -y mysql mysql-server
mysql -u amoeba -p123123 -h 192.168.184.20 -P8066

adopt amoeba Server proxy access mysql , Connect through the client mysql Only the main service will record the data written after , Then synchronize to the slave server

---

On the main server

use db_test;
create table test (id int(10),name varchar(10),address varchar(20));

Two from the server

stop slave; # Turn off synchronization
use db_test;

stay slave1 On

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

stay slave2 On

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

On the main server

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

On the client server

use db_test;
select * from test; // The client will report to slave1 and slave2 Reading data , Only the data added from the two servers is displayed , No data added on the primary server
insert into test values('4','qianqi','this_is_client'); // Only the primary server has this data

Execute on two slave servers start slave; You can synchronize the data added on the primary server
start slave;