Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to achieve master-slave replication and read-write separation of Mysql database". In daily operation, I believe that many people have doubts about how to achieve master-slave replication and read-write separation of Mysql database. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubt of "master-slave replication and read-write separation of Mysql database". Next, please follow the editor to study!

Preface

In the actual production environment, if the read and write of the MySQL database are operated in a database service, it can not meet the actual demand in terms of security, high availability, high concurrency and so on. Generally speaking, the scheme is to synchronize the data through master-slave replication (Master-Slave), and then to improve the concurrent load capacity of the database through read-write separation.

1. MySQL master-slave copy 1. Supported replication types

Statement-based replication (statement): execute sql statements on the server and the same statements on the slave server. Mysql defaults to statement-based replication and efficient row-based replication (row): copy the changed content instead of performing a mixed type of replication on the slave server (mixed): execute the sql statement on the server and the same statement on the slave server Mysql adopts statement-based replication by default, which has high execution efficiency.

two。 The working process of master-slave replication is based on logs.

Master binary log

Slave Relay Lo

3. Request mode

IPUBO thread

Dump thread

SQL thread

4. The principle of master-slave replication

The ① Master node records the changes in the data as a binary log (bin log), and when the data on the Master changes, the changes are written to the binary log

The ② Slave node will detect whether the binary log of Master has changed within a certain time interval.

If there is a change, start an I / O thread to request the binary event of Master

At the same time, the Master node starts a ③ thread for each Slave O thread, which is used to send binary events to it and save it to the relay log (Relay log) local to the Ithumb node.

The ④ Slave node will start the SQL thread to read the binary log from the relay log and replay it locally, that is, parsing it into sql statements one by one to make its data consistent with that of the Master node. Finally, the I / O thread and SQL thread will go to sleep and wait for the next time to wake up.

There is a very important limitation in the replication process, that is, replication is serialized on Slave, that is, parallel update operations on Master cannot be operated in parallel on Slave

Relay logs are usually located in the OS cache, so the overhead of relay logs is very small.

In what scenarios are 5.MySQL clusters and master-slave replication suitable for use respectively?

Cluster and master-slave replication are designed to cope with high concurrency and large traffic. If the number of website visits and concurrency is too large, a small number of database servers cannot handle it, which will cause slow access to the website. Data writing will cause the data table or record to be locked, which means that other access threads cannot read and write until the write is completed. This will affect the reading speed of other users. Using master-slave replication can make some servers read specially, and some special writes can solve this problem.

6. Why use master-slave replication and read-write separation

Master-slave replication, read-write separation are generally used together, the purpose is very simple, is to improve the concurrent performance of the database. You think, suppose it is a stand-alone machine, reading and writing are done on a MySQL, the performance must not be high. If there were three MySQL, one mater only for write operations and two salve for read operations, wouldn't the performance be greatly improved?

So the purpose of master-slave replication and read-write separation is for the database to support greater concurrency.

With the expansion of business, if it is a stand-alone deployment of MySQL, it will lead to high frequency of Icano. Master-slave replication and read-write separation can improve the availability of the database.

7. Uses and conditions

The purpose of mysql master-slave replication:

Real-time disaster recovery for failover

Separation of reading and writing, providing query service

Backup to avoid affecting servic

Necessary conditions:

Binlog log is enabled in the main library (set log-bin parameter)

Master-slave server-id is different.

The slave server can connect to the master library.

Problems in 8.mysql Master-Slave replication

Data may be lost after the main library goes down

There is only one SQL Thread in the slave library, so the master database is under great pressure to write, and the replication is likely to be delayed.

Solution.

Semi-synchronous replication-solving the problem of data loss

Parallel replication-solving the problem of replication delay from the library

9.MySQL master-slave replication delay

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

② network delay

③ master-slave hardware device causes-- cpu master frequency, memory io, hard disk io

④ is not synchronous replication, but asynchronous replication.

