2

I'm migrating a rather busy site to another server cluster.

The site with a MySQL master and two slave servers. I'd like to have as little downtime as possible during the switch, and my biggest concern is the time it takes for the DNS change to propogate.

The master database server operates under mysql.domain.com.

My thought was to copy the master database to our new server, and then switch DNS for mysql.domain.com to the IP of the new master. However, this will mean that I'll have requests coming to both master servers until DNS has propogated world-wide, causing havoc with my data.

My plan is to alter the /etc/hosts file on the old master server, so that any requests for mysql.domain.com go to the IP of the new server.

Does anyone see potential hazards with this?

JdeBP
  • 3,970
  • 17
  • 17
Aaron Craig
  • 95
  • 4

4 Answers4

3

If both servers are located next to each other, why not transfer the IP address of the old server to the new one. You can have two (or more) IP addresses per server. Having your new server answer to both will mitigate all your problems.

If possible, I'd vote:

  • Shut down old server
  • Activate new server with new IP AND the IP of the old server
  • If necessary: Provide a different IP to the old server and bring it back on.

Alternatively set up whatever IP-failover mechanism you feel comfortable with. Make sure that your new master has all the data from the old one.

Regarding your question:

Does that mean that you're providing "international" mysql-service or are you talking about a webserver farm or similar that just happens to use a clustered mysql installation in the background? Do you control the computers that connect to the database? This would open up even more possibilities

Olaf
  • 908
  • 5
  • 7
0

I don't follow what your proposal achieves. How will tweaking /etc/hosts on the old master SQL help you with your goal of minimizing DNS propagation delay?

If you control the DNS servers and resolvers on the hosts, you might be able to publish the old master with a shorter TTL in advance of the change, and then when you make the change restart any nscd processes.

Best, based on what I'm assuming to be the scale of your business, would be to declare an outage window during which time you deem the databases to be read-only.

medina
  • 1,970
  • 10
  • 7
  • Maybe I haven't explained my question well enough. The point isn't to reduce DNS propogation time (this would be an impossible task). The point is to redirect traffic arriving to the old machine over to the correct machine. If the two servers have ips 192.168.0.1 (old server) 192.168.0.2 (new server), for a time, traffic to mysql.domain.com wil go to both servers, depending on where you are in the world. My thought is to tell the machine at 192.168.0.1 to reroute mysql.domain.com traffic to 192.168.0.2 – Aaron Craig Jul 24 '10 at 12:21
  • You won't be able to do that with `/etc/hosts`, which is only consulted by local processes. That is sort of like changing a number saved in your cell phone's address book and expecting calls to forward from the old number. You should be able to redirect traffic on the network level, however, or use @medina's solution. – dsolimano Jul 24 '10 at 12:46
  • Exactly. It is the server that connects to mysql.domain.com. The clients connect through a web server on domain.com. So changing hosts on the server will do what I want -- redirect the server's requests to the correct IP. – Aaron Craig Jul 24 '10 at 13:37
0

This isn't working like that. /etc/hosts is not about redirecting at all, you would have to edit this file on all client systems until DNS has propagated completely so this would work only if you control all clients.

What might work is to set up some forwarding via iptables or ssh on the old machine to the new one.

Something to try (untested, just a quick idea, 192.168.0.2 is the new, 192.168.0.1 is the old server):

On the old server, deactivate mysql. On the new server, start an ssh session like that: 

ssh user@192.168.0.1 -R 3306:192.168.0.2:3306

and keep it open. This should redirect the traffic arriving at port 3306 to the new server, port 3306. If you use another port for mysql, adapt accordingly.

Sven
  • 97,248
  • 13
  • 177
  • 225
  • This looks like a good backup if reassigning the old IP to the new machine won't work. Just to clarify, the hosts trick *should* work, because the client computers aren't connecting to the host through DNS -- they are connecting to the front end web site (through domain.com), and it is the servers that connect to mysql through mysql.domain.com. I could simply change the mysql replication configuration to hard-coded IPs, but prefer the flexibility of changing out machines and using the same domain in the future. – Aaron Craig Jul 24 '10 at 13:36
0

Wouldn't it be easier to edit your DNS Zone settings, and modify the A record for mysql.domain.com to point to the new ip of the new server? If any requests are still fired to the old server (due to DNS propagation delay), the new Zone settings should correctly point them back to your new server.

blacklotus
  • 85
  • 6