I need to use separate reader/writer MySQL servers. One writer and one (or more) read replica.
What is the best way to do this?
I found a lot of examples:
http://www.dragishak.com/?p=307
Use special JDBC driver: com.mysql.jdbc.ReplicationDriver
And usage is:
@Transactional(readOnly=true)
@ReadOnlyConnection
public Result serviceMethod(…) {
…
}
https://stackoverflow.com/a/33169879/1974494
Using Spring AbstractRoutingDatasource:
Usage:
@Transactional(readOnly = true)
public Page<BookDTO> getBooks(Pageable p) {
try{
DbContextHolder.setDbType(DbType.REPLICA1); // <----- set ThreadLocal DataSource lookup key
In each method I need to set DbType.
Is possible to automatically send "read query" to replica server and "write query" (insert/update) to master server?
And second question:
I would like to have one mysql database per user (very big). I expect about 2 000 users. So I cannot define datasource for each users * reader+writer.
Example:
spring.ds_items.driverClassName=com.mysql.jdbc.Driver
spring.ds_items.url=jdbc:mysql://mysql-master/user1
spring.ds_items.username=root
spring.ds_items.password=12345
spring.ds_items.driverClassName=com.mysql.jdbc.Driver
spring.ds_items.url=jdbc:mysql://mysql-replica1/user1
spring.ds_items.username=root
spring.ds_items.password=12345
spring.ds_items.driverClassName=com.mysql.jdbc.Driver
spring.ds_items.url=jdbc:mysql://mysql-master/user2
spring.ds_items.username=root
spring.ds_items.password=12345
spring.ds_items.driverClassName=com.mysql.jdbc.Driver
spring.ds_items.url=jdbc:mysql://mysql-replica1/user2
spring.ds_items.username=root
spring.ds_items.password=12345
I would like to have one "main mysql database" with table something like:
user db_name
--------------
test1 db_test1
test2 db_test2
And if I need some data for user test2 I look at "main database" and get info for database db_test2 to use. After that send query to database db_test2.
