MySQL 读写分离与负载均衡

默北 MySQL1 23,9955字数 2285阅读7分37秒阅读模式

MySQL 数据库的读写分离和负载均衡一般是通过第三方软件来实现的。 也可以通过mysql驱动程序来实现,如com.mysql.jdbc.ReplicationDriver。

详细文档参见:http://dev.mysql.com/doc/refman/5.5/en/connector-j-info.html文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Properties;
 
import com.mysql.jdbc.ReplicationDriver;
 
public class ReplicationDriverDemo {
 
  public static void main(String[] args) throws Exception {
    ReplicationDriver driver = new ReplicationDriver();
 
    Properties props = new Properties();
 
    // We want this for failover on the slaves
    props.put("autoReconnect", "true");
 
    // We want to load balance between the slaves
    props.put("roundRobinLoadBalance", "true");
 
    props.put("user", "foo");
    props.put("password", "bar");
 
    //
    // Looks like a normal MySQL JDBC url, with a
    // comma-separated list of hosts, the first
    // being the 'master', the rest being any number
    // of slaves that the driver will load balance against
    //
 
    Connection conn =
        driver.connect("jdbc:mysql:replication://master,slave1,slave2,slave3/test",
            props);
 
    //
    // Perform read/write work on the master
    // by setting the read-only flag to "false"
    //
 
    conn.setReadOnly(false);
    conn.setAutoCommit(false);
    conn.createStatement().executeUpdate("UPDATE some_table ....");
    conn.commit();
 
    //
    // Now, do a query from a slave, the driver automatically picks one
    // from the list
    //
 
    conn.setReadOnly(true);
 
    ResultSet rs =
      conn.createStatement().executeQuery("SELECT a,b FROM alt_table");
 
     .......
  }
}

读写分离:

jdbc:mysql:replication://master:3306,slave1:3306,slave2:3306/dbname

When using the following connection string: jdbc:mysql:replication://dbmaster:3306,dbslave1:3306,dbslave2:3306/dbname文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

dbmaster is used for all write connections as expected and dbslave1 is used for all read connections, but dbslave2 is never used. I would have expected distributed reads between dbslave1 and dbslave2.文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

原理是:ReplicationDriver生成代理的connection对象,当设置这个connection.readOnly=true时,连接slave,当connection.readOnly=false时,连接master文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

负载均衡:

jdbc:mysql:loadbalance://master:3306,slave1:3306,slave2:3306/dbname

When using the following connection string: jdbc:mysql:loadbalance://dbmaster:3306,dbslave1:3306,dbslave2:3306/dbname
connections are load-balanced between all three servers for both read and write connections.文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

问题:文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

读写分离时可能会碰到刚写完master,再马上到slave进行查询的情况,而主从复制的时候有延迟,这时怎么解决呢?有两个办法:文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

1. 比如增加页面保存数据后马上跳转到列表页面,这时可能出不来数据,因为复制还没完成,这时可以在前台添加一些成功的提示,成功页面等进行一些页面跳转延迟处理,让服务器有时间去复制(复制延迟一般在毫秒级,而这种提示处理在秒级,所以时间上一般是足够的)文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

2. 第1种办法可能部分场景是可行的,但是有些场景要求比较高,需要实时的,这时可以在读取的时候进行处理,强制从master中读取,可以通过注解,加参数/标识等来指定从master读取数据文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
默北
  • 本文由 发表于 17/12/2014 01:00:20
  • 转载请务必保留本文链接:https://www.ttlsa.com/mysql/read-and-write-separation-and-load-balanced/
评论  1  访客  1
    • 匿名
      匿名 9

      :neu吗tral:

    评论已关闭!