mysql master replication internet

There are countless documents on the net on replication setups and while I can usually figure things out and learn as I go, this isn't one I want to take on without asking questions and perhaps even steps if possible.

The scenario is as follows;

In this case, I have an overall low volume mysql server with around 20 databases on it with one of those databases about to get pretty busy. Since overall it is low volume, replicating will work just fine for me. Internally, I've always used rsync to keep servers synchronized but in this case, I need replication because I need real time redundancy and, this will be over the internet.

I'll be replicating from a physical to a VPS. I can't think of any reasons why this could not be done between physical and VPS servers but if you know of any, please point it out.
If that is a problem, no big deal, I'll fire up another blade and do physical to physical.

So the question is;

1: Since this is going to be over the internet, I will need this to be secure, plus, should I be using an rsync method or should the mysql servers be communicating together in a standard firewall/firewall setup? Also, should I still use port 3306 between them or something else?

2: The mysql servers which I am using are both 5.5 versions and I have yet to find a decent (simple) Centos walk-through setup.

Please don't simply send me a lot of links, I've found plenty and am here to get answers, not more and more reading ;).

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm in a similar situation and just beginning to setup replication.  We have about 25 pairs of mysql servers (main and backup) and will be switching from using mysqldump and rsync as our primary "backup and replication" process and instead will use mysql replication.  Another person in my group has already setup replication on a couple of our servers and I will be working on it starting Tuesday.

You didn't say what documentation you've already read but have you looked over the official mysql documentation?  From what I've read and my co-worker's description, it looks to be fairly straight forward and relatively easy.  I know you don't want documentation links but, as a starting point, I think I should give 1 link to the official documentation.  How to Set Up Replication
Follow the links in that page to get the details on each step of the process.

I'll try to answer your 2 questions with my current limited knowledge of the subject.

1) rsync is not needed (or used) in a properly configured mysql replication setup.  The replication process can be configured to communicate over an SSL connection.  Keep mysql on its standard 3306 port.  You may or may not need to adjust your firewall rules; that really depends on how they are currently setup.

2) There's no specific CentOS configuration that needs to be done, unless you're referring to the firewall and SSL keys.
projectsAuthor Commented:
Thanks for all the info. I'll take a look at the link.
What I meant about looking for documentation was that there is a lot of info out there but it's not always clear which versions of one thing or another that the doc is referring to.
In my case, I am using all centos 6.5 systems and mysql 5.5 servers so was hoping to find one of those 'howtos' on the net specific to these.
I'm also working on all CentOs servers but the exact OS and mysql version varies.  In all cases we are planning on using the exact same replication config and so far have not found any issues that would cause us to alter that plan.

The only difference between our setup and yours is that our slave servers are on the same LAN as their master.  According to the mysql docs that is a minor insignificant difference because the replication process is designed to work across TCP/IP connections so it will work across a WAN as long as the firewall rules don't block the communication.

Sure you can place your slave on VPS, but I warn you, there could be about 5% CPU overhead on virtualization (KVM) + disk will be shared with other VPSes and this may impact performance. We use MySQL servers on VPS without problems (if your virtualization host is on SSD or if there is a raid controller with gig RAM + battery backed).

1. How to provide security over Internet.
Preferred way is to use embedded SSL support, read: (if you compile mysqld with ssl support, you can use both encrypted or not encrypted connections, easy way to determine if mysqld is compiled with ssl is to run 'ldd /usr/sbin/mysqld | grep ssl') (on slave use 'CHANGE MASTER TO' command to specify SSL:
CHANGE MASTER TO option [, option] ...

    MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | IGNORE_SERVER_IDS = (server_id_list)

2. How to lower traffic. On slave and master I recommend to turn on slave compression protocol:
(this is dynamic variable, it should be turned on on both master and slave, it has effect after 'slave start'. This option is is safe, so if any slave doesn't support compression, it won't be used).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.