XtraBackup Not Working On Fresh EC2 Instances

We have 4 instances/nodes in Amazon EC2 and 1 instance/node in New York, they are Ubuntu 12.04 with Percona XtraDB 5.5

We followed the directions exactly as is on the Percona XtraDB 5.5 Documentation for installation, however MySQL keeps putting out an error and doesn’t start.

The error we get is:

[INDENT]* Starting MySQL (Percona XtraDB Cluster) database server mysqld
* SST in progress, setting sleep higher mysqld
* The server quit without updating PID file (/var/lib/mysql/ip-172-31-26-31.pid).[/INDENT] 

​The /etc/mysql/my.cnf file for the bootstrapped node looks like this:

[INDENT][B]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so

# Empty gcomm
wsrep_cluster_address=gcomm://

# Cluster connection URL contains IPs of node#1, node#2 and node#3
#wsrep_cluster_address=gcomm://54.86.47.57,54.85.76.128,54.187.116.44,54.215.250. 224,149.4.223.202

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=54.86.47.57

# Node name
wsrep_node_name=node1

# Cluster name
wsrep_cluster_name=production

# SST method
wsrep_sst_method=xtrabackup

#Authentication for SST method
wsrep_sst_auth=*************[/B][/INDENT] 

The /etc/mysql/my.cnf file for the second/joining node looks like this:

[INDENT]
[B]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://54.86.47.57,54.85.76.128,54.187.116.44,54.215.250. 224,149.4.223.202

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=54.85.76.128

# Node name
wsrep_node_name=node2

# Cluster name
wsrep_cluster_name=production

# SST method
wsrep_sst_method=xtrabackup

#Authentication for SST method
wsrep_sst_auth=InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resour ces:
[URL]http://www.percona.com/xb/p[/URL]

140626 17:24:49 innobackupex: Connecting to MySQL server with DSN 'dbi:mysq l:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtraba ckup;mysql_socket=/var/run/mysqld/mysqld.sock' as 'sstuser' (using password : YES).
140626 17:24:49 innobackupex: Connected to MySQL server
140626 17:24:49 innobackupex: Executing a version check against the server. ..
140626 17:24:49 innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using mysql server version 5.5.37-35.0-55

innobackupex: Created backup directory /tmp

140626 17:24:49 innobackupex: Starting ibbackup with command: xtrabackup - -defaults-file="/etc/mysql/my.cnf" --defaults-group="mysqld" --backup --sus pend-at-end --target-dir=/tmp --tmpdir=/tmp --extra-lsndir='/tmp' --stream=t ar
innobackupex: Waiting for ibbackup (pid=1811) to suspend
innobackupex: Suspend file '/tmp/xtrabackup_suspended_2'

xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revisi on id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Error: log file ./ib_logfile0 is of different size 5242880 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!
innobackupex: Error: The xtrabackup child process has died at /usr//bin/inno backupex line 2672.[/INDENT] 

The /var/lib/mysql/ip-172-31-26-31.err file for the second/joining node looks like this:

[INDENT]
WSREP_SST: [ERROR] Cleanup after exit with status:32 (20140626 17:24:50.797 )
WSREP_SST: [INFO] Removing the sst_in_progress file (20140626 17:24:50.811)
140626 17:24:50 [ERROR] WSREP: Process completed with error: wsrep_sst_xtra backup --role 'joiner' --address '54.85.76.128' --auth 'sstuser:s3cretPass' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent ' 1941': 32 (Broken pipe)
140626 17:24:50 [ERROR] WSREP: Failed to read uuid:seqno from joiner script .
140626 17:24:50 [ERROR] WSREP: SST failed: 32 (Broken pipe)
140626 17:24:50 [ERROR] Aborting

140626 17:24:51 [Note] WSREP: view(view_id(NON_PRIM,8afc7a38-fd56-11e3-84ed -b7677f085512,4) memb {
c459150c-fd56-11e3-9936-9ac4ec9d0599,
} joined {
} left {
} partitioned {
8afc7a38-fd56-11e3-84ed-b7677f085512,
})
140626 17:24:51 [Note] WSREP: view((empty))
140626 17:24:51 [Note] WSREP: gcomm: closed
140626 17:24:51 [Note] WSREP: /usr/sbin/mysqld: Terminated.
Aborted (core dumped)
140626 17:24:51 mysqld_safe mysqld from pid file /var/lib/mysql/ip-172-31-2 6-31.pid ended[/INDENT] 

Any Idea How To Solve this, Please!?

Please HELP!

InnoDB: Error: log file ./ib_logfile0 is of different size 5242880 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!

Seems you don’t have the same innodb_log_file_size configured on those nodes. Please, double check it.

Hi,

I tried changing the innodb_log_file_size in the /var/lib/mysql/backup-my.cnf file on the second/joining node to match the size of ib_logfile0 file size, however it did not solve the issue at all.

Am I supposed to set the innodb_log_file_size in the file mentioned above or should I set it in the /etc/mysql/my.cnf file?

Also, is the bootstrapped node supposed to have the /var/lib/mysql/backup-my.cnf file as well? Because as of right now, it does not have this file in that directory.

This is what the /var/lib/mysql/backup-my.cnf file on the second/joiner node looks like after changing the innodb_log_file_size:

[INDENT]# 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=5242880
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_tablespaces=0[/INDENT] 

I changed it in the following way:

  1. Stop MySQL
  2. Move the ib_logfile0 and ib_logfile1 files out of the directory
  3. Change the innodb_log_file_size value
  4. Restart MySQL

The above procedure did not work.

Hi,

Yes, this seems to be the config file to check and modify: /etc/mysql/my.cnf

We should check if that file has the same innodb_log_file_size value in all the nodes. Can you do that check?

Hi,

Yes, I added that to everything, I actually found the solution. These are the steps I took:

mysql -uroot -p..... -e"SET GLOBAL innodb_fast_shutdown = 0"

/etc/init.d/mysql stop

****check ib_logfile0 size using ls -l ****

rm /var/lib/mysql/ib_logfile*

vim /etc/mysql/my.cnf

[Add to end of file: innodb_log_file_size=(check the size of ib_logfile0)]

vim /var/lib/mysql/backup-my.cnf

[Make edit: innodb_log_file_size=(check the size of ib_logfile0)]

***Do the previous 2 commands if you have the backup-my.cnf. In my case after fresh reinstall of EC2 I did not ***

Bootstrap initial node:
/etc/init.d/mysql bootstrap-pxc

Start Joining nodes:
/etc/init.d/mysql start

This procedure worked.

Thank You for your replies and help!

Good to know that it worked, thanks for sharing!