SoFunction
Updated on 2025-03-04

Implementation steps for MySQL master-slave replication data synchronization

1. What is the master-slave replication of MySQL

Master-Slave Replication for MySQL is a technology that replicates data from a master database server (master library) to one or more slave database servers (slave libraries). The main library is responsible for all data writing operations, while the slave library synchronizes the data changes of the main library by reading the binary log of the main library. Master-slave replication is mainly used to implement data backup, load sharing and high availability.

2. Specific process

1. Main library record writing operation

When the main library performs a write operation (such asINSERT、UPDATEorDELETE), these operations are logged into the binary log (binlog). The binary log holds a history of all data changes that will become the source for synchronized data from the library.

  • step: The main library records all write operations (data changes) to the binary log.

2. Connect the main library from the library and read the binary log

From the library, it is calledIO threadThe process establishes a connection with the main library and starts reading the binary log of the main library. The main library will send the log content to the slave library's IO thread.

  • step: Connect to the main library from the IO thread of the library to continuously receive the latest content of the main library binary log.

3. Write binary logs from the library to the relay log

The IO thread of the slave library copies the received primary library binary log into the relay log of the slave library. The relay log saves a copy of the master database data changes locally in the slave database for use by the slave database.

  • step: Write the received log from the library's IO thread to the relay log to prepare for data changes.

4. Apply relay logs from the library and perform data synchronization

Another thread from the library (calledSQL threads) Responsible for reading the content in the relay log and executing it one by one on the slave library to achieve data synchronization. Whenever there is new data change in the main library, the slave library will retrieve and perform these changes from the relay log to maintain the same data as the main library.

  • step: Read and perform logged operations from the relay log from the library's SQL thread, and gradually update the data from the library.

5. Continuous copying and synchronization

The entire master-slave replication process is a continuous process. As long as the master library has new data changes, the slave library will automatically obtain and execute corresponding changes, thereby maintaining consistency with the master library data. Master-slave replication is continuously synchronized to ensure that the slave library can reflect the latest data of the master library in real time or near real time.

  • step: All write operations of the main library will be recorded in the log and synchronized to the slave library in real time; the slave library will continuously read and execute log content, and update its own data.

3. Functions and benefits

  • Read and write separation to improve performance

    • Reduce pressure on the main warehouse: Spread a large number of read operations to the slave library. The main library is mainly responsible for writing operations, which greatly reduces the load pressure of the main library.
    • Improve concurrent processing capabilities: Increasing the number of slave libraries can improve the system's read concurrency ability, handle more users' concurrent requests, and improve the overall performance of the system.

  • High availability and fault tolerance

    • Data redundancy and disaster recovery: Master-slave replication provides real-time backup of data. The slave library can quickly take over services when the master library fails, improving system availability.
    • Failover: When the main library fails, the slave library can be temporarily upgraded to the main library to ensure the continuous operation of the service.

  • Load balancing

    • Share the pressure of inquiry: Multiple slave libraries can jointly undertake read requests, and allocate requests through load balancing algorithms (such as polling, randomness, etc.) to improve the stability of the system.
    • Avoid single-point bottlenecks: Read operations are distributed to different slave libraries to execute, avoiding a single database becoming a system bottleneck due to excessive access.

  • Extensibility

    • Horizontal expansion: According to business growth, flexibly increase the number of slave libraries to meet performance needs without major transformation of the main library.
    • Elastic expansion: The library can be increased or decreased according to the actual traffic, so as to flexibly respond to load changes.

  • Data backup and security

    • Data protection: The slave library can be used for data backup and disaster recovery to prevent data loss caused by failure of the main library.
    • Quick recovery: When data is accidentally lost or corrupted, the data from the main library can be restored from the library.

4. Scenarios and examples in practical applications

1. E-commerce platform

In e-commerce platforms, users' browsing and query operations will generate a large number of read requests, while order creation, payment and other operations will generate write requests. Read and write separation through master-slave replication:

  • High-frequency reading operations such as inquiry of inventory and product details can be undertaken by the slave library to reduce the pressure on the main library.
  • The main library is responsible for writing operations such as user orders and payments to ensure the integrity and consistency of the data.

2. Social media or content website

In social media applications, a large number of content browsing and searching will produce a large number of read operations, while publishing, likes, etc. are write operations. Through master-slave replication, the platform can:

  • Use the slave library to undertake browsing, querying and other operations to ensure fast response under high concurrency.
  • Point the write operation to the main library to ensure real-time updates of users post or likes.