Optimize Mysql parameters from the library. For example, increase innodb_buffer_pool_size, let more operations be done in Mysql memory, and reduce disk operations.

The slave library uses high-performance hosts, including strong cpu and large memory. Avoid using virtual CVMs and use physical hosts, which improves the aspects of iUniver.

Use SSD disks from the library

Network optimization to avoid synchronization across computer rooms

Second, the form of master-slave replication

Third, the separation of reading and writing. Principle

① is written only on the master server and read only on the slave server

The ② master database handles transactional queries and select queries from the database

③ database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster

two。 Why the separation of reading and writing?

Because the "write" operation of the database (it may take 3 minutes to write 10000 pieces of data) is time-consuming, but the "read" of the database (it may only take 5 seconds to read 10000 pieces of data), the separation of read and write solves the problem of writing to the database, which affects the efficiency of the query.

3. When do you want to separate reading and writing?

The database does not have to be read-write separate. if the program uses the database more, updates less, and queries more, it will consider using the master-slave synchronization of the database, and then through read-write separation can share the pressure of the database and improve performance 4. Master-slave replication and read-write separation in the actual production environment, reading and writing to the database are all in the same database server, which can not meet the actual needs.

5. At present, MySQL read-write separation is more common.

It is divided into the following two categories

① is based on internal implementation of program code.

Classifying routes according to select and insert in the code, this kind of method is also the most widely used advantage in production environment at present, because it is implemented in program code, there is no need to add additional equipment for hardware expenses. The disadvantage is that developers are required to implement it, and there is no way for operators to start, but not all applications are suitable to achieve read-write separation in program code. Like some large and complex Java applications, if read-write separation is implemented in program code, the code will be changed greatly.

② is implemented based on intermediate proxy layer.

The agent is generally located between the client and the server. After receiving the request from the client, the proxy server is forwarded to the back-end database after judgment. The following representative programs are available.

(1) MySQL-Proxy,MySQL-Proxy is an open source MySQL project, and 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 Qihoo 360's Web platform infrastructure team. It optimizes it and adds some new features on the basis of mysql-proxy version 0.8.2. The mysql business run by Atlas internally carries billions of read and write requests every day. Support things and stored procedures

(3) Amoeba, developed by Chen Silu, the writer used to work for Alibaba. The program is developed by Java language and used in production environment by Alibaba. However, it does not support transactions and stored procedures.

Because you need to write a lot of Lua scripts to use MySQL Proxy, these Lua are not ready-made, but need to be written by yourself, which 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 proxy layer of database in production environment.

Fourth, case implementation 1. Case environment

This case environment uses the stage server to grind you to build it. The topology diagram is as follows.

Host name host operating system IP address main software CentOS 7-1MasterCentOS 7192.168.126.11ntp, mysql-boost-5.7.17.tar.gzCentOS 7-2AmoebaCentOS 7192.168.126.12jdk-6u14-linux-x64.bin, amoeba-mysql-binary-2.2.0.tar.gzCentOS 7-3Slave1CentOS 7192.168.126.13ntp, ntpdate, mysql-boost-5.7.20.tar.gzCengOS 7-4Slave2CentOS 7192.168.126.14ntp, ntpdate, Mysql-boost-5.7.17.tar.gzCentOS 7-5 client CentOS 7-5192.168.126.15mysql5.72. Experimental ideas (solving needs)

Client access proxy server

The proxy server writes to the primary server

The master server rewrites the additions and deletions to its own binary log.

Synchronize the binary log of the master server to its own relay log from the slave server

Replay the relay log from the server to the database

If the client reads, the proxy server directly accesses the slave server

Reduce the load and play the role of load balancing

3. Prepare for

Except for the client, you need to compile and install MySQL first.

All need to turn off the firewall and control the access mechanism.

