The current server setup is simply a computer in my apartment attached with a Sympatico HSE DSL connection. The only issue with this is that a static IP address is not possible (it changes whenever the computer reboots or is disconnected and sometimes just changes suddenly).

One possible solution is to use services from EasyDns. If it works as advertised the cost is small (20$/year) and would provide a way of associating the dynamic IP address of the mirror with a static domain name (mirror.uesp.net).

Note that for simple replication and backup purposes there is no need to assign a domain name to the mirror site. MySQL replication does not require the master to know the slave address and similarly for any direct file backup. All such requests originate from the slave so only the master's address/name needs to be known.

We'll manually specify which databases to replicate rather than everything to prevent any test or backup databases from being logged (as long as we remember to add any new databases to the list when needed). Ensure that the output path for the log is writable by the mysql user.

There are two ways to get the replication started. One is to use the SQL command on the slave:

LOAD DATA FROM MASTER;

This will cause the slave to update the database directly from the master. The problem with this is that the UESP databases are over 1GB in size and doing so would cause a significant outage on the master server would be read-only during that time due to the table locks.

The second option is to perform a database backup on the server, noting the log file position for future use, and then restore those backups on the slave.

To create the backup on the master, first run the SQL commands:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Record the log filename position shown from the status command. While still in the MySQL session (do not exit or the tables will be unlocked), backup all databases:

mysqldump -u root -p --opt uesp_net_wiki5 > wikibackup.sql
...

Note that the site will not allow any edits or statistic updates during this time (should still display fine). Once complete unlock the tables with the SQL command:

Exchange the log filename and position with values previously recorded on the master during the backup. To start the replication:

START SLAVE;

If everything is setup properly the slave should immediately start updating from the master. You can check for the existence of the files master.info and relay-log.info which store the current replication state on the slave.

Full Database Backup on Master: About 30 seconds, mostly for the Wiki, not including compression. Note that the site will be mostly unavailable during this time due to the table locks and the load on the database server. Effects will be visible for a few minutes as the web server becomes overloaded and connections are waiting to connect.

Compression of Database Backups: Around 6 minutes using gzip with default options.

In order to setup a NFS share the partition holding the share must be set to use the acl option in fstab. The current UESP server setup is one hard disk with one main partition (ignoring swap, boot and tmp partitions). While we could set the main partition to use acl it seems this is not recommended and it may be possible to make the server inaccessible.

A better, and safer, setup is to have a second partition setup with the acl option and setup the NFS shares from there. A second hard drive is also convenient in terms of providing space for backups and uploads and provides some degree of reliability (if the main hard drive fails then backups on the secondary should still be fine).

The user/group ids on the server and all clients must match otherwise there will be file permission errors.

Note: You will need to edit hosts.allow and add entries like on the server for all hosts accessing the NFS shared drive. If you don't do this the lockmgr may deny other hosts from locking (and thus accessing) files on the share.

A simple test for seeing if the necessary components are already setup on the server and client is to execute the command:

rsync -avz -e ssh remoteuser@uesp.net:/home2/dhackimages /home2

from the client (backup) server. The remoteuser must have ssh and read access to the specified directory on uesp.net. The current user on the client must have write access in /home2. If this works the given directory on the server (/home2/dhackimages/*) will be copied to the /home2/dhackimages/ path on the current server.

Rather than using clear text passwords for the ssh session we will using a pair of private/public keys for added security. On the client machine create a pair of keys:

ssh-keygen -t dsa -b 1024 -f /home/someuser/uesp2net-rsync-key

Do not enter a passphrase for the key or you will still be prompted when logging in using that key. Ensure that the private key file is not readable by any other users (it should be set to this automatically). Copy the public key to the primary server and add it to the /home/sshuser/.ssh/authorized_keys file:

For additional security the authorized_keys file can be further modified to only allow that key to be used from the backup server and to only execute a specific program. Add the following to the beginning of the line in the key file (before ssh-dss):

from="66.49.211.195",command="/home/sshuser/validate-rsync"

The validate-rsync file is to ensure only an rsync command is available using this connection and must be executable by sshuser:

Initial replication test. Master log file uesp-mysql-bin.001 at position 373,883,456. 240MB compressed database backup size. Replication seems to be working fine. Will let it run for a while and see if any problems develop.

May-June 2007

Finalized uesp2.net mirror domain with database replication and the read-only display of a daily snap-shot of the Wiki. Uses live database replication and hourly rsync/ssh for mirroring the necessary files.