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.