ndbcluster引擎表同步到innodb引擎报错Error 'Unknown storage engine 'ndbcluster'
环境是 ndbcluster集群环境到innodb环境的复制环境 在master上执行
CREATE TABLE t3 (id int unsigned NOT NULL auto_increment PRIMARY KEY) ENGINE=ndbcluster;
在slave查看发现复制Slave_SQL_Running停止了,如下
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.91
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ndb1.000010
Read_Master_Log_Pos: 1149
Relay_Log_File: mydb3-relay-bin.000002
Relay_Log_Pos: 1113
Relay_Master_Log_File: ndb1.000010
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.ndb_apply_status
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.ndb\_index\_stat\_%
Last_Errno: 1286Last_Error: Error 'Unknown storage engine 'ndbcluster'' on query. Default database: 'myslave'. Query: 'CREATE TABLE `slavetest` ( `id` int NULL , `name` varchar(20) NULL , PRIMARY KEY (`id`) ) ENGINE=ndbcluster' Skip_Counter: 0Exec_Master_Log_Pos: 955 Relay_Log_Space: 1480 Until_Condition: None Until_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key:Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1286 Last_SQL_Error: Error 'Unknown storage engine 'ndbcluster'' on query. Default database: 'myslave'. Query: 'CREATE TABLE `slavetest` ( `id` int NULL , `name` varchar(20) NULL , PRIMARY KEY (`id`) ) ENGINE=ndbcluster' Replicate_Ignore_Server_Ids: Master_Server_Id: 1000Master_UUID: 67004af0-0c27-11e6-9fff-7427eaa3c20f Master_Info_File: /data/mysql/master.info SQL_Delay: 0SQL_Remaining_Delay: NULL Slave_SQL_Running_State:Master_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 160427 09:13:29 Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) ERROR: No query specified 跳过这个错误: mysql> stop slave; Query OK, 0 rows affected (0.06 sec) mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.06 sec) mysql> mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.91Master_User: myslaveMaster_Port: 3306 Connect_Retry: 60 Master_Log_File: ndb1.000010Read_Master_Log_Pos: 1149 Relay_Log_File: mydb3-relay-bin.000003 Relay_Log_Pos: 278Relay_Master_Log_File: ndb1.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table: Replicate_Ignore_Table: mysql.ndb_apply_status Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.ndb\_index\_stat\_%Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1149 Relay_Log_Space: 1638 Until_Condition: None Until_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key:Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1000Master_UUID: 67004af0-0c27-11e6-9fff-7427eaa3c20f Master_Info_File: /data/mysql/master.info SQL_Delay: 0SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) ERROR: No query specified 解决ndbcluster表到innodb引擎同步方法如下: 在master设置默认的存储引擎 default-storage-engine=ndbcluster 在创建表的时候不要显式指定存储引擎 mysql> use myslave; Database changed mysql> mysql> CREATE TABLE t2 (id int unsigned NOT NULL auto_increment PRIMARY KEY); Query OK, 0 rows affected (0.81 sec) 查看slave发现同步正常