MySQL Master-Slave Copy and Read-Write Separation

Summary

• In enterprise applications, mature business usually has a large amount of data
• Single MySQL does not meet practical requirements in terms of security, high availability, and high concurrency
• Configure multiple master-slave database servers for read-write separation

1. Principle of master-slave replication

1. Generate two threads from the library, one I/O thread and one SQL thread;
2. The i/o thread requests the binlog of the primary library and writes the resulting binlog log to the relay log file;
3. The Primary Library generates a log dump thread to pass binlog from the library i/o thread;
4. The xSQL thread reads the logs in the relay log file and parses them into specific operations to achieve master-slave consistency and final data consistency.

2. Principle of Read-Write Separation

The basic principle of read-write separation is that the primary database handles transactional add, change, delete operations (INSERT, UPDATE, DELETE) and SELECT query operations from the database. Database replication is used to synchronize changes caused by transactional operations to the secondary database in the cluster.

3. Why read-write separation

1. Because the database "write" (writing 10,000 pieces of data may take 3 minutes) is time consuming.
2. But the database "reads" (it may only take 5 seconds to read 10,000 pieces of data).
3. So read-write separation solves the problem that database writing affects the efficiency of query.

4. When do I need to read-write separation

1. The database does not necessarily have to be read-write separated. If a program uses more databases, updates are less, and queries are more, it will be considered.
2. Using database master-slave synchronization and read-write separation can share database pressure and improve performance.

5. Master-Slave Copy and Read-Write Separation

In the actual production environment, both reading and writing to the database are in the same database server, which cannot meet the actual needs. Whether it is in security, high availability or high concurrency, it is completely unable to meet the actual needs. Therefore, data is synchronized by master-slave replication, and the concurrent load capacity of the database is enhanced by read-write separation. A little like rsync, but the difference is that Rsync backs up disk files, while mysql master-slave replication backs up data and statements in the database.

6. Replication types supported by MySQL

