A brief analysis of MySQL read-write separation and load Balancing example tutorial

Source: Internet
Author: User

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:

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:

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.