Understanding mysql--Replication (Replication)

1. Replication overview

1.1. Replication-Resolved issues
Data replication technology has some of the following features:
(1) Data distribution
(2) Load balancing (load Balancing)
(3) Backup
(4) High availability (Hi availability) and fault tolerance

1.2. How replication works
From the top, replication is divided into three steps:
(1) Master changes the record to binary log (these are called binary log events, binary logs event);
(2) Slave copies the binary log events of master to its trunk logs (relay log);
(3) Slave redo the event in the trunk log and change the data to reflect its own.

Describes this process:

The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.
The next step is to slave copy the binary log of master to its own trunk logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.
The SQL slave thread handles the final step of the process. The SQL thread reads events from the log, updating the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.
In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

2. Experience MySQL replication
MySQL is a simple process to start copying, however, depending on the specific scenario, there will be some changes in the basic steps. The simplest scenario is a newly installed master and slave, and from the top, the whole process is as follows:
(1) Create a copy account on each server;
(2) Configuring Master and slave;
(3) Slave connection master begins replication.

2.1. Create a Copy Account
Each slave uses a standard MySQL username and password to connect to master. The user who makes the copy operation grants replication slave permissions. The password for the user name is stored in the text file Master.info. If you want to create a REPL user, the following:
Mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT on *. *
-To [e-mail protected] ' 192.168.0.% ' identified by ' P4ssword ';

2.2. Configure Master
Next, the master is configured, including opening the binary log, specifying a unique SERVR ID. For example, add the following values to the configuration file:
[Mysqld]
Log-bin=mysql-bin
server-id=10
Restart MASTER, run the show Master STATUS, and the output is as follows:

2.3, configuration slave
Slave configuration is similar to master, you also need to restart slave mysql. As follows:
Log_bin = Mysql-bin
server_id = 2
Relay_log = Mysql-relay-bin
Log_slave_updates = 1
READ_ONLY = 1
SERVER_ID is a must, and the only. Slave there is no need to open the binary log, but in some cases it must be set, for example, if Slave is the master of the other slave, you must set the Bin_log. Here we have the binary log turned on, and the display is named (the default name is hostname, but if the hostname changes there will be a problem).
Relay_log configures the trunk log, log_slave_updates indicates that slave writes the copy event into its own binary log (which you will see later).
Some people turn on the slave binary log, but do not set log_slave_updates, and then see if slave's data changes, which is a bad configuration. So, try to use READ_ONLY, which prevents the data from changing (except for special threads). However, read_only is useful, especially for applications that need to create tables on slave.

2.4. Start slave

The next step is to have slave connect to master and begin to redo the events in the master binary log. Instead of doing this with a configuration file, you should use the change MASTER to statement, which completely supersedes the modification of the configuration file, and it can specify a different MASTER for slave, without having to stop the server. As follows:

mysql> change MASTER to master_host= ' Server1 ',

Master_user= ' Repl ',

Master_password= ' P4ssword ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=0;

The value of Master_log_pos is 0 because it is the starting position of the log. You can then use the show SLAVE status statement to see if the SLAVE settings are correct:

mysql> SHOW SLAVE Status\g

1. Row ***************************

Slave_io_state:

Master_host:server1

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

Read_master_log_pos:4

relay_log_file:mysql-relay-bin.000001

Relay_log_pos:4

relay_master_log_file:mysql-bin.000001

Slave_io_running:no

Slave_sql_running:no

..... omitted ...

Seconds_behind_master:null

Slave_io_state, slave_io_running, and slave_sql_running indicate that Slave has not started the replication process. The location of the log is 4 instead of 0, because 0 is only the start of the log file, not the log location. In fact, MySQL knows the location of the first event is 4.

In order to start copying, you can run:

Mysql> START SLAVE;

Run Show SLAVE status to view the output:

mysql> SHOW SLAVE Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

Master_host:server1

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

read_master_log_pos:164

relay_log_file:mysql-relay-bin.000001

relay_log_pos:164

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes

Slave_sql_running:yes

..... omitted ...

seconds_behind_master:0

Note that the slave I/O and SQL threads are already running, and seconds_behind_master is no longer null. The location of the log is increased, which means that some events have been acquired and executed. If you make changes on master, you can see the changes in the location of the various log files on the slave, as well as you can see the changes in the data in the database.

