inne
By:
inne

SQL replication and manage

May 19, 2017 78 views
MySQL CentOS

Hi

I would like to have 2 separate droplets with same operating system (CentOS 7) and MySQL. I would like to create a Master-Master replication for the SQL, so (for example) I want to copy the changes from ServerA to ServerB every day, if ServerA crashes, then ServerB takes control and is used by the website. My question is, how can we do this and is there a graphical interface to manage the replication (set schedules, select replicated tables, change IP of the replicated server, etc.) that you could recommend?

1 Answer

@inne

I'd recommend taking a look at this guide to get the ball rolling:

https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04

...

There's not a GUI, so this would all have to be done manually, by hand. To manage failover, you'd do that one of two ways -- using a load balancer or programming.

NGINX can be used as a load balancer to handle TCP requests on port 3306 using streams (slightly different setup than a standard NGINX configuration), or you can implement something at the app level which handles checking to see if server X is down, and if it is, use Y, or Z.

Since networking is often more reliable than code in terms of handling the initial request, I'd most likely recommend using NGINX as a load balancer to handle the TCP requests.

...

Using NGINX and the stream module, you can setup a new block, above http, called stream. That would look something like:

events {
    ...
    ...
}

stream {
    upstream db {
        server 11.22.33.444:3306;
    }

    server
    {
        listen 3306;

        proxy_pass db;
        proxy_connect_timeout 1s;
    }
}

http {
    ...
    ...
}

In the above I'm only using one server, though multiple could be added. It works much like proxying does (and load balancing in general).

NGINX now listens for a request on 3306 and when it receives one, it'll be sent to 11.22.33.444.

This is just a basic example, but one I actually have in testing right now and it works very well.

MySQL replication reliably mirrors the data and operations from one database to another. Conventional replication involves a primary server configured to accept database write operations. Secondary servers copy and apply actions from the primary's log to its own data set. ...
Have another answer? Share your knowledge.