Copy & Transfer: MySQL Offline Database Snapshot to NERSC/Cori

These instructions are provided for you to copy and transfer the latest MySQL Offline Database Snapshot to NERSC/Cori. The DB is required to run data production on NERSC/Cori.

Fc3.star.bnl.gov takes a backup of the MySQL database every 6 hours. The backups are located in fc3.star.bnl.gov:/db01/mysql-zrm/offline-daily-raw. Fc3 is on the Science DMZ network and the easiest way to get the snapshot from fc3 to Cori/NERSC is login to an rcas node, copy the the snapshot to rcas:/gpfs working directory. Before the snapshot is copied to Cori, you will need to unpack the tar and merge in the permissions and mysql-db to the snapshot directory. Specifically:

- my.cnf
- mysql/
- mysql-files/
- perofrmance_schema/
- tmp
 
You can grab these files from a current DB snapshot on Cori or you can grab them from here: /gpfs01/star/pwg/mpoat/merge_with_new. Once the files are merged with the snapshot. You need to repack the tarball and send it to Cori using globus.org (you can use scp but it may be very slow and not recommended).</p>

I have written a script that takes care of Steps 1 - 8 located at: /gpfs01/star/pwg/mpoat/get_latest_db_snapshot.sh

Here are the manual instructions if needed

1. Login to fc3.star.bnl.gov (must be root) and get the name of the newest snapshot in /db01/mysql-zrm/offline-daily-raw

# ssh root@fc3.star.bnl.gov

# ls -lart /db01/mysql-zrm/offline-daily-raw

2. Login to an rcas node on the RCF (using mpoat as an example

# ssh mpoat@ssh.sdcc.bnl.gov

# rterm -i

[rcas6006] ~/>

3. cd to /gpfs work area

[rcas6006] ~/> cd /gpfs01/star/pwg/mpoat

4. Copy the DB Snapshot from fc3 to rcas:/gpfs (Using 0211221060002 as an example snapshot

[rcas6006] ~/> scp -rp root@fc3.star.bnl.gov:/db01/mysql-zrm/offline-daily-raw/0211221060002 /gpfs01/star/pwg/mpoat

5.Make a directory to unpack the snapshot into

[rcas6006] ~/> mkdir unpack_2021-12-21

6. Unpack the Tarball into the directory

[rcas6006] ~/> tar -xzvf 0211221060002/backup-data -C unpack_2021-12-21

7. Merge the needed files with the unpack_2021-12-2

[rcas6006] ~/> cp merge_with_new/* unpack_2021-12-21

8. Change directoriy to the unpack_2021-12-21 directory and repack the tar as mysql57Vault-$DATE

[rcas6006] ~/> tar -czvf mysql57Vault-2021-12-21.tgz .

Note: The next portion requires you to transfer the new tar to NERSC/Cori. In a pinch you could use scp but it may take a long time. Its best to use the globus.org data transfer web interface.

9. Login to https://app.globus.org/

Enable the SDCC Managed Endpoint & The NERSC DTN Managed Endpoint. Choose the source from SDCC side and destination on the NERSC DTN side and begin the transfer

10. SSH to cori.nersc.gov

 cd to the directory you transferred the .tgz to

mpoat@cori09:~> mkdir mysql57Vault-2021-12-22

mpoat@cori09:~> tar -xzvf mysql57Vault-2021-12-22.tgz -C mysql57Vault-2021-12-22