Systemctl stop firewalldsystemctl disable firewalld# turn off firewall (and boot disable) setenforce turn off security access control mechanism 4. Build MySQL master-slave replication

① Mysql master-slave server time synchronization

Master server settin

# install NTPyum-y install ntp# configure the last line of NTPvim / etc/ntp.conf# add the following: server 127.127.126.0fudge 127.127.126.0 set the local clock source, pay attention to modify the network segment # set the time level to 8 (limited to 15) # restart the service service ntpd restart

Set from the server

Yum-y install ntp ntpdate# installation service, ntpdate is used to synchronize time service ntpd start# enable service / usr/sbin/ntpdate 192.168.126.1 to synchronize time, point to Master server IPcrontab-e# write scheduled tasks, and perform time synchronization every half hour * / 30 * / usr/sbin/ntpdate 192.168.126.11

② configure MySQL Master master server

Vim / etc/my.cnf# configuration the following content server-id = 1log-bin=master-bin# is added, the master server enables binary log log-slave-updates=true# addition, allows the slave server to update the binary log systemctl restart mysqld# restart service to make the configuration effective mysql-uroot-p12312login database program, authorize GRANT REPLICATION SLAVE ON to the slave server *. * TO 'myslave'@'192.168.126.%' IDENTIFIED BY' 123123 login flush PRIVILEGES;show master status The quit#File column shows the log name and the Fosition column shows the offset

③ configuration slave server

Vim / etc/my.cnfserver-id = modify. Note the difference between id and Master. The id of the two Slave should also be added with different relay-log=relay-log-bin#. Enable relay log, 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 file systemctl restart mysqldmysql-uroot-p123123change master to master_host='192.168.126.11', master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=604 # configure synchronization. Note that the values of master_log_file and master_log_pos should be synchronized with the consistent start slave;# of Master. If an error is reported, execute reset slave;show slave status\ G# to check the Slave status / / make sure that both IO and SQL threads are Yes, representing normal Slave_IO_Running: Yes# is responsible for communicating with the host's io Slave_SQL_Running: Yes# is responsible for its own slave mysql process

Slave1:

Slave2: same as slave1 configuration, id cannot be the same

General Slave_IO_Running: there are several possibilities for No:

The network is not connected.

There is a problem with my.cnf configuration

Incorrect password, file file name, pos offset

The firewall is not off

④ verifies the effect of master-slave replication

5. Set up MySQL read-write separation

This software is dedicated to MySQL's distributed database front-end proxy layer. It mainly acts as a SQL route for the application layer to access MySQL, and has load balancing, high availability, SQL filtering, read-write separation, routing related to the target database, and concurrent requests for multiple databases.

Through Amoeba, we can achieve the functions of high availability, load balancing and data slicing of multiple data sources.

① installs the Java environment on the host Amoeba because Amoeba is based on jdk1.5 development, so jdk1.5 or version 1.6 is officially recommended. Higher version is not recommended.

Cd / opt/# in FinalShell, drag the package into amoeba-mysql-binary-2.2.0.tar.gzjdk-6u14-linux-x64.bincp jdk-6u14-linux-x64.bin / usr/local/cd / usr/local/chmod + x jdk-6u14-linux-x64.bin./jdk-6u14-linux-x64.bin# and hold down enter all the way to the bottom If prompted to enter YES+, you can rename mv jdk1.6.0_14/ / usr/local/jdk1.6# to vim / etc/profile# and edit the global configuration file. Add the following configuration export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin# output definition Java working directory # output specified java type # add java to path environment variable # output definition amoeba working directory # join path Environment variable source / etc/profile# modified global configuration file java-version# view the java version information to check whether the installation is successful

② install and configure Amoeba

Mkdir / usr/local/amoebatar 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 is displayed | stop indicates that the installation is successful

③ configures Amowba read-write separation, and two slave read-write load balancers open permissions to amoeba access on the mysql of master, slave1, and slave2.

④ edits the amoeba.xml profile in the host amoeba

