Docker

How To Backup A SQL Database From Docker

Carl Peterson

• 2 min read
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.