Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a mysql database master and slave in production. I want to setup additional mysql slave. There is around 15 Terabyte of data in the database and there are MYISAM and InnoDB tables in the database.

I am thinking of below options:

  1. Shutdown master database and copy the mysql data folder to secondary slave. Can Innodb tables be copied like this?
  2. Run flush table with read lock, scp the file to new slave and unlock the table and this is possible for myisam tables, can I do the same for innodb tables too?

Thanks for looking at the question.

share|improve this question
add comment

2 Answers

You won't need to shut down the master as you already have a slave. You'll need to shut down the slave though (well, that's easiest, it's not absolutely required, but is required in the process below).

This is the process I use to create a slave (linux, mysql data on lvm):

  • Stop the existing slave
  • Take an LVM snapshot
  • Start the slave
  • Mount the snapshot under /snap
  • Copy /snap/var/lib/mysql to /var/lib/mysql on the new slave
  • Make sure server-id is set on the new slave, and that it's set to a different value than the old one
  • Start mysqld on the new slave

And as last step: if replication works, unmount and destroy the lvm snapshot.

If your mysql data isn't on linux+lvm, you can skip the lvm steps but need to keep mysql on the existing slave shut down until the copying to the new slave finishes.

share|improve this answer
2  
If the data isn't on lvm, you can use xtrabackup to do a hot snapshot, see percona.com/doc/percona-xtrabackup/howtos/… –  kormoc Jan 29 '13 at 0:18
    
For a 15TB database, I really wouldn't do a backup+restore, takes far too long. –  Dennis Kaarsemaker Jan 29 '13 at 0:37
    
xtrabackup can stream directly to the new slave. It's doesn't have to do a binary backup/restore. –  kormoc Jan 29 '13 at 0:39
add comment

You dont need to stop the slave, you can use innobackupex to stream the actual backup to the new slave:

on actual slave you can issue a

slave2> cd /var/lib/mysql; rm -rf *
slave2> nc -l 9898 | tar -xvif -
slave1> innobackupex --sream=tar --slave-info /tmp | nc slave2 9898
slave2> innobackupex --apply-log /var/lib/mysql

after this the slave is set up, you can start it up, and in the /var/lib/mysql/xtrabackup_slave_info file you can find the actual binlog file and binlog position where you need to point the second slave. Innobackupex can be found here: http://www.percona.com/software/percona-xtrabackup

I rebuild slaves like this in daily basis, it is proven good.

share|improve this answer
    
Are there any drawbacks to this technique such as locking the tables on slave1 during the MyISAM part of the process? –  Ladadadada Oct 29 '13 at 13:03
    
as the xtrabackup FAQ says: xtrabackup is a C program written to take advantage of the InnoDB or XtraDB features in order to make it as fast, less resource-consuming and unobtrusive (locking the database server) as possible. That is only possible with those engines. In the case of MyISAM a “read lock” is needed. innobackupex is a script written in Perl which wraps xtrabackup and other backup tools (such as tar4ibd) and provides you with the functionality of each tool taking care of the details. " –  banyek Oct 29 '13 at 13:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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