在使用Elasticsearch 时碰到了这样的问题,记录以备查
使用go-mysql-elasticsearch插件同步时,数据库是 MYSQL,使用的是引擎是 NDB Cluster ;
go-mysql-elasticsearch 使用说明中有如下说明:
Notice
- binlog format must be row.
- binlog row image must be full for MySQL, you may lost some field data if you update PK data in MySQL with minimal or noblob binlog row image. MariaDB only supports full row image.
- Can not alter table format at runtime.
- MySQL table which will be synced should have a PK(primary key), multi columns PK is allowed now, e,g, if the PKs is (a, b), we will use "a:b" as the key. The PK data will be used as "id" in Elasticsearch. And you can also config the id's constituent part with other column.
- You should create the associated mappings in Elasticsearch first, I don't think using the default mapping is a wise decision, you must know how to search accurately.
mysqldump
must exist in the same node with go-mysql-elasticsearch, if not, go-mysql-elasticsearch will try to sync binlog only.- Don't change too many rows at same time in one SQL.
################BINLOG 配置#########
log-bin=mysql-bin
binlog_format = row
binlog-row-image = full
但当更新了数据库表中某些字段时,go-mysql-elasticsearch 同步数据到搜索引擎时出现 除id,和更新的字段外其他字段为空的情况。
eg:
原内容如下图:
当更新第一条的字段 buyers 为1 时,同步后内容如下图:
原因:查看mysql配置
show global variables like 'ndb_%';
| ndb_log_update_as_write | ON
| ndb_log_updated_only | ON
查看MYSQL 配置说明:
Logging Full or Partial Rows (--ndb-log-updated-only Option)
Command-Line Format | --ndb-log-updated-only | ||
System Variable | Name | ndb_log_updated_only | |
Scope | Global | ||
Dynamic | Yes | ||
Permitted Values | Type | boolean | |
Default | ON |
For purposes of conflict resolution, there are two basic methods of logging rows, as determined by the setting of the --ndb-log-updated-only
option for mysqld:
-
Log complete rows
-
Log only column data that has been updated—that is, column data whose value has been set, regardless of whether or not this value was actually changed. This is the default behavior.
It is usually sufficient—and more efficient—to log updated columns only; however, if you need to log full rows, you can do so by setting --ndb-log-updated-only
to 0
or OFF
.
--ndb-log-update-as-write Option: Logging Changed Data as Updates
Command-Line Format | --ndb-log-update-as-write | ||
System Variable | Name | ndb_log_update_as_write | |
Scope | Global | ||
Dynamic | Yes | ||
Permitted Values | Type | boolean | |
Default | ON |
The setting of the MySQL Server's --ndb-log-update-as-write
option determines whether logging is performed with or without the “before” image. Because conflict resolution is done in the MySQL Server's update handler, it is necessary to control logging on the master such that updates are updates and not writes; that is, such that updates are treated as changes in existing rows rather than the writing of new rows (even though these replace existing rows). This option is turned on by default; in other words, updates are treated as writes. (That is, updates are by default written as write_row
events in the binary log, rather than as update_row
events.)
To turn off the option, start the master mysqld with --ndb-log-update-as-write=0
or --ndb-log-update-as-write=OFF
. You must do this when replicating from NDB tables to tables using a different storage engine; see Replication from NDB to other storage engines, and Replication from NDB to a nontransactional storage engine, for more information.
设置 ndb-log-update-as-write=OFF 即可
show global variables like 'ndb_%';
| ndb_log_update_as_write | OFF
| ndb_log_updated_only | ON
重启数据后再同步问题解决