Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the principle of mysql replication, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1 Overview of replication

Mysql's built-in replication function is the basis for building large, high-performance applications. Distribute the data of Mysql to multiple systems. The mechanism of this distribution is to copy the data of one host of Mysql to another host (slaves) and execute it again. During replication, one server acts as the master server, while one or more other servers act as the slave server. The primary server writes updates to the binary log file and maintains an index of the file to track the log cycle. These logs can record updates sent to the slave server. When a slave server connects to the master server, it informs the master server of the location of the last successful update read by the slave server in the log. Receive any updates that have occurred since then from the server, then block and wait for the primary server to notify the new update.

Please note that when you replicate, all updates to the replicated tables must be made on the primary server. Otherwise, you must be careful to avoid conflicts between user updates to the tables on the master server and updates to the tables on the slave server.

1.1 types of replication supported by mysql: (1): statement-based replication: SQL statements executed on the master server and the same statements executed on the slave server. MySQL uses statement-based replication by default, which is more efficient.

Row-based replication is automatically selected when it is found that exact replication is not possible.

(2): line-based replication: copy the changes instead of executing the command from the server. Support starts with mysql5.0

(3): mixed-type replication: statement-based replication is used by default, and row-based replication is used once it is found that statement-based replication cannot be accurately replicated.

1.2. Problems solved by replication MySQL replication technology has the following characteristics:

(1) data distribution (Data distribution)

(2) load balancing (load balancing)

(3) backup (Backups)

(4) High availability and fault tolerant line High availability and failover

1.3 how replication works overall, there are three steps to replication:

(1) master records changes in binary log (binary log) (these records are called binary log events, binary log events)

(2) slave copies the binary log events of master to its relay log (relay log)

(3) slave redoes the events in the relay log and changes the data that reflects itself.

The following figure describes the replication process:

The first part of the process is that master records binary logs. Before each transaction updates the data, master records these changes in the second log. MySQL writes transactions serially to the binary log, even if the statements in the transaction are executed across each other. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is for slave to copy master's binary log to its own relay log. First, slave starts a worker thread-- the Imax O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process. Binlog dump process reads events from master's binary log, and if it has caught up with master, it sleeps and waits for master to generate new events. The Icano thread writes these events to the relay log.

SQL slave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and replays the events to update the data in slave to make it consistent with the data in master. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of OS, so the overhead of the relay log is small.

In addition, there is a worker thread in master: like other MySQL connections, slave opening a connection in master causes master to start a thread. There is an important limitation to the replication process-replication is serialized on slave, which means that parallel update operations on master cannot operate in parallel on slave.

2. Copy configuration

There are two MySQL database servers, Master and slave,Master, and slave is the slave server. In the initial state, the data information in Master and slave is the same. When the data in Master changes, slave also changes accordingly, so that the data information of master and slave is synchronized to achieve the purpose of backup.

Main points:

The medium responsible for transmitting various modification actions in the master and slave server is the binary change log of the master server, which records the various modification actions that need to be transmitted to the slave server. Therefore, the primary server must activate the binary logging feature. The slave server must have sufficient permissions to connect to the master server and request the master server to transfer the binary change log to it.

Environment:

The MySQL database version of both Master and slave is 5.0.18

Operating system: unbuntu 11.10

IP address: 10.100.0.100

2.1. Create a replication account

1. Set up a backup account in Master's database: each slave connects to master with a standard MySQL username and password. The user performing the replication operation is granted REPLICATION SLAVE permission. The password of the user name is stored in the text file master.info

The command is as follows:

Mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *. *

TO backup@'10.100.0.200'

IDENTIFIED BY '1234'

Set up an account backup and only allow login from the address 10.100.0.200 with a password of 1234.

(if the new and old password algorithms of the mysql version are different, you can set: set password for 'backup'@'10.100.0.200'=old_password (' 1234'))

2.2. Copy data

If you completely newly install the mysql master and slave server, this step is not required. Because the newly installed master and slave have the same data)

Shut down the Master server, copy the data in Master to server B, synchronize the data in Master and slave, and make sure that write operations in Master and slave servers are prohibited before all setup operations are completed, so that the data in the two databases must be the same!

2.3.Config master

Next, configure master, including opening the binary log and specifying a unique servr ID. For example, add the following values to the configuration file:

Server-id=1

Log-bin=mysql-bin

Server-id: ID value of primary server A

Log-bin: binary change date valu

