I wanted to thank everyone for their patience with me last week. I did finally get the
MySQL replication to work. There were several things at play here and I just wanted to
document what I did for the benefit of anyone else who might be struggling with this:
(on MySQL MASTER server, as root user)
innobackupex --defaults-file=/opt/pkgs/mysql/my.cnf --rsync --safe-slave-backup --compress
--compress-threads=4 --user=bkpuser --password=defaultmysqlpwd /apps/Dbbackup/jamen/ (this
allowed me to do the backup while the database was running - took about 3 hours to do,
creating the directory 2013-07-17_21-51-42)
tar cvzf 2013-07-17_21-51-42
scp 2013-07-17_21-51-42.tar.gz jamen@libvm1:
[[ NOTE: the above did a compress command; that got me in the next steps... ]]
(as root on MySQL SLAVE server)
mv /home/jamen/2013-07-17_21-51-42.tar.gz /data/slave/slave.tar.gz
tar xvzif slave.tar.gz (this creates 2013-07-17_21-51-42) (key point here is the "i" in
the tar command -- missed that initially)
for bf in `find . -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) && rm $bf; done
(this uncompresses the compressed ibdata1.qp file - which is a piece I didn't realize I
needed to do until reading the fine lines in the documentation; this was a big GOTCHA!)
/usr/bin/innobackupex --apply-log --use-memory=2G --ibbackup=xtrabackup /data/slave/data
(this gets the transactions that happened while the snapshot was running on the MAIN
MySQL server and adds it to the databases)
chown -R mysql:mysql /data/slave/data
/opt/pkgs/mysql/bin/mysqld_safe --datadir=/data/slave/data --read-only &
CHANGE MASTER TO MASTER_HOST='mysql.library.vanderbilt.edu', MASTER_USER='xxxxxxx',
MASTER_PASSWORD='xxxxxxx', MASTER_LOG_FILE='mysql-bin.000470', MASTER_LOG_POS=671478277;
So, keys are: if compress the dump, will have to uncompress it on the slave; if tar'ing
the dump, will need to untar with the -i option as well; apply-log can be done on the
data at the SLAVE machine - but HAS to be done in order for it to work correctly; and
when starting the mysql slave, has to be READ-ONLY!
Shew! Thanks again everyone!
Systems Services Librarian
Vanderbilt University Library
From: Marcus Bointon [mailto:marcus@stripped]
Sent: Thursday, July 18, 2013 10:38 AM
To: McGranahan, Jamen
Subject: Re: 2 slaves - one Master; continues to fail
On 18 Jul 2013, at 17:29, "McGranahan, Jamen" <jamen.mcgranahan@stripped>
> So I followed your steps and here is what I get (when I try to do the --apply-log):
> 130718 10:28:02 InnoDB: Error: all log files must be created at the same time.
> 130718 10:28:02 InnoDB: All log files must be created also in database creation.
> 130718 10:28:02 InnoDB: If you want bigger or smaller log files, shut down the
> 130718 10:28:02 InnoDB: database and make sure there were no errors in shutdown.
> 130718 10:28:02 InnoDB: Then delete the existing log files. Edit the .cnf file
> 130718 10:28:02 InnoDB: and start the database again.
> xtrabackup: innodb_init(): Error occured.
> innobackupex: Error:
> innobackupex: ibbackup failed at /usr/bin/innobackupex line 389.
That sounds like your innodb_log_file_size in your slave's my.cnf is different from the
master you backed up from. They should really have almost-identical configs.
Alternatively, it may be that there's something amiss with your master (since xtrabackup
is only really copying the files) that's only showing up here.