Vvv: Too many database backups

[simonwheatley]

I've finally got around to installing the triggers on my VVV, and wondered why ups and halts were taking so long… it's the database backups. So many database backups. Some quite large. Some I don't need.

This ticket is a place to wonder "if we have auto site setup, to create sites and associated databases, how do we remember to remove the databases we no longer need?"

I have no answer. Just a lot of database backups.

[lkwdwrd]

Completely agree here. Some backups on my machine are for installs I no
longer have or use, but because I haven't cleaned MySQL yet, vagrant is
still dumping backups every halt. It would be nice to specify which DBs to
watch for backup/re-import. Might also be nice to trigger something for
auto setup to allow sites set up that way to deal with dumps/imports
themselves.

On Fri, Jul 18, 2014 at 7:56 AM, Simon Wheatley [email protected]
wrote:

I've finally got around to installing the triggers on my VVV, and wondered
why ups and halts were taking so long… it's the database backups. So many
database backups. Some quite large. Some I don't need.

This ticket is a place to wonder "if we have auto site setup, to create
sites and associated databases, how do we remember to remove the databases
we no longer need?"

I have no answer. Just a lot of database backups.

—
Reply to this email directly or view it on GitHub
https://github.com/Varying-Vagrant-Vagrants/VVV/issues/407.

[simonwheatley]

Might also be nice to trigger something for
auto setup to allow sites set up that way to deal with dumps/imports
themselves.

Or even just to drop and load the database from a directory in that site, rather than the central database backups directory… then when you delete the site, it's properly gone.

[simonwheatley]

How about a new VVV file called vvv-db-{database name}.mysql? If this file exists, the database gets backed up to it, if not it gets backed up as now. When a backup is made to a custom location, the script creates a link to the custom location in the standard DB backup directory. The script can then find broken links and delete the database represented by the broken link.

Flow for the halt trigger:

# Backups
For each database name
    If there is a file anywhere in /srv/www called "vvv-db-{database name}.sql (the custom DB backup file)
        # Custom procedure
        Dump the database into that file
        Delete /srv/database/backups/{database name}.sql
        Link to the custom DB backup file as /srv/database/backups/{database name}.sql
    Else
        # Standard procedure
        Dump the database into /srv/database/backups/{database name}.sql
# Cleanup
For each broken link file in /srv/database/backups/
    Drop the database from MySQL
    Delete the broken link

If you wanted to take advantage of the local DB storage, you would need the vvv-db-{database name}.sql file(s) (more than one would be fine).

Stackoverflow answer on finding broken links.

[simonwheatley]

An initial cut of allowing backups to custom locations is here: https://github.com/simonwheatley/varying-vagrant-vagrants/commit/c1c4db2f0e4421938fd19c480269b50afaea370e

Remaining:

• During the DB load (on vagrant up or halt), check for broken links and drop DBs where these are found

[lkwdwrd]

This approach makes sense to me, and is a huge step in the right direction.

I do have a couple installs that have very large databases, it would be nice to have a way of opting out of DB backups altogether for those in particular. Waiting for some of these installs to import/export on halt/up takes a _long_ time. They are databases that I don't care too much about and if they got lost I can always go back and pull a new copy of productions or a different staging site.

How does a site opt into custom backups without a sql file to begin with? I suppose a null file named vvv-db-{name}.sql would trigger the backup and since it wouldn't start with a link in the main backup folder it would be ignored until halt. We should do some testing around this to make sure it works right.

[simonwheatley]

@lkwdwrd To opt out of a backup: how about a vvv-db-{database name}.sql file containing the string no backup? Would be more obvious, ideally. Perhaps some file called vvv-db-{database name}-no-backup.sql? Ideas welcome.

I'm not sure the restoration after vagrant destroy works for non-core VVV DBs (i.e. not WP default, etc) anyway. Perhaps this is a known issue/feature, @jeremyfelt?

[tomjn]

Adding a no_backup: true or backup: false in vvv-config.yml makes sense here

[ivankristianto]

Agree to @tomjn, I have 1.3 GB of mysql database. And it take long time to backup.
I don't need to be backup every time I vagrant halt.
I have to skip it by creating the vagrant_halt_custom right now

[tomjn]

@LoreleiAurora do you have any notes on how this would be implemented? Is it as simple as a conditional around the check? Or is the triggers backup just doing DB tables?

[mariadanieldeepak]

Adding a no_backup: true or backup: false in vvv-config.yml makes sense here

Could anyone tell me if the above feature has been implemented?

[tomjn]

@mariadanieldeepak no it hasn't, there's the problem that given a database it's not possible to determine which site it came from, so adding a no_backup key isn't straight forward

[tomjn]

So in the custom site template we have:

DB_NAME=`get_config_value 'db_name' "${VVV_SITE_NAME}"`

Which could be used to figure out the database in those sites, assuming it hasn't been changed. In that case, I think if db_name is defined, then the no_backup key could be used. I would need to test this in the vagrant file though, and I don't have the time this week to try it out

[mariadanieldeepak]

@tomjn Thank you for getting back me to on this. Meanwhile, I've used vagrant_halt_custom I actually hardcoded the DB name, to skip it for now.

[tomjn]

@mariadanieldeepak can you post that in a gist and link here for reference?

[mariadanieldeepak]

@tomjn,

I've both the vagrant_halt_custom and db_backup_custom scripts here.

[tomjn]

@simonwheatley you'll be glad to know there are now toggles in the develop branch for database backup and restore thanks to https://github.com/Varying-Vagrant-Vagrants/VVV/pull/1705

Modifying a vagrant_halt_custom and db_backup_custom file should no longer be necessary

[simonwheatley]

Yay!

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.