Database Deployment Recommendations

Data storage holds prime importance for critical business applications. To achieve the desired goal, you must choose the right database. Working with the database may lead to several crashes, which can destroy your data.

Recommendations

• Backup your database. This enables you to recover data after any crash. The types of crashes can be:

  • Operating System
  • Power Failure
  • File System
  • Hardware Problem (Hard Drive, CPU, and more)

• MySQL provides some backup strategies, which can be selected as per your requirements. Before selecting this, you must be familiar with backup and recovery topics:

  • Backup Types: Logical versus Physical, Full versus Incremental, and more
  • Methods for creating backups
  • Recovery methods including point-in-time recovery
  • Backup scheduling, compression, and encryption
  • Table maintenance to enable recovery of corrupt tables

For more information, refer to Backup and Recovery.

Database Backup in MySQL

A database backup is a database copy that can be used to restore the database in case of a data loss event. MySQL provides the following methods to back up a database:

• mysqldump: This is a command-line utility that can be used to create a backup of the entire database or some selected tables.
• phpMyAdmin: This is a graphical user interface (GUI) tool that can be used to create a backup of the database.
• MySQL Enterprise Backup: This is a commercial backup tool that offers more features than mysqldump or phpMyAdmin.

For MySQL docker container, the following steps are followed with the help of mysqldump tool for the backup:

1. Execute a bash shell in the running MySQL Docker container.

   1. 
      docker exec -it <mysqldb_container> bash
      
      
      

      For example:

      
      docker exec -it mysqldb bash
      
      
      

   2. 
      mysqldump --user=<db_user> --password=<db_password> <db_name> --result-file=<location_of_backupfile_inside_container>
      
      
      

      For example:

      
      mysqldump --user=idpv2 --password=<idpv_password> idpv5 --result-file=/tmp/dump.sql
      
      
      

   3. 
      exit
      
      
      

2. Copy the backup file from container to the host.

   
   docker cp <mysql_container>:<location_of_backupfile_inside_container> <location_of_backupfile_on_host>
   
   
   

   For example:

   
   docker cp mysqldb:/tmp/dump.sql /tmp/
   
   
   

Database Recovery in MySQL

A database recovery is the process of restoring a database from a backup. This can be done in case of a data loss event, such as database crash, or database corruption. The recovery process for MySQL depends on the type of backup that was created. If a full backup was created, the entire database can be restored. If an incremental backup was created, only the changes that have been made since the last full backup can be restored. The following steps are involved in the MySQL database recovery:

1. Restore the backup to a new database.

   1. Create a new database inside the MySQL container.

   2. Exit from the MySQL Container.

2. Import the data from the original database into the new database. Execute the following command:

   
   cat <location_of_backup_sqlfile> | docker exec -i <mysql_container> /usr/bin/mysql -u <MySQL_user> --password=<MySQL_DB_Password> <Name of DB to restore>
   
   
   

   For example:

   
   cat dump5.sql | docker exec -i mysqldb /usr/bin/mysql -u root --password=password idpv5
   

3. For using the database in the IDPV server, open appsettings.yml and change the name of the database. Refer to the database configuration for more details.

Database Backup in MariaDB

A database backup is a database copy that can be used to restore the database in case of a data loss event. MariaDB provides the following methods to back up a database:

• mysqldump: This is a command-line utility that can be used to create a backup of the entire database or some selected tables. For mysqldump, refer to the following steps.

• mariabackup: This is a command-line utility that can be used to create a backup of the database. For mariabackup, refer the below steps:

  1. Install MariaBackup inside the container. You can do this by accessing the container's shell and running the following commands:

     
     docker exec -it mariadb-container /bin/bash
     apt-get update
     apt-get install mariadb-backup
     

  2. Use MariaBackup to take a backup of the database. This will create a full backup.

     
     mariabackup --user=root --password=your_password --backup --target=/backup/
     

     Replace your_password with your root password. This command will create a backup in the /backup/ directory within the container.

  3. Exit the container's shell, and then copy the backup directory from the container to your host machine. You can use the docker cp command for this.

     
     docker cp mariadb-container:/backup /path/on/your/host
     

     Replace /path/on/your/host with the path on your host machine where you want to store the backup.

Database Recovery in MariaDB

A database recovery is the process of restoring a database from a backup. This can be done in case of a data loss event, such as database crash, or database corruption. The following steps are involved in the MariaDB database recovery:

For mysqldump, refer to the following steps.

For mariabackup, refer the below steps:

1. Copy the backup directory from your host to the new container.
   Use docker cp:
   

   
   docker cp /path/on/your/host/backup mariadb-container:/restore/
   

   Replace /path/on/your/host/backup with the actual path to the backup directory on your host machine and /restore/ with the path inside the container.

2. Restore the database:

   1. Access the new container's shell:

      
      docker exec -it mariadb-container /bin/bash
      

   2. Now, use mariabackup to perform the database restoration:

      
      mariabackup --copy-back --target=/restore/backup/
      

      Replace /restore/backup/ with the actual path inside the container where you copied the backup directory.

3. Start the MariaDB service within the container:

   
   service mysql start
   

4. For using the database in the IDPV server, open appsettings.yml and change the name of the database. Refer to the database configuration for more details.

Database Backup in PostgreSQL

A database backup is a copy of the database that can be used to restore the database in case of a data loss event. PostgreSQL provides the following method for backing up a database:

