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

This article mainly introduces "the usage of high-performance MySQL replication and caching". In daily operation, I believe many people have doubts about the usage of high-performance MySQL replication and caching. Xiaobian consulted various materials and sorted out simple and easy operation methods. I hope to help you answer the doubts about "the usage of high-performance MySQL replication and caching"! Next, please follow the small series to learn together!

Replicate basic problems solved

Let the data of one server keep other servers synchronized, the data of one main database can be synchronized to multiple backup databases, and the pain itself can also be configured as the main database of another server.

MySQL supports two types of replication: row-based replication and statement-based replication (logical replication). Both of them record binary logs in the primary database and replay logs in the backup database to achieve asynchronous data replication, which indicates that the primary and backup inventories are inconsistent at the same time, and there is no guarantee of delay between the primary and backup.

Common replication uses

Data distribution: MySQL replication usually does not cause a lot of loan pressure, but row-based replication will be more bandwidth pressure than statement-based replication, you can stop or start replication at will, and distribute data backups in different geographical locations, such as different data centers, even in unstable network environments, remote replication can also work, the unit has low replication latency, preferably with a stable low latency connection.

Load Balancer: Distributes reads across multiple servers to optimize read-intensive applications

Backup: Replication is a valuable technical complement to backup, but it is neither backup nor a substitute for backup.

High availability and failover: Help applications avoid MySQL single points of failure, a well-designed failover system with replication can significantly reduce downtime

MySQL upgrade test: Use a higher version of MySQL as a backup to ensure that all instances are upgraded before. The query can be executed as expected in the backup repository.

replication step

The master repository logs data changes to the binary log

Before the commit error completes the data update, the master repository records the time of the data update in the binary log, recording the binary log in the order in which the transaction was committed rather than in the order in which each statement was executed. After recording, the master repository tells the storage engine that it is ready to commit the transaction.

Backup copies logs from the primary repository to its own relay logs

The backup reads the time in the relay log and replaces it on top of the backup data.

statement-based replication

The main repository logs queries that cause data changes, and when these events are painstakingly read and replayed, it is really just executing SQL that has been executed on the main repository

Advantages: Simple implementation, more compact time in binary logs, does not use too much bandwidth

Disadvantages: Updates must be serial, require more locks, not all engines support this replication pattern, there are SQL that cannot be copied correctly, stored procedures and triggers may also have problems when using statement-based replication patterns

Row-based replication

By recording the actual data in binary logs, each line can be copied correctly, and some statements can be copied more efficiently

One-master multi-backup structure

Use different backup libraries for different roles, such as adding different so's or using different storage engines

A backup library is treated as a surrogate master library, with no data transfer other than replication.

Place a backup library in a remote data center for disaster recovery

Defer one or more backup repositories for disaster recovery

Use a backup repository as a backup, training, development, or test server

Active-passive mode server symmetry settings

Make sure you have the same data on both servers

Enable binary logging, select a unique server ID, and create a replication account

Enable logging of backup updates

Configure passive servers as read-only to prevent possible conflicts with updates on active servers

Start MySQL instances for each server

Set each master library as a backup library for the other and start working with the newly created binary log

When updating on active server: updates are logged into binary log, passive server executes queries by copying relay log passed to passive server and logs them into its own binary log, active server ignores events since server ID is the same as active server.

This is similar to creating a hot backup, but you can use this backup to improve performance, such as performing reads, backups, offline maintenance upgrades, etc. But you won't get better write performance than a single server.

Master-master structure with backup library

Add a backup database for each primary database, increasing redundancy. For replication topologies with different geographical locations, the problem of site but TV system can be eliminated. Read queries can be assigned to the backup database.

When the main library fails, it is feasible to replace the main library with a backup library, or it is possible to replace the backup library with a different main library, but the increased complexity needs to be considered.

circular replication topology

Each server is the backup repository of the server before it and the master repository of the server after it.

Master Library Distribution Master Library Backup Library

The distribution master library is actually a backup library. Its purpose is to extract and provide binary logs of the master library. Multiple backup libraries are connected to the distribution master library. The original master library gets rid of the burden. In order to avoid making actual queries on the distribution master library, its tables can be modified to blackhole storage engines.

Filtering and rewriting rules for binary log times can be implemented using the distribution master library, which is much more efficient than repeating logging, rewriting, and filtering on each backup library

Using blackhole tables on the distribution master library can support more backup libraries. Although queries will be performed on the distribution master library, the cost is very small, because there is no data in the blackhole table. The disadvantage of blackhole tables is that there are bugs. In some cases, they forget to write the self-increasing id to the binary log.

It is impossible to use a backup library instead of the master library when using the distribution master library, because the binary log coordinates of each backup library and the original master library are different due to the existence of the distribution master library.

log server

mysqlbinlog: Used to record the database update content such as mysql internal additions, deletions, and queries. Database queries such as select or show will not be recorded by binlog. Mainly used for database master-slave replication and incremental recovery.

Replication as a method of applying binary logs has been tested by a large number of users and can prove to be feasible. mysqlbinlog may not correctly generate data updates in binary logs.

Replication is faster because there is no need to export statements from the log to mysql

It's easy to observe replication.

Easy to handle errors, such as skipping failed statements

Easy to filter replication events

Sometimes mysqlbinlog cannot read binary logs due to logging format changes

cache

Caches below the application layer: MySQL servers have their own internal caches, and they can also build their own caches and summary tables, which are more persistent than many application layer caches and survive server restarts.

Application-layer caching: Caching data in memory on the same machine, or in memory on another machine over the network, saves on fetching data and computing based on it. But cache hits are low and more memory may be used.

Local cache of application cache: Small, only exists in process memory while the process is processing requests. Repeated requests for certain resources can be avoided

Local shared memory cache for application cache: Medium size, fast, difficult to synchronize across multiple machines, suitable for small semi-static bit data, but very fast access, usually faster than any remote cache access

Distributed memory cache for application cache: Much larger than local shared memory cache, easy to grow, only one copy of each bit of data created in cache, no memory waste, and no coherency problems introduced because the cache creates data in different places. It is very suitable for storing shared objects, but the demonstration is high. The most effective way is to perform multiple fetch operations in batches. Also consider how to add more nodes and how to deal with a node crash. The application must decide how to distribute objects among nodes or fully cache them.

Disk cache of application cache: preferably persistent objects, objects that are difficult to store all into memory or Jintai content

cache control strategy

Problem: Duplicate data, there are many places where data needs to be updated, so avoid supervising dirty data

TTL (time to live): Cache object storage is to set an expiration time, you can delete the object after the expiration time is not reached through the cleanup process, or save it for the next access, for the case of little data change or no new data, this is the best invalidation strategy.

Display invalidation: If dirty data cannot be accepted, invalidate cache while updating original data

Write--Invalidation: Marks cache data as expired (cleaning cache data is optional).

Write-Update: Replace cache entries when updating data

Read-time invalidation: Object versioning

Cache object hierarchy: Hierarchical cache objects are helpful for retrieval, invalidation, and memory utilization. Instead of caching only objects, you can also cache data that needs to be retrieved together, such as object IDs and object ID groups.

Pre-generated content: request some pages in advance in the background and save the results as static pages.

Cache as a base component

Use handlerSocket and memcached: handlerSocketto just a simple protocol to access innodb handler bypass the upper server layer, connect innodb engine directly through the network, access innoDB through memcached protocol

At this point, the study of "high-performance MySQL replication and cache usage" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more 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.