MySQL master-slave replication, SSL-based master-slave replication, primary master replication and semi-synchronous replication

MySQL replication is primarily replicated from the primary master.

What is master-slave replication

Master-slave replication means that there is a single device to do as the primary server, one or more servers as from the server. No data is written from the server, and the source of the data is the primary server. The purpose of the replication is to synchronize data, if the data from the server is also written, in the master-slave architecture, the master server will not replicate data from the server, so will cause the data of the different steps, so, from the server can not write data. MySQL is the basis of replication, is binary log, because the binary log logs, all can change or potentially change the database data of the SQL statements, when the SQL statements from the server to copy over, and then execute again, the data will be the same as the primary server. Therefore, the MySQL version from the server, must be higher than the primary server or the same as the primary server, preferably the master-slave version, so that will not because of different versions, SQL statements are different, and caused errors.

What is primary master replication

Master copy refers to two or more MySQL servers, are the main server, we copy each other, the advantage is that each server, can be written to the data, and the master-slave inside, from the server is unable to write operations. The principle of master copying is similar to that of master-slave replication, except that data is duplicated and synchronized with each other.

Whether it's a master-slave copy or a primary master copy, the replication process is asynchronous. You can speed up the query of the data, but write the data without any improvement. Master or primary copy, you can also achieve the redundancy of data, when from the server down machine, the primary server can work without any impact. When the primary server down machine, you only need to temporarily switch from the server to the primary server. Also convenient data backup, backup time, you can stop from the server, and then the data from the server backup, after the backup is completed, and then enabled.

Implementation of master-slave replication

When start slave, create an I/O thread from the server to connect to the primary server and have the primary server send the binary log.

The primary server creates a Binlog dump thread that sends the contents of the binary log to the server. Read the main service from the server I/O thread

Binlog the content sent by the dump thread and copies the data to a local file from the server data directory, or relay

Log. The 3rd thread is a SQL thread that uses this thread from the server to read the relay log and perform the updates that are contained in the log.

The show Processlist statement can query for information about replication that occurs on the primary server and from the server.

Create two additional state files from the server in the data directory Master.info and Relay-log.info. State File Save

On the hard drive, it will not be lost when it is shut down from the server. The next time you start from the server, read these files to make sure it has been moved from the main

How many binary logs are read by the service and the extent to which it handles its own relay logs.

From the server flush tables with read lock; After that, the data cannot be synchronized for the time being, only the statements copied after it is unlocked will be executed.

From the server can not shut down the binary log, you can not open the log, because the server will automatically help you open the account or shut down. If you do not close the binary log, the SQL statement copied from the master server is executed, and will not be logged to the binary log, only SQL statements executed on this computer will be logged to the binary log. The relay log is stored in the data directory by default, and the file name is host name. relay-00000n. If you want to save to a different location, you can specify it in the configuration file.