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

This article mainly introduces the replication principle and configuration examples of the high-performance Mysql master-slave architecture, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading 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: a SQL statement executed on the master server and the same statement 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. Master-slave replication 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

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=1log-bin=mysql-binserver-id: ID value of primary server A log-bin: binary change date value

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-binserver_id = 2relay_log = mysql-relay-binlog_slave_updates = 1read_only = 1

Server_id: it's necessary, and it's unique.

It is not necessary for log_bin:slave to turn on binary log, 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: configure the relay log, and log_slave_updates means 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.

Read_only: 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\ gateway * 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: NULLSlave_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\ Graph * 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.

In practical application scenarios, more than 90% of MySQL replication is an architecture mode in which one Master is replicated to one or more Slave, which is mainly used for cheap database expansion solutions for applications with high reading pressure. Because as long as the pressure on Master and Slave is not too great (especially on the Slave side), the latency of asynchronous replication is generally very little. Especially since the replication mode of the Slave side has been changed to two threads, the delay problem of the Slave side has been reduced. The benefit is that for applications where data real-time requirements are not special Critical, we only need to use cheap pcserver to expand the number of Slave and spread the read pressure to multiple Slave machines, that is, we can disperse the read pressure of a single database server to solve the read performance bottleneck of the database. After all, in most database application systems, the read pressure is much greater than the write pressure. To a large extent, this has solved the database pressure bottleneck of many small and medium-sized websites, and even some large websites are using similar solutions to solve database bottlenecks.

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

It should be clear to everyone that multiple Slave nodes can be copied from one Master node. Some people may wonder whether that Slave node can be copied from multiple Master nodes. At least for now, MySQL is impossible, and it is not clear whether it will support it in the future.

MySQL does not support the architecture in which a Slave node replicates from multiple Master nodes, mainly to avoid conflicts and to prevent data conflicts between multiple data sources, resulting in final data inconsistency. However, I heard that someone has developed a related patch to let MySQL support a Slave node to replicate from multiple Master nodes as a data source, which is the benefit of the open source nature of MySQL.

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. In this way, changes made by either party will be applied to the other party's database through replication.

Some readers may have a worry, after setting up the replication environment, will it not cause circular replication between the two MySQL? In fact, MySQL thought of this a long time ago, so the server-id of the current MySQL is recorded in the BinaryLog of MySQL, and this parameter must be clearly specified when we build the MySQLReplication, and the server-id parameter values of Master and Slave need to be inconsistent to make the MySQLReplication build successfully. Once you have the value of server-id, it is easy for MySQL to determine which MySQLServer a change was originally generated from, so it is easy to avoid circular replication. Moreover, if we do not turn on the option to record Slave's BinaryLog (--log-slave-update), MySQL will not record changes during replication to BinaryLog at all, let alone worry about the possibility of circular replication.

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.4Cascade replication architecture Master-Slaves-Slaves

In some application scenarios, there may be a great difference between reading and writing pressure, and the reading pressure may be especially high. A Master may need 10 or more Slave to support the pressure of reading. At this time, Master will be more difficult, because there are more SlaveIO threads connected to it. When the pressure of writing is a little greater, the Master side will consume more resources because of replication, which can easily cause replication delay.

How to solve this kind of situation? At this point, we can take advantage of the ability of MySQL to record the BinaryLog information generated by replication on the Slave side, that is, turn on the-log-slave-update option. Then, reduce the pressure on the Master side due to replication through secondary (or more level) replication. In other words, we first replicate from the Master with a few MySQL, which we call the first-level Slave cluster, and then the other Slave replicates from the first-level Slave cluster. The Slave replicated from the first-level Slave is what I call the second-level Slave cluster. If necessary, we can continue to add more levels of replication. In this way, we can easily control the number of Slave attached to each MySQL. This architecture is what I call the Master-Slaves-Slaves architecture.

This multi-level cascading replication architecture easily solves the risk that the Master side will become a bottleneck because there are too many Slaves attached to it. The following figure shows the Replication architecture of multi-level cascading replication.

Of course, if conditions permit, I prefer to suggest that you solve the problem by splitting it into multiple Replication clusters.

The above bottlenecks. After all, Slave does not reduce the amount of writing, and all Slave actually applies all data change operations, without reducing any write IO. On the contrary, the more Slave, the more IO will be written in the whole cluster. We don't have a very obvious feeling, just because it is scattered to multiple machines, so it is not easy to show.

In addition, by increasing the cascading hierarchy of replication, there will be more MySQL for the same change to be transferred to the lowest Slave, which may also cause a risk of long latency.

