How to handle the database backup and restore in different environments?

I am using Directus in prod and multiple non-prod environments. In prod, users will only be updating content in collections. In non-prod, admins will be testing all other updates regarding new collections, user roles, permissions, etc. I plan on backing up tables related to collection content in prod and restoring them to lower environments in order to keep data up to date. Once testing of new collections or other admin changes are done in non prod environments I plan on backing them up and then restoring the tables related to these changes up to prod. Right now, the plan is to write shell scripts using mysqldump and mysqlimport to perform these operations.

My question is what tables relating to collection content do I need to backup in prod and move to lower environments? Also, could I use the method described here to not overwrite any activity records and avoid losing data?

Solution

Mission critical tables would be:

directus_collections (what collections are there)
directus_fields (what fields are in those collections)
directus_relations (which collections are connected to each other)

I'd personally copy over all the directus_* tables. Even if you don't necessarily have to move over things like activity, I'd play it safe and make sure you don't run into any weird issues later on.

Also, could I use the method described here to not overwrite any activity records and avoid losing data?

If you're not going to edit any actual content in the non-prod environments, you should be able to ignore directus_activity and directus_revisions, as those two are directly related to the raw content.