Einstein@Home Database Replication

Involved machines

• Production:
  • Master: einstein-db1.phys.uwm.edu (VPN endpoint: 192.168.11.1, Database IP: 192.168.10.1)
  • Slave: einstein-dl.aei.uni-hannover.de (VPN endpoint: 192.168.11.100)
• Testing:
  • Master: albert.phys.uwm.edu (VPN endpoint: 192.168.0.1, Database IP: 192.168.0.1)
  • Slave: einstein-dl.aei.uni-hannover.de (VPN endpoint: 192.168.0.2)

Setup "Master"

1. Prepare /etc/my.cnf
   • Assign a unique server ID (by convention master gets "1")

     server-id=1

   • Replication requires a binary log (store them on a different hard drive and/or partition!)

     log-bin=/var/lib/mysql/binary_log

   • Enable log expiration to ensure consistency

     expire-logs-days=7

   • Make sure that binary log is updated frequently (check if it affects I/O performance!):

     sync_binlog=1

2. Restart the MySQL daemon
3. Create e dedicated user account for replication
   • Log in to MySQL as root
   • Using a fixed IP (slave's VPN endpoint!):

     GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'<SLAVE-IP>' IDENTIFIED BY '<REPLICATOR-PASSWORD>';

   • Update settings:

     FLUSH PRIVILEGES;

4. Create database dump for initial setup of slave database
   • Flush and lock all tables:

     FLUSH TABLES WITH READ LOCK;

   • Quit the MySQL shell
   • Dump the database:

     mysqldump --opt --verbose [-p] EinsteinAtHome --master-data=2 | gzip > EinsteinAtHome.dump.sql.gz

   • Log in to MySQL as root
   • Release the table lock again:

     UNLOCK TABLES;

Setup "Slave"

1. Set up the replication database
   • Log in to MySQL as root
   • Create the database instance:

     CREATE DATABASE EinsteinAtHome;

   • Quit the MySQL shell
2. Import the dump of the master database:

   gunzip < /path/to/EinsteinAtHome.dump.sql.gz | mysql [-p] EinsteinAtHome

3. Configure MySQL to act as slave in /etc/my.cnf (in our case: /etc/mysql/conf.d/slave@albert.cnf)
   • Assign a unique server ID (by convention first slave gets "2")

     server-id=2

   • Define what to replicate (more efficient than ?replicate-do-db=!)

     replicate-wild-do-table=EinsteinAtHome.%

   • Add replication (relay) logging (make sure directory exists and "mysqld" has write access!)

     relay-log = /var/log/mysql/replication-relay.log
     relay-log-info-file = /var/log/mysql/replication-relay-log.info
     relay-log-index = /var/log/mysql/replication-relay-log.index
     

4. Restart the MySQL daemon
5. Synchronize master and slave
   • Log in to MySQL as root
   • Stop the replication thread:

     SLAVE STOP;

   • Get the master database status information for and below (stored at the beginning of the dump file EinsteinAtHome.dump, grep for "CHANGE MASTER")
   • Synchronize replication:

     CHANGE MASTER TO MASTER_HOST='<MASTER-IP>', MASTER_USER='replicator', MASTER_PASSWORD='<REPLICATOR-PASSWORD>', MASTER_PORT=3306, MASTER_LOG_FILE='<MASTER-LOG>', MASTER_LOG_POS=<MASTER-LOG-POS>;

   • Restart the replication thread:

     START SLAVE;