Strict Standards: Redefining already defined constructor for class wpdb in /home/thornock/public_html/wordpress/wp-includes/wp-db.php on line 52

Deprecated: Assigning the return value of new by reference is deprecated in /home/thornock/public_html/wordpress/wp-includes/cache.php on line 36

Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /home/thornock/public_html/wordpress/wp-includes/cache.php on line 389

Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 556

Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 678

Deprecated: Assigning the return value of new by reference is deprecated in /home/thornock/public_html/wordpress/wp-includes/query.php on line 21

Deprecated: Assigning the return value of new by reference is deprecated in /home/thornock/public_html/wordpress/wp-includes/theme.php on line 508

Strict Standards: Non-static method GoogleSitemapGenerator::Enable() should not be called statically in /home/thornock/public_html/wordpress/wp-content/plugins/sitemap.php on line 2452

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_query_vars() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 57

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_posts_where() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 57

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_posts_join() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 57

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_tag_templates() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 160
Gary Thornock's Weblog » MySQL Failover with CARP
Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_add_ajax_javascript() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 160
27th Mar, 2008

MySQL Failover with CARP


Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/thornock/public_html/wordpress/wp-includes/formatting.php on line 74

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_the_content_filter() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 57

I've been working the last few weekends on setting up redundancy and failover for a MySQL server at work. In the process, I've run across various bits of useful information in different places, but nowhere have I seen it all brought together. So, I'm going to attempt to do that here.

The basic idea was to set up a dual-master replication setup with CARP failover. (I've heard of similar setups with Linux-HA heartbeat failover, but we're using FreeBSD, so CARP was a natural fit for us.)

A couple of notes in the examples below:

  • Our servers have two network ports, so we have the replication on a separate interface from all of the "real" traffic. This isn't strictly necessary, but since we have the capacity available, we might as well use it.
  • The IP addresses for replication in my examples will be 10.0.0.10 for the original server, and 10.0.0.11 for the new failover server.
  • The IP addresses for "real" traffic will be 192.168.0.3 (the old IP for the original server, later the CARP shared IP), 192.168.0.10 (the new physical IP for the original server), and 192.168.0.11 (the new server).

Week 1 - Replication

The easiest way to set up multi-master replication would be to start with two blank servers, set them up to replicate from each other, and then load the data. Naturally, we didn't get to do it that way — the original master server, already populated with the data, needed to be part of the replication setup.

That being the case, the process went something like this:

  • Create replication permissions for both servers:
    GRANT REPLICATION SLAVE ON *.* to 'replication-user'@'10.0.0.10' IDENTIFIED BY 'replication-password';
    GRANT REPLICATION SLAVE ON *.* to 'replication-user'@'10.0.0.11' IDENTIFIED BY 'replication-password';
  • Dump the data from the first server with mysqldump --master-data and load it onto the second server.
  • Make sure the second server is set to replicate from the first:
    CHANGE MASTER TO MASTER_HOST='10.0.0.10', MASTER_USER='replication-user', MASTER_PASSWORD='replication-password';

Week 2 - Multi-Master

We let the new server replicate from the old one for a week to make sure everything was working. Then, we set up multi-master replication:

  • Clear the binary logs on the new server: RESET MASTER;
  • Set the original server to replicate from the new one:
    CHANGE MASTER TO MASTER_HOST='10.0.0.11', MASTER_USER='replication-user', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS='98';

Week 2 - CARP

Also in the second week, we switched the IP address of the old server and made the old IP address a virtual CARP IP on that machine:

  • First, the kernel needs to be recompiled with the device carp option enabled.
  • Then, /etc/rc.conf needs to be updated with the new network information. The old rc.conf information was:
    ifconfig_bce0="inet 10.0.0.10 netmask 255.255.255.0"
    ifconfig_bce1="inet 192.168.0.3 netmask 255.255.255.0"
    defaultrouter="192.168.0.1"

    The new information is:
    ifconfig_bce0="inet 10.0.0.10 netmask 255.255.255.0"
    ifconfig_bce1="inet 192.168.0.10 netmask 255.255.255.0"
    cloned_interfaces="carp0"
    ifconfig_carp0="vhid 3 pass mekmitasdigoat 192.168.0.3/24"
    defaultrouter="192.168.0.1"

    I picked VHID 3 to match the IP address (192.168.0.3). That's not required. What is required is that the VHID be unique on your network, and we're using CARP elsewhere as well, so matching the IP is the convention we chose.
  • Reboot the machine and make sure everything still works (e.g. the web servers can still see the database on 192.168.0.3 where they expect it).

Week 3 - CARP, part 2

We let things run for another week at this point to be sure everything is still working. Then we configured CARP on the new machine.

  • Once again, the kernel needs to be compiled with device carp enabled.
  • The new rc.conf information on this machine is:
    ifconfig_bce0="inet 10.0.0.11 netmask 255.255.255.0"
    ifconfig_bce1="inet 192.168.0.11 netmask 255.255.255.0"
    cloned_interfaces="carp0"
    ifconfig_carp0="vhid 3 advskew 100 pass mekmitasdigoat 192.168.0.3/24"
    defaultrouter="192.168.0.1"

    Note that the CARP passwords must match exactly on both machines. (The example password here is taken from the CARP man page. Don't use that one in real life.)
  • Reboot the new server, and again, test to make sure everything still works.

Resources

No Tags

Comments are closed.

Categories