In this article, we will schedule a cronjob command in Ubuntu to backup MySQL database, and then use a dotnet core console application to upload this file in a Dropbox account.

We will accomplish this with the following components:

  • Configure Dropbox to allow programmatic access
  • Build a C# console application that uploads a specific file in Dropbox and then deletes the local file
  • Build a Cronjob in Ubuntu that will backup a specific database in a preset location
  • Build another Cron that will execute the dotnet application

Dropbox configurations

In order to use Dropbox programmatically, we will need to create an app. This will allow us to upload a file programmatically inside some specific folder where the app has access.

Choose Dropbox API and AppFolder for the access type. This will allow us to get a token that can be authenticated only for some specific folder, not the entire account. Which is great by the way, there is no reason you would ask for full permissions in an app like this one.

After the application is created, you are redirected to the settings screen. Don't worry for Status:Development, this is enough for what we need this for.

Scroll down to Generated access token option and hit the Generate button. Copy the provided token as that's what we need for our app.

Create a console application

Create a dotnet core console application, I'm naming it DemoDropboxUpload. Open package manager and install the Dropbox package:

Install-Package Dropbox.Api

Add a new C# Class: DropBoxService.cs and replace its content with the following:

    public class DropBoxService
    {
        private string ApiToken { get; set; }
        public DropBoxService(string token)
        {
            ApiToken = token;
        }

        public async Task Upload(string remoteFileName, string localFilePath)
        {
            using (var dbx = new DropboxClient(this.ApiToken))
            {
                using (var fs = new FileStream(localFilePath, FileMode.Open, FileAccess.Read))
                {
                    await dbx.Files.UploadAsync($"/{remoteFileName}", WriteMode.Overwrite.Instance, body: fs);
                }
            }
        }
    }

Modify Program.cs and replace the constants with your own values.

class Program
{
    static void Main(string[] args)
    {
        var dropboxApiToken = "<DROPBOX_TOKEN_HERE>";
        var bakupFolderPath = "<YOUR ABSOLUTE PATH TO THE BACKUP FOLDER IN HERE>";
        try
        {
            var dateNowString = System.DateTime.Now.ToString("yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
            var backupName = $"<YOUR_DB_NAME>_db_bak{dateNowString}.sql.gz";
            var localFilePath = Path.Combine(bakupFolderPath, backupName);

            if (File.Exists(localFilePath))
            {
                var dbs = new DropBoxService(dropboxApiToken);
                Task.Run(async () =>
                {
                    await dbs.Upload(backupName, localFilePath);
                }).Wait();
                File.Delete(localFilePath);
            }
            else
            {
                // WARN
            }
        }
        catch (System.Exception ex)
        {
            while (ex.InnerException != null)
                ex = ex.InnerException;

            // WARN
        }
    }
}

Build the project for Ubuntu runtime as a self contained application and then deploy the production build in your server. You can follow this article for more information.

Backup a MySql database in a schedule using Ubuntu crontab command

We will use mysqldump utility to interact with the MySql database and the command structure is something like:

$/usr/bin/mysqldump -u <USER> -p<PASSWORD> <DB_NAME_TO_BAK> > /path/to/file.bak.sql

So we have a problem, the password is written in clear text! I couldn't find a solution to this but there is a workaround: Create a specific user for backup purpose and lock this user privileges to the possible minimum.

Note that there is no space character between the -p option and the password.

Create a backup user:

create user '<BAK_USER_NAME>'@'localhost' identified by '<BAK_USER_-_PASSWORD>';

Based on your storage engine you will need to grant on this user:

  • INNODB: SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER
  • MYISAM: LOCK TABLES, SELECT

Grant the necessary permissions on your user and MySQL is ready.

Grant LOCK TABLES, SELECT ON test_db.*  to '<BAK_USER_NAME>'@'localhost';

Now we need to create a cronjob to execute the backup command. Enter in edit mode using:

$crontab -e

add the following command in the crontab list:

01 02 * * * /usr/bin/mysqldump -u <YOUR_BACKUP_USER> -p<YOUR_BACKUP_USER_PWD> <YOUR_DB_NAME> | gzip > "/home/<YOUR_USER>/ql/<YOUR_DB_NAME>_db_bak$(date +\%Y\%m\%d).sql.gz"

This cron will be executed every day at 2:01 AM, a successful result means there is a gzip file called <YOUR_DB_NAME>_db_bakyyyyMMdd.sql.gz.

Save and close crontab.

Execute the C# application in a schedule to complete the project

The last step is to execute our dotnet core application, that will take the backup file, upload it in Dropbox and delete the local file.

Type crontab -e in your terminal to enter in edit mode, and register the following task

01 03 * * * /usr/bin/dotnet /var/netcore/console/DemoDropboxUpload/DemoDropboxUpload.dll

This will be executed every day at 3:01 AM, and that's it. We have successfully implemented an automated backup strategy using simple crontab commands and a dotnet core console application.

You can find the full application in Github