0

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.

Improve this question
1
  • Here is how we do it. This step by step example uses Linux LVM to take a consistent snapshot of the master. If you don't shutdown the master I don't think there is anyway to guarantee a consistent snapshot because MySQL holds cached pages in the memory. juhavehnia.com/2015/05/…
    – Juha Vehnia
    May 20, 2015 at 19:13

2 Answers 2

Reset to default
2

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.

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

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.

Improve this answer
2
  • Are there any drawbacks to this technique such as locking the tables on slave1 during the MyISAM part of the process?
    – Ladadadada
    Oct 29, 2013 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, 2013 at 13:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .