catalogue

1, Case overview

2, Principle of mysql master-slave replication

1. Replication type of mysql

2. Working principle of mysql master-slave replication

3, mysql read-write separation principle

4, Deploy master-slave synchronization

1. Set time synchronization between master and slave servers

2. Server installation mysql

3. Configure database rules

5, Deploy read write separation

1. Install amoeba

2. Configure read write separation

Test the write of master server

Vi. summary

1, Case overview

• In enterprise applications, mature businesses usually have a large amount of data
• A single Mysql cannot meet the actual needs in terms of security, high availability and high concurrency
• Configure multiple master-slave database servers to realize read-write separation

2, Principle of mysql master-slave replication

1. Replication type of mysql

• Statement based replication
• Row based replication
• Mixed type replication

2. Working principle of mysql master-slave replication

• The master database of the master server records the operations to the Binary log through the dump thread
• Start the I/O thread from the server and send the synchronization log request to the master server
• The master server sends the binary log content to the slave server
• Synchronize binary logging operations from the server to the relay log (stored in the cache of the slave server)
• The sql thread in the slave server writes the operation recorded in the relay log to the slave server database after it is executed from the server.
   

 

3, mysql read-write separation principle

1. 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 queries and the slave database handle select queries. Database replication is used to synchronize the changes caused by transactional queries on the master database to the slave database in the cluster.

2. Why do we do read-write separation

Because the "write" (writing 10000 pieces of data may take 3 minutes) operation of the database is time-consuming.

But 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

PS: when to use read-write separation

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.

3. Implementation mode

Common MySQL read-write separation can be divided into the following two types:

① 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 at present.

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.

② 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 project of MySQL. SQL judgment is made through its own lua script.

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

Amoeba: it is a proxy with MySQL as the underlying data storage and corresponding MySQL protocol interface, nicknamed amoeba

When the read request is sent to the slave server, the polling scheduling algorithm is adopted

amoeba is written in java language, and the configuration file is xml

amoeba is mainly responsible for an external proxy IP

When accessing this IP, if the request sent is a "write" request, it will be forwarded to the primary server

When the sent request is "read", it will be forwarded to the slave server through scheduling, and the polling algorithm will be used to allocate it to two slave servers in turn

amoeba can be regarded as a scheduler. If the main server hangs (single point of failure), MHA will solve this problem

 

4, Deploy master-slave synchronization

1. Set time synchronization between master and slave servers

① . main server (192.168.174.10)

yum -y install ntp

vim /etc/ntp.conf
 Last line add
fudge 127.127.184.0 stratum 10					#Set time limit to 15 in level
server 127.127.184.0							#Set the local clock source. Pay attention to modifying the network segment


systemctl start ntpd

 

 

②. Slave server (192.168.174.7192.168.184.27)

yum -y install ntp ntpdate

systemctl start ntpd
/usr/sbin/ntpdate 192.168.184.10			#Time synchronization, pointing to the Master server IP

systemctl stop firewalld 
setenforce 0

 

Two slave servers operate in the same way

2. Server installation mysql

establish mysql user
useradd -s /sbin/nologin mysql

#Install dependent packages
yum -y install \
gcc \
gcc-c++ \
make \
pcre-devel \
expat-devel \
perl \
ncurses-devel \
autoconf \
cmake

#compile
cd /opt/mysql-5.7.20   
cmake  \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DSYSCONFIDIR=/etc \
-DMYSQL_DATADIR=/home/mysql/ \
-DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock

make && make install



#Support environment variables (mysql command)
echo export PATH=$PATH:/usr/local/mysql/bin/ >>/etc/profile
export PATH=$PATH:/usr/local/mysql/bin/
source /etc/profile

cd /opt/mysql-5.6.26
cp support-files/my-default.cnf /etc/my.cnf

In order to ensure safety, according to the principle of minimum authority myql directory right
chown -R mysql.mysql /usr/local/mysql/

cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add /etc/init.d/mysqld
chkconfig mysqld --level 35 on

vi /etc/init.d/mysqld
#Modify data file directory and working directory
basedir=/usr/local/mysql
datadir=/home/mysql

/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--ldata=/var/lib/mysql \
--basedir=/usr/local/mysql \
--datadir=/home/mysql


service mysqld start

3. Configure database rules

① . main server

vim /etc/my.cnf
server_id = 1
log_bin=master-bin							#Add, and the primary server opens the binary log
log_slave=updates=true						#Added to allow binary logs to be updated from the server

systemctl restart mysqld

mysql -u root -p
#Authorize the slave server to copy all tables of all databases (the first account)
grant replication slave on *.* to 'myslave'@'192.168.174.%' identified by '123456';			

flush privileges;

see master Database status
show master status\G

 

 

②. Slave server

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 the relay log file

systemctl restart mysqld

mysql -u root -p
change master to master_host='192.168.174.10' , master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=2385;
#Configure synchronization, pay attention to master_log_file and Master_ log_ The value of POS should be consistent with that of the Master query. Here is an example. Everyone's is different

start slave;						#Start synchronization, and execute reset slave in case of error;
show slave status\G					#View Slave status

ensure IO and SQL Threads are Yes，It means the synchronization is normal.
Slave_IO_Running: Yes				#Responsible for io communication with host
Slave_SQL_Running: Yes				#Be responsible for your own slave mysql process

 

Summary:

MySQL master-slave replication and read-write separation are closely related. This paper introduces MySQL master-slave replication (synchronization), and then the next article will introduce MySQL read-write separation

Here is a brief introduction to binary logs:

MySQL binary log is a binary file, which is mainly used to record modified data or SQL statements that may cause data changes

The binary log records all operations that change the MySQL database, and records other additional information such as statement occurrence time, execution time, operation data, etc., but it does not record SQL statements that do not modify data such as SELECT and SHOW.

Binary logs are mainly used for database recovery( ⭐⭐) And master-slave copy and audit operations.

PS: if synchronization fails, you can try to solve it by using the following methods

#Ignore the current error and perform the next synchronization

stop slave first;

① slave database: SET GLOBAL sql_slave_skip_counter=1;

START SLAVE;

Problem: the I/O thread is always connecting

First, look at the last error. The content of the error report is that the fulcrum cannot be found

stop slave first;

② slave database: CHANGE MASTER TO master_log_file=‘mysql-bin.000001’,master_log_pos=0;

First of all, this is because of the problem at the binlog location index. In the production environment, do not directly reset slave (delete the change master operation);

reset slave will restore the master-slave synchronized files and locations to the initial state. It's ok if there is no data at first. If there is data, it's equivalent to restarting synchronization, and some problems may occur;

Generally, master-slave synchronization is required for subsequent data to realize master-slave synchronization. For old data, you can use the database synchronization tool to synchronize the database first, and then conduct master-slave synchronization after completion;

OK, the correct way to solve the above problems is:

1. Open the master server and enter mysql

2. Execute flush logs// At this time, the master server will re create a binlog file;

3. Execute show master status\G on the main service; The display is as follows:

4. Go to mysql from the server;

5.stop slave;

6.change master to master_log_file='mysql-bin.000012',master_log_pos=154;// The file and POS here are displayed by the master server above.

7.start slave; // It should be OK at this time

8.show slave status\G;

5, Deploy read write separation

1. Install amoeba

take jdk-6u14-linux-x64.bin and amoeba-mysql-binary-2.2.0.tar.gz.0 Upload to/opt Directory.

cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/

cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin 
./jdk-6u14-linux-x64.bin
 Press space to the last line
 Press yes，Press enter

mv jdk1.6.0_14/ /usr/local/jdk1.6

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

source /etc/profile
java -version

##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, the installation is successful

 

2. Configure read write separation

① Configure amoeba docking on the server

grant all on *.* to test@'192.168.174.%' identified by '123456';

② configure server docking on amoeba server

##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, the installation is successful

##to configure Amoeba Read write separation, two Slave Read load balancing##
#First, open the permission to Amoeba on the mysql of Master, Slave1 and Slave2
grant all on *.* to 'test'@'192.168.174.%' identified by '123456';

 

② configure server docking on amoeba server

cd /usr/local/amoeba/conf/

cp amoeba.xml{,.bak}
vim amoeba.xml									#Modify amoeba configuration file

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

32 modify
<property name="password">010230</property>

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

117 Remove comments–
<property name="writePool">master</property>
<property name="readPool">slaves</property>


cp dbServers.xml{,.bak}
vim dbServers.xml								#Modify database configuration file

23 modify
<property name="schema">mysql</property>

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

28-30 Remove comments
<property name="password">010230</property>

45 Modify and set the name of the master server Master
<dbServer name="master"  parent="abstractServer">

48 Primary server address, modify settings
<property name="ipAddress">192.168.184.10</property>

52 Modify and set the name of the slave server slave1
<dbServer name="slave1"  parent="abstractServer">

55 Modify and set the address of slave server 1
<property name="ipAddress">192.168.184.20</property>

58 Copy and paste the above 6 lines and set the name from server 2 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 Amoeba software and press ctrl+c to return
netstat -anpt | grep java							#Check whether port 8066 is enabled. The default port is TCP 8066

 

 

 

 

 

 

 

3. Test read-write separation

download mariadb client
yum -y install mariadb

systemcrl stop firewalld
setenforce 0

Remote login amoeba The server
mysql -u amoeba -p123456-h 192.168.174.27 -P8066

Test on client:
yum install -y mysql mysql-server

mysql -u amoeba -p123456 -h 192.168.174.10 -P8066	

adopt amoeba Server proxy access mysql ，Connecting through the client mysql Only the master service will record the data written later, and then synchronize it to the slave server

Test the write of master server

From server:

Primary server:

 

From server:

Test the read of slave server

 

 

Vi. summary

1. Master slave synchronization principle

• Through amoeba proxy server, it can write only on the master server and read only on the slave service;
• The main database handles transactional queries, and the select query is processed from the database;
• Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster

2. How to check whether master-slave synchronization 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 IO threads, as well as the IP address, port and transaction start number of the master server,
• When slave_io_running and slave_ sql_ When running is displayed as yes, it indicates that the master-slave synchronization status is successful

3. If I/O and SQL are not yes, how do you troubleshoot them

• First, eliminate the network problem and use the ping command to check whether the slave service can communicate with the master server
• Furthermore, check whether the firewall and core protection are turned off
• Then check whether the slave in the slave server is enabled
• Whether the server IDs of the two slave servers are the same, so that only one can be connected
• master_log_file and Master_ log_ Whether the value of POS is consistent with that of Master query

4. What information can show slave status see (more important)

• Status information of IO thread
• IP address, port and transaction start location of master server
• The latest error information and error location, etc

5. What are the possibilities of slow (delayed) master-slave replication

• The load of the main server is too large, which is occupied by multiple sleep or zombie threads, resulting in excessive system load
• The slave library hardware is worse than the master library, resulting in replication delay
• Master slave replication is a single thread. If the write concurrency of the master library is too large to be transmitted to the slave library, it will lead to delay.
• Too many slow SQL statements
• Network delay