mysql master replication internet

Posted on 2014-08-02
Last Modified: 2014-08-06
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 ;).

Question by:projects
    LVL 28

    Expert Comment

    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.

    Author Comment

    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.
    LVL 28

    Expert Comment

    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.
    LVL 27

    Accepted Solution


    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_CONNECT_RETRY = interval
      | MASTER_HEARTBEAT_PERIOD = interval
      | 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).


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now