MySQL Master-slave 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.

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

Assume that MySQL is installed on 192.168.0.1, 192.168.0.2, and 192.168.0.1 as the primary database.

First, on master, create a user for slave:
GRANT REPLICATION SLAVE, REPLICATION CLIENT on *. * to ' SLAVE ' @ ' 192.168.0.1 ' identified by ' MySQL ';

Modify the master's my.cnf or My.ini file, and add it below the section [Mysqld]:

Server-id=1  Log-bin=master-binlog-bin-index=master-bin.index

To restart the MySQL service, use the: Show MASTER status command to view the status of the binary log and note the value of the position, which is used when configuring from the database.

File Position binlog_do_db binlog_ignore_db
-----------------         --------         ------------            ------------------
master-bin.000001 2794

Modify the slave my.cnf or My.ini file, and add it below the section [Mysqld]:

Server-id=2relay-log-index=slave-relay-bin.indexrelay-log=slave-relay-bin

Note that Server-id cannot be duplicated. After restarting the service, execute the following command in slave:

Change MASTER to master_host= ' 192.168.0.52 ', master_port=3306,master_user= ' repl ', master_password= ' MySQL ', master_ Log_file= ' master-bin.000001 ', master_log_pos=1840;

Where Master_log_pos is the value of position in the MASTER binary log. Finally execute the command in SLAVE: START SLAVE;

At the end of the configuration, it is now possible to master the data again, get the Add table, and so on, slave detects that the binary log changes after the asynchronous replication.

MySQL Master-slave configuration