You can view the status of Master and slave on-line. On master, you can see the connection created by the slave I/O thread:

Mysql> Show Processlist \g 1. Row *************************** Id:1 User:root host:localhost:2096 Db:test Command:query time:0 State:null Info:show processlist 2. Row *************************** Id:2 User:repl host:localhost:2144 Db:null Command:binlog Dump time:1838 State:has sent all binlog to slave; Waiting for Binlog to be updated Info:null 2 rows in Set (0.00 sec)

Line 2 is the connection to the I/O thread that handles slave.
Run the statement on the slave:

Mysql> Show Processlist \g 1. Row *************************** Id:1 User:system User Host: Db:null Command:connect time:2291 State:waiting for Master to send event Info:null 2. Row *************************** Id:2 User:system User Host: Db:null Command:connect time:1852 State:has read all relay log; Waiting for the slave I/O thread to update it Info:null 3. Row *************************** Id:5 User:root host:localhost:2152 Db:test Command:query time:0 State:null Info:show processlist 3 Rows in Set (0.00 sec)

Row 1 is the I/O thread state and row 2 is the SQL thread state.

2.5. Initialize slave from another master
The assumption discussed earlier is that you are the newly installed master and slave, so slave has the same data as master. However, most of the time this is not the case, for example, your master may have been running for quite a while, and you want to synchronize data with the newly installed slave, even if it does not have master data.
At this point, there are several ways to get slave to start from another service, such as copying data from Master, cloning from another slave, and starting a slave from the most recent backup. When synchronizing with master, you need three things: Slave
(1) Data snapshot at a certain time of master;
(2) The master current log file, and the byte offset at the time the snapshot was generated. These two values can be called log file coordinate, because they determine the location of a binary log, and you can use the show MASTER Status command to find the coordinates of the log file;
(3) The binary log file of master.

You can clone a slave in the following ways:
(1) Cool copy (Cold copy)
Stop master, copy Master's files to slave, and restart master. The disadvantage is obvious.
(2) Hot copy (warm copy)
If you use only the MyISAM table, you can use the mysqlhotcopy copy even if the server is running.
(3) using mysqldump
Using mysqldump to get a snapshot of a data can be divided into the following steps:
<1> Lock Table: If you do not have the lock table, you should lock the table, prevent other connections to modify the database, otherwise, you can get the data is inconsistent. As follows:
Mysql> FLUSH TABLES with READ LOCK;
<2> in another connection, create a dump of the database you want to replicate with mysqldump:
shell> mysqldump--all-databases--lock-all-tables >dbdump.db
<3> release lock on table.
Mysql> UNLOCK TABLES;

3. In-depth replication
Some basic things about replication have been discussed, and the following is an in-depth discussion of replication.

3.1. Statement-based replication (statement-based Replication)
MySQL 5.0 and earlier versions only support statement-based replication (also called logical replication, logical replication), which is not common in databases. Master records the query that changes the data, and then slave reads the event from the log, and executes it, the same SQL statements as the statement that master executes.
The advantage of this approach is that it is simple to implement. In addition, the binary log of statement-based replication can be well compressed, and the amount of data in the log is small and consumes less bandwidth-for example, a query that updates gigabytes of data requires only dozens of-byte binary logs. And Mysqlbinlog is very convenient for statement-based log processing.

However, statement-based replication is not as simple as it seems, because some query statements depend on the specific conditions of master, for example, master and slave may have different times. So, MySQL's binary log format is not just a query statement, but also includes some metadata information, such as the current timestamp. Even so, there are some statements, such as the current user function, that cannot be copied correctly. In addition, stored procedures and triggers are also an issue.
Another problem is that statement-based replication must be serialized. This requires a lot of special code, configuration, such as InnoDB's Next-key lock, and so on. Not all storage engines support statement-based replication.

3.2. Record-based replication (row-based Replication)
MySQL adds record-based replication, recording the actual data changes in the binary log, similar to how some other DBMS implementations do. There are pros and cons to this approach. The advantage is that you can work correctly with any statement, and some statements are more efficient. The main drawback is that binary logs can be large and not intuitive, so you can't use Mysqlbinlog to view binary logs.
For some statements, record-based replication can work more efficiently, such as:
Mysql> INSERT into Summary_table (col1, col2, Sum_col3)
-SELECT col1, col2, sum (col3)
From enormous_table
GROUP by col1, col2;
Assume that there are only three unique combinations of col1 and col2, but the query scans many rows of the original table, but returns only three records. At this point, record-based replication is more efficient.
On the other hand, the following statement, statement-based replication is more efficient:
mysql> UPDATE enormous_table SET col1 = 0;
The cost of using record-based replication at this time is very high. Because both of these methods do not work well for all situations, MySQL 5.1 supports dynamic exchange before statement-based replication and record-based replication. You can control it by setting the session variable Binlog_format.

