0

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.

Remiz
  • 135
  • 6
  • You could just dump the data to a file, copy that to the slave in read it using mysql. – tex Dec 17 '10 at 20:20

2 Answers2

0

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.

gorilla
  • 1,207
  • 9
  • 6
  • 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. – Remiz Feb 23 '10 at 16:32
0

Why you don't just forward it to aan file and transfer it later by scp or rsync? 

mysqldump master --master-data=1 >master.dump

on slave

mysql <master.dump

Alternate, run the mysqldump command on the slave not on the master. Maybe it will work better without the error above.

Hint: Maybe you should dump all databases not just one?

Patrick Mevzek
  • 9,273
  • 7
  • 29
  • 42
MWE
  • 26
  • 2