If we split the cluster to solve the problem, it may be much better, of course, splitting the cluster also requires more complex technology and more complex application system architecture.

4.5. 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.

To some extent, cascading replication does solve the problem that Master becomes a bottleneck because of too many attached Slave, but it can not solve the problem of manual maintenance and the possibility of rebuilding Replication after manual maintenance and abnormal switching. This naturally leads to the Replication architecture that combines DualMaster and cascading replication, which I call Master-Master-Slaves architecture.

Compared with the Master-Slaves-Slaves architecture, the only difference is that the first-level Slave cluster is replaced with a separate Master as a backup Master, and then replicated from this standby Master to a Slave cluster.

The biggest advantage of this combination of DualMaster and cascade replication is that it can avoid that the write operation of the primary Master will not be affected by the replication of the Slave cluster, at the same time, when the master Master needs to be switched, there is basically no situation of redoing the Replication. However, the drawback of this architecture is that the standby Master may become a bottleneck, because if the subsequent Slave cluster is large, the standby Master may become a bottleneck due to too many SlaveIO thread requests. Of course, when the standby Master does not provide any read services, the possibility of bottleneck is not particularly high. If there is a bottleneck, you can also cascade replication after the standby Master and set up a multi-tier Slave cluster. Of course, the more levels of cascade replication, the more obvious the data latency that may occur in the Slave cluster, so before considering the use of multi-tier cascade replication, you also need to evaluate the impact of data latency on the application system.

5. Common problems with replication

Error 1: caused by change master:

Last_IO_Error: error connecting to master 'repl1@IP:3306'-retry-time: 60 retries

Error 2: stop the slave process without unlocking:

Mysql > stop slave

ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

Error 3: change master without stopping the slave process

Mysql > change master to master_host='IP', master_user='USER', master_password='PASSWD', master_log_file='mysql-bin.000001',master_log_pos=106

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

Error 4: the server-id of A B is the same:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids

These ids must be different for replication to work (or the-- replicate-same-server-id option must be used on

Slave but this does not always make sense; please check the manual before using it).

View server-id

Mysql > show variables like 'server_id'

Manually modify server-id

Mysql > set global server_id=2; # the values here are the same as those set in my.cnf

Mysql > slave start

Error 5: after change master, check the status of slave and find that slave_IO_running is still NO

It should be noted that after the above errors are completed, the mysql process will be restarted, and slave_IO_running will be changed to Yes.

Error 6: MySQL master-slave synchronization exception Client requested master to start replication from position > file size

Literally: the location of reading binlog from the library is greater than the value of the current binglog of the main library

This is generally caused by the restart of the master database. The slave parameter sync_binlog of the master database defaults to 1000, that is, the data of the master database is cached to 1000 items and then unified fsync to the binlog file on disk.

When the main library restarts, read directly from the main library and then re-pull the binlog at the previous point, but the main library will return an error of 1236 because it does not have the last binlog of fsync.

It is normally recommended to configure sync_binlog=1, that is, each transaction is immediately written to the binlog file.

1. Check the slave status from the slave library:

The offset is 4063315

2. Check the offset location of mysql-bin.001574 in the main database

Mysqlbinlog mysql-bin.001574 >. / mysql-bin.001574.bak

Tail-10. / mysql-bin.001574.bak

The last few lines of the mysql-bin.001574 file find that the final offset is 4059237, and the offset from the 4063315 of the library is 4059237 of the offset of the main library, which is caused by the parameter sync_binlog=1000.

3. Reset salve

Mysql > stop slave;mysql > change master to master_log_file='mysql-bin.001574', master_log_pos=4059237;mysql > start slave

Error 8: data synchronization exception

The first: delete a record on master, but not find it on slave.

Last_Error: Could not execute Delete_rows event on table market_edu.tl_player_task; Can't find record in 'tl_player_task', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.002094, end_log_pos 286434186

Solution: because master wants to delete a record and no error can be found on slave, the master deletes it, so the slave machine can skip it directly.

Available command: stop slave; set global sql_slave_skip_counter=1; start slave

Second: the primary key is repeated. The record already exists in slave, and the same record is inserted on master.

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1

Duplicate entry'2' for key 'PRIMARY'

Error_code: 1062

Handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

Solution: delete duplicate primary keys in slave

Third: update a record on master, but cannot find it on slave and lose data.

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1

Can't find record in 't1'

Error_code: 1032

Handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 263

Solution: fill in the lost data on slave and skip the error report.

Insert into T1 values (2 recordings BTV')

Stop slave; set global sql_slave_skip_counter=1;start slave

Thank you for reading this article carefully. I hope the article "replication principle and configuration examples of High-performance Mysql Master-Slave Architecture" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.