SNOMED International Mapping Tool

Space shortcuts

Child pages
  • Cloning PROD Database to UAT

Overview

Documents the process for downloading a PROD daily backup to UAT (or DEV) and importing it.

Details


Step 1: Download the latest backup package from the PROD server to your local laptop.  It will be in the /opt/backup/mapping/tmp directory.

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.

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. 

% 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. 


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:

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:

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.

To avoid errors (may be avoidable in another fashion), log in as root user (requires password) and delete the existing schema

% mysql -uroot -p
> drop schema mappingservicedb;
CREATE DATABASE mappingservicedb
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
GRANT ALL ON mappingservicedb.* TO 'otf';


Alternately, clear out the contents of the database, rather than removing/recreating it

-- 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;



  • n/a



  • No labels