Einstein@Home Database Replication

This topic describes how the Einstein@Home database replication is setup.

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;

This topic: Main > WebHome > EinsteinAtHomeDatabaseReplication
Topic revision: 13 Jul 2018, OliverBock
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback