Home / server / Questions / 473231
In Process
Boolean
Asked: 2013-01-29 14:21:14 +0800 CST

How to proceed setting up a secondary mysql linux slave?

  • 772

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.

mysql-replication

2 Answers

  1. 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.

    • 2

  2. 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.

    • 0