MySQL数据库实现读写分离与负载均衡

2024-07-24 12:39:50

字体：大中小

来源：转载

供稿：网友

最近在一个群里老看到有一个朋友问游戏数据充值如何实现读写分享了,在此小编正好看到了一篇文章下面整理与各位看看,MySQL 数据库的读写分离和负载均衡一般是通过第三方软件来实现的,也可以通过mysql驱动程序来实现,如com.mysql.jdbc.ReplicationDriver.

详细文档参见:http://dev.mysql.com/doc/refman/5.5/en/connector-j-info.html

代码如下:

import java.sql.Connection;

import java.sql.ResultSet;

import java.util.Properties;



import com.mysql.jdbc.ReplicationDriver;



public class ReplicationDriverDemo {

  //开源软件:Vevb.com

  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

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.

原理是:ReplicationDriver生成代理的connection对象,当设置这个connection.readOnly=true时,连接slave,当connection.readOnly=false时,连接master.

负载均衡:

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.