Database Master-Slave separation

What are the benefits of database read/write separation?

1. Separate read and write operations on different databases to avoid performance bottlenecks on the primary server;

2. When the primary server writes, it does not affect the query performance of the query application server, reduces the congestion and improves concurrency;

3. Data has multiple disaster recovery copies, improve data security, and when the primary server failure, can immediately switch to other servers, improve system availability;

The basic principle of read and write separation is to allow the primary database to handle transactional increment, change, delete operations (INSERT, UPDATE, delete) operations, and to process select queries from the database. Database replication is used to synchronize changes caused by transactional operations to other slave databases. In SQL, for example, the main library is responsible for writing data and reading data. The Read library is only responsible for reading data. Each time there is a write library operation, synchronize updates to the Read library. Write library on one, read library can have more than one, the use of log synchronization to achieve the main library and multiple read library data synchronization.

 replication principle  
MySQL Replication is an asynchronous replication process that replicates from one MySQL node (called master) to another MySQL node (called slave). The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and I/O thread) are on the Slave side, and another thread (I/O thread) on the master side.  
  www.2cto.com  
to implement the Replication of MySQL, you must first turn on the Binary Log on the master side, because the entire copy process is actually Slave from the master The end gets the log and then performs the various operations that are logged in the full sequential execution log on its own.  

It seems that the replication principle of MySQL is very simple, summed up:  
* each from only one master can be set.  
  * Log the binary log file (Bin-log) after the main execution of SQL.  
  * From the connection master, and get Binlog from the main, stored in the local relay-log, and from the location of the last remember to execute SQL, once encountered an error will stop synchronization.  
 
from these replication principles, you can have these inferences:  
* Master-slave database is not real-time synchronization, even if the network connection is normal, there is an instant, master-slave data inconsistency.  
  * If the master-slave network is disconnected, from the network after normal, batch synchronization.  
  * If you modify the data from, it is very likely to stop the synchronization from the error that occurred while executing the main bin-log, which is a very dangerous operation. So in general, be very careful to modify the data from above.  
  * A derivative configuration is a dual-master, mutual main from the configuration, as long as the two sides of the changes do not conflict, can work well.  
  * If you need more than one master, you can use a ring configuration so that any node modification can be synchronized to all nodes.

Master and slave settings

Because the principle is relatively simple, so replication from MySQL 3 support, and on all platforms can work, multiple MySQL nodes can even different platforms, different versions, different LAN. The replication configuration includes both the user and My.ini (Linux under MY.CNF) settings.

First, on the main MySQL node, create a user for slave:

GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to ' SLAVE ' @ ' 192.168.1.10 ' identified by ' SLAVE ';

In fact, in order to support the master-slave dynamic synchronization, or manual switching, it is generally created on all the master and slave nodes on this user. Then there is the configuration of MySQL itself, which requires modifying the MY.CNF or My.ini files. In the Mysqld section, add the following:

Server-id=1
auto-increment-increment=2
Auto-increment-offset=1
Log-bin
Binlog-do-db=mstest
Binlog_format=mixed

master-host=192.168.1.62
Master-user=slave
Master-password=slave
Replicate-do-db=mstest

Above these two paragraphs set, the previous paragraph is the main and set, the latter paragraph is set from. In other words, on the two MySQL nodes, each plus a paragraph is good. Binlog-do-db and Replicate-do-db are set up the corresponding database needs to be synchronized, auto-increment-increment and Auto-increment-offset are set to support dual-master (refer to the next section) , can not be set when only the master from.

Dual-master settings  

from the original theory, MySQL also supports the dual-master settings, that is, two MySQL nodes are master of each other, although in theory, the dual-master as long as the data does not conflict can work very well, but the actual situation is still very tolerant of data conflicts, such as before the completion of synchronization , both sides modify the same record. So in practice, it's best not to let both sides change at the same time. That logically still works in the master-slave way. But the dual-master setting still makes sense, because after doing so, switching the master and standby will be very simple. Because after a failure, if you have previously configured dual-master, it is easy to switch the master backup directly.  
  Double master when set up, just copy one of the above settings, write the configuration files of two MySQL nodes separately, but modify the corresponding Server-id,auto-increment-offset and master-host. Auto-increment-offset is to allow the dual-master to add operations in a table without an ID conflict, so it is good to set the Auto-increment-offset to a different value on two nodes.  : Don't forget to create a user for each other on two nodes. Application tier Load Balancing   This article only introduces MySQL's own repilication configuration, as can be seen in the above figure, with the replication, but also the application layer (or middleware) to do a load balancing, so as to maximize the use of MySQL Replication's advantages, these will be explored in the future.

MySQL Replication is an asynchronous copy process, copied from one MySQL instace (we call Master) to another MySQL instance (we call it Slave). The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the Slave side, and another thread (IO thread) on the master side. To implement MySQL Replication, you must first turn on the binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it will not be possible. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence. The Binary Log for MySQL can be opened by using the "-log-bin" parameter option in the process of starting MySQL Server, or by adding "Log-bi" in the mysqld parameter group in the MY.CNF configuration file (the Parameters section after the [mysqld] identity) N "parameter entry.
The basic process for www.2cto.com MySQL replication is as follows: 1.　 　Slave the above IO line thread attached the Master and requests the log content from the specified location (or from the beginning of the log) to the designated log file; 2. When Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side. In addition to the information contained in the log, the return information includes the name of the binary log file on the Master side of the returned information and its location in binary log;

Database Master-Slave separation