0

I had to install my mysql slave server due to storage issue. I have synced my master disk to the slave disk. Now i'm not able to start my slave server (4GB RAM) due to the error: Unknown table engine 'InnoDB'

I can see that:

mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | NO    |
+---------------+-------+
1 row in set (0.00 sec)

I went over some similar questions, and tried to do as recommended. Nothing helped so far. Here is what i've tried:

  • Delete/rename/move ib_logfile
  • Check if skip-innodb is enabled (was disabled).
  • Edit innodb configuration settings

Here is my current settings.

cat my.cnf | grep inno
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb_plugin.so
#skip-innodb
#innodb_io_capacity=400
#innodb_support_xa=0
innodb_additional_mem_pool_size = 50M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:200G;ibdata2:20G;ibdata3:20G;ibdata4:20G;ibdata5:20G;ibdata6:20G;ibdata7:10G;ibdata8:10G;ibdata9:20G;ibdata10:10G;ibdata11:10G;ibdata12:10G;ibdata13:5G;ibdata14:5G;ibdata15:20G;ibdata16:10G;ibdata17:20G;ibdata18:20G;ibdata19:20G:autoextend
innodb_autoextend_increment = 1000
innodb_data_home_dir = /data/mysql/
innodb_file_io_threads = 4
#innodb_force_recovery=1
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 0
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 500M
innodb_log_files_in_group = 2
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120

Here is my data folder:

ll /data
-rw-r--r--  1 mysql mysql    0 Dec 27  2013 i_am_new_data
-rw-rw----  1 mysql mysql 500M Dec 28  2013 ib_logfile0.bak
-rw-rw----  1 mysql mysql 500M Dec 28  2013 ib_logfile1.bak
-rw-rw----  1 mysql mysql 500M Dec 28  2013 ib_logfile2.bak
-rw-r--r--  1 mysql mysql  22K Mar 22 03:53 my.cnf
drwxr-xr-x 13 mysql mysql 4.0K Mar 22 03:56 mysql
drwxr-xr-x  2 mysql mysql 4.0K Dec 27  2013 recovering
drwxr-xr-x  2 mysql mysql    6 Mar 22 03:56 tmp

Here is my /data/mysql folder:

   drwx------ 2 mysql mysql   19 Dec 28  2013 default
    -rw-rw---- 1 mysql mysql  12G Mar 22 01:04 ibdata1
    -rw-rw---- 1 mysql mysql 2.0G Mar 22 01:04 ib_logfile0
    -rw-rw---- 1 mysql mysql 2.0G Mar 22 01:04 ib_logfile1
    -rw-rw---- 1 mysql mysql   76 Mar 22 04:16 master.info
    drwx------ 2 mysql mysql 4.0K Dec 28  2013 mysql
    -rw-rw---- 1 mysql mysql 1.1G Mar 12 07:19 mysql-bin.000314
    -rw-rw---- 1 mysql mysql 1.1G Mar 14 17:56 mysql-bin.000315
    -rw-rw---- 1 mysql mysql 1.1G Mar 17 09:53 mysql-bin.000316
    -rw-rw---- 1 mysql mysql 602M Mar 19 12:34 mysql-bin.000317
    -rw-rw---- 1 mysql mysql  18M Mar 19 13:18 mysql-bin.000318
    -rw-rw---- 1 mysql mysql  125 Mar 19 13:18 mysql-bin.000319
    -rw-rw---- 1 mysql mysql 313M Mar 20 08:45 mysql-bin.000320
    -rw-rw---- 1 mysql mysql 3.8M Mar 20 08:58 mysql-bin.000321
    -rw-rw---- 1 mysql mysql  125 Mar 20 08:58 mysql-bin.000322
    -rw-rw---- 1 mysql mysql 765M Mar 22 01:04 mysql-bin.000323
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:20 mysql-bin.000324
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:21 mysql-bin.000325
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:33 mysql-bin.000326
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:36 mysql-bin.000327
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:36 mysql-bin.000328
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:10 mysql-bin.000329
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:13 mysql-bin.000330
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:15 mysql-bin.000331
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:47 mysql-bin.000332
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:53 mysql-bin.000333
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:56 mysql-bin.000334
    -rw-rw---- 1 mysql mysql  106 Mar 22 03:56 mysql-bin.000335
    -rw-rw---- 1 mysql mysql  399 Mar 22 03:56 mysql-bin.index
    -rw-rw---- 1 mysql mysql  155 Mar 22 02:18 mysql-relay-bin.000001
    -rw-rw---- 1 mysql mysql 173K Mar 22 02:18 mysql-relay-bin.000002
    -rw-rw---- 1 mysql mysql 5.8M Mar 22 02:20 mysql-relay-bin.000003
    -rw-rw---- 1 mysql mysql  155 Mar 22 02:20 mysql-relay-bin.000004
    -rw-rw---- 1 mysql mysql  49K Mar 22 02:21 mysql-relay-bin.000005
    -rw-rw---- 1 mysql mysql  155 Mar 22 02:22 mysql-relay-bin.000006
    -rw-rw---- 1 mysql mysql 564K Mar 22 02:33 mysql-relay-bin.000007
    -rw-rw---- 1 mysql mysql  155 Mar 22 02:35 mysql-relay-bin.000008
    -rw-rw---- 1 mysql mysql 116K Mar 22 02:36 mysql-relay-bin.000009
    -rw-rw---- 1 mysql mysql  125 Mar 22 02:36 mysql-relay-bin.000010
    -rw-rw---- 1 mysql mysql  125 Mar 22 03:10 mysql-relay-bin.000011
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:10 mysql-relay-bin.000012
    -rw-rw---- 1 mysql mysql 1.7M Mar 22 03:13 mysql-relay-bin.000013
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:14 mysql-relay-bin.000014
    -rw-rw---- 1 mysql mysql  85K Mar 22 03:15 mysql-relay-bin.000015
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:46 mysql-relay-bin.000016
    -rw-rw---- 1 mysql mysql 2.5M Mar 22 03:47 mysql-relay-bin.000017
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:47 mysql-relay-bin.000018
    -rw-rw---- 1 mysql mysql 762K Mar 22 03:53 mysql-relay-bin.000019
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:54 mysql-relay-bin.000020
    -rw-rw---- 1 mysql mysql 203K Mar 22 03:56 mysql-relay-bin.000021
    -rw-rw---- 1 mysql mysql  155 Mar 22 03:56 mysql-relay-bin.000022
    -rw-rw---- 1 mysql mysql 1.7M Mar 22 04:16 mysql-relay-bin.000023
    -rw-rw---- 1 mysql mysql  575 Mar 22 03:56 mysql-relay-bin.index
    srwxrwxrwx 1 mysql mysql    0 Mar 22 03:56 mysql.sock
    -rw-r--r-- 1 mysql mysql    6 Dec 28  2013 mysql_upgrade_info
    drwx------ 2 mysql mysql   59 Jan 21  2016 percona
    -rw-rw---- 1 mysql mysql   56 Mar 22 03:56 relay-log.info
    drwx------ 2 mysql mysql   48 Dec 28  2013 test
    drwx------ 2 mysql mysql   57 Dec 28  2013 test2
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb0
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb1
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb2
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb3
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb4
    drwx------ 2 mysql mysql 4.0K Mar  1 13:08 mydb5
    -rw-rw---- 1 mysql mysql 3.9K Dec 27  2013 mydb5_percona.test.co.il.err
    -rw-rw---- 1 mysql mysql 117K Mar 22 01:04 mydb5_repair.test.co.il.err
    -rw-r----- 1 mysql root   28K Mar 22 03:56 mydb5_slave.test.co.il.test.co.il.err
    -rw-rw---- 1 mysql mysql    5 Mar 22 03:56 mydb5_slave.test.co.il.test.co.il.pid

