NVO Replication

From MyWiki

Contents 1 MySQL 1.1 Overview 1.1.1 Further Documentation 1.1.2 Debugging 1.2 Setup Steps 1.2.1 Avoid auto_increment collisions 1.2.2 Open ports 1.2.3 Create replication users 1.2.4 Enable binary logging 1.2.5 Configure replication on a slave 1.3 Replication Management 1.4 Current Configuration

MySQL

Overview

We will set up our login servers in a replication loop, with each server acting as both a master (to the next server in the loop) and a slave (to the previous server in the loop). Masters provide data to be replicated by slaves.

After you set up replication, all SQL commands made on a master will be replicated on the slave, such as:

• Creating and dropping new databases
• Creating and modifying tables
• Inserting new rows
• Modifying and deleting existing rows

Further Documentation

• MySQL's documentation on replication
• A simple tutorial on MySQL replication on OnLamp

Debugging

Watch output in MySQL's log file, which is can be found on some systems in /var/log/mysql.log. For example, executing this in a terminal will create a live monitor of the log file:

tail -f /var/log/mysql.log

Setup Steps

Avoid auto_increment collisions

If any of your tables use auto_increment fields, it can create a race condition that can lead to collisions if changes occur simultaneously on two replicated servers. To avoid the race collisions, you can modify the two servers' auto_increment behavior by adding lines to the mysqld section of their configuration files (by default, /etc/my.cnf):

[mysqld]
...
auto_increment_increment=100;
auto_increment_offset=X;

Or you can change it dynamically (the change will revert when the server restarts) by executing this SQL:

mysql> set auto_increment_increment = 10;
mysql> set auto_increment_offset    = X;

Where the value for X is different on each server. As a result, inserts on a server whose auto_increment_offset is 1 will create rows with auto_increment values 11, 21, 31, etc., and inserts on a server with auto_increment_offset of 2 will yield 12, 22, 32, etc.

To show the current value of auto_increment_increment and all other MySQL System Variables, call:

mysql> show variables where Variable_name like "auto_%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 2     |
| automatic_sp_privileges  | ON    |
+--------------------------+-------+
3 rows in set (0.00 sec)

Open ports

If your servers are firewalled, open port 3306, MySQL's default replication port.

Create replication users

Create a replication user on each server that will act as a master (MySQL Docs)

mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'nvoauth2.ncsa.uiuc.edu' IDENTIFIED BY 'slavepass';

Note that the hostname of the replication server should be that master's slave. For example, in a two-server replication loop between nvoauth1 and nvoauth2, the replication user on nvoauth1 would be 'repl'@'nvoauth2.ncsa.uiuc.edu', and vice-versa.

Enable binary logging

Enable binary logging on the primary server (MySQL Docs)

1. Shut down MySQL daemon

   $ sudo service mysql stop

2. Create a logging dir

   # mkdir /var/local/nvo/

   # mkdir /var/local/nvo/mysql

   # chown mysql.mysql /var/local/nvo/mysql

3. Add settings to /etc/my.cnf, in the [mysqld] section
   • Designate a binary logging file
   • Assign a server ID (unique 32-bit int among all replicated servers)
   • Relay updates from other servers, when acting as a slave—necessary if you have more than two servers replicating in a loop.

     [mysqld]

     ...

     log-bin=/var/local/nvo/mysql/mysql-bin.log # binary logging file

     server-id=1 # unique server ID

     log-slave-updates # relay updates from other servers

     relay-log-index=/var/local/nvo/mysql/relay-bin.index # avoid /var/run/mysql, which gets

     relay-log=/var/local/nvo/mysql/relay-bin # erased each time mysql restarts

   Note that if you are using InnoDB, follow the additional instructions on replicating InnoDB transactions.

4. Start the MySQL daemon back up

   $ sudo service mysql start

Configure the secondary server the same way, except with a different ID.

Configure replication on a slave

These instructions assume that the master and slave start out with identical data; if they do not — for example, if the slave needs to get an initial copy of a database from the master — you will need to do choose a method of pre-synchronization, as described on this page of the MySQL docs and several subsequent pages (follow the next links).

1. Determine log position of master, as described in the MySQL docs, and, if the master is a live server, lock it momentarily.

   mysql> flush tables with read lock; (optional - only necessary if live)

   mysql> show master status;

   +------------------+----------+--------------+------------------+

   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

   +------------------+----------+--------------+------------------+

   | mysql-bin.000004 | 98 | | |

   +------------------+----------+--------------+------------------+

   1 row in set (0.00 sec)

   mysql> unlock tables; (optional - only necessary if live)

2. Set parameters on slave as described in the MySQL docs, by executing this SQL:

   mysql> change master to master_host='nvoauth1.ncsa.uiuc.edu',

   -> master_user='repl',

   -> master_password='slavepass',

   -> master_log_file='[name from File column above]',

   -> master_log_pos=[value from Position column above],

   -> master_port=[value if not using default 3306];

   In the example above, the values would be master_log_file='mysql-bin.000004', master_log_pos=98.

   You can find a list of optional parameters, such as the port to connect to on the master (master_port), via show slave status.

Replication Management

mysql> stop slave;     # replication has been stopped
mysql> start slave;    # replication has been resumed, and will pick up from where it left off

See also Onlamp's instructions on adding nodes.

Current Configuration

all

• auto_increment_increment=10

nvologin.ncsa.uiuc.edu (old development server)

• MySQL
  • server_id=8
  • auto_increment_offset=8
  • slaved to nvoauth1.tuc.noao.edu
• simpleCA serial starts at 1 (different name & key from production servers)

nvoauth1.ncsa.uiuc.edu

• MySQL
  • server_id=2
  • auto_increment_offset=2
  • slaved to nvologin.ncsa.uiuc.edu
• simpleCA serial starts at 1

nvoauth1.tuc.noao.edu

• MySQL
  • server_id=3
  • auto_increment_offset=3
  • slaved to nvoauth1.ncsa.uiuc.edu
• simpleCA serial starts at 10,000,000

nvoauth_backup.ncsa.uiuc.edu

• MySQL
  • server_id=4
  • auto_increment_offset=4
  • slaved to nvoauth1.ncsa.uiuc.edu
• simpleCA serial starts at 20,000,000
• hourly backup (tar of MySQL database directory) copied to backup@nvo-vm1.ncsa.uiuc.edu.

nvoauth_openid.ncsa.uiuc.edu (OpenID test server)

• MySQL
  • server_id=5
  • auto_increment_offset=5
  • slaved to nvoauth1.ncsa.uiuc.edu
• simpleCA serial starts at 30,000,000