Cross-Server Database Pollination

Nov 8, 2011 MySQL

Background

In my lab, I have two servers running instances of MySQL. One of the servers acted as a development, and the other was staging/production. When playing around with some updates and upgrades for some time killers, I found the need to copy some of the tables automatically from a Cron job, with no user input. I played around with some options such as SSH and replication, but neither were exactly what I was looking for. I ended up using the following which would copy the database table from the remote host and execute it in the active machines mysql space.

mysqldump --protocol=TCP -h <remote ip> -u <user> -p'<pass>' <database> <table> | mysql -u <user> -p’<password>' <database>

If you omit the table from the first command, it will export the whole database.

Note: These two remote servers talk across an encrypted VLAN so I don’t stress too much on security here. If you need to, you could always tunnel the MySQL connection to add a simple layer of security.


comments powered by Disqus