MySQL Replication, master-slave and dual-host configuration

MySQL Replication, master-slave and dual-host configuration

MySQL replication is a multi-MySQL database master from a synchronous scheme, characterized by asynchrony, widely used in a variety of situations where MySQL has higher performance and higher reliability requirements. Another technique that corresponds to this is the synchronous MySQL Cluster, but because of the complexity, fewer users.

Is the MySQL official gave the scene using replication:

Replication Principle

MySQL Replication is an asynchronous replication process that replicates from one MySQL node (called master) to another MySQL node (called the 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.

To implement MySQL's Replication, you must first turn on the Binary log on the master side, because the entire replication process is actually Slave the log from the master side and then the full sequence of execution logs on its own.

It seems that the replication principle of MySQL is very simple, summed up:
* Each from only one master can be set.
* After the master executes the SQL, log the binary log file (Bin-log).
* From the connection master, and obtain Binlog from the master, stored in the local relay-log, and from the location of the last remembered to execute SQL, once encountered an error will stop synchronization.

Judging from these replication principles, these inferences can be inferred:
* 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, then it is very likely to be from the execution of the main Bin-log error and stop synchronization, this 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 modification does not conflict, can work well.
* If you need multi-master, you can use a ring configuration, so that any one of the node's modifications 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 dual-master settings, that is, two MySQL nodes are mainly prepared for each other, although in theory, as long as the data do not conflict with the dual-master can work very well, but the actual situation is still very tolerant of data conflicts, such as the completion of synchronization, both sides to 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.
When setting up a dual master, simply copy one of the above settings and write the configuration files for each of the two MySQL nodes, 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. Another: Don't forget to create a user for each other on two nodes.load balancing for application tiersThis article only describes the MySQL itself repilication configuration, in the above diagram can also be seen, with replication, also need to apply layer (or middleware) to do a load balancing, so as to maximize the advantage of MySQL replication, These will be explored in the future.

MySQL Replication, master-slave and dual-host configuration