5. Integrate MySQL master-slave replication in Spring Boot project

Integrating MySQL's master-slave replicated data synchronization in Spring Boot project refers to connecting Spring Boot applications to the MySQL database system configured with master-slave replication, completing the configuration management of the master-slave database, and realizing automatic read and write separation. Specifically, through multi-data source configuration, Spring Boot automatically recognizes whether it is a write request or a read request, and sends the write request to the master library and the read request to the slave library.

Key elements of integration:

  • Multi-data source configuration: Configure the connection between the master and slave databases in Spring Boot.
  • Dynamic data source routing: Realize dynamic selection of data sources at the application level, use slave libraries for read operations, and use master libraries for write operations.
  • Read and write separation annotations or facets: Use custom annotations or AOP sections to control the switching of data sources.

1. Configure MySQL's master-slave replication environment

Configuring MySQL's master-slave replication environment is the first step in implementing the synchronization of MySQL master-slave replication data. The main steps include setting up the master library (Master) and slave library (Slave), and verifying the success of master-slave replication.

1.1 Setting up the main library (Master)

Configuring the master library is the first step in master-slave replication. The master library is responsible for recording data changes and passing them to the slave library.

1.1.1 Modify the configuration file of the main library

In the MySQL configuration file of the main library (usually located in/etc/or/etc/mysql/), binary logs need to be enabled and a unique one is set for the main libraryserver-id. exist[mysqld]Part of the following configurations are added:

[mysqld]
server-id=1                 # The unique ID of the main librarylog-bin=mysql-bin           # Enable binary logging, master-slave replication depends on thisbinlog-do-db=your_database  # The database name that needs to be synchronized, multiple databases can add multiple rows
  • server-id: A unique identifier used to identify each MySQL instance, the main libraryserver-idGenerally set to 1.
  • log-bin: Enable binary logging, which is the basis for master-slave replication.
  • binlog-do-db: Specify the database that needs to be synchronized (multiple databases can be set in multiple rows).

Notice: If you need to synchronize multiple databases, you can add them multiple timesbinlog-do-dbRight, for example:

binlog-do-db=database1
binlog-do-db=database2

1.1.2 Restart MySQL service

After modifying the configuration file, you need to restart MySQL to make the configuration take effect:

# Linux systemsudo systemctl restart mysqld

# Windows Systemnet stop mysql
net start mysql

1.1.3 Create a user for replication

Create a user for replication in the main library and grantREPLICATION SLAVEPermissions. This user is used to connect to the main library and synchronize data from the slave library.

Execute the following command in the MySQL command line of the main library:

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;
  • replica_user: The username used for copying, can be customized.
  • password: Copy the user's password and pay attention to using a strong password.
  • %: Allow all remote IP access. If only specific slave libraries are allowed, you can use the IP address of the slave library instead.%。

1.1.4 Get the binary log information of the main library

In order for the slave library to know where to start copying data, the master library needs to provide the current binary log location. You can view it through the following command:

SHOW MASTER STATUS;

After the command is executed, the following content will be output:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      154 | your_database|                  |
+------------------+----------+--------------+------------------+
  • File: The current binary log file name.
  • Position: The offset of the binary log, starting from this location to read the data.

Suggestion: In a production environment, before executing SHOW MASTER STATUS;, you can first execute FLUSH TABLES WITH READ LOCK; to lock the table to prevent data inconsistency caused by data writing. After obtaining the information, execute UNLOCK TABLES; unlock.

1.2 Setting the slave library (Slave)

Configuring the slave library is the second step in master-slave replication. Configure the slave library to connect to the master library and start synchronizing data.

1.2.1 Modify the configuration file of the library

In the MySQL configuration file from the library (usually/etc/or/etc/mysql/), set a uniqueserver-idAnd configure relay log and read-only mode. exist[mysqld]Partially add the following configuration:

[mysqld]
server-id=2                 # The unique ID of the slave library cannot be the same as the master library or other slave libraryrelay-log=relay-log         # Set the prefix of the relay log file nameread-only=1                 # Set read-only mode to prevent misoperation
  • server-id: Unique identifier of the library, each of the libraryserver-idIt also needs to be unique, usually starting from 2.
  • relay-log: Specifies the prefix of the relay log to store data synchronized from the main library.
  • read-only: Turn on read-only mode to prevent accidental writing. In this mode, haveSUPERUsers with permission can still write data.

1.2.2 Restart MySQL service

After modifying the configuration file, restart the MySQL service from the library to apply the configuration:

# Linux systemsudo systemctl restart mysqld

# Windows Systemnet stop mysql
net start mysql

1.2.3 Configuring the slave library to the master library

In MySQL of the slave library, configure the main library information to start replication. Need to use the records on the main libraryFileandPositionValue.

CHANGE MASTER TO
    MASTER_HOST='The IP address of the main library',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',  -- The main library File value
    MASTER_LOG_POS=154;                  -- The main library Position value
  • MASTER_HOST: The IP address of the main library.
  • MASTER_USER: Users created on the main library for replication (such asreplica_user）。
  • MASTER_PASSWORD: Copy the user's password.
  • MASTER_LOG_FILE: The binary log file name of the main library.
  • MASTER_LOG_POS: The position offset of the binary log.

1.2.4 Start the replication process

After the configuration is complete, start the copy process from the slave library to start copying data from the master library:

START SLAVE;

1.2.5 View the copy status from the library

Run the following command to check the status of the slave library, confirm that the slave library has been successfully connected to the master library and start copying the data:

SHOW SLAVE STATUS\G
  • Slave_IO_Running: It should beYes, indicating that the IO thread of the slave library is reading the log of the main library.
  • Slave_SQL_Running: It should beYes, indicates that the SQL thread from the library is executing the log passed by the main library.
  • Last_IO_ErrorandLast_SQL_Error: If there is an error message, it will be displayed here.

If Slave_IO_Running or Slave_SQL_Running is No, please check the error messages in Last_IO_Error or Last_SQL_Error and troubleshoot the problem according to the error prompts.

1.3 Verify that the master-slave replication is successful

After the configuration is complete, verify the success of master-slave replication. Ensure that the write operations of the master library can be correctly synchronized by the slave library.

1.3.1 Create test data on the main library

Select the database for replication on the main library and create a test table to insert the data:

USE your_database;
CREATE TABLE test_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO test_table (id, name) VALUES (1, 'Test Data');

1.3.2 Checking data synchronization status on the library

Select the same database from the library and querytest_tableTable, confirm whether the data of the main library is synchronized:

USE your_database;
SELECT * FROM test_table;
  • If you can seeTest Data, it means that the master-slave replication configuration is successful and is working normally.

1.3.3 Verify the real-time synchronization effect

Continue to insert or update data on the main library, query again on the slave library to verify whether the data can be synchronized in time. Under normal circumstances, master-slave replication will be synchronized immediately, with a small delay.

1.4 Troubleshooting

When configuring master-slave replication, you may encounter the following common problems:

1 The slave library cannot connect to the master library

  • Check network connection: Ensure that the master library's firewall allows the slave library's IP address to access MySQL's port 3306.

2 Permissions issues

  • User permissions: Ensure that the user created on the main library hasREPLICATION SLAVEPermissions.

3 Master-slave version compatibility issues

  • Version compatibility: Ensure that the MySQL versions of the master and slave libraries are compatible with each other, and the same version is recommended.

4 The log location is incorrect

  • File and location errors: If configuredFileandPositionIncorrect, you can reset the master-slave replication position.

2. Configure multiple data sources in Spring Boot project

Configuring multiple data sources is an important step in realizing master-slave replication. Through multi-data source configuration, Spring Boot applications can automatically distinguish and select master or slave libraries, enabling read-write separation. The specific operations of this step include adding dependencies, configuring data source information, and configuring data source routing to enable automatic selection of master or slave libraries. The following is a detailed step-by-step explanation.

2.1 Add necessary dependencies

When using Spring Data JPA and MySQL multi-data sources, you need to add the following dependencies:

  • MySQL driver: Supports connection to MySQL databases.
  • Spring Data JPA: Provides JPA support to simplify database operations.
  • HikariCP connection pool: Spring Boot default connection pool, suitable for high concurrency environments and supports multi-data source configuration.

On the projectAdd the following dependencies to:

<dependencies>
    <!-- MySQL drive -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <!-- Spring Data JPA -->
    <dependency>
        <groupId></groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- HikariCP Connection pool（Spring Boot 默认Connection pool） -->
    <dependency>
        <groupId></groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
</dependencies>

2.2 Define the data source information of the master and slave libraries in the configuration file

Next, you need toThe connection information of the master library and the slave library is defined. The master library is usually used for write operations, and the slave library is used for read operations.

File content

#Main library configuration-class-name=
=jdbc:mysql://Main library IP address: 3306/your_database?useSSL=false&characterEncoding=utf8=Main library username
=Main library password

#Configuration from library-class-name=
=jdbc:mysql://From the library IP address: 3306/your_database?useSSL=false&characterEncoding=utf8=From library username
=From library password

illustrate：

  • : Main library connection information, used for writing operations.
  • : Connect information from the library, used for reading operations.
  • driver-class-name: MySQL driver class name.
  • url: The database connection URL, which contains the IP address and database name of the database.
  • usernameandpassword: The user name and password of the database.

2.3 Create a data source configuration class and configure the master and slave data source

Add a configuration class to the project to define the master library and slave data sources, and automatically select the master library or slave library through a dynamic data source.

2.3.1 Configure the data source of the master and slave libraries

First, we need to define two data sources in the configuration class, namely the master library and the slave library. The main library is mainly used for write operations, and the slave library is used for read operations.

package ;

import ;
import ;
import ;
import ;
import ;

@Configuration
public class DataSourceConfig {

    // Define the main library data source    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "")
    public DataSource masterDataSource() {
        return ().build();
    }

    // Define the source of data from the library    @Bean(name = "slaveDataSource")
    @ConfigurationProperties(prefix = "")
    public DataSource slaveDataSource() {
        return ().build();
    }
}

illustrate：

  • @ConfigurationProperties: This annotation willmiddleandConfiguration information is bound tomasterDataSourceandslaveDataSourcesuperior. In this way, Spring Boot automatically reads the master and slave library information in the configuration file and injects them into two data source objects separately.

  • DataSourceBuilder: This is a tool class provided by Spring, through which you can build based on configuration filesDataSourceObject.DataSourceIt is the core component of the database connection, mainly used to manage database connections, connection pools and other information.

2.3.2 Dynamic data source routing

In order to achieve master-slave separation, we need to automatically select the data source of the master or slave library according to the request.AbstractRoutingDataSourceIt is an abstract class provided by Spring, which allows dynamic selection of data sources based on user-defined routing rules.

CreateDynamicDataSourcekind: This class inherits fromAbstractRoutingDataSource, enable data source selection by setting key-value mapping.

package ;

import ;

public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    // Set the data source of the current thread (master or slave)    public static void setDataSource(String dataSourceKey) {
        (dataSourceKey);
    }

    // Clear the data source of the current thread    public static void clearDataSource() {
        ();
    }

    // Determine the current data source (called by AbstractRoutingDataSource)    @Override
    protected Object determineCurrentLookupKey() {
        return ();
    }
}

Detailed code explanation：

  • contextHolder:useThreadLocalStoring the data source identifier of the current thread (masterorslave）。ThreadLocalEnsure that each thread has an independent copy of variables and will not interfere with other threads.

  • setDataSource: Used to set the data source of the current thread, and pass it inmasterorslaveTo specify the master or slave library.

  • clearDataSource: Used to clear the data source of the current thread to avoid confusion between data source information.

  • determineCurrentLookupKey:This isAbstractRoutingDataSourceProvided method, it will be called every time the database operation. according tocontextHolderIn the data source identifier, select the master library or slave library.

2.3.3 Injecting master-slave data source into dynamic data source

We need to inject the data source of the master and slave libraries into the dynamic data source and automatically select according to different business needs.

@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource(
        @Qualifier("masterDataSource") DataSource masterDataSource,
        @Qualifier("slaveDataSource") DataSource slaveDataSource) {

    Map<Object, Object> targetDataSources = new HashMap<>();
    ("master", masterDataSource);
    ("slave", slaveDataSource);

    DynamicDataSource dynamicDataSource = new DynamicDataSource();
    (masterDataSource); // Set the default data source    (targetDataSources);      // Add the data sources of the master and slave libraries to the route
    return dynamicDataSource;
}

Detailed code explanation：

  • @Qualifier:SpecifymasterDataSourceandslaveDataSource, pass in the data source of the master library and the slave library through dependency injectiondynamicDataSource。

  • targetDataSources:WillmasterandslaveAs keys, map tomasterDataSourceandslaveDataSource。DynamicDataSourceWill passdetermineCurrentLookupKeyThe method automatically selects the corresponding data source.

  • setDefaultTargetDataSource: Set the default data source. In the absence of a specified data source, the system will use the default data source (usually the main library).

2.3.4 Configuring EntityManagerFactory Use Dynamic Data Sources

For projects using JPA,EntityManagerFactoryIt is one of the core components of JPA. It is responsible for managing the entity manager and handling the persistence of JPA operations. Here you need toEntityManagerFactoryConfigure to use dynamic data sources to achieve master-slave separation.

@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
        @Qualifier("dynamicDataSource") DataSource dynamicDataSource) {
    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    (dynamicDataSource); // Use dynamic data sources    (""); // Entity package name    (new HibernateJpaVendorAdapter()); // Set the JPA vendor to Hibernate    return em;
}

