View source

{{NVO SSO Related}}

== 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 ====

* [http://dev.mysql.com/doc/refman/5.0/en/replication.html MySQL's documentation on replication]
* [http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html 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 <code>/var/log/mysql.log</code>.  For example, executing this in a terminal will create a live monitor of the log file:

<pre>
tail -f /var/log/mysql.log
</pre>

=== Setup Steps ===

==== Avoid auto_increment collisions ====

If any of your tables use <code>auto_increment</code> 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' <code>auto_increment</code> behavior by adding lines to the <tt>mysqld</tt> section of their configuration files (by default, <code>/etc/my.cnf</code>):

<pre>
[mysqld]
...
auto_increment_increment=100;
auto_increment_offset=X;
</pre>

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

<pre>
mysql> set auto_increment_increment = 10;
mysql> set auto_increment_offset    = X;
</pre>

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

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

<pre>
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)
</pre>

==== 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 ([http://dev.mysql.com/doc/refman/5.0/en/replication-howto-repuser.html MySQL Docs])

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

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

==== Enable binary logging ====

Enable binary logging on the primary server ([http://dev.mysql.com/doc/refman/5.0/en/replication-howto-masterbaseconfig.html MySQL Docs])

# Shut down MySQL daemon
#: <pre>$ sudo service mysql stop</pre>
# Create a logging dir
#: <pre>
#: # mkdir /var/local/nvo/
#: # mkdir /var/local/nvo/mysql
#: # chown mysql.mysql /var/local/nvo/mysql</pre>
# Add settings to <code>/etc/my.cnf</code>, in the <code>[mysqld]</code> 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&#8212;necessary if you have more than two servers replicating in a loop.
#*:<pre>
#*:[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
#*:</pre>
#: Note that if you are using InnoDB, follow the additional instructions on [http://dev.mysql.com/doc/refman/5.0/en/replication-howto-masterbaseconfig.html replicating InnoDB transactions].
# Start the MySQL daemon back up
#: <pre>$ sudo service mysql start</pre>

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 &#8212; for example, if the slave needs to get an initial copy of a database from the master &#8212; you will need to do choose a method of pre-synchronization, as described on [http://dev.mysql.com/doc/refman/5.0/en/replication-howto-mysqldump.html this page of the MySQL docs] and several subsequent pages (follow the '''next''' links).

# Determine log position of '''master''', as described in the [http://dev.mysql.com/doc/refman/5.0/en/replication-howto-masterstatus.html MySQL docs], and, if the master is a live server, lock it momentarily.
#:<pre>
#: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)
#:</pre>
# Set parameters on '''slave''' as described in the [http://dev.mysql.com/doc/refman/5.0/en/replication-howto-slaveinit.html MySQL docs], by executing this SQL:
#:<pre>
#: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];
#:</pre>
#:In the example above, the values would be <code>master_log_file='mysql-bin.000004', master_log_pos=98</code>.
#:You can find a list of optional parameters, such as the port to connect to on the master (<code>master_port</code>), via <code>show slave status</code>.

=== 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 [http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html?page=2 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 <tt>backup@nvo-vm1.ncsa.uiuc.edu</tt>.

'''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