MySQL database realizes read-write separation and load balancing

The reading and writing separation and load balancing of MySQL database are usually realized by the third party software. It can also be implemented through the MySQL driver, such as Com.mysql.jdbc.ReplicationDriver.

Detailed documentation See also: http://dev.mysql.com/doc/refman/5.5/en/connector-j-info.html

The code is as follows

Copy Code

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 to 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 Being the ' master ', the rest being any number Of slaves that the driver would 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, does 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"); ....... } }

Read and write separation:

The code is as follows	Copy Code
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 to all write connections as expected and dbslave1 is used to all read connections, but Dbslave2 is Neve R used. I would have expected distributed reads between DBSLAVE1 and Dbslave2.

The principle is: Replicationdriver generation Agent Connection object, when setting this connection.readonly=true, connect slave, when Connection.readonly=false, Connect Master

Load Balancing:

The code is as follows	Copy Code
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.

Problem:

Read and write separation may encounter just finished master, and then immediately to the slave to query the situation, and the master-slave replication when there is a delay, then how to solve it? There are two ways:

1. For example, add a page to save data immediately jump to the list page, at this time may not come out of the data, because the replication has not been completed, at this time can add some success in the foreground of the prompts, successful pages, such as some page jump delay processing, so that the server has time to replicate (replication delay is generally in milliseconds, and this hint processing in the second level, So the time is generally enough)

2. The 1th approach may be part of the scenario is feasible, but some scenarios require a higher, real-time, which can be processed at the time of reading, forced read from master, can be annotated, plus parameters/identification, etc. to specify from master reading data

PS This approach is not the best way to achieve load balancing we can implement the server cluster.