2

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
  • 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. – jynus Jul 25 '14 at 16:10
  • 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 – WooDzu Jul 25 '14 at 16:17
  • 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. – jynus Jul 25 '14 at 16:30
  • 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 – WooDzu Jul 25 '14 at 16:34
  • 1
    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! – jynus Jul 25 '14 at 17:41
2

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.

  • 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 – WooDzu Jul 25 '14 at 16:09
  • 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. – jynus Jul 25 '14 at 16:11
  • 1
    Restoring from version 2.1.9 was successful and MySQL starts fine now. Thank you very much. – WooDzu Jul 25 '14 at 18:43
  • 1
    Nice Final Answer. +1 !!! – RolandoMySQLDBA Jul 25 '14 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.