ndbcluster引擎表同步到innodb引擎报错Error 'Unknown storage engine 'ndbcluster'

Myth丶恋晨 2022-08-21 10:47 208阅读 0赞

环境是 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: 1286  
                   Last\_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: 0  
          Exec\_Master\_Log\_Pos: 955  
              Relay\_Log\_Space: 1480  
              Until\_Condition: None  
               Until\_Log\_File:   
                Until\_Log\_Pos: 0  
           Master\_SSL\_Allowed: No  
           Master\_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: 1000  
                  Master\_UUID: 67004af0-0c27-11e6-9fff-7427eaa3c20f  
             Master\_Info\_File: /data/mysql/master.info  
                    SQL\_Delay: 0  
          SQL\_Remaining\_Delay: NULL  
      Slave\_SQL\_Running\_State:   
           Master\_Retry\_Count: 86400  
                  Master\_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 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.000003  
                Relay\_Log\_Pos: 278  
        Relay\_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: 0  
                   Last\_Error:   
                 Skip\_Counter: 0  
          Exec\_Master\_Log\_Pos: 1149  
              Relay\_Log\_Space: 1638  
              Until\_Condition: None  
               Until\_Log\_File:   
                Until\_Log\_Pos: 0  
           Master\_SSL\_Allowed: No  
           Master\_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: 1000  
                  Master\_UUID: 67004af0-0c27-11e6-9fff-7427eaa3c20f  
             Master\_Info\_File: /data/mysql/master.info  
                    SQL\_Delay: 0  
          SQL\_Remaining\_Delay: NULL  
      Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
           Master\_Retry\_Count: 86400  
                  Master\_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发现同步正常