MySQL master-slave replication principle

A simple and complete Mysql master-slave copy, read and write separated.

1. First build Mysql master-slave architecture to automatically copy Mater data to Slave

MySQL replication works very simply, one server as a host, and one or more servers as slave computers. The host will log changes to the database. Once these changes are recorded to the log, they are sent to the slave immediately (or at a set interval).

Using MySQL replication provides the ability to scale large Web sites where the databases are primarily read operations (selects). Slave-to-host consumed are rare (typically 1% overhead per slave), and deploying 30 slave machines per host in a large web site is also common.

Asynchronous replication vs. synchronous replication

Asynchronous replication: MySQL natively supports one-way, asynchronous replication. Asynchronous replication means there is a delay in copying data from one machine to another-most importantly this means that the data cannot be copied/applied to the slave at the same time when the application's transaction commit has been confirmed. Typically, this delay is determined by network bandwidth, resource availability, and system load. However, with the right components and tuning, replication can be done almost instantaneously.

Synchronous replication: Synchronous replication can be defined as data being submitted to one or more machines at the same time, usually through a well-known "two-phase commit". While this does give you consistency in multiple systems, it also degrades performance due to increased additional message exchange.

MySQL, which uses the MyISAM or InnoDB storage engine, does not natively support synchronous replication, but some technologies, such as the Distributed Replication Block device (DRBD), can provide synchronous replication on the underlying file system. Allow the second MySQL server to take over if the primary server is lost (using a replica of the second server). To learn more, see: http://www.drbd.org/

Asynchronous replication scenarios:

1. Mysql Database Installation

The installation process is omitted: See more: http://pengranxiang.iteye.com/admin/blogs/1138059

Server master:192.168.14.131

Mysql installation directory:/home/mysql/mysql (use source installation, standalone directory)

Server slave:192.168.14.132

Mysql installation directory:/home/mysql/mysql

2. Modify the configuration

In order not to affect the original configuration file:/etc/my.cnf

Create a new configuration file,

Cp/etc/my.cnf/home/mysql/mysql/conf/master.cnf

Cp/etc/my.cnf/home/mysql/mysql/conf/slave.cnf

Modify the MASTER.CNF, add the following settings,

(Official note: In order to use transactional InnoDB for maximum persistence and consistency in replication, you should specify the innodb_flush_log_at_trx_commit=1,sync_binlog=1 option.) ）

CNF Code

1. Log-bin=mysql-bin #slave会基于此log-bin to do replication.
2. server-id=1 #master的标示
4. innodb_flush_log_at_trx_commit=1
6. sync_binlog=1

Modify SLAVE.CNF

CNF Code

1. [Mysqld]
3. server-id=2 #slave的标示

3. Start the service

CNF Code

1. # Master
3. # If Mysql is started, turn it off first.
5. /home/mysql/mysql/bin/mysqladmin-u root-p shutdown
7. # start MySQL with the modified MASTER.CNF
9. /home/mysql/mysql/bin/mysqld_safe--DEFAULTS-FILE=/HOME/MYSQL/MYSQL/CONF/MASTER.CNF &

Java code

1. # Slave
3. # If Mysql is started, turn it off first.
5. /home/mysql/mysql/bin/mysqladmin-u root-p shutdown
7. # start MySQL with the modified SLAVE.CNF
9. /home/mysql/mysql/bin/mysqld_safe--DEFAULTS-FILE=/HOME/MYSQL/MYSQL/CONF/SLAVE.CNF &

4. Create an account on Master that is dedicated to replication Repl_user

5. Start the master-slave copy function

Need to see the master status in master

Mysql> Show master status;

And then Slave, start the replication

The above window is the connection Master, the following window connects Slave

6. Test replication

Insert a piece of data in Master and then query in Slave. can be verified.

2 Simple read-write separation implementation

The read-write separation can be implemented either directly on the client or through a proxy server.

Proxy servers generally have the option of:

Official: MySQL proxy address: http://dev.mysql.com/downloads/mysql-proxy/#downloads

Domestic Open source project: Amoeba

Amoeba Developer Blog: http://amoeba.meidusa.com

Amoeba Open Source project address: Http://www.sourceforge.net/projects/amoeba
Amoeba Chinese Document: Http://amoeba.meidusa.com/amoeba.pdf

This demonstrates only the simplest scenario: JDBC directly implements read-write separation.

Java code

2. Package Prx.dao;
6. Import java.sql.Connection;
8. Import Java.sql.ResultSet;
10. Import java.util.Properties;
14. Import Com.mysql.jdbc.ReplicationDriver;
18. Public class Test {
22. public static void Main (string[] args) throws Exception {
24. Replicationdriver Driver = new Replicationdriver ();
28. Properties props = new properties ();
32. //We want this for failover on the slaves
34. Props.put ("AutoReConnect", "true");
38. //We want to load balance between the slaves
40. Props.put ("roundrobinloadbalance", "true");
44. Props.put ("user", "foo");
46. Props.put ("password", "bar");
50. //    
52. //Looks like a normal MySQL JDBC URL with a
54. //comma-separated list of hosts, the first
56. //Being the ' master ', the rest being any number
58. //Of slaves that the driver would load balance against
60. //    
64. Connection conn = Driver.connect (
66. "Jdbc:mysql://master,slave1,slave2,slave3/test", props);
70. //    
72. //Perform read/write work on the master
74. //By setting the READ-ONLY flag to "false"
76. //  
78. //Whether the Conn ReadOnly is true to determine if the database to be connection connected is the primary database or the database
80. //False connection to primary database
82. //True for connections from the database
86. Conn.setreadonly (false);
90. Conn.setautocommit (false);
92. Conn.createstatement (). Executeupdate ("UPDATE some_table ....");
94. Conn.commit ();
98. //    
100. //Now, does a query from a slave, the driver automatically picks one
102. // from the list
104. //    
108. Conn.setreadonly (true);
112. ResultSet rs = conn.createstatement (). ExecuteQuery (
114. "Select a, b from alt_table");
118. }
120. }

MySQL master-slave replication principle