MySQL – Create a Slave from Another Slave and Point to Master

backup-restorationMySQLreplication

Problem

I have MySQL replication setup between 2 servers, master (A) and slave (B). I need to add a new slave to the mix (C). I want this slave to get it's updates directly from the master, I do not want chain replication from the slave. However, the master is "hot", I usually use Xtrabackup to create a full backup of the master, but this will lock it for a good 10 minutes, as the database is around 20GB in size.

Possible Solution

FLUSH TABLES WITH READ LOCK on slave B, use SHOW SLAVE STATUS on B, write down binlog and position. Then backup database with Xtrabackup, ship the backup to C and use it to create the slave, and set replication to point to A with the binlog position I just wrote down.

Question

Is there a better way that doesn't require me to lock B for so long? Or something that is more easily automated?

Best Answer

Hey I know a crazy method to create a slave without augmenting any operation of master (ServerA) or slave (ServerB)

Step 1) Setup a New Server (ServerC)

Step 2) On ServerC, Install MySQL (same version as ServerB)

Step 3) On ServerC, service mysql stop

Step 4) Copy /etc/my.cnf from ServerB to ServerC

Step 5) On ServerC, change server_id to a value different from ServerA and ServerB

Step 6) rsync /var/lib/mysql on ServerB to ServerC

Step 7) When rsync is completed, run "STOP SLAVE;" on ServerB

Step 8) rsync /var/lib/mysql on ServerB to ServerC

Step 9) On ServerB, run "START SLAVE;"

Step 10) On ServerC, service mysql start

Step 11) On ServerC, run "START SLAVE;" (Do this if skip-slave-start is in /etc/my.cnf)

Give it a Try !!!

BTW I have the utmost confidence this will work because I just did this for client over the last 2 days. Client had 2.7TB of data on a slave. I rsyncd to another server while the slave was still active. rsync took like 11 hours. I then ran STOP SLAVE; on the first slave and ran rsync again. That took another hour. I then performed the above step and everything is done.

Related Solutions

MySQL replication: Getting the master in sync with the slave again

Instead of calling them "master" and "slave," let's outline them this way:

HostA: The original "master"
HostB: The original "slave"

When you inserted data directly into HostB, HostA fell behind the binary oplog. You'll first need to overcome this by making HostA a slave of HostB by using the CHANGE MASTER TO command ( http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html ). This should cause HostA to sync up with HostB.

Once HostA has caught up with HostB, you'll want to reset HostA's binary log and promote it to master.

On HostA: RESET MASTER;
On HostB: CHANGE MASTER TO ...

For more information, take a look at http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html#figure_replication-redundancy-after

Mysql – Setup a thesql replication, but new data are not updated

I think I see the problem

You have the following in your question:

Replicate_Do_DB: db1; db2

That is not supposed to be a semicolon-separated list. It is supposed to be a comma-separated list.

My guess is your have this in your slave's /etc/my.cnf

replicate-do-db=db1; db2

The correct way to set this up is

replicate-do-db=db1
replicate-do-db=db2

Please adjust this in the slave's /etc/my.cnf and restart mysql on the slave

Give it a Try !!!

UPDATE 2011-10-31 15:20 EDT

You may want to clear out the relay log by doing this

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000008',master_log_pos-98;
START SLAVE;

then run SHOW SLAVE STATUS\G and make sure replication is running by making sure you see this

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

just as you posted in your question