Notes on the IPP Database Backup scheme

(Up to IPP for PS1)

(Last update: see history)

NOTE: Backups of the nebulous database are no longer perfomed. This section is no longer applicable.

The database on the replication slave is backed up twice a day at 0:30 and 12:30 (see the root crontab on ippdb02). Dumping the whole database takes about 3 hours.

Every day at 09:05 and 21:05 the last nebulous dump is copied on ipp001 by the neb_copy.sh script running on ipp001 (documentation is in the script). neb_copy.sh verifies that the copy is fine otherwise sends an e-mail to ps-ipp-ops. The log file can be found at /export/ippdb02.0/mysql-dumps/neb_dump.log

The backup file can be ingested to create a replication slave of the master (ippdb00). The master coordinates are dumped just before the actual mysqldump and are written to /export/ippdb02.0/mysql-dumps/mysql-neb-coordinates-ippdb02-*.txt (take the coordinates that corresponds to the dump). Use those coordinates after the mysqldump has been ingested (there are other coordinates in the mysqldump file but they cannot be used to create a replication slave). Documentation about use of master coordinates can be found there.

  • gpc1 on ippdb01 is replicated on ippdb03 and ipp001.

The database on ippdb03 is backed up every 12 hours (at 4:05 and 16:05). Dumping the database takes about six hours. The script responsible for the dump (gpc1_dump) is also responsible for the copy onto ipp001 (it is then different from the previous one). This script is run as the ipp user (look at the ipp crontab on ippdb03).

The log file can be found at /export/ippdb03.0/mysql-dumps/gpc1_dump.log

On ipp001, every 12 hours (at 1:05 and 13:05), gpc1 is verified and distributed with its md5 checksum on the rsync server. Note that the dump no longer needs to be injested onto the mysql instance on ipp001 because it is a replicant.

The backup file can be ingested to create a replication slave of the master (ippdb01). The master coordinates are dumped just before the actual mysqldump and are written to /export/ippdb03.0/mysql-dumps/mysql-gpc1-coordinates-ippdb03-*.txt (take the coordinates that corresponds to the dump). Use those coordinates after the mysqldump has been ingested (there are other coordinates in the mysqldump file but they cannot be used to create a replication slave). Documentation about use of master coordinates can be found there.

  • Backups of ippRequestServer, isp, ippadmin is /home/panstarrs/ipp/mysql-dump/ops_dump.csh (Manoa cluster).
    • They are backed up every four hours at 0:00, 4:00, 8:00, 12:00, 16:00, and 20:00).
    • ippRequestServer is taken from the ippRequestServer replication host (that is ippc19)
    • isp and ippadmin are dumped from ipp001 (replication slave of ippdb01)
    • Dumps can be found in /export/ipp001.0/ipp/mysql-dumps.
    • Command for backup is started from ipp@ipp001 crontab.
  • Dumps of the mysql databases being used by the processing system on Maui are available via the rsync server on ipp0012.ifa.hawaii.edu. The three databases distributed are the gpc1 processing database, the isp processing database, and the ippadmin database describing the database schema. These are available from the rsync location: rsync://ipp0012.ifa.hawaii.edu/ippdb
    • If you want all three, give a command like: rsync -auv rsync://ipp0012.ifa.hawaii.edu/ippdb/ ippdb/
    • The databases have names of the form ippdb01-DBNAME.dump.bz, eg, the GPC1 processing information is in the file called ippdb01-gpc1.dump.bz. They are dumped every 4 hours, and the new one replaces the old name. If people are unable to retrieve these in less than the 4 hour period, then we can adjust the naming to keep more than one old version around.
    • If you want just one of the above databases, use a command like: rsync rsync://ipp0012.ifa.hawaii.edu/ippdb/ippdb01-gpc1.dump.bz .
    • Note: The validity of the ippdb01-gpc1.dump.bz file can be asserted via its MD5 checksum (stored in ippdb01-gpc1.md5 and distributed in the same place rsync://ipp0012.ifa.hawaii.edu/ippdb/ippdb01-gpc1.md5)

Other

MySQL Servers Naming Convention

It is required (for replication) to have distinct server-id's.

The default 1 is replaced by YYYYMMDD<RUNNING_ID_FOR_DAY_ON_TWO_CHARACTERS>. For instance: 2011050602 means the MySQL server was the second MySQL Server (which means that there should be a 2011050601 MySQL server somewhere) configured on May 6th 2011. Note that it is expected that different people configuring MySQL servers exchange information.

Archives / Old databases dumps

  • All backups for the past 2 months can be found in /data/ipp001.0/ipp/mysql-dumps
  • For backups older than 2 months , the last valid dumps of each month is available in the /data/ipp001.0/ipp/mysql-dumps/Archives/<year> directory.

Backup Monitoring

Log files (the best is to check in the relevant crontab though):

  • gpc1:
    ippdb03:/export/ippdb03.0/mysql-dumps/gpc1_dump.log
    ipp001:/home/panstarrs/ipp/mysql-dump/gpc1_install.log
    
  • nebulous:
    ippdb02:/export/ippdb02.0/mysql-dumps/neb_dump.log
    ipp001:/home/panstarrs/ipp/mysql-dump/neb_copy.log
    

pbzip2

Parallel bzip2 is used for compressing the dumps.

Its web site is here: http://www.compression.ca/pbzip2/

It can be found (on the production cluster) in ~ipp/softwares and it is installed in ~ipp/local.

ipp001 dumps and log-spaced retention

ipp001 is the destination for gpc1 and other mysql database dumps. these start to fill the disk occasionally. there is a script in the ipp user's home directory to remove all but a log-spaced set of copies (dayly for a week, weekly for a month, monthly for years). to run this, log into ipp001 as the ipp user and follow the commands below:

cd /data/ipp001.0/ipp/mysql-dumps>
~ipp/mysql-dump/delete_log_spacing.sh mysql-ssp-ipp001 test
~ipp/mysql-dump/delete_log_spacing.sh mysql-ssp-ipp001 commit
~ipp/mysql-dump/delete_log_spacing.sh mysql-isp-ipp001 test
~ipp/mysql-dump/delete_log_spacing.sh mysql-isp-ipp001 commit
~ipp/mysql-dump/delete_log_spacing.sh mysql-gpc1-ippdb03 test
~ipp/mysql-dump/delete_log_spacing.sh mysql-gpc1-ippdb03 commit