Thursday, January 20, 2011

Initialize a MySQL slave server located on another network than the master

Hello, it's my first question here and English is not my native language but I'll try to explain.

I've a master MySQL server with a public IP address running in my provider infrastructure and I want to run a local MySQL slave server in my office that will replicate all the data for testing purpose.

Setting up the replication works perfectly, I created a SSH tunnel to have my slave reading the binlog from the master, here everything is fine.

My problem is to set up the data from the master. Usually when I want to load the data from the master to any slave on the same network, I run the following command on the master :

mysqldump 'master' --master-data=1 | mysql 'slave' 

but here I can't have any IP for the slave because it's located in my office behind a series of NAT routers...

Does anybody have a solution, knowing that I can't stop the master and there is about 50GB of data on it. If you have any other solution to make a 'hot' data transfer from a master to slave I'm also very interested.

Thank you by advance.

  • Assuming you can ssh to the master, How about, from the slave.

    ssh master 'mysqldump \'master\' --master-data=1' | mysql 'slave'

    This will run the dump command on the master, but reload it locally.

    Remiz : Thanks for the answer, it seems that the way to go for me. However, I'm facing a new problem when I use mysqldump through ssh : after few minutes of loading I've an error 2013 : Lost connection to MySQL server during query when dumping... I assume it's more related to my master configuration, I'm looking into this. Have you ever seen this before ? Thank you for your help anyway.
    From gorilla

0 comments:

Post a Comment