Saturday, August 10, 2013

Create a Slave ( secondary) server with Percona Xtrabackup



So first you might just save yourself some time and read the Percona example for this:
http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html

But just in case here is an example based on a real situation.

PRIMARY SERVER

# innobackupex /tmp/  <---- this is whatever directory you want to store the backup in. This is a very basic no fluff hot backup.

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
.........
130809 14:40:11  innobackupex: Connection to database server closed
130809 14:40:11  innobackupex: completed OK!

Make sure you see the xtrabackup_binlog_info file. If you do not you will not easily have the position and log information. You will have to dig into the binlogs based on time and etc. Which is more work than needed.

 innobackupex --apply-log /tmp/<Timestamp Directory Here>

Now up to you. You can rsync the directory to the slave or tar[gzip] then scp to the slave. Regardless of method to move to slave, you have a hotbackup created and ready to go.


SECONDARY SERVER

# /etc/init.d/mysql stop
mv /var/lib/mysql  /var/lib/mysql_ORIG

However you moved the file from the master to the slave, put the contents into the datadir folder, assumed for example: /var/lib/mysql .

# chown -R mysql:mysql mysql
 /etc/init.d/mysql start
Starting MySQL...                                          [  OK  ]

Now in your slave MySQL server, you can set the replication user information easily.

CHANGE MASTER TO
MASTER_HOST='<MASTER_HOST>',
MASTER_USER='<MASTER_USER>',
MASTER_PASSWORD='<MASTER_PASSWORD>',
MASTER_CONNECT_RETRY = 10 ;

Get the log and position from the xtrabackup file.

# more xtrabackup_binlog_info
<BinLog info> <POSITION INFO>

CHANGE MASTER TO MASTER_LOG_FILE='<BinLog info>', MASTER_LOG_POS=<POSITION INFO>;

Start slave;


That is it in a nutshell. For more information review the Percona url given at the start.