Hi all, and I guess in particular Flemming and Betty,
I was wondering wether we could do automatic mirroring of the BRAHMS
databases, so that unfortunate incidents like the one with the pii3
before the weekend, does not obstruct analysis. jsfbmwrite has an
option for using an arbiterary BrahmsMain database on any host, so
suppose we mirrored the RUNDB and BrahmsMain to
deadstable.brahms.bnl.gov, and BrahmsMain.DB contained something like
+--------------+---------------------------+-------------+
| name | host | file |
+--------------+---------------------------+-------------+
| calibrations | rcas0005.rcf.bnl.gov | BrahmsCalib |
| runs | deadstable.brahms.bnl.gov | RUNDB |
| geometries | rcas0005.rcf.bnl.gov | BrahmsGeom |
+--------------+---------------------------+-------------+
or even (supposing BrahmsCalib and BrahmsGeom is also mirrored)
+--------------+---------------------------+-------------+
| name | host | file |
+--------------+---------------------------+-------------+
| calibrations | deadstable.brahms.bnl.gov | BrahmsCalib |
| runs | deadstable.brahms.bnl.gov | RUNDB |
| geometries | deadstable.brahms.bnl.gov | BrahmsGeom |
+--------------+---------------------------+-------------+
There's a full section [1] in the MySQL manual that talks about
database mirroring. From a quick glance it looks responably easy.
I've sometimes mirrored the database to NBI, by doing
#!/bin/sh
######################################################################
#
# Setup
#
### Databases we can easily dump fully
db1="BrahmsMain BrahmsCalib BrahmsGeom"
### This database needs special care
db2="RUNDB"
### We must dump these tables from ${db2}
tables2="ConditionsBrahmsMagnets ConditionsDVM ConditionsData \
echo "Dumping database table ${db2}.${table}"
ConditionsHVL1440 ConditionsHVL1458 ConditionsHVL4032 \
ConditionsKeithley ConditionsVars Files Runs ShiftReports"
### The host for the first kind of databases
host1="rcas0005.rcf.rhic.bnl.gov"
### The host for the second kind
host2="pii3.brahms.bnl.gov"
### User name to use
user="query"
######################################################################
#
# First part - Exporting
#
### dump the full databases
for db in ${db1} ; do
echo "Dumping database $db"
mysqldump -u ${user} -p -h ${host1} ${db} > ${db}.mysql
done
### dump the database tables from RUNDB
for table in ${table2} ; do
echo "Dumping database table $db2.${table}"
mysqldump -u ${user} -p -h ${host2} ${db2} ${table}> ${db}.${table}.mysql
done
######################################################################
#
# Second part - Importing
#
### First Clean the databases
for db in ${db1} ${db2} ; do
echo "Cleaning database $db"
echo "DROP DATABASE ${db};" | mysql -u root -p
done
### Import the full databases
for db in ${dbs1} ; do
echo "Importing database ${db}"
cat ${db}.mysql > mysql -u root -p
done
### Make sure we have the RUNDB database
echo "Creating database $db2"
echo "CREATE DATABASE ${db2};" | mysql -u root -p
### Make all the tables
for table in $tables ; do
echo "Importing database table ${db}.${table}"
cat ${db2}.${table}.mysql | mysql -u ${user} -p
done
######################################################################
#
# Third part - Change host names
#
mysql -u ${user} -p << EOF
USE BrahmsMain;
UPDATE TABLE DB SET host=`hostname`;
EOF
### EOF
This is a kind of 'poor-mans-backup', but it seems to work, though
it's very time (and CPU) consuming. Proper replication would probably
be a better idea.
Yours,
Christian Holm Christensen -------------------------------------------
Address: Sankt Hansgade 23, 1. th. Phone: (+45) 35 35 96 91
DK-2200 Copenhagen N Cell: (+45) 28 82 16 23
Denmark Office: (+45) 353 25 305
Email: cholm@nbi.dk Web: www.nbi.dk/~cholm
[1] http://www.mysql.com/doc/R/e/Replication.html
This archive was generated by hypermail 2b30 : Wed Dec 05 2001 - 06:05:26 EST