1. STATEMENT: Statement-based replication. Execute sql statements on the server and execute the same statements from the server. mysql uses statement-based replication by default, which is efficient.
   (
2. ROW: Row-based replication. Copy the changes over instead of executing the command from the server once.
3. MIXED: Mixed type of replication. Statement-based replication is used by default, and line-based replication is used whenever it is found that statement-based replication cannot be accurately replicated.

7. The working process of master-slave replication

1. Master records these changes in the Binary log before each transaction updates the data. When the Binary log is written, Master notifies the storage engine to commit the transaction.
2. Slave copies the Master to its Relay log. First slave starts a worker thread (I/O), the I/O thread opens a normal connection on Master and begins the Binlog dump process. Binlog dump process reads events from Master's binary logs, and if it has followed Master, it sleeps and waits for Master to generate new events, which the I/O thread writes to the relay log.
3. The last step of the process is handled by the SQL slave thread, which reads events from the relay log and replays them to update Slave data to match the data in Master. As long as the thread is consistent with the I/O thread, the relay log is usually in the OS cache, so the cost of relay logging is minimal.
   An important limitation of the replication process is that replication is serialized on Slave, which means that parallel update operations on Master cannot operate on Slave side by side.

Copy Frame

The common MySQL read-write separation is divided into the following two types:

1. Internal implementation based on program code
   Routing is classified in code by select, insert, which is also the most widely used method in production environments.
   The advantage is better performance because it is implemented in program code and does not require additional equipment for hardware costs; The disadvantage is that developers are required to implement it, and operations and maintenance personnel are unable to start.
   However, not all applications are suited to achieve read-write separation in program code, such as some large and complex Java applications, which will change the code a lot if read-write separation is implemented in program code.
2. Implementation based on intermediate proxy layer
   The proxy is generally located between the client and the server. The proxy server forwards to the back-end database after receiving the client's request through judgment, which is the following representative program.
   (1) MySQL-Proxy. MySQL-Proxy is an open source MySQL project, which makes SQL judgment through its own lua script.
   (2) Atlas. A MySQL protocol-based data intermediate project developed and maintained by the Infrastructure Team of Qihoo 360's Web Platform Department. It is optimized on the basis of mysql-proxy version 0.8.2 and adds some new features. The Mysql business running inside 360 uses Atlas and hosts billions of read and write requests per day. Stored procedure.
3. Amoeba. Developed by Chen Siru, the author once worked in Alibaba. The program was developed in the Java language and used in the production environment by Alibaba. However, it does not support transactions and stored procedures.

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

Amoeba is a very easy-to-use, highly portable software. Therefore, it is widely used in the proxy layer of the database in the production environment.

8.Amoeba Profile

• Main Profile: $AMOEBA_HOME/conf/amoeba.xml, which configures the basic parameters of the Amoeba service, such as Amoeba host address, port, authentication method, user name used for connection, password, number of threads, timeout, location of other configuration files, and so on.
• Database Service Profile: $AMOEBA_HOME/conf/dbServers.xml to store and configure information about the database server proxied by Amoeba, such as host IP, port, user name, password, and so on.
• Sharing Rules Profile: $AMOEBA_HOME/conf/rule.xml to configure the slicing rules.
• Database Function Profile: $AMOEBA_HOME/conf/functionMap.xml, which is used to configure how database functions are handled. Amoeba will use the method in the configuration file to parse database functions.
• Sharing Rule Function Profile: $AMOEBA_HOME/conf/ruleFunctionMap.xml, which configures the handling of user-defined functions used in the slicing rules.
• Access Rule Profile: $AMOEBA_HOME/conf/access_list.conf, used to authorize or prohibit certain server IP access to Amoeba.
• Log Specification Profile: $AMOEBA_HOME/conf/log4j.xml to configure the level and manner of Amoeba output logs.

9. Actual operations

1. Environment Configuration

master Server: 192.168.254.10   mysql5.7
slave1 Server: 192.168.254.20   mysql5.7
slave2 Server: 192.168.254.30   mysql5.7
Amoeba Server: 192.168.254.40   jdk1.6,Amoeba
 Client Server: 192.168.254.50   mysql

2. Initial environmental preparation

systemctl stop firewalld
setenforce 0

Set up mysql master-slave replication

Build Time Synchronization

[root@localhost ~]#yum install ntp -y

Modify Profile

#Insert at end of line after opening file
vim /etc/ntp.conf  
server 127.127.254.0   #Set Local Clock Source
fudge 127.127.254.0 stratum 8  #Set the time level to 8 and limit to 15

Open Service

service ntpd start

4. Configure slave servers (192.168.254.20, 192.168.254.30)

Configure Slave Server
[root@localhost ~]# yum install ntp -y
[root@localhost ~]# yum install ntpdate -y
#Install Synchronization Service
[root@localhost ~]# service ntpd start
#Open Service
Redirecting to /bin/systemctl start ntpd.service
[root@localhost ~]# /usr/sbin/ntpdate 192.168.254.10
#Perform synchronization
23 Dec 11:18:18 ntpdate[2534]: the NTP socket is in use, exiting
 
[root@localhost ~]# crontab -e
*/30 * * * *  /usr/sbin/ntpdate 192.168.254.10

slave2:192.168.254.30 Same as above

5. Configure the master server (192.168.254.10)

[root@localhost ~]#vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin=master-bin
#Open Binary Log
binlog_format=MIXED
#Binary Log Format
log-slave-updates=true
#Turn on synchronization from 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 the segment, log in with the myslave user 123123 password, and use any tables in the library

FLUSH PRIVILEGES;
#Save user and permission configurations in memory

show master status;
#View the binary log being used by the master server and the current execution binary log location

#File column shows log name, Fosition column shows offset

6. Configuring mysql from the server (192.168.254.20, 192.168.254.30)

vim /etc/my.cnf
server-id = 2						
#Modify, note that the id and Master are different, so are the IDs of the two Slave s

relay-log=relay-log-bin						
#Add, turn on relay logging, synchronize log files from primary server to local

relay-log-index=slave-relay-bin.index		
#Add, 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;
#Configure synchronization, note master_log_file and master_ Log_ The value of POS should be consistent with the Master query, here is an example, everyone is different

start slave;						
#Start synchronization and reset slave if there is an error;
show slave status\G					
#View Slave Status
//Ensure that both IO and SQL threads are Yes, indicating that 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

General Slave_ IO_ Running: Possibility of No:

1. Network is not accessible

2. my.cnf configuration problem

3. The password, file name, pos offset are incorrect

4. Firewall is not closed

Verify master-slave replication

Execution on primary server

create database db_test;

To view from the server

show databases;

server1:

server2:

Set up MySQL read-write separation

Amoeba(192.168.254.40)

1. Install Java Environment

because Amoeba Based on Yes jdk1.5 Developed, so officially recommended jdk1.5 Or 1.6 Version, higher version is not recommended.
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/
#Copy executable to / usr/local directory

cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin 
#Use chmod +x to give it weight

./jdk-6u14-linux-x64.bin
#Execute the file, press space to the last line, press yes, press enter

mv jdk1.6.0_14/ /usr/local/jdk1.6
#Move the JDK directory to the / usr/local directory and rename it jdk1.6

vim /etc/profile
#Edit Global Profile

#Add at 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 Profile
java -version
#View 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 amoeba start|stop is displayed, the installation is successful

3. Configure Amoeba read-write separation, two Slave read load balancing

Now open access to Amoeba on mysql for Master, slave1, slave2

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 profile

30 Modifications

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

32 Modifications

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

115 Modification

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

117 Remove Comments-

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

cp dbServers.xml dbServers.xml.bak

vim dbServers.xml								#Modify Database Configuration File

23 commented out
Role: Enter the test library by default to prevent errors when there is no test library in mysql

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

26 Modifications

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

28-30 Remove comments

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

45 Modify to set Master as the name of the master server

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

48 Modify to set the address of the primary server

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

52 Modify to set slave1 from the server name

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

55 Modify to set the address from Server 1

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

58 Copy the above 6 lines and paste, set slave2 name and address from Server 2

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

65 Modifications

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

71 Modification

<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 if port 8066 is open, default port is TCP 8066

3. Test Read-Write Separation

Test on the client server:
Quick installation of MySQL virtual client using yum

yum install -y mysql mysql-server

mysql -u amoeba -p123123 -h 192.168.184.20 -P8066		

Accessing mysql through amoeba server proxy, only the primary service records the data written after connecting mysql through the client, and then synchronizes it to the slave server

On the primary server

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

Two slave servers

stop slave;											#Turn off synchronization
use db_test;

On slave1

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

On slave2

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

On the primary server

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

On Client Server

use db_test;
select * from test;		//The client reads data to slave1 and slave2, respectively, and shows only two data added from the server, not the primary server.

insert into test values('4','qianqi','this_is_client');		//Only the primary server has this data

Execute the start slave on two more slaves from the server; This synchronizes the data added on the primary server
start slave;