[Feature Request] Option to export structure for each database individually

[jurgenhaas]

Currently the script generates daily, weekly and monthly schema exports for all databases at once in the fullschema subdirectory. We do require schema files for each database individually and would like to implement that in a PR if we can agree with the maintainers on the approach to take.

What's our use case?

We are hosting Drupal sites with multiple sites on each host and each site has their own mysql db. As those databases contain session and caching tables that are not required for the backups, we do exclude them with the CONFIG_table_exclude configuration settings.

In cases where we have to restore a backup for an individual Drupal database, we first drop the database and then source the backup file which will then recreate all the tables except those that got excluded. But we can't use the fullschema either, because that would have an impact on other databases on the same host which we don't want at that time.

If we had a schema file matching each of the dumps, we could first source that schema file followed by sourcing the dump and then would get exactly what we were looking for.

Proposed solution:

There should be a new config option (defaults to "no") which could be set to "yes" if one was looking for individual db schema files. If that was turned on, in process_dbs() we could simply execute a second command which also dump the schema and appends .schema to the filename.

What do you think?

[kdebisschop]

I know this is old. But we have exactly the same need. In our case, I would think maybe pairing your changes. with code to ignore tables with database wildcards -- so we could ignore '.cache_' for the data but keep all the tables when dumping the schema.

[jurgenhaas]

I stopped waiting for the maintainers to respond here, so I have copied the file over to my self-hosted GitLab instance and continued maintenance for my own needs over there. You are welcome to grab it and use it as well, if it suits your needs too. You can find it at https://gitlab.lakedrops.com/ansible/roles/mysql/-/blob/master/templates/automysqlbackup.sh