本教程我們來談談MySQL讀寫分離與負載均衡的原理及實現代碼實例,有需要的朋友可以參考學習一下。
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 {
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.
問題:
讀寫分離時可能會碰到剛寫完master,再馬上到slave進行查詢的情況,而主從復制的時候有延遲,這時怎麼解決呢?有兩個辦法:
1. 比如增加頁面保存數據後馬上跳轉到列表頁面,這時可能出不來數據,因為復制還沒完成,這時可以在前台添加一些成功的提示,成功頁面等進行一些頁面跳轉延遲處理,讓服務器有時間去復制(復制延遲一般在毫秒級,而這種提示處理在秒級,所以時間上一般是足夠的)
2. 第1種辦法可能部分場景是可行的,但是有些場景要求比較高,需要實時的,這時可以在讀取的時候進行處理,強制從master中讀取,可以通過注解,加參數/標識等來指定從master讀取數據