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:
- Execute a bash shell in the running MySQL Docker container.
docker exec -it <mysqldb_container> bash <br>
For example:docker exec -it mysqldb bash
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
exit
- 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:
- Restore the backup to a new database.
- Create a new database inside the MySQL container.
- Exit from the MySQL Container.
- 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
- 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:
-
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
-
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.
- Replace
-
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:
-
Copy the backup directory from your host to the new container.
Usedocker 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.
- Replace
-
Restore the database:
- Access the new container's shell:
docker exec -it mariadb-container /bin/bash
- 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.
- Replace
- Access the new container's shell:
-
Start the MariaDB service within the container:
service mysql start
-
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:
- Access the docker container:
docker exec -it <container_name> bash
- Perform the backup:
- Use the
pg_dump
command to create a backup of your PostgreSQL database. Replacedatabase_name
,username
, andbackup_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.
- Use the
- 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:
- Copy the backup file to the container:
- Use the
docker cp
command to copy the backup file into the PostgreSQL container. Replacecontainer_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>
- Use the
-
Access the docker container:
docker exec -it <container_name> bash
-
Restore the Backup:
- Use the
psql
command to restore the database from the backup file. Replacedatabase_name
,username
, and/path/inside/container.sql
with your specific details.
psql -U username -d database_name -f /path/inside/container.sql
- Use the
-
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:
- 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
- Perform the backup:
BACKUP DATABASE <db-name>TO DISK = '/var/opt/mssql/backup/db-name.bak';
GO
- 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
- The backup file is stored within the container. To retrieve it, you can use
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:
- Copy the backup file to the container:
docker cp ./Db-name.bak sql_server_container:/var/opt/mssql/backup/Db-name.bak
-
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
-
Restore the Database:
USE master;
RESTORE DATABASE YourDatabaseName FROM DISK = '/var/opt/mssql/backup/Db-name.bak' WITH REPLACE;
GO
-
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:
- 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
- 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
- Use Oracle's DATA PUMP utility (
- 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:
-
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
andtarget_schema
as required.
-
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.