cascading mysql 主键_MySQLReplication常见错误处理方法

When the SQL thread applies the change from a

row based event, it will have to locate the exact row that was

updated. With a primary key, this is trivial as only one row can

possible have the same value for the primary key.

However if there is no primary key on the

table on the replication slave side, the SQL thread must search the

entire table to locate the row to update or delete. The search is

repeated for each updated row. This search is both very labor

intensive (usually using close to one CPU at 100%) and slow causing

the slave to fall behind.

For InnoDB tables, the

hidden key used for the clustered index for tables without a

primary key cannot be used to avoid searching the entire table for

the rows to update or delete. The hidden key is unique only to each

MySQL instance, so the replication master and replication slave

will not in general have the same values for the hidden key for the

same row.

For cascading/chained replication (for example

master to slave 1 to slave 2) where master and slave1 both

use binlog_format =

MIXED, (affects 5.7 and earlier) can cause

a statement based event using a temporary table to be converted to

a row based on slave 1 causing slave 2 to fall behind if the table

does not have a primary key.

解决方法：确保主库中的每一个表都有主键。临时解决方案是在从机执行如下命令：

SET GLOBAL

slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';

解决方案的官方说明：

The best solution is to ensure all tables have

a primary key. This not only ensures the SQL thread can easily

locate rows to update or delete, but is also

considered best practice as it

ensures all rows are unique. If there is no natural primary key for

the table, a potential solution is to add an auto-increment

unsigned integer column as the primary key.

Note: For InnoDB tables, there

must always be a unique NOT NULL key for all tables

as it is required for the clustered index. So adding an explicit

"dummy" column will not add to the overall storage requirements as

it will merely replace the hidden key.

If it is not possible to add a primary key

immediately, a short term solution is to change the search

algorithm used by the replication slave to locate the rows changed

by row based events. The search algorithm is set using

the slave_rows_search_algorithms option

which is available in MySQL 5.6 and later. The default value is to

use an index scan if possible, otherwise a table scan. However for

tables without a primary key using a hash scan which causes the SQL

thread to temporarily cache hashes to reduce the overhead of

searching the whole table. The value

of slave_rows_search_algorithms can

be changed dynamically using:

mysql> SET GLOBAL slave_rows_search_algorithms =

'INDEX_SCAN,HASH_SCAN';

Query OK, 0 rows affected (0.01 sec)

INDEX_SCAN,HASH_SCAN is the

default value in MySQL 8.0.2 and later.

It is not necessary to restart neither the

whole MySQL instance nor the replication threads for the change to

take effect. Setting the value to INDEX_SCAN,HASH_SCAN or INDEX_SCAN,HASH_SCAN,TABLE_SCAN makes

no difference. Ensure to update your MySQL

configuration file as well to make the change

persist when MySQL is restarted.

One thing to be aware of when using hash scans

that the hashes are only reused within one row based events. (Each

row based event may have changes to several rows in the same table

originating from the same SQL statement.) The maximum size of a row

based event is controlled with

the binlog_row_event_max_size option

on the replication master. The default max event size is 8kB. This

means that switching to hash scans only improves the performance of

the SQL thread when:

Several rows fit into one row based event. It may help to

increase the value of binlog_row_event_max_size on

the replication master, if you perform updates or deletes on large

rows (e.g. with blob or text data). binlog_row_event_max_size can

only be set in the MySQL configuration file and a restart is

required after setting a new value.

One statement changes several rows.

There is no significant drawbacks of enabling

hash scans.

Even if enabling hash

scans improves the performance enough for the replication slave to

keep up, it is strongly recommended to keep working towards adding

a primary key to all tables.