Principle analysis
1. MySQL master-Slave Replication
- How it works: MySQL master-slave replication synchronizes data through binary logs. The master server records all changes into the binary log, reads these logs from the server and executes corresponding SQL statements to keep the data consistent with the master server.
- Delay issues: Due to network transmission and processing time, there may be short data lag in the slave library, which is a challenge for scenarios where real-time consistency is required.
2. Read and write separation
- Purpose: Improve system performance and availability. By assigning read requests to slave libraries and sending write requests to master libraries, the pressure on the master library can be reduced and the overall performance of the system can be improved.
- Implementation method: It can be implemented through database middleware or framework-level configuration. In this example, we use Apache ShardingSphere to achieve read-write separation.
3. Automatic failover
- principle: When the master library is detected to be unavailable, the system will automatically select a slave library as the new master library and read-write allocations are readjusted. This usually involves mechanisms such as heartbeat detection and status monitoring.
- Tool support: In addition to ShardingSphere, you can also use MHA (MySQL Master High Availability) or other high availability solutions.
Detailed explanation of implementation steps
1. Introduce dependencies
existAdd necessary dependencies to the Spring Boot Starter, MyBatis Starter, and ShardingSphere:
Dark version
<dependencies>
<!-- Spring Boot Starter -->
<dependency>
<groupId></groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<!-- MyBatis Starter -->
<dependency>
<groupId></groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<!-- ShardingSphere for read-write splitting -->
<dependency>
<groupId></groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
</dependencies>2. Configure the data source
editFile to configure multiple data sources and set ShardingSphere rules to achieve read and write separation:
Dark version
spring:
shardingsphere:
datasource:
names: master,slave0,slave1
master:
type:
driver-class-name:
jdbc-url: jdbc:mysql://master_host:3306/your_db?useSSL=false&serverTimezone=UTC
username: your_username
password: your_password
slave0:
type:
driver-class-name:
jdbc-url: jdbc:mysql://slave0_host:3306/your_db?useSSL=false&serverTimezone=UTC
username: your_username
password: your_password
slave1:
type:
driver-class-name:
jdbc-url: jdbc:mysql://slave1_host:3306/your_db?useSSL=false&serverTimezone=UTC
username: your_username
password: your_password
masterslave:
load-balance-algorithm-type: round_robin # Load balancing strategy name: ms_ds # Data source name master-data-source-name: master #Main library data source name slave-data-source-names: slave0,slave1 # List of Data Source Names from the Library props:
sql:
show: true # Whether to displaySQLStatement3. Configure MyBatis
Create the MyBatis Mapper interface and configure SQL statements using annotations or XML. Here we take the annotation method as an example:
Dark version
package ;
import ;
import ;
import ;
import ;
import ;
@Mapper
public interface UserMapper {
/**
* Query all user information.
* Note: For query operations, ShardingSphere will select one of the slave libraries to execute the query.
*
* @return User Information List
*/
@Select("SELECT * FROM users")
List<Map<String, Object>> findAllUsers();
/**
* Update user information.
* Note: Write operations will only be performed for the main library.
*
* @param id User ID
* @param newName New username
*/
void updateUserById(@Param("id") Long id, @Param("newName") String newName);
}Make sure your Spring Boot app scans to the Mapper interface. Can be added on the main class@MapperScanannotation:
Dark version
package ;
import ;
import ;
import ;
@SpringBootApplication
@MapperScan("")
public class DemoApplication {
public static void main(String[] args) {
(, args);
}
}4. Use Mapper for database operations
Now you can inject and use the Mapper interface to perform database operations in the Service layer:
Dark version
package ;
import ;
import ;
import ;
import ;
import ;
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
/**
* Get all user information.
*
* @return User Information List
*/
public List<Map<String, Object>> getAllUsers() {
return ();
}
/**
* Update user name.
*
* @param id User ID
* @param newName New username
*/
public void updateUserName(Long id, String newName) {
(id, newName);
}
}Notes and best practices
-
Transaction Management: Ensure that all write operations are executed in the same transaction and only write operations to the main library. Available
@TransactionalAnnotations to manage transactions. - Data consistency: Considering the problem of master-slave replication delay, in some scenarios (such as reading immediately after completing the write operation), it may be necessary to directly query the master library to ensure data consistency.
- Health check: It is recommended to monitor the master-slave status regularly to ensure that the slave library is synchronized normally and that the master library is accessible. It can be achieved through timing tasks or external tools.
- Performance optimization: Adjust load balancing strategies according to actual business needs, such as weight polling or other advanced algorithms to optimize query efficiency.
This is the article about the automatic master-slave switch of MySQL in the Spring Boot project combining MyBatis to implement MySQL. For more related contents of Spring Boot MyBatis automatic master-slave switch, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!