Cross Data Center MySQL Replication Behind NodeBalancer
Linode
Linode Staff
I am following this guide. In each data center I have two database servers behind a NodeBalancer. I am trying to replicate the database servers across data centers.
Here's what I'm calling things:
| Name | Description | Public IP | Private IP |
|---|---|---|---|
| DC1 | Data Center 1 | ||
| DC2 | Data Center 2 | ||
| DB1-DC1 | Database 1 in Data Center 1 | 198.51.100.132 | 198.168.200.56 |
| DB2-DC1 | Database 2 in Data Center 1 | 198.51.100.204 | 198.168.200.10 |
| NB-DC1 | NodeBalancer in Data Center 1 | 198.51.100.17 | 198.168.200.45 |
| DB1-DC2 | Database 1 in Data Center 2 | 203.0.113.54 | 198.168.200.45 |
| DB2-DC2 | Database 2 in Data Center 2 | 203.0.113.96 | 198.168.200.111 |
| NB-DC2 | NodeBalancer in Data Center 2 | 203.0.113.23 | 198.168.200.88 |
I am attempting to perform replication to my database servers behind the NodeBalancers (nb-dc1.example.com and nb-dc2.example.com).
On my databases in data center 1, I have configured grants like this:
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'nb-dc2.example.com' IDENTIFIED BY 'password';
Likewise, in data center 2, I have configured things in the opposite direction.
The replication is not working.
I've looked for my log files and can't find them.
1 Reply
hphillips
Linode Staff
Note: The terms primary and replica are the preferred naming convention for replication, replacing the archaic master and slave terminology.
There are a few gotchas exhibited in this configuration.
NodeBalancer's Role
A NodeBalancer can be helpful with distributing the load between interchangeable endpoints, but for replication, you need to specify the exact endpoint you want to replicate with. So instead of replicating with nb-dc2.example.com you will want to replicate with the public IP address of one of your database servers (i.e. db1-dc2.example.com at 203.0.113.54) if you are replicating between data centers OR via the private IP (198.168.200.45) if replicating in the same data center.
At Linode, private IP addresses are unique within a data center, but servers in different data centers could conceivably have the same private IP address. (i.e. nb-dc1 and db1-dc2, both have a private IP address of 198.168.200.45 in the example.)
MySQL Grants and Hostnames
Granting privileges to 'replication'@'db1-dc2.example.com' and 'replication'@'198.168.200.45' are two completely different animals. If you use a fully qualified domain name in your grant, the address from which you are querying must match both forward and reverse DNS. This means that db1-dc2.example.com must point to 203.0.113.54 and 54.113.0.203.in-addr.arpa must point to db1-dc2.example.com.
For more information on setting up rDNS for a Linode, you will want to refer to:
Replica Topology
Depending on your needs you may want designate one database as primary in both data centers and configure primary-primary replication between them over their public IPs (i.e. between db1-dc1 and db1-dc2).
Primary-replica replication could take place within a data center (i.e. db1-dc1 to db2-dc2) over the database's private IPs. In this case, the application should direct write queries to the local primary, but reads can be load balanced between any available database in the data center. There are many other topologies to consider. You can refer to the following documentation for a discussion of the trade offs:
Log Files
You will want to look at /etc/mysql/my.cnf and under /etc/mysql/mariadb.conf.d/ or /etc/mysql/mysql.conf.d/ to see where data is being stored for your MariaDB or MySQL instance. The parameter that controls this is 'datadir'. Usually this will be /var/lib/mysql. A quick command to check is:
sudo grep -R 'datadir' /etc/mysql
Usually the log files are stored in the datadir, but could be configured to elsewhere. Refer to the following guide if they aren't in the usual place: