Restore from filesys backup

[wzrdtales]

When you have big sql file backups it takes like forever to restore them, restoring the MySQL/MariaDB file system is much faster. Probably the password needs to be set again, but just archiving the /var/lib/mysql directory is way faster than restoring a sql file.

[josegonzalez]

This sounds like something that should not be implemented in the core. It would require stopping the database to get a consistent disk snapshot, and thats something you can orchestrate already using the provided interfaces.

[wzrdtales]

But not really in a nice way and to be honest: Making a difference between sql dumps and filesys dumps: In both cases "you stop" the database. If the SQL Dump is overwriting and deleting/truncating tables you use, you also need to stop working with your application. Making changes at the database with zero downtime is the job of migrations though not of dumps. Those are for initialization or seeding tasks.

However will still fork it, but will not open the PR, may be a hard fork.

[josegonzalez]

Calling mysqldump on a database doesn't require downtime in all cases. For InnoDB, it will lock the table to make a consistent snapshot. Depending upon the amount of data, yes, it may pause writes for you, but imo that's a tradeoff you have to make when using the plugin.

Here is a short script you can put somewhere to run maintenance on your database. As you can see, this would require orchestration with your application to ensure users don't just get 500 pages and instead get an error page. An alternative would be to slave a database to your mysql instance and run backups against that to ensure your site stays up during deploys.

# using the official maintenance plugin
dokku plugin:install https://github.com/dokku/dokku-maintenance.git

# some configuration
SERVICE=service-name
BACKUP_DATE=$(date +%Y-%m-%d-%H.%M.%S)
APP=app-name

# enable maintenance on linked applications
dokku maintenance:on $APP

# actually do the backup
dokku mysql:stop $SERVICE
tar -zcvf "$SERVICE-$BACKUP_DATE.tar.gz" /var/lib/dokku/services/mariadb/$SERVICE
dokku mysql:start $SERVICE

# some command to disable maintenance mode for your database
dokku maintenance:off $APP

The goal of the official datastore plugins is to provide primitives for building out datastore setups, not to solve every problem. For instance, we also don't have clustering, or master-master setups. This is a complex topic, and handling all cases for each plugin is out of scope for sure.

Note: you can also probably use something like Percona Xtrabackup for faster database backups, as documented here.

[wzrdtales]

I'm only interested in this plugin for test instances and yes mysqldump doesn't "require" a downtime, a lock still is kind of a downtime, especially if you need to lock everything to ensure integrity of the backup. And still importing a sql dump to create schemas and seed always doesn't seed in a zero downtime friendly manner, unless this dump was explicitly crafted to perform like that.

I will still fork the plugin and buld the filesys dumps and restores into it to have fast boot up of new test instances at any wished time. For this use case this plugin is useful, haven't found it useful for real productive scenarios yet, unless the app is very very small.

By providing this functionality within the plugin I provide also all developers/qas the possibility to initilialize dumps without waiting forever for the instance. It's actually in this case 15minutes vs. 20 seconds. And on the test instance that would be even worse, as it is way less powerful than the production dbs.

And how I archive a filesys dump is already known to me, and percona xtrabackup is not an option.

[josegonzalez]

If you want to add that functionality in a PR, I would definitely accept it.

[wzrdtales]

Ok, then I'm going to open that PR, I need to take a look at generalizing this functionality a bit more, there are already changes I have made https://github.com/wzrdtales/dokku-mariadb/tree/mariadbFileSys, but those are still designed to some of our processes, such as using pixz to compress as it supports multi core decompression and not only mutli core compression which 7z and others do not. Also to note that these changes are also not yet final.