Detailed code explanation：

  • setDataSource: Set the data source of JPA todynamicDataSource, so that JPA will select the appropriate data source according to the routing of the dynamic data source.

  • setPackagesToScan: Specify the package path where the JPA entity class is located. JPA will scan the entity class in the package to map database operations.

  • HibernateJpaVendorAdapter: Set up the vendor adapter for JPA. Here we use Hibernate as an implementation of JPA, which provides Hibernate-specific optimization and configuration support.

By configurationEntityManagerFactoryusedynamicDataSource, We can let JPA automatically switch to the master or slave library according to business needs when operating the database, thereby realizing master-slave separation and read-write separation.

2.3.5 Configuring the Transaction Manager

Spring Data JPA requires a transaction manager to manage transactions by default. We need to configure a dynamic data sourceJpaTransactionManagerto ensure that transaction operations can be applied correctly to the currently selected data source (master or slave).

@Bean(name = "transactionManager")
public JpaTransactionManager transactionManager(
        @Qualifier("entityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
    return new JpaTransactionManager(());
}

Detailed code explanation：

  • JpaTransactionManager: The JPA transaction manager provided by Spring, which will automatically handle itEntityManagercreate and close, and ensure ACID characteristics (atomicity, consistency, isolation, persistence) of database operations in transactions.

  • ():WillentityManagerFactory(Entity Manager Factory with dynamic data source configured) Pass toJpaTransactionManager. In this way, the transaction manager manages transactions based on dynamic data source routing to ensure transaction consistency.

3. Realize dynamic switching of data sources

In order to realize the master-slave switching of the database, so that the Spring Boot project can automatically select the master or slave library according to the operation type, we need to implement dynamic switching of the data source. The key steps to implement dynamic switching include: defining custom annotations, creating AOP facets, and dynamically deciding which data source to use when the method is executed.

3.1 Create @DataSource annotation (used to identify which data source to use)

First, we create a custom annotation@DataSource, used to identify the data source type specified on a specific method or class (such as the main librarymasterOr from the libraryslave). With this annotation, we can flexibly specify in the code which operations use the main library and which operations use the slave library.

package ;

import .*;

@Target({, }) // Can be used on methods or classes@Retention()             //Reserve at runtime, easy to obtain through reflection@Documented
public @interface DataSource {
    String value() default "master"; // Use the main library by default}

Annotation parameter description：

  • @Target: Define the use location of the annotation.It means that it can be used in the method.Denotes that it can act on the class.

  • @Retention: Specify the retention policy of the annotation asRUNTIME, i.e. the annotation will be retained until runtime and can be obtained by reflection, so that the facet class can recognize the annotation at runtime.

  • value: The annotated attribute is used to specify the data source type, and the default is"master", represents the main library. We can set the annotation when using@DataSource("slave")To specify the slave library.

3.2 Create DataSourceAspect facet class (dynamic switching of data sources according to annotations)

AOP (Shape-oriented Programming) can dynamically cut into code logic before and after method calls. Here we write an AOP section for before the method call, according to@DataSourceThe annotation value sets the data source. After the method call, clear the identity of the data source to ensure that subsequent operations are not affected.

package ;

import ;
import ;
import ;
import .*;
import ;
import ;

@Aspect
@Component
public class DataSourceAspect {

    // Define the point-cut point: Match the method or class with @DataSource annotation    @Pointcut("@annotation() || @within()")
    public void dataSourcePointCut() {
    }

    // Before the method is executed, switch the data source according to the annotation value    @Before("dataSourcePointCut()")
    public void before(JoinPoint point) {
        String dataSource = "master"; // Use the main library by default        Class<?> targetClass = ().getClass();
        MethodSignature signature = (MethodSignature) ();
        
        // Get the @DataSource annotation on the method        DataSource ds = ().getAnnotation();
        if (ds != null) {
            dataSource = ();
        } else if (()) {
            // If there is no annotation on the method, read the @DataSource annotation on the class            ds = ();
            if (ds != null) {
                dataSource = ();
            }
        }

        // Set the data source identifier of the current thread        (dataSource);
    }

    // After the method is executed, clear the data source identifier    @After("dataSourcePointCut()")
    public void after(JoinPoint point) {
        ();
    }
}

Detailed explanation of the section code：

  • @Pointcut: Define the tangent pointdataSourcePointCut, used to match all with@DataSourceThe method or class of annotation. This means that we can use it on methods and classes.@DataSourceTo control data source selection.

  • @Before: Triggered before the target method is executedbeforemethod.

    • MethodSignature:passMethodSignatureYou can get annotations for the method.

    • dataSource: The default value is"master"(Main library). First check the method@DataSourceAnnotation, if not found, check the class@DataSourceAnnotation.

    • (dataSource): Set the data source (master or slave) used by the current thread according to the annotation value. This allows subsequent database operations to be performed according to the data source specified by the annotation.

  • @After: Triggered after the target method is executedafterMethod, used to clear the data source identity of the current thread to avoid interference with other requests during thread reuse.()The method will remove the data source identifier of the current thread.

4. Use in business code

4.1 Use @DataSource annotation

In business code, you can add it on business methods that need to use the master or slave library.@DataSourceAnnotation. By default,@DataSourceAnnotatedvalueThe attribute is"master", represents the main library. We can mark the query class method@DataSource("slave")To use slave libraries, thereby achieving read and write separation.

4.1.1.Use @DataSource annotation at the service layer

Suppose we have oneUserServiceService class, this class provides the functions of querying user list and saving users. We can pass@DataSourceAnnotation specifies the database used.

import ;
import ;
import ;
import ;

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    // Use slave to read    @DataSource("slave")
    public List<User> getUsers() {
        // Database query operation, here you will select the database through the section selection        return ();
    }

    // Use the master to write    @DataSource("master")
    public void saveUser(User user) {
        // For database writing operation, the main library will be selected through the sections here.        (user);
    }
}

Example description：

  • getUsers method: Due to the label@DataSource("slave")Annotation, so it is executedgetUsersWhen the method is used, the slave library will be selected as the current data source, so that the query operation can be completed by the slave library and reduce the pressure on the master library.

  • saveUser method: marked@DataSource("master")Annotation, so it is executedsaveUserWhen the method is used, the main library will be selected as the current data source to ensure that the data writing operation is carried out on the main library and ensure the consistency of the data.

4.1.2. Use @DataSource annotation in the DAO layer (data persistence layer)

Suppose we further refine the data source control to the DAO layer. For example, inUserRepositorySpecify the data source on the query and save methods in  .

import ;
import ;
import ;
import ;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // Use slave to read    @DataSource("slave")
    List<User> findByName(String name);

    // Use the master to write    @DataSource("master")
    User save(User user);
}