log file:

170322 03:46:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql
170322  3:46:22 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
170322  3:46:22  InnoDB: Initializing buffer pool, size = 3.0G
170322  3:46:22  InnoDB: Completed initialization of buffer pool
InnoDB: Error: data file /data/mysql/ibdata1 is of a different size
InnoDB: 770688 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 13107200 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
170322  3:46:22 [ERROR] Plugin 'InnoDB' init function returned error.
170322  3:46:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170322  3:46:23 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-rel14.11-log'  socket: '/data/mysql/mysql.sock'  port: 3306  Percona Server (GPL), 14.11, Revision 603
170322  3:46:36 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000323' at position 801756782, relay log './mysql-relay-bin.000002' position: 251
170322  3:46:36 [ERROR] Slave SQL: Error 'Unknown table engine 'InnoDB'' on query. Default database: ''. Query: 'INSERT INTO mydb4.obj_mail. Error_code: 1286
170322  3:46:36 [Warning] Slave: Unknown table engine 'InnoDB' Error_code: 1286
170322  3:46:36 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000323' position 801756782
170322  3:46:36 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000324' at position 8573354
170322  3:47:33 [Note] /usr/sbin/mysqld: Normal shutdown

170322  3:47:33 [Note] Slave I/O thread killed while reading event
170322  3:47:33 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000324', position 11116963
170322  3:47:33 [Note] /usr/sbin/mysqld: Shutdown complete

170322 03:47:33 mysqld_safe mysqld from pid file /data/mysql/mydb5_slave.test.co.il.test.co.il.pid ended
170322 03:47:39 mysqld_safe Starting mysqld daemon with databases from /data/mysql
170322  3:47:39 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
170322  3:47:39  InnoDB: Initializing buffer pool, size = 3.0G
170322  3:47:40  InnoDB: Completed initialization of buffer pool
InnoDB: Error: data file /data/mysql/ibdata1 is of a different size
InnoDB: 770688 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 13107200 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
170322  3:47:40 [ERROR] Plugin 'InnoDB' init function returned error.
170322  3:47:40 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170322  3:47:40 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-rel14.11-log'  socket: '/data/mysql/mysql.sock'  port: 3306  Percona Server (GPL), 14.11, Revision 603
170322  3:47:51 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000323' at position 801756782, relay log './mysql-relay-bin.000002' position: 251
170322  3:47:51 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000324' at position 11116963
170322  3:47:51 [ERROR] Slave SQL: Error 'Unknown table engine 'InnoDB'' on query. Default database: ''. Query: 'INSERT INTO mydb4.obj_mail, Error_code: 1286
170322  3:47:51 [Warning] Slave: Unknown table engine 'InnoDB' Error_code: 1286
170322  3:47:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000323' position 801756782

Please save me. I feel lost.

2
  • Show the MySQL log entries from when you started it. Mar 22, 2017 at 2:38
  • dba.stackexchange.com
    – alexus
    Mar 22, 2017 at 2:49

0

You must log in to answer this question.

Browse other questions tagged .