MySQL master-slave replication and read-write separation

1, Understand MySQL master-slave replication

1. Supported replication types

STATEMENT: STATEMENT based replication. Execute sql statements on the server and the same statements on the slave server. mysql adopts STATEMENT based replication by default, with high execution efficiency.
ROW: ROW based replication. Copy the changed content instead of executing the command from the server.
MIXED: MIXED type replication. By default, statement based replication is adopted. Once it is found that statement based replication cannot be accurately copied, row based replication will be adopted.

2. Replication principle and working process of master-slave replication

principle
Replication type of MySQL
Statement based replication
Row based replication
Mixed type replication

Process:
The Master node records the data changes into a bin log. When the data on the Master changes, the changes are written into the bin log.

The Slave node will detect whether the binary log of the Master has changed within a certain time interval. If it has changed, it will start an I/O thread 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 to the 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, so that its data is consistent with that of the Master node, Finally, the I/O thread and SQL thread will go to sleep and wait for the next wake-up.

Note:
Relay logs are usually located in OS Cache, so the overhead of relay log is very small.
An important limitation of the replication process is that replication Slave It is serialized, that is to say Master Parallel update operations on cannot be performed on Slave Parallel operation on.

3. Reasons for high delay of MySQL master-slave replication

The master server is highly concurrent, forming a large number of transactions
Network delay
Caused by master-slave hardware devices (cpu master frequency, memory io, hard disk io)
It is not synchronous replication, but asynchronous replication

4. Solutions to high latency of master-slave replication

Optimize MySQL parameters from the library. For example, increase innodb_buffer_pool_size, which allows more operations to be completed in MySQL memory and reduces disk operations
Use high-performance hosts (high-performance cpu and large memory) from the library, avoid using virtual virtual hosts, and use physical hosts to improve I/O throughput
Using SSD disks from the library
Network optimization to avoid synchronization across machine rooms

2, Understand MySQL read / write separation

1. What is read-write separation

The basic principle of read-write separation is to let the primary database handle transactional addition, deletion and modification operations (INSERT, UPDATE and DELETE), while the secondary database handles query (SELECT) operations. Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.

2.MySQL read-write separation 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 operations and the select query from the database. Database replication is used to synchronize changes caused by transactional operations on the master database to the slave database in the cluster.

3. Why do you want to separate reading and writing

Because the database write operation (writing 10000 pieces of data may take 3 minutes) is time-consuming. However, the read operation of the database (reading 10000 pieces of data may take only 5 seconds) is very fast. Therefore, read-write separation solves the problem that the writing performance of the database is too low, which affects the efficiency of query.

4. When should I separate reading from writing

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.

5. Master slave replication and read / write separation

In the actual production environment, if the read and write of the database are in the same database server, it can not meet the actual needs in terms of security, high availability and high concurrency. Therefore, master-slave replication is used to synchronize data, and then read-write separation is used to improve the concurrent load capacity of the database. 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.

6. Common MySQL read / write separation methods

1. 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. The advantage is that the performance is better, because it is implemented in the program code, and there is no need to increase the cost of additional hardware equipment. The disadvantage is that it needs developers to implement it, and the operation and maintenance personnel have no way to start.
It should be noted that 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.

2. 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 proxy applications:

(1)MySQL-Proxy
MySQL proxy is an open source MySQL project, and SQL judgment is performed through its own lua script.

(2)Atlas
It is a data middle tier project based on MySQL protocol developed and maintained by the infrastructure team of Qihoo 360's Web Platform Department. It optimizes mysql-proxy0.8.2 and adds some new features. The MySQL service running in Atlas in 360 supports several billion read and write requests every day and supports 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.
Since a large number of Lua scripts need to be written to use MySQL proxy, 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. Amoeba is a very easy to use and portable software. Therefore, it is widely used in the agent layer of database in production environment.

3, MySQL master-slave replication architecture

1. Server configuration

The server	host name	IP	application
Master server	master	192.168.9.150	mysql5.7
Slave1 server	slave1	192.168.9.151	mysql5.7
Slave2 server	slave2	192.168.9.152	mysql5.7
Amoeba server	amoeba	192.168.9.153	jdk1.6,Amoeba
Client server	client	192.168.9.154	mysql

2. Turn off firewall and selinux on all hosts

systemctl stop firewalld && systemctl disable firewalld
setenforce 0

3. mysql master-slave server time synchronization

master server (192.168.9.150)

yum -y install ntp

vim /etc/ntp.conf
#--Add--
server 192.168.9.2							#Set the local clock source. Pay attention to modifying your own network segment
fudge 192.168.9.2 stratum 8				#Set the time level to 8 (limited to 15)

service ntpd start

slave1 server (192.168.9.151)

yum -y install ntp ntpdate

service ntpd start

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.9.150           #Time synchronization

slave2 server (192.168.9.152)

yum -y install ntp ntpdate

service ntpd start
/usr/sbin/ntpdate 192.168.9.150

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.9.150

4. mysql configuration of master server

vim /etc/my.cnf
#Add the following configuration
server-id = 1			       #Define the server ID. each host cannot be the same
log-bin=master-bin		       #The primary server turns on the binary log
binlog_format = MIXED	       #MIXED mixed mode is used this time
log-slave-updates=true	       #Allow binary logs to be updated from the server

systemctl restart mysqld

mysql -uroot -p123456
#Set the slave server account and authorize
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.9.%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;
mysql> use mysql;
mysql> select user,host,authentication_string from user;
+-----------+-------------+-------------------------------------------+
| user      | host        | authentication_string                     |
+-----------+-------------+-------------------------------------------+
| root      | localhost   |                                           |
| mysql.sys | localhost   | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| myslave   | 192.168.9.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      602 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# The File column displays the log name and the Position column displays the offset

5. mysql configuration from the server

slave1

vim /etc/my.cnf
server-id = 2					              #Modify. Note that the id cannot be the same as that of other hosts
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
relay_log_recovery = 1                        #Options
#After the slave goes down from the database, if the relay log is damaged and some relay logs are not processed, all unexecuted relay logs will be automatically discarded and the logs will be obtained from the master again, so as to ensure the integrity of the relay log. By default, this function is turned off, and relay will be enabled_ log_ When the value of recovery is set to 1, this function can be enabled on the slave library. It is recommended to enable it.

systemctl restart mysqld

mysql -u root -p123456
mysql> CHANGE master to master_host='192.168.9.150',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
#To configure synchronization, pay attention to the master_log_file and Master_ log_ The value of POS should be consistent with that of Master query
mysql> start slave;
#Start synchronization, and execute reset slave in case of error;

mysql> show slave status\G;			#Check the Slave status and ensure that both IO and SQL threads are Yes, which means that the synchronization is normal
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.9.150
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 603
               Relay_Log_File: relay-log-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes		#Responsible for IO communication with host
            Slave_SQL_Running: Yes		#Be responsible for your own slave mysql process
					..........

slave2

vim /etc/my.cnf
server-id = 3		                    #Note that it is different from master and slave1
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
relay_log_recovery = 1

systemctl restart mysqld

mysql -uroot -p123456
mysql> CHANGE master to master_host='192.168.9.150',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
mysql> start slave;
mysql> show slave status\G;

General slave_ IO_ Running: possibility of no

The network is blocked
There is a problem with my.cnf configuration
Incorrect password, file name, pos offset
The firewall is not turned off

6. Verify the master-slave replication effect