Restart master and run SHOW MASTER STATUS. The output is as follows:

2.4.Configuring slave

The configuration of Slave is similar to that of master, and you also need to restart MySQL for slave. As follows:

Log_bin = mysql-bin

Server_id = 2

Relay_log = mysql-relay-bin

Log_slave_updates = 1

Read_only = 1

Server_id is necessary and unique. It is not necessary for slave to turn on binary logging, but in some cases, it must be set, for example, if slave is the master of another slave, bin_log must be set. Here, we turn on the binary log and display the name (the default name is hostname, but there will be a problem if the hostname changes).

Relay_log configures relay logs, and log_slave_updates indicates that slave writes replication events to its own binary log (you'll see its usefulness later).

Some people open the binary log of slave, but do not set log_slave_updates, and then check to see if the data of slave has changed, which is a misconfiguration. Therefore, try to use read_only, which prevents data from being changed (except for special threads). However, read_only is not very useful, especially for applications that need to create tables on slave.

2.5.Starting slave

The next step is to have slave connect to master and start redoing 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 replaces changes to the configuration file, and it can specify a different master for the slave without stopping 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 use the SHOW SLAVE STATUS statement to see if the slave setting is 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 are No

Indicates that slave has not started the replication process. The location of the log is 4 instead of 0, because 0 is the starting location of the log file, not the log location. In fact, the location of the first event that MySQL knows is 4.

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

The main purpose here is to see:

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

Both slave's Icano and SQL threads are running, and Seconds_Behind_Master is no longer a NULL. The location of the log has increased, which means that some events have been obtained and executed. If you make changes on master, you can see changes in the location of various log files on slave, as well as changes in data in the database.

You can check the status of threads on master and slave. On master, you can see the connections created by slave's I / O thread:

Enter show processlist\ G on master

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 that handles the I / O thread of the slave.

Run this statement on the slave server:

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)

Line 1 is the Imax O thread state, and line 2 is the SQL thread state.

Add a new slave server

If master has been running for a long time, you want to synchronize the newly installed slave, or even it doesn't have master data.

At this point, there are several ways to make slave start with another service, for example, copy data from master, clone from another slave, and start a slave from the most recent backup. When Slave synchronizes with master, you need three things:

(1) data snapshot at a certain time in master

(2) the current log file of master and the byte offset when the snapshot was generated. These two values can be called log file coordinates (log file coordinate) because they determine the location of a binary log, and you can use the SHOW MASTER STATUS command to find the log file coordinates

(3) binary log file of master.

You can clone a slave in the following ways:

(1) Cold copy (cold copy)

Stop master, copy the master file to slave;, and restart master. The disadvantages are obvious.

(2) Hot copy (warm copy)

If you only use MyISAM tables, you can use mysqlhotcopy copies, even if the server is running.

(3) use mysqldump

Using mysqldump to get a snapshot of data can be divided into the following steps:

Lock table: if you have not locked the table, you should lock the table to prevent other connections from modifying the database, otherwise, the data you get can be inconsistent. As follows:

Mysql > FLUSH TABLES WITH READ LOCK

Create a dump of the database you want to replicate with mysqldump on another connection:

Shell > mysqldump-- all-databases-- lock-all-tables > dbdump.db

Release the lock on the table.

Mysql > UNLOCK TABLES

3. Learn more about replication

Now that we've discussed some of the basics of replication, let's take a closer look at replication.

3.1. statement-based replication (Statement-Based Replication)

