...
Documents the process for downloading a PROD daily backup to UAT (or DEV) and importing it.
Details
...
Step 1a (for regular PROD mapping clone): Do these steps on UAT. Retrieve the available backups. This currently requires logging in as the root user.
| Code Block |
|---|
> sudo su - root
> sudo aws s3 ls backup-ihtsdo-t1/daily/mapping.ihtsdotools.org/mapping/mapping |
Output will appear as a list of backups by date. The last six days of backups are retained, e.g.:
| Code Block |
|---|
root@uat-mapping:~# sudo aws s3 ls mapping.backup.ihtsdo
2014-09-28 01:02:47 343656836 dbbackup_mappingservicedb_201409280100.bak.gz
2014-09-29 01:02:48 343750530 dbbackup_mappingservicedb_201409290100.bak.gz
2014-09-30 01:02:49 343867437 dbbackup_mappingservicedb_201409300100.bak.gz
2014-10-01 01:02:46 343892129 dbbackup_mappingservicedb_201410010100.bak.gz
2014-10-02 01:02:46 349749641 dbbackup_mappingservicedb_201410020100.bak.gz
2014-10-03 01:04:15 349779484 dbbackup_mappingservicedb_201410030100.bak.gz |
Step 1b (for US-NLM PROD mapping clone): Do these steps on uat-us-mapping. Retrieve the available backups. This currently requires logging in as the root user.
| Code Block |
|---|
> sudo su - root
> sudo aws s3 ls s3://backup-ihtsdo-t1/daily/prod-mapping-us.ihtsdotools.org/mapping/ |
Step 2: Choose the desired backup (i.e. replace the date in code below), download the compressed file into the /home/ihtsdo/data/doc/sqldump directory, and unzip it. If cloning US-NLM instance, modify URL as in Step1b.
| Code Block |
|---|
sudo aws s3 cp s3://backup-ihtsdo-t1/daily/mapping.ihtsdotools.org/mapping/mapping.ihtsdotools.org_2017-06-06T075349Z.zip /home/ihtsdo/data/doc/sqldump/backup.bak.zip
cd /home/ihtsdo/data/doc/sqldump
chown ihtsdo:ihtsdo backup.bak.zip
# then as the "ihtsdo" extract solely the file that has the db clone in it:
unzip -j backup.bak.zip mapping.ihtsdotools.org_2017-06-06T075349Z/mappingservicedb.sql -d /home/ihtsdo/data/doc/sqldump
|
1: Download the latest backup package from the PROD server to your local laptop. It will be in the /opt/backup/mapping/tmp directory.
| Code Block |
|---|
mapping-rest@prod-mapping:/opt/backup/mapping/tmp$ ls
prod-mapping.ihtsdotools.org_2020-09-30T020009Z.zip |
Step 2: Upload the backup package to the UAT or DEV where you'll be refreshing the db. You can put it in your home directory. Extract the file contents.
Step 3: Turn off tomcat. Change user. Log into db and truncate all tables. Exact truncate code will be in the file truncate_all.sql in the project repository.
| Code Block |
|---|
supervisorctl stop mapping-rest
sudo su - mapping-rest
mapping-rest@uat-mapping:~$ mysql -u otf -p mappingservicedb
# enter pwd from config.properties
SET FOREIGN_KEY_CHECKS = 0;
-- Find tables to drop
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT(table_name) INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;
-- Drop tables
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Reenable foreign key constraints
SET FOREIGN_KEY_CHECKS = 1; |
Step 4: Import the dump file (requires password). Note that this requires a about thirty minutes and this command does not provide useful output until complete.
| Code Block |
|---|
% mysql -uotf -p -h localhost -D mappingservicedb < /home/ihtsdo/data/doc/sqldump/mappingservicedb.sql |
Note: During the load, it is possible to check progress using the 'show tables' command in mysql.
Step 5: Remove the old indexes (as root user). The backup package should contain the associated indexes already built and they can simply be copied into place. Bring tomcat back up.
| Code Block |
|---|
cd /opt/mapping-indexes/mapping
rm -rf *Jpa
cp -R /home/dshapiro/prod-mapping.ihtsdotools.org_2020-11-20T170543Z/opt/mapping-indexes/mapping/* .
chmod -R 766 *
supervisorctl start mapping-rest
|
If indexes need to be built from scratch, do the following:
| Code Block |
|---|
cd /opt/mapping-indexes/mapping
rm -rf *Jpa
export MAVEN_OPTS='-Xmx3800M -XX:+UseG1GC'
cd /opt/mapping-admin/lucene
mvn install -PReindex -Drun.config=/opt/mapping-rest/config.properties > reindex.log
% supervisorctl start mapping-rest |
Step 6: Delete the backup package from your home directory to preserve space on the disk.
Prior Notes and Special Circumstances:
If you run out of space due to the ibdata1 file becoming too large, you can do this:
| Code Block |
|---|
supervisorctl stop mapping-service
--Delete mappingservicedb database (rather than just dropping the tables)
drop schema mappingservicedb;
--stop MySQL
service mysql stop
--Delete those three files
rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile0
rm /var/lib/mysql/ib_logfile1
--Start MySQL
service mysql start
--Recreate table
\mysql ( to open mysql without alias)
CREATE DATABASE mappingservicedb
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON mappingservicedb.* TO 'otf';
--Checking df to ensure storage usage did in fact go down a good idea
--And then load the dump file as normal. |
Upload the backup package to the UAT or DEV where you'll be refreshing the db.Step 3: Import the backup database contents
To avoid errors (may be avoidable in another fashion), log in as root user (requires password) and delete the existing schema
| Code Block |
|---|
% mysql -uroot -p > drop schema mappingservicedb; CREATE DATABASE mappingservicedb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci; GRANT ALL ON mappingservicedb.* TO 'otf'; |
Import the dump file (requires password). Note that this requires a about thirty minutes and this command does not provide useful output until complete. There is an option to display the DROP TABLE/INSERT commands, which may be desirable for log files in future.
| Code Block |
|---|
% mysql -uotf -p -h localhost -D mappingservicedb < mappingservicedb.sql |
Step 4: Reindex the contents of the database using the Lucene Reindex admin tool.
Alternately, clear out the contents of the database, rather than removing/recreating it
| Code Block |
|---|
-- Disable foreign key constraints
SET FOREIGN_KEY_CHECKS = 0;
-- Find tables to drop
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT(table_name) INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;
-- Drop tables
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Reenable foreign key constraints
SET FOREIGN_KEY_CHECKS = 1; |
References/Links
- n/a
...