Overview
Documents the process for downloading a PROD daily backup to UAT 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.
> sudo su - root > sudo aws s3 ls s3://backup-ihtsdo-t1/daily/mapping.ihtsdotools.org/mapping/
Output will appear as a list of backups by date. The last six days of backups are retained, e.g.:
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.
> 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.
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
Step 3: Import the backup database contents (this may not be necessary or can be done another way)
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;
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.
% mysql -uotf -p -h localhost -D mappingservicedb < mappingservicedb.sql
Step 4: Reindex the contents of the database using the Lucene Reindex admin tool.
Notes: 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.
References/Links
- n/a