Connect with us

Hi, what are you looking for?

Docker

How to Backup a SQL database from Docker

Backups are important in case of a computer crash or an accident where data gets lost. Some Docker containers have a dependency on storing data in a database like WordPress or Bookstack. An easy way to make sure a backup is made is to create a Powershell script and set it to run daily.

Command to backup a SQL database from a Docker container

The below code snippet will generate a backup of the SQL database and save it to the path chosen:

docker exec mariadb /usr/bin/mysqldump -u root --password=<sqlpassword> --default-character-set=utf8mb4 --databases <database_name> | Set-Content <path\<database_name>.sql>

Description of the Important Fields

  • “mariadb” is the container name.
  • “root” is the username that is used.
  • <sqlpassword> should be replace with the passed used by the username which in this case is root.
  • <databasename> should be the name of the database

Enhance the Backup Script

Currently running the backup script will save the database into a SQL file but if set to run daily eventually there will be a lot of backups which depending on size could become an issue. The below script will do two things.

  1. The first half of the script will add the year, month, day and time to the filename. This adds the timestamp to the filename and ensures that each time the backup script runs it will not already exist.
  2. The second half of the script will check to see how many files exist in the folder and keep only the 5 files with the most recent Creation Time. Change ‘Skip 5’ to a different value to keep more or less backup files.
[string]$filePath = "<path\<database_name>.sql">;

[string]$directory = [System.IO.Path]::GetDirectoryName($filePath);
[string]$strippedFileName = [System.IO.Path]::GetFileNameWithoutExtension($filePath);
[string]$extension = [System.IO.Path]::GetExtension($filePath);
[string]$newFileName = $strippedFileName + '_' + [DateTime]::Now.ToString("yyyyMMdd-HHmmss") + $extension;
[string]$newFilePath = [System.IO.Path]::Combine($directory, $newFileName);

Move-Item -LiteralPath $filePath -Destination $newFilePath;

$archive = "<path>"
gci $archive -Recurse| where{-not $_.PsIsContainer}| sort CreationTime -desc| 
    select -Skip 5| Remove-Item -Force

Congrats! From here copy both code blocks into one Powershell file (.ps1) and it can be reused for other databases if necessary.

Click to comment

Leave a Reply

Your email address will not be published.

You May Also Like

Docker

Setting up NGINX with a free Let’s Encrypt SSL certificate is a breeze using Docker and the container maintained by Linuxserver.io. The default setup...

Plex

Plex updated it’s support of collections at the end of 2017 by letting the user choose to group movies in a collection ie. Star...

Linux

Configure the TP-Link AX50 router so that it can be shared between both Windows and Linux. The router has USB sharing built into the...

Technology

Cloudflare has plenty to offer even to free users. I looked into some methods of improving the TTL or time to first byte as...

Copyright © 2021 Carl Peterson. Theme by MVP Themes, powered by WordPress.