Not the answer you need?
Register and ask your own question!

Setting up a slave by streaming

redleafredleaf EntrantInactive User Role Beginner
I've seen the "6 step tutorial" and it is great, except it requires me to login into the master shell and it requires double the db size disk space because the initial backup is done locally. For these and simplicity I'd prefer to setup a slave from the slave itself using streaming. I've put together the following procedure. I am having doubts if these is all correct and also necessary, specifically the step 3. After all, the slave will catch up with master once connected. Can someone, please, advise.

/etc/init.d/mysql stop
rm -rf /var/lib/mysql/*

ssh root@master "innobackupex --user=root --password master_password --stream=tar /tmp/ --slave-info | gzip -" | gunzip - | tar xfi - -C /var/lib/mysql

innobackupex --user=root --password=master_password --apply-log /var/lib/mysql/

chown mysql:mysql /var/lib/mysql -R
/etc/init.d/mysql start

cat /var/lib/mysql/xtrabackup_binlog_info
mysql -uroot -pmaster_password  -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='mirror', MASTER_PASSWORD='mirror_password', MASTER_LOG_FILE='mysql-bin.XXX', MASTER_LOG_POS=XXX;"


  • mirfanmirfan Database Administrator Inactive User Role Beginner
    You can backup and stream the backup from master to slave if you have limited disk space. You can't take backup remotely by connecting master from slave.
  • redleafredleaf Entrant Inactive User Role Beginner
    Let me rephrase my question: do I need to have the "--apply-log" step if I am setting a slave and ok with it starting from a past point because it will catch up anyway?

    If "--apply-log" is needed, does it need to be done on the machine with the original db or the copy has everything needed and thus it can be run on another computer?
  • redleafredleaf Entrant Inactive User Role Beginner
    Does anybody know answers to these two?
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    --apply-log is mandatory because there might be transactions which need to rollback and re-execute which occurred during the course of backup. You can read more about it here but yes, you can run --apply-log i.e. prepare the backup either on source server or on destination anywhere you like.
  • redleafredleaf Entrant Inactive User Role Beginner
    Thank you!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.