InnoDB: "Only one log file found" after binary import

Question

My first one on DBA...

After successful upgrading a master MySQL server 5.5 to 5.6.17 (CentOS 6 VPS serer), I done and moved my binary backups to a slave server (same version 5.6.17 but different environment on Jelastic). The binary backup was done using Percona Xtrabackup 2.1.7 with preparation. Both systems are x86_64 GNU/Linux

After restoring the binary backups to /var/lib/mysql and starting MySQL I was welcomed with the following error from MySQL log:

140725 15:02:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014年07月25日 15:02:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014年07月25日 15:02:13 11854 [Note] Plugin 'FEDERATED' is disabled.
2014年07月25日 15:02:13 11854 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014年07月25日 15:02:13 11854 [Note] InnoDB: The InnoDB memory heap is disabled
2014年07月25日 15:02:13 11854 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014年07月25日 15:02:13 11854 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014年07月25日 15:02:13 11854 [Note] InnoDB: Using Linux native AIO
2014年07月25日 15:02:13 11854 [Note] InnoDB: Using CPU crc32 instructions
2014年07月25日 15:02:13 11854 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014年07月25日 15:02:13 11854 [Note] InnoDB: Completed initialization of buffer pool
2014年07月25日 15:02:13 11854 [ERROR] InnoDB: Only one log file found.
2014年07月25日 15:02:13 11854 [ERROR] Plugin 'InnoDB' init function returned error.
2014年07月25日 15:02:13 11854 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014年07月25日 15:02:13 11854 [ERROR] Unknown/unsupported storage engine: InnoDB
2014年07月25日 15:02:13 11854 [ERROR] Aborting
2014年07月25日 15:02:13 11854 [Note] Binlog end
2014年07月25日 15:02:13 11854 [Note] Shutting down plugin 'partition'

This is ls /var/lib/mysql

backup-my.cnf
ibdata1
ib_logfile0
xtrabackup_binary
xtrabackup_binlog_info
xtrabackup_binlog_pos_innodb
xtrabackup_checkpoints
mysql
.... OTHER DATABASE FOLDERS .....

This is very strange, and it used to work perfectly fine when I was setting up 5.5> 5.5 replication exactly the same was.

Why is it failing with 5.6 now? What does it mean: [ERROR] InnoDB: Only one log file found" Additionally on the master I can see ib_logfile0 and ib_logfile1 but Xtrabackup gives only ib_logfile0

• I tried restarting the server.
• I tried doing the backup/restore prcedure twice to make sure I didn't miss something.
• I tried removing ib_logfile0 file as suggested somewhere on the official MySQL forum.
• I tried explicitly enabling InnoDB in my.cnf with Innodb=ON

EDIT: Answering for questions:

The actual line I'm using for the backup?

ulimit -n 10240 && sudo -u mysql \
 /bin/sh -c "cd /var/lib/mysql && /usr/bin/innobackupex \
 --user=${MYSQL_BACKUP_USER} --password=${MYSQL_BACKUP_PASS} \
 --lock-wait-query-type=update --lock-wait-timeout=300 \
 --kill-long-query-type=select --kill-long-queries-timeout=10 \
 --rsync --slave-info --safe-slave-backup ${XB_TARGET_DIR}"

/usr/bin/innobackupex --version

InnoDB Backup Utility v1.5.1-xtrabackup;

cat xtrabackup_binary

xtrabackup_56

cat backup-my.cnf

# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_tablespaces=0

Do you have the log files outside of the datadir?

No, log files are within the datadir

• jynus: What is the actual line you are using for the backup? Is it using the right binary -_55 or _56 (are you using xtrabackup or innobackupex)? Has the backup-my.cnf have a innodb_log_files_in_group = 1? Do you have the log files outside of the datadir? All these are common mistakes.
• WooDzu: Hi @jynus Just updated my question with some answers. Is it possible that it may be related to me not using xtrabackup to restore but extracting the backup on my own? I don't have it installed on slave and probably won't be able to since it's a Jelastic DBaS with very limited SSH access
• jynus: So, the big question is: do yo run innobackupex --apply-log -that should run xtrabackup twice, the second run regenerates the (empty) logs. You can run that anywhere, but before starting your slave.
• WooDzu: I do run ONCE: sudo -u mysql innobackupex --user=U --password=P --apply-log --use-memory 2G /path-to-backup Again, used this many times and it's always worked for 5.5
• jynus: Ok, bug in 2.1.7 fixed in 2.1.8 (and in 2.2.x): bugs.launchpad.net/percona-xtrabackup/+bug/1273468 Now you own me a beer!

Answer 1

Assuming you have run innobackupex --apply-log successfully you either have a configuration problem (probably innodb_log_files_in_group is 1 in the original and 2 -or not set- in the restored server) or you lost ib_logfile1 somewhere in the process.

InnoDB is ok, it is normal for it to fail when it detects a different configuration on disk than on the file.

Please be aware that xtrabackup/innobackupex only copies data files, not the /etc/my.cnf.

Final answer: apply-log wasn't being executed successfully- a mistake while logging was concealing that. The actual error was due to a specific bug in an old version of Percona XtraBackup. Update to a newer version>= 2.1.8 should fix that.

• WooDzu: Hi. Just checked innodb_log_files_in_group is set to 2 on both servers. I'm gonna change it to 1 on the slave and see how it goes
• jynus: Don't do it. It is supposed to generate an exact copy, you may corrupt your backup. Check for my comment above for suggestions to check. Check both global status and my.cnf.
• WooDzu: Restoring from version 2.1.9 was successful and MySQL starts fine now. Thank you very much.
• RolandoMySQLDBA: Nice Final Answer. +1 !!!