Sample Header Ad - 728x90

Unable to start mysql slave due to the error: Unknown table engine 'InnoDB'

0 votes
1 answer
583 views
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 'repl@192.118.71.35: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 'repl@192.118.71.35: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.
Asked by Omri (383 rep)
Mar 22, 2017, 02:23 AM
Last activity: Apr 16, 2025, 04:05 AM