Database Backup & Restore

Pre-requisite:

  • How to connect to the server is known.
  • Application and Database server is known.

Steps:

Taking database backup

  1. Log in to the server from which you want to take backup.
  2. Open context.xml file from $TOMCAT_HOME/conf/context.xml to get a Username and Password of that database.

    Username and Password from the context.xml file
    <Resource name="jdbc/openspecimen" auth="Container" type="javax.sql.DataSource" 
      maxActive="100" maxIdle="30" maxWait="10000" 
      username="root" password="<password>"  driverClassName="com.mysql.jdbc.Driver" 
      url="jdbc:mysql://localhost:3306/<database name>" 
      validationInterval="3600" 
      testOnBorrow="true" 
      validationQuery="Select 1 from dual" 
    /> 
  3. Access the database using mysql -u<username> -p command
  4. Take the backup of the database using db_export_script.sh  script. (Fill the appropriate values in the script for DB name, user, root password, hostname etc..)

  5. Take the backup of the data folder of OpenSpecimen.

Copying the database backup file and data folder.

  1. Follow the command to copy that backup file from one server to another server (Copy from DB server to a Test server).
  2. Use command scp <backup_file_path>/<filename> <username>@<destination_host>:<destination_directory>/ 

    Copy backup file one server to another server
    scp filename.SQL.gz username2@destination_host:directory2/
  3. Use command scp <backup_data_dir_path>/<folder_name>  <username>@<destination_host>:<destination_directory>/ 

    scp openspecimen/app/openspecimen/data_bkp.zip username2@destination_host:directory2/

Importing the backup file into the required database.

  1. Log in to the server where you need to import the backup file.
  2. Shutdown the OpenSpecimen tomcat service.

    Shutdown tomcat
    ./catalina.sh stop
  3.  Drop the existing database.

    Note

    Confirm the database before dropping.


    Drop Existing Database
    DROP DATABASE database_name;
  4. First, uncompress the file using the below command.

    Uncompress the file
    gunzip <file_name>.sql.gz
  5.  Open the dump(.sql) file in text editor.
    1. Change the DEFINER value relevant to the database you are using.
      1. To find the current database user hostname('current hostname'), enter the following query:
        1. SHOW GRANTS FOR current_user();
      2. Find and replace every occurrence of  DEFINER = `<other user>`@`<other hostname>` with DEFINER = `<current user>`@`<current hostname>`. 
      3. For example, while importing the backup of a dump taken from the prod server, every entry of DEFINER = `prod_user`@`prod_host`
        should become DEFINER = `test_user`@`localhost`
    2. Save the file as .sql
  6. Create the Database of the same name (Use dropped database name).

    Create Database
    CREATE DATABASE database_name;
  7.    Import the dump file into the database using the following command.

    Restore Database Backup
    mysql -u <new_user> -p <database_name> < <file_name>.SQL;
  8. Exit from the database and take the backup of the existing data folder. and after taking backup clear all the data directory.
  9. Unzip the copied data folder zip. and copy all data from the unzipped data folder and paste it into the current cleared data directory.
  10. Start the stopped tomcat.

    Start tomcat
    ./catalina.sh start

Errors may occur after taking the database backup from one server to another server.

Error 1: "couldn't create the directory for storing de file data."

Reason: While taking database backup from the server1 all OpenSpecimen related configuration setting comes with the database dump. so the data directory path of server 1 also comes with the database dump file. so when we take this dump to another server say server 2 and import that dump file to MySQL. (Remember data directory path is same as Server 1. which is not valid).& when we Start the server we get above error because server 2 don't have permission to create the directory which is an actually wrong path.

Solution: Update the path of data directory in the database and restart the server.

Connect to the MySQL. get into the database.

Step 1: Get the Identifier value for the property data_dir.

select * from os_cfg_props where name = 'data_dir';


Step 2: Get all values for the data_dir property from the os_cfg_settings table.

select * from os_cfg_settings where property_id = <Identifier value of Step 1>


Step 3: Delete the older data_dir property.

delete from os_cfg_settings where property_id = <Identifier value of Step 1> and value is not null and length(value) > 0;


Step 4: Update the Activity status to Active.

update os_cfg_settings set activity_Status = 'Active' where identifier = <Identifieer value of Step 2>;


Error 2:  "Invalid label print output directory."

Reason: The reason for this error is also the same as previous Error 1, i.e the value for the label print directory comes from the server 1 from where we took the database dump. so we need to edit path of the label print output directory with the server 2 path.

Connect to the MySQL. get into the database.

 Run the following query.

select RULE_DEF from os_print_rules where ACTIVITY_STATUS = 'Active';

you will find a JSON formatted data. now just see the value of this attribute "cmdFilesDir". The value for this attribute might be matching with the path specified in the error. this path is from server 1 we need to change this path with the server2 path.

so just update or replace the path of the print-labels directory of server2. (print-labels directory is present in the data directory of OpenSpecimen.)

eg : /home/user/openspecimen/app/openspecimen/data/print-lebels.