Example description：

  • findByName method: This method is marked@DataSource("slave"), so invokingfindByNameWhen the data query operation will be completed by the library.

  • save method: This method is marked@DataSource("master"), ensure that data writing operations are always carried out through the main library, ensuring data integrity.

Notice:Will@DataSourceThe annotation will take effect when placed in the service layer and the DAO layer. In practical applications, we can decide which layer to implement the switching of data sources based on the complexity of the business logic.

4.2 Ensure that the read operation goes to the slave library and the write operation goes to the main library

By using it in business methods@DataSourceAnnotation can implement the following logic:

1. Read operation to go from the library: Add to the method of querying data@DataSource("slave"), make these methods be executed from the library.

  • The slave library is usually used to handle read operations. This can share the load of the main library and improve the system's read performance.
  • Because the data from the slave library comes from the replication of the main library, there is a certain delay, so it is generally not used in scenarios with strong consistency requirements, but is suitable for query scenarios with low real-time requirements.

2. Write operations to the main library: Add to the methods of inserting, updating, and deleting data@DataSource("master"), ensure that these operations are performed through the main library.

  • The main library is the source of data and is responsible for handling write, update and other operations to ensure the accuracy and consistency of the data.
  • This can avoid data inconsistency due to replication delays.

Example: InProductServiceRealize read and write separation