Cd / usr/local/amoeba/conf/cp amoeba.xml amoeba.xml.bakvim amoeba.xml# modify amoeba configuration File # 30 Line modify amoeba#32 Line modify 123123 "115 Line modify master#117 remove comment masterslaves

⑤ Editing dbServers.xml configuration File

Cp dbServers.xml dbServers.xml.bakvim dbServers.xml# modify database configuration file # 23 comment out effect: when entering the test library by default in case there is no test library in mysql, it will report an error of # 26 line modification, test#28-30 line modification, remove comment 123.com#45 line modification, set master # 48 line modification, set master server address 192.168.126.11 comment 52 line modification, set slave server name 1 # 55 line modification Set to copy and paste the above 6 lines from the address 192.168.126.13058 of server 1, set the name slave2 of server 2 and address 192.168.184.14065, modify line # 71 modify slave1,slave2

After ⑥ determines that the configuration is correct, you can start the Amoeba software, whose default port is tcp 8066

/ usr/local/amoeba/bin/amoeba start&# starts the Amoeba software and presses ctrl+c to return netstat-anpt | grep java# to check whether port 8066 is enabled. The default port is TCP 8066.

⑦ test

1. Go to the client and quickly install the MySQL virtual client, and then access the MySQL through the proxy

Yum-y install mysql# quickly installs MySQL virtual client mysql-u amoeba-p123123-h 192.168.126.12-P806 through YUM access MySQL,IP address points to amoba# after connecting to the mysql through the client, only the master service will record the data, and then synchronize it to the slave server

two。 Create a table on Master and synchronize it to two slave servers

Use club;create table puxin (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 insert the data content manually

Stop slave;# shuts down synchronous use club;insert into puxin values ('1century recollective Wangyijia relegated slave 1'); # slave2insert into puxin values (' 2pure recalcitrant wangerweak recalculating synchronization isometric slave2')

4. Then go back to the main server and insert other content.

Insert into pucin values ('3century, such as wangwuzhong, wangwu.com, etc., etc.)

5. Test the read operation and go to the client host to query the result

Use club;select * from puxin

6. Insert a statement on the client, but cannot query it on the client. In the end, the content of the statement can only be seen on the Master, indicating that the write operation is on the Master server.

Insert into puxin values ('4examples, recordings, liuliu.com, etc., etc.

7. Then execute start slave; on two slave servers to synchronize the data added on the master server.

Summary

It is verified that the separation of MySQL read and write has been realized. At present, all write operations are on the Master master server, which is used to avoid data asynchrony.

All read operations are allocated to the Slave slave server to share the pressure on the database.

1. How to check whether the master-slave synchronization status is successful

Enter the command show slave status\ G in the slave server to view the master and slave information, which contains the status information of the IO thread, as well as the IP address, port and transaction start number of the master server.

When both slave_io_running and slave_sql_running are displayed as yes, the master-slave synchronization status is successful

two。 If I am not yes, how do you troubleshoot it?

First troubleshoot the network problem and use the ping command to see if the slave service can communicate with the master server

Furthermore, check to see if the firewall and core protection are off.

Then check to see if slave is enabled from the server.

Whether the server-id of the two slave servers is the same, so that only one slave can be connected.

Whether the values of master_log_file and master_log_pos are consistent with those of Master query

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

State information of the IO thread

Master server IP address, port, transaction start location

The latest error information and location, etc.

4. What is the possibility of master-slave replication being slow (delayed)

The load on the primary server is too large and is occupied by multiple sleep or zombie threads, resulting in excessive system load

The hardware of the slave library is worse than the master library, resulting in replication delay

Master-slave replication is single-threaded. If the write concurrency of the master library is too large to transfer to the slave library, it will cause delay.

Too many slow SQL statements

Network delay

At this point, the study on "how to achieve master-slave replication and read-write separation of Mysql database" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.