MySQL 5.0 and previous versions only support statement-based replication (also known as 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 relay log and executes it, and these SQL statements are the same as those executed by master.

The advantage of this approach is that it is easy to implement. In addition, statement-based replicated binary logs can be well compressed, and the log has a small amount of data and takes up less bandwidth-for example, a query that updates GB data requires only a few dozen bytes of binary logs. 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 specific conditions of master, for example, master and slave may have different times. Therefore, the format of MySQL's binary log is not only a query statement, but also some metadata information, such as the current timestamp. Even so, there are still some statements, such as the CURRENT USER function, that cannot be copied correctly. In addition, stored procedures and triggers are a problem.

Another problem is that statement-based replication must be serialized. This requires a lot of special code and configuration, such as InnoDB's next-key lock, etc. Not all storage engines support statement-based replication.

3.2. record-based replication (Row-Based Replication)

MySQL adds record-based replication to record changes to actual data in binary logs, similar to some other DBMS implementations. This approach has both advantages and disadvantages. The advantage is that it can work correctly for any statement, and some statements are more efficient. The main disadvantage is that binary logs can be large and unintuitive, so you can't use mysqlbinlog to view binary logs.

For some statements, record-based replication works more effectively, such as:

Mysql > INSERT INTO summary_table (col1, col2, sum_col3)

-> SELECT col1, col2, sum (col3)

-> FROM enormous_table

-> GROUP BY col1, col2

Suppose there are only three unique combinations of col1 and col2, but this query scans many rows of the original table and returns only three records. At this point, record-based replication is more efficient.

On the other hand, statement-based replication is more efficient for the following statements:

Mysql > UPDATE enormous_table SET col1 = 0

Using record-based replication at this point can be very expensive. Because both approaches do not handle all cases well, 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. Copy related files

In addition to binary log 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 second log file but ends with .index. It is used to track which binary log files are present on disk. MySQL uses it to locate binary log files. Its contents are as follows (on my machine):

(2) mysql-relay-bin.index

The function of this file is similar to that of mysql-bin.index, but it is for relay logs, not binary logs. The contents are as follows:

.\ mysql-02-relay-bin.000017

.\ mysql-02-relay-bin.000018

(3) master.info

Save information about master. Do not delete it, otherwise, you will not be able to connect to master after slave restarts. The content is as follows (on my machine):

The iCompo thread updates the master.info file as follows (on my machine):

.\ mysql-02-relay-bin.000019

two hundred and fifty four

Mysql-01-bin.000010

two hundred and eighty six

0

52813

(4) relay-log.info

Contains information about the current binary log and relay log in slave.

Send replication events to other slave

When setting up log_slave_updates, you can ask slave to play the master of other slave. At this point, slave writes the events executed by the SQL thread into its own binary log (binary log), and then its slave can get these events and execute it. As follows:

3.5. copy filtering (Replication Filters)

Replication filtering allows you to replicate only part of the data in the server. There are two types of replication filtering: filtering events in binary logs on master and events in relay logs on slave. As follows:

4. Common topological structures of replication

The replicated architecture has the following basic principles:

(1) there can be only one master per slave

(2) each slave can have only one unique server ID.

(3) each master can have many slave.

(4) if you set log_slave_updates,slave can be the master of other slave, thus spreading the update of master.

MySQL does not support multi-master server replication (Multimaster Replication)-- that is, a slave can have multiple master. However, through some simple combinations, we can build a flexible and powerful replication architecture.

4.1single master and multiple slave

A replication system consisting of a master and a slave is the simplest case. Slave do not communicate with each other, only with master. As follows:

This structure can be adopted if there are fewer writes and when reads are rare. You can distribute reads to other slave, thus reducing the pressure on master. However, when slave increases to a certain number, the load of slave on master and network bandwidth will become a serious problem.

Although this structure is simple, it is flexible enough to meet the needs of most applications. Some suggestions:

(1) different slave play different roles (for example, using different indexes or different storage engines)

(2) use a slave as a backup master, and only copy

(3) use a remote slave for disaster recovery

4.2.The active mode Master-Master (Master-Master in Active-Active Mode)

The two servers replicated by Master-Master are both master and slave of another server. As shown in the figure:

Active Master-Master replication has some special uses. For example, both parts of a geographical distribution require their own writable copies of data. The biggest problem with this structure is update conflicts. Suppose a table has only one row (one column) of data with a value of 1, if two servers execute the following statements at the same time:

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 is the result? One server is 4 and the other server is 3, but this does not cause an error.

In fact, MySQL does not support some other DBMS-supported multi-host server replication (Multimaster Replication), which is a great limitation of MySQL replication (the difficulty of multi-host servers is to resolve update conflicts), but if you really have this 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 simulate this multi-master server replication.

4.3.The Master-Master in active-passive mode (Master-Master in Active-Passive Mode)

This is a change in the structure of master-master, which avoids the shortcomings of Mmurm. In fact, it is a system with fault tolerance and high availability. The difference is that one of the services can only operate read-only. As shown in the figure:

4.4.The Master-Master structure with slave server (Master-Master with Slaves)

The advantage of this structure is that it provides redundancy. Geographically distributed replication structure that does not have a single node failure problem and can also put read-intensive requests on the slave.

After reading the above, do you have any further understanding of the principle of mysql replication? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.