Other aop/offline_db

Table Of Contents

Previous topic

SSH Setup For Automated transfers

Next topic

DBSVN : dybaux SVN pre-commit hook

This Page

Daya Bay Links

Content Skeleton

Offline DB Backup

Backup System

The backup system described here is in addition to the standard IHEP disk backup system.

MySQL DB servers at IHEP are backed up via mysqldump and rsync scripts that are invoked by cron jobs running on the nodes:

  1. dybdb1.ihep.ac.cn
  2. dybdb2.ihep.ac.cn (rsync to cms01.phys.ntu.edu.tw not currently operational)

Issues

Common issues over many years of operation:

  • SSH agent not properly restarted and re-authenticated following server reboots
  • out of disk space on target node
    • large mysql logfile resulting from daily auto-recovery on target node is implicated (TODO: move backups to larger disk)
  • IHEP firewall configuration changes block SSH connection, preventing rsync

Monitoring

Very old functions remain in operation, sending daily status emails:

40 05 * * * ( . $ENV_HOME/env.bash ; db- ; db-backup-rsync-monitor ) > $CRONLOG_DIR/db-backup-rsync-monitor.log 2>&1

Crontab Auto Recovery

User blyth@cms01 crontab:

08 09 * * * ( . $ENV_HOME/env.bash ; env- ; python- source ; db- ; db-backup-recover offline_db dybdb1.ihep.ac.cn ; db-test ) > $CRONLOG_DIR/db-backup-recover-offline_db-dybdb1.log 2>&1

A database named after the day is created, eg offline_db_20130115 from the mysqldump and the prior days database is dropped.

Interactive Recovery

[blyth@cms01 var]$ db-backup-recover
=== db-backup-recover : name offline_db sqz /var/dbbackup/rsync/dybdb1.ihep.ac.cn/20130109/offline_db.sql.gz dbtoday offline_db_20130109 dbyesterday offline_db_20130108
27.46user 6.36system 7:49.51elapsed 7%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (0major+1383minor)pagefaults 0swaps
=== db-backup-recover : SUCCEEDED to create DB offline_db_20130109
=== db-backup-recover : dropping offline_db_20130108
[blyth@cms01 var]$

Table Size Checks

mysql> select table_name,round((data_length+index_length-data_free)/1024/1024,2) as MB from information_schema.tables where table_schema = 'offline_db' order by MB desc ;
+-----------------------+--------+
| table_name            | MB     |
+-----------------------+--------+
| DcsPmtHv              | 542.75 |
| CalibPmtFineGain      | 119.27 |
| DcsAdLidSensor        |  53.94 |
| DaqRawDataFileInfo    |  44.52 |
| DcsAdWpHv             |  44.04 |
| DaqRunConfig          |  27.37 |
| DaqRawDataFileInfoVld |  15.76 |
| GoodRunList           |   9.77 |
| CalibPmtHighGain      |   9.14 |
| CalibPmtSpec          |   7.92 |
| HardwareID            |   5.50 |
| DcsAdLidSensorVld     |   5.39 |
| DcsPmtHvVld           |   4.98 |
...
| DcsAdTempVld          |   4.84 |
| CoordinateReactorVld  |   0.00 |
| PhysAdVld             |   0.00 |
| CalibSrcEnergyVld     |   0.00 |
| LOCALSEQNO            |   0.00 |
+-----------------------+--------+
60 rows in set (0.65 sec)

mysql>