3.3. Copying related documents
In addition to binary logs and relay log files, there are other replication-related files. As follows:
(1) Mysql-bin.index
Once the server opens the binary log, it produces a file with the same name as the 2nd log file, but ending with. Index. It is used to track which binary log files exist on the disk. MySQL uses it to locate binary log files. The contents of it are as follows (on my machine):

(2) Mysql-relay-bin.index
The file functions like Mysql-bin.index, but it is for the trunk log, not the binary log. The contents are as follows:
. \mysql-02-relay-bin.000017
. \mysql-02-relay-bin.000018
(3) Master.info
Saves information about the master. Do not delete it, otherwise, slave cannot connect to master after a reboot. The content is as follows (on my machine):

I/O thread updates the Master.info file with the following (on my machine):

. \mysql-02-relay-bin.000019 254 mysql-01-bin.000010 286 0 52813

(4) Relay-log.info
Contains information about the current binary and trunk logs in slave.

3.4. Send replication events to other slave
When setting log_slave_updates, you can let slave play the Master of other slave. At this point, slave writes the events executed by the SQL thread to its own binary log (binary logs), and then its slave can get the events and execute it. As follows:

3.5. Copy filter (Replication Filters)
Replication filtering allows you to replicate only a subset of the data in the server, with two replication filters: Filtering events from the binary log on master, and filtering events in the trunk log on slave. As follows:

4. Common topological structure of replication
The architecture of replication has the following basic principles:
(1) Each slave can have only one master;
(2) Each slave can only have a unique server ID;
(3) Each master can have a lot of slave;
(4) If you set Log_slave_updates,slave to be the master of other slave, it will spread the update of master.

MySQL does not support multi-master server replication (multimaster Replication)-that is, a slave can have more than one master. However, with a few simple combinations, we can build a flexible and powerful replication architecture.

4.1, single master and multi-slave
It is easiest to make a replication system from a master and a slave. The slave does not communicate with each other and only communicates with master. As follows:

This structure can be taken if the write operation is small and the read operation is very good. You can distribute the read operations to other slave, thereby reducing the pressure on the master. However, when slave is increased to a certain number, the slave load on master and the bandwidth of the network become a serious problem.
This structure is simple, but it is flexible enough to meet the needs of most applications. Some suggestions:
(1) Different slave play different roles (e.g. using different indexes, or different storage engines);
(2) using a Slave as the backup master, copy only;
(3) Use a remote slave for disaster recovery;
4.2. Active mode Master-master (Master-master in active-active mode)
Master-master replicates two servers, both master and slave of the other server.

Active Master-master replication has some special uses. For example, two parts that are geographically distributed require a copy of their own writable data. The biggest problem with this structure is the update conflict. Assume that a table has only one row (one column) of data, with a value of 1, and if two servers execute the following statements, respectively:
Execute on the first server:
mysql> UPDATE tbl SET col=col + 1;
Execute on the second server:
mysql> UPDATE tbl SET col=col * 2;
So what's the result? One server is 4 and the other server is 3, but this does not produce an error.
In fact, MySQL does not support a number of other DBMS-supported multi-master server replication (multimaster Replication), which is a significant limitation of the replication functionality of MySQL (the difficulty of a multi-master server is to resolve the update conflict), but if you really have that need, You can use MySQL Cluster and combine Cluster and replication to build a powerful, high-performance database platform. However, there are other ways to emulate this multi-master server replication.

4.3. Active-Passive mode master-master (Master-master in active-passive mode)
This is a master-master structure change, it avoids the disadvantage of m-m, in fact, this is a fault-tolerant and high-availability system. The difference is that one of the services can only be read-only.

4.4. Master-master structure with Slave server (master-master with slaves)
The advantage of this structure is that it provides redundancy. Geographically distributed replication structure, it does not have a single node failure problem, and can also put read-intensive requests on the slave.

Understanding mysql--Replication (Replication)