Restore mysql fails

[mariovitale1979]

Restore mysql fails

[fghaas]

Hi! Thanks for reporting this. Here's a workaround for you: assuming that you haven't created any users or set privileges and passwords in your MySQL database manually (you shouldn't), you can add this bit to your Tutor configuration, then after tutor config save run a fresh backup and restore.

BACKUP_MYSQL_DATABASES:
  - openedx

(Add other databases except mysql to this list if you are running any plugins that create another database, such as tutor-contrib-webhook-receiver.)

This way, the mysql database is not included in the backup, and consequently, no attempt is made to overwrite it on restore.

We should probably make this the default, now that people are moving to Palm and with it, to MySQL 8. Any MySQL user accounts and privileges are managed by init jobs in Tutor anyway, so the mysql database shouldn't really contain anything that's necessary to restore.

Please let us know if this workaround gets you moving again. Thanks!

[fghaas]

@dagg Yes please, do send a PR for that! I have a couple of questions/suggestions on your approach, but those are best discussed in a PR thread. Thanks for looking into this!

[mariovitale1979]

That worked like a charm !!!!
Thanks for the quick support !

[fghaas]

Good to know, but let's not close this just yet — like I said, what I recommended you do was a workaround, not a fix. :)

[mariovitale1979]

hmm, maybe I was too fast confirming.
I don't know if this is expected but after I restored the backup on a different openedx instance, I see that the courses are there but I lost some settings like the 'indigo' theme, the OAuth configuration I had configured.
Is this expected?

[fghaas]

@mrtmm What do you think, should we make [openedx] the default for BACKUP_MYSQL_DATABASES?

• We shouldn't need to back up the mysql database, for reasons discussed above.
• If people are running on AWS RDS/Aurora, we know that including the mysql already broke things for MySQL 5.7, so anyone using that would have needed to exclude mysql from BACKUP_MYSQL_DATABASES for a while (see #44).

So, it probably makes more sense to default to backing up just the openedx database.

The only situation in which this is problematic is if people are running with databases other than openedx that they do want backed up. If they rely on the current default behavior of mysqldump --all-databases, and forget to subsequently set BACKUP_MYSQL_DATABASES to a list that includes all the databases they want backed up, they'd lose some backup data that they are presumably interested in.

[fghaas]

@mariovitale1979 OAuth settings are in the openedx database, so they would be included in the backup. However, they are keyed to the site configuration so if you restore your database to a different Open edX instance than where you backed it up (with a different site name, presumably), then it would be expected that you "lose" them. (Which is reasonable, because your OAuth callbacks wouldn't work anyway.) In other words, that's a wholly different issue compared to what you initially reported — and it does sound like the behaviour you describe is by design.

[mariovitale1979]

ok, makes sense. Thanks for clarifying. So the plugin's purpose is mainly made for backup/restoring of 1 openedx instance. Not for transferring everything to a different instance.

[fghaas]

You can totally transfer everything to an exactly identically configured instance, as you would if you ran a production Open edX instance in one location and another, non-production one that's running in a different location, so that you can cut over from one instance to the other by, say, a DNS update (or by transferring the IP address behind your DNS name, using a dynamic routing protocol). You can even automate this via scheduled backup and restore; see the README for details.

But backing up one database and trying to drop it into a completely different site is something you can't expect to work, without additional modifications, in any Django application. That's not a limitation of Open edX specifically, nor of Tutor, and certainly not of this plugin.

[mrtmm]

@mrtmm What do you think, should we make [openedx] the default for BACKUP_MYSQL_DATABASES?

Or maybe, since we know not having it defined can be problematic in various cases, we can make it a required setting? So anyone who is not using it yet will get a message to set it? So there is no risk of someone silently losing some data from their backups.

[fghaas]

Hmmm at the risk of asking a very silly question, how does one do that, in a way that doesn't break Tutor's expectations? The way I read the cookiecutter, I think we can

• set a variable with a default (even if that default is None/null, the empty string, an empty list, etc.),
• set a variable with a "unique" value, such as a generated password,
• set an override for an existing Tutor configuration value.

But setting a variable with no default that the user must set (that is, a "required" setting) — is that a thing that a plugin is "allowed" (expected) to do?

[mrtmm]

Okay, I was thinking of the "unique" option (previously named "add") and when doing something like:

config = {
    "unique": {
        "MYSQL_DATABASES": "{{ MYSQL_DATABASES }}",
    },
    "defaults": {
         ...
    }

the setting requires itself basically, if you don't have BACKUP_MYSQL_DATABASES defined, when running tutor config save you should see an error like: "Error: Missing configuration value: 'MYSQL_DATABASES' is undefined"

But setting a variable with no default that the user must set (that is, a "required" setting) — is that a thing that a plugin is "allowed" (expected) to do?

Well that I am not sure about, it was my interpretation that the "unique" option was suitable for this but now that I read the cookiecutter example again I see that it suggests that a default value should be set.

So on second thought, I believe "unique" would still be the right place for this setting because I believe the "settings that don't have a reasonable default for all users" bit applies here; who's also running ecommerce or plugins with a database, we don't know. In addition in that case the setting will be added to config.yml so folks who haven't defined it yet should notice it. But what should be the default value either the empty list or openedx only I am still conflicted but I would trust your judgement on that.

[fghaas]

There is another option that we have here, which is to retain the default for BACKUP_MYSQL_DATABASES being [], but modify the plugin's behavior in that case.

Right, now, we do this:

tutor-contrib-backup/tutorbackup/templates/backup/build/backup/backup_services.py

Lines 53 to 54 in 1192464

	else:
	databases_statement = "--all-databases"

What we could also do is that rather than set --all-databases, we enumerate the databases first, then exclude mysql from that enumeration, and then back up all others. That way we'd catch notes, ecommerce, etc. if someone runs those.

If we did that, and

• someone has already set BACKUP_MYSQL_DATABASES, the plugin already does (and continues to) backup the databases the user wants.
• someone hasn't set BACKUP_MYSQL_DATABASES and manages their all their database users, passwords, and privileges with Tutor (as they should), then the backup includes all databases that matter.
• someone hasn't set BACKUP_MYSQL_DATABASES and they manually created or set users, privileges, or passwords, they would be in trouble. But they probably are anyway, because in a Tutor environment you really aren't supposed to do that.

[mrtmm]

What we could also do is that rather than set --all-databases, we enumerate the databases first, then exclude mysql from that enumeration, and then back up all others. That way we'd catch notes, ecommerce, etc. if someone runs those.

That sounds quite reasonable to me :)

[borgdrone7]

Hi! Thanks for reporting this. Here's a workaround for you: assuming that you haven't created any users or set privileges and passwords in your MySQL database manually (you shouldn't), you can add this bit to your Tutor configuration, then after tutor config save run a fresh backup and restore.

BACKUP_MYSQL_DATABASES:
  - openedx

(Add other databases except mysql to this list if you are running any plugins that create another database, such as tutor-contrib-webhook-receiver.)

This way, the mysql database is not included in the backup, and consequently, no attempt is made to overwrite it on restore.

We should probably make this the default, now that people are moving to Palm and with it, to MySQL 8. Any MySQL user accounts and privileges are managed by init jobs in Tutor anyway, so the mysql database shouldn't really contain anything that's necessary to restore.

Please let us know if this workaround gets you moving again. Thanks!

Where do we actually set this BACKUP_MYSQL_DATABASES conf? In config.yml that is located in the root of tutor folder?

[fghaas]

Yes, and in addition you have to add the backup plugin to the PLUGINS list.

[dagg]

Hello @fghaas , and everyone else, I had the same problem with the 'mysql' schema when trying to restore.

I saw the whole conversation here and there are some very interesting solutions.

I have one suggestion though which I have implemented in a fork:
Get a list of all database schemas except from 'mysql' (and 'sys', 'performance_schema' and 'information_schema', I think those aren't needed as well but I might be wrong), and use that list of databases instead of all.
So, instead of '--all-databases' use '--databases {databases_list}'...

My changes in the code are here (just one line):

https://github.com/dagg/tutor-contrib-backup/blob/ca66844bded505e90d0176ce509b7fd3222aaadf/tutorbackup/templates/backup/build/backup/backup_services.py#L24

And here:

https://github.com/dagg/tutor-contrib-backup/blob/ca66844bded505e90d0176ce509b7fd3222aaadf/tutorbackup/templates/backup/build/backup/backup_services.py#L56-L80

Maybe they can be proven useful.
I didn't create a PR for it but if you want me to, just let me know.

~ ~ ~

Something else I would like to propose is the possibility to create the backup files/folders (before compressing them into the .tar.xz file) inside the /data/backup folder which is mounted and visible outside the container, instead of the /data folder which only exists inside the container.

My problem is that our implementation has a disk space problem with our huge data (about 90G for MySQL and MongoDB).
I think if I mount the $(tutor config printroot)/env/backup/ folder to another drive (or a network drive), this would solve our disk space problem while backing up the databases... Since I'm quite novice with docker, I cannot be sure if that will work, and also I cannot manage to make my changes to the backup above to work yet, but I'd love to know your thoughts on these suggestions.

[dagg]

Thank you @fghaas the PR has been created!