• pg_dump: This is a command-line utility that can be used to create a backup of the entire database or some selected tables.

For PostgreSQL docker container, following steps are required with the help of pg_dump tool for taking the database backup:

1. Access the docker container:

   
   docker exec -it  bash
   

2. Perform the backup:

   • Use the pg_dump command to create a backup of your PostgreSQL database. Replace database_name, username, and backup_file.sql with your specific details:

   
   pg_dump -U <username> -d <database_name> -f /path/to/backup_file.sql
   
   
   

   This command will dump the database to the specified SQL file.

3. Exit Container.

   
   exit
   

Database Recovery in PostgreSQL

A database recovery is the process of restoring a database from a backup. This can be done in case of a data loss event, such as database crash, or database corruption. The following steps are involved in the PostgreSQL database recovery:

1. Copy the backup file to the container. Use the docker cp command to copy the backup file into the PostgreSQL container. Replace container_name, /path/to/backup_file.sql, and /path/inside/container.sql with appropriate values:

   
   docker cp </path/to/backup_file.sql> <container_name>:</path/inside/container.sql>
   
   
   

2. Access the docker container.

   
   docker exec -it <container_name> bash
   
   
   

3. Restore the Backup. Use the psql command to restore the database from the backup file. Replace database_name, username, and /path/inside/container.sql with your specific details.

   
   psql -U username -d database_name -f /path/inside/container.sql
   

4. For using the database in the IDPV server, open appsettings.yml and change the name of the database. Refer to the database configuration for more details.

Database Backup in MSSQL

A database backup is a copy of the database that can be used to restore the database in case of a data loss event. MSSQL provides the following method for backing up a database:

For MSSQL docker container, following steps are required for taking the database backup:

1. Connect to the MSSQL container.

   
   docker exec -it <mssqldb-container> /opt/mssql-tools/bin/sqlcmd -U <username> -P <password> -S <port-on-which-db-container-is-hosted>
   
   
   

   Example:

   
   docker exec -it mssqldb /opt/mssql-tools/bin/sqlcmd -U sa -P <password> -S 172.17.0.4
   
   
   

2. Perform the backup.

   
   BACKUP DATABASE <db-name>TO DISK = '/var/opt/mssql/backup/db-name.bak';
   Go
   
   
   

3. Copy the backup file. The backup file is stored within the container. To retrieve it, you can use docker cp to copy it from the container to your host machine.

   
   docker cp sql_server_container:/var/opt/mssql/backup/ db-name.bak ./db-name.bak
   

Database Recovery in MSSQL

A database recovery is the process of restoring a database from a backup. This can be done in case of a data loss event, such as a database crash, a data corruption, or a disaster.

The following steps are involved in recovering a database in MSSQL:

1. Copy the backup file to the container.

   
   docker cp ./Db-name.bak sql_server_container:/var/opt/mssql/backup/Db-name.bak
   

2. Connect to the MSSQL Container.

   
   docker exec -it <mssqldb-container> /opt/mssql-tools/bin/sqlcmd -U <username> -P <password> -S <port-on-which-db-container-is-hosted>
   
   
   

   Example:

   
   docker exec -it mssqldb /opt/mssql-tools/bin/sqlcmd -U sa -P <password> -S 172.17.0.4
   
   
   

3. Restore the Database.

   
   USE master;
   RESTORE DATABASE YourDatabaseName FROM DISK = '/var/opt/mssql/backup/Db-name.bak' WITH REPLACE;
   GO
   
   
   

4. For using the database in the IDPV server, open appsettings.yml and change the name of the database. Refer to the database configuration for more details.

Database Backup in OracleDB

A database backup is a copy of the database that can be used to restore the database in case of a data loss event. OracleDB provides following method for backing up a database:

• expdp: This is a command-line utility that allows you to export specific tables, schemas, or the entire database. You can choose how to export both data and metadata (such as table structures) or just one of them.

For OracleDB docker container, following steps are followed with the help of expdp tool for taking the backup:

1. Connect to the OracleDB Container. You can connect to the OracleDB container using SQL*Plus or another Oracle client.

   
   docker exec -it <oracle-db-container> sqlplus sys/<your_password_here>@//localhost:1521/ORCLCDB as sysdba
   
   
   

2. Take the backup. Use Oracle's DATA PUMP utility (expdp) or another backup method to create a backup of your database.

   For example:

   
   expdp system/<your_password_here>@ORCLCDB directory=DATA_PUMP_DIR dumpfile=backup.dmp logfile=backup.log
   
   
   

3. Store the backup file (backup.dmp in this case) in a safe location outside of the docker container.

Database Recovery in OracleDB

A database recovery is the process of restoring a database from a backup. This can be done in case of a data loss event, such as a database crash, a data corruption, or a disaster.

The following steps are involved in recovering a database in OracleDB:

1. Connect the OracleDB container again and use the DATA PUMP to restore the database from the backup.

   
   impdp system/<your_password_here>@ORCLCDB directory=DATA_PUMP_DIR   dumpfile=backup.dmp logfile=restore.log remap_schema=source_schema:target_schema
   
   
   

   Replace source_schema and target_schema as required.

2. For using the database in the IDPV server, open appsettings.yml and change the name of the database. Refer to the database configuration for more details.

These are general steps for taking backup and performing recovery in an OracleDB docker container. Refer to the Oracle Docker documentation for more specific details and options for your setup and requirements.