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

This article introduces the relevant knowledge of "Mysql master-slave copy and the concept of read-write separation". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

What is master-slave replication?

When the data volume of MySQL database is too large, it is very difficult to query the data. No matter how to optimize it, it will produce bottlenecks. At this time, we need to add server equipment to realize distributed database. First, we must understand the requirements of the version of the master-slave database server. The installation and operation version of the master-slave MySQL should be consistent. Therefore, we use mysql's own REPLICATION to achieve mysql multi-machine master-slave synchronization function, mysql version 5.7 for demonstration.

What is Read-Write Separation?

That is to separate the read and write operations on the database, and share the read and write pressure on multiple servers, usually used for scenarios where reads are much larger than writes. The basic principle of read-write separation is to let the master database handle transactional add, change, delete operations (INSERT, UPDATE, Delete), while the slave database handles SELECT query operations. Database replication is used to synchronize changes resulting from transactional operations to slave databases in the cluster. After more data, there will be a lot of reading and writing to the database. There is only one write library, and there can be multiple read libraries. Master-slave replication is used to synchronize the data of the master library and multiple read libraries.

What is hot standby?

Mutual master-slave synchronization is also called master-master synchronization. It is based on master-slave synchronization and adds bin-log from master synchronization. The virtual ip of keepalive is also called vip. Its role is to monitor two databases. The default setting is master1. If master1 hangs, it goes to master2. If master2 hangs, it goes to master1. It eliminates the previous master single-point problem of one master and one master and multiple slaves. Thus, it can achieve high availability of databases.

Steps for master-slave synchronization configuration

1. master: modify the database configuration file vi /etc/my.cnf

2. master: delegate the slave database, create an account for synchronization, password, and grant bin-log permissions

3. slave: modify the database configuration file vi /etc/my.cnf

4. Slave: Set the account created by 2 master_host, master_user, master_password

master-slave synchronization

We usually mentioned master-slave synchronization, master-master synchronization default to asynchronous synchronization, starting from MySQL 5.5, MySQL in the form of plug-ins to support semi-synchronous replication. How do you understand semi-synchronization? First let's look at asynchronous, fully synchronous concepts.

asynchronous replication

MySQL default replication is asynchronous, the master database will immediately return the results to the client after executing the transactions submitted by the client, and does not care whether the slave database has received and processed, so there will be a problem, if the master crashes, at this time the transactions submitted by the master may not be transmitted to the slave, if at this time, forcibly promote the slave to the master, may lead to incomplete data of the new master.

Fully synchronous replication

When the master library finishes executing a transaction, all slaves execute the transaction before returning it to the client. Because you have to wait for all slaves to execute the transaction before returning, the performance of full synchronous replication is bound to suffer severely.

Semisynchronous replication

Between asynchronous replication and fully synchronous replication, the master library does not immediately return to the client after executing the transaction submitted by the client, but waits for at least one slave library to receive and write to the relay log before returning to the client. Semi-synchronous replication improves data security over asynchronous replication, but it also introduces a delay of at least one TCP/IP round trip. Therefore, semi-synchronous replication is best used in low-latency networks.

Here's a schematic of semi-synchronous replication:

java read-write separation dynamically switches data sources

1. Handwritten DynamicDataSource extends AbstractRoutingDataSource implements determineCurrentLookupKey method, HandleDataSource.get() fetches values, which can be data source names or multi-slave Load Balancer based on fetched values

2. Configure multiple data source names master, salve1, salve2..., All assembled into DynamicDataSource targetDateSource

3. Handwritten HandleDataSource uses thread variables to store the current thread data source

4. Handwriting a DataSourceAspect facet, pointcut sql statement prefix (select, update, delete, insert) or annotation Custom annotation (such as @DateSource("salve2")) Set the corresponding data source name or custom value to HandleDataSource

"Mysql master-slave copy and the concept of read-write separation" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.