import ;
import ;
import ;

import ;

@Service
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    // Use to read product list from library    @DataSource("slave")
    public List<Product> getAllProducts() {
        // Execute query from the library        return ();
    }

    // Use the main library to save product information    @DataSource("master")
    public void addProduct(Product product) {
        // The main library performs an insert operation        (product);
    }

    // Update product information and use the main library    @DataSource("master")
    public void updateProduct(Product product) {
        // The main library performs an update operation        (product);
    }

    // Delete product information and use the main library    @DataSource("master")
    public void deleteProductById(Long productId) {
        // The main library performs a delete operation        (productId);
    }
}

Code explanation:

  • getAllProducts() method：

    • use@DataSource("slave")Annotation: The query operation in this method will be performed by the library.
    • Suitable for read operations to reduce the pressure on the main library.

  • addProduct(), updateProduct() and deleteProductById() methods：

    • These methods are used@DataSource("master")Annotation, so it will go to the main library.
    • Suitable for write operations (add, modify, delete), ensuring strong consistency and real-time data.

4.3 Things to note

When implementing read and write separation, the following points need to be paid attention to:

  • Master-slave data consistency: The data from the slave library comes from the replication of the main library, so there is a certain delay. For operations that do not allow data delays, it is recommended to force the use of the main library. For example, operations such as order payment or inventory update usually require high real-time requirements and should be directly connected to the main library.

  • Transaction Management: Read operations in a transaction may cause data source switching to fail because the main library is used by default in a transaction. In this case, you can use@Transactional(readOnly = true)The tag method is read-only transaction, allowing Spring to still use the slave library in the transaction.

  • Thread safety: Because data source switching is based onThreadLocalImplemented, so the data source identifier can be safely set and switched in a multi-threaded environment. However, if there is an asynchronous operation, it may cause the data source information transfer to fail, and special attention is required.

5. Testing and Verification

After completing the configuration of multiple data sources in Spring Boot projects, you need to test whether the project can correctly implement read-write separation. The main steps include: checking the service status of the MySQL master and slave library, starting the project, writing and running test classes, and verifying the data synchronization of the master and slave database.

5.1 Check the service status of the MySQL master and slave libraries

To ensure that your Spring Boot project can connect to the master and slave database, you first need to confirm the service status of the MySQL master and slave database. If the master and slave libraries are not started, the Spring Boot application will not be able to connect to the database.

5.1.1 Check the MySQL service status using the command line

On both the master and slave servers, you can check the MySQL service status using the following command:

# Check if the MySQL service is runningsudo systemctl status mysql

If displayedactive (running), it means that the MySQL service is running.

5.1.2 Start MySQL Service using the command line

If the MySQL service is not running, you can start it with the following command:

# Start MySQL servicesudo systemctl start mysql

After starting the service, you can use it againstatusCommands check the service status to ensure that the MySQL service is started.

Notice: If the master library and slave library are on different servers, you need to execute the above commands on the master library server and slave server respectively.

5.2 Start Spring Boot Project with IntelliJ IDEA

After confirming that both the MySQL master and slave library are started, you can start the Spring Boot project.

5.2.1 Start Spring Boot Project in IntelliJ IDEA

  • Open the project: Open your Spring Boot project in IntelliJ IDEA.
  • Positioning the main category: On the projectsrc/main/javaIn the directory, find the main class (usually with@SpringBootApplicationAnnotated classes, such asYourApplicationkind).
  • Run the project: Right-click on the main class file and select "Run 'YourApplication'" (RunYourApplication) option. IDEA will display the startup log in the console.

5.2.2 Check console output

After the project is started, check the log output of the IDEA console to ensure that there is no error message. If you see something similar to the following, the project starts successfully:

INFO 12345 --- [main]        : Started YourApplication in 3.456 seconds (JVM running for 4.123)

5.3 Write a test class to test whether the read and write operations follow the corresponding data source as expected

After the project is started, we can write a test class to test whether the master-slave separation is achieved in the Spring Boot project (read operations and slave libraries, write operations and master libraries).

5.3.1 Create a test class

On the projectsrc/test/javaIn the directory, create a new test class, for exampleUserServiceTest, used to test read and write methods in service classes. Here is the sample code for the test class:

import ;
import ;
import ;
import ;
import ;

@SpringBootTest
public class UserServiceTest {

    @Autowired
    private UserService userService;

    // Test reading operation    @Test
    public void testReadOperation() {
        // Call the method to read the user        ("Executing read operation (should use slave database)...");
        ();
    }

    // Test write operation    @Test
    public void testWriteOperation() {
        // Create a new user        User user = new User();
        ("Test User");
        ("testuser@");

        // Call the method to save the user        ("Executing write operation (should use master database)...");
        (user);
    }
}

Code description

  • @SpringBootTest: This annotation is used to start the Spring Boot context in the test class. It automatically loads the configuration file and application context, ensuring that dependencies can be injected into the test class.
  • @Autowired: InjectUserService, so as to call in the test methodgetUsersandsaveUsermethod.
  • @Test: Each test method is used@TestAnnotation indicates that this is a test case, and the test framework will automatically run with@TestMethod.

Test method of test class

  • testReadOperation: Test the read operation from the library.

    • This method will be called(), this should use the query operation from the library. We can confirm in the log whether we have successfully reached the library.

  • testWriteOperation: Test the write operation of the main library.

    • This method will create a new user and call(user), the main library should be used to perform the insertion operation. You can check whether the main library is successfully used in the log.

5.3.3 Run tests in IntelliJ IDEA

In IntelliJ IDEA, you can run the test class by following the steps:

  • Run a single test method:existtestReadOperationortestWriteOperationRight-click on the method and select "Run 'testReadOperation'" or "Run 'testWriteOperation'" to run the specific test.
  • Run the entire test class:existUserServiceTestRight-click on the class name and select "Run 'UserServiceTest'" to run all test methods.

5.3.4 Check the test output

View output information in the console. If you areDynamicDataSourceLog information has been added to the class, and you can see logs similar to the following:

Switching to data source: slave
Executing read operation (should use slave database)...
Switching to data source: master
Executing write operation (should use master database)...
  • Read the operation log:iftestReadOperationLog displaySwitching to data source: slave, which means that the read operation has successfully left the library.
  • Write operation log:iftestWriteOperationLog displaySwitching to data source: master, which means that the write operation has successfully left the main library.

5.4 Check the data synchronization of the master and slave database

After verifying that the read and write operation has left the correct data source, it is also necessary to check the data synchronization status of the master and slave database to confirm whether the data changes in the master database have been successfully synchronized to the slave database.

5.4.1 Perform write operations and check data synchronization

  • Run the write test method:passtestWriteOperationOr directly call the write method in the service to insert new data into the main library.

  • Check data in the main library: Connect to the main library, execute the following query to confirm whether the data is successfully inserted:

    SELECT * FROM your_database.users WHERE name = 'Test User';

  • Check data synchronization from the library: After waiting for a while, connect to the slave library, execute the same query, and check whether the data is synchronized:

    SELECT * FROM your_database.users WHERE name = 'Test User';

    If this record can also be queried from the slave library, it means that the data of the master library has been successfully synchronized to the slave library.

5.4.2 Perform update operations and check data synchronization

  • Run the update operation: Update a field of the record in the main library, for example, by(user)Method updateemailField (assuming that the method exists), or execute update SQL statements directly in the main library:

    UPDATE your_database.users SET email = 'updateduser@' WHERE name = 'Test User';

  • Check data synchronization from the library: After waiting for a while, execute the same query from the library, confirmemailWhether the field has been updated to'updateduser@'。

5.4.3 Perform the deletion operation and check the data synchronization

  • Run the delete operation:pass(userId)Method (assuming that exists) or execute the delete statement directly in the main library:

    DELETE FROM your_database.users WHERE name = 'Test User';

  • Check data synchronization from the library: Execute the following query from the library to confirm whether the data has been deleted synchronously:

    SELECT * FROM your_database.users WHERE name = 'Test User';

    If the query result is empty, it means that the delete operation has been successfully synchronized to the slave library.

This is the article about the implementation steps of MySQL master-slave copy data synchronization. For more related content on MySQL master-slave copy data synchronization, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!