Restore Production Data into Another Environment - MySQL

The following procedure will provide steps to copy the database and associated files from one instance to another, in this case from production to development. Prior to performing the following steps, it may be useful to perform a full backup of the target RiskVision instance.

1. Consider deactivating the Notification Sender job to prevent unwanted email from the Dev/QA server.   Once the backup is complete, these may be re-activated.
2. Verify all servers are running the same version down to the hotfix level.
3. Acquire a recent backup of the production environment by navigating to %RiskVision_Home%\backup.  Open one of the compressed backup files and locate the dump file which has a '.sql' suffix.
4. Make a backup of the production database
   1. Open a command prompt (run cmd)
   2. cd %AGILIANCE_HOME%\MySQL\bin\
   3. mysqldump -uroot -p<password> --database agiliance --add-drop-database --single-transaction --force --max_allowed_packet=1MB > <file_output_name>.sql (replace values contained in '<>' brackets with the correct value)
   4. You will be prompted to enter the password
5. Copy the output file to the target instance (development) in the %RiskVision_Home%\'\mysql\bin directory
6. Stop all RiskVision services, inclusive of Tomcat, Apache and Jasper Reports and all connectors.
7. Open a command prompt on the database server.
8. Navigate to the %RiskVision_Home%\mysql\bin directory.
9. Login to the database as root, for example: mysql -uroot -p<pwd> (Replace <pwd> with the correct password)
10. Drop and then re-create the Agiliance database using the following commands: a.    drop database agiliance;  <wait for completion> b.    create database agiliance;
11. Log out of MySQL by typing 'exit' and then hitting the 'Enter' key.
12. Import the extracted database file by using the following steps
    1. Open a command prompt and navigate to %RiskVision_Home%\MySQL\bin
    2. From the command prompt, enter the following command and hit the [Enter] key. mysql -uroot -p<enter> < Agiliance_YYMMDD.sql
    3. The import will normally take several minutes to complete and depends on the size of the file.
13. Copy relevant files with the '.xml' extension from production located in %RiskVision_Home%\config to the same directory for the target instance. These files contain modifications to the default RiskVision instance and may not exist.
14. Copy entire folder structure from %RiskVision_Home%\data of the production instance and copy to the same folder of the target instance.
15. Restart the RiskVision services that were stopped in step 6.  After several minutes, you should be able to login to RiskVision   ** Optional: the following steps will help restore reports from production into the target environment.
16. Install Jasper Reports Server on the target environment if not already installed. Refer to the Installation and Configuration Guide for guidance
17. Open command prompt on the server that contains the production instance and navigate to <JASPER_HOME>\buildomatic
18. Create an export file containing all production reports: Js-export --everything--output-zip productionReports.zip
19. Copy file "productionReports.zip" created in step 13 to any folder on the target server
20. Open command prompt on the server that contains the target instance and navigate to <JASPER_HOME>\buildomatic
21. Modify then run the following command which will import the Jasper report contained in file productionReports.zip. Js-import ?input-zip <folder_location>\repository.zip (replace <folder_location> with the actual location of the file you are importing)

For example:  Js-import ?input-zip d:\server\backup\repository.zip