Spring + Hibernate：读取/写入不同的数据源和数千个mysql数据库 [英] Spring+Hibernate: read/write different datasource and thousands mysql databases

问题描述

我需要使用单独的读取器/写入器MySQL服务器。一名作家和一个（或多个）阅读副本。

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?

我发现了很多示例：

http://www.dragishak.com/?p= 307

使用特殊的JDBC驱动程序： com.mysql.jdbc.ReplicationDriver

Use special JDBC driver: com.mysql.jdbc.ReplicationDriver

其用法是：

@Transactional(readOnly=true)
@ReadOnlyConnection
public Result serviceMethod(…) {
   …
}

---

https://stackoverflow.com/a/33169879/1974494

使用Spring AbstractRoutingDatasource ：

用法：

@Transactional(readOnly = true)
public Page<BookDTO> getBooks(Pageable p) {
  try{
    DbContextHolder.setDbType(DbType.REPLICA1);   // <----- set ThreadLocal DataSource lookup key

在每种方法中，我需要设置DbType。

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?

第二个问题：

我想每个用户一个mysql数据库（非常大）。我预计大约有2000名用户。因此，我无法为每个用户*读者+写者定义数据源。

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.

示例：

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

我想拥有一个带有以下内容的主mysql数据库：

I would like to have one "main mysql database" with table something like:

user   db_name
--------------
test1  db_test1
test2  db_test2

如果我需要用户 test2 的一些数据我查看主数据库并获取要使用的数据库 db_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.

推荐答案

最好的方法是使用MySQL Connector / J复制感知驱动程序配置。您的连接字符串将如下所示

The Best Way is to use MySQL Connector/J replication aware driver configuration . Your Connection string will be as below

jdbc:mysql:replication://[master host][:port],[slave host 1][:port][,[slave host 2][:port]]...[/[database]]

将事务标记为只读后，它将自动路由为只读从属服务器

Once you tag a transaction as read only , it will be automatically routed to read only slaves

https://dev.mysql.com/doc/connector-j /5.1/zh-CN/connector-j-master-slave-replication-connection.html

这篇关于Spring + Hibernate：读取/写入不同的数据源和数千个mysql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！