mysql provider and mysql_database resource

[apparentlymart]

Allows databases on pre-existing MySQL servers to be created and managed by Terraform.

• Provider
• Database resource
• Documentation
• Acceptance Test

In future I imagine this provider growing to be able to manage other top-level MySQL objects like users, permission grants and system configuration variables. To start it supports only databases, since that is my main use-case for now (creating additional databases on an RDS instance once it has booted.)

[josephholsten]

sounds awesome, but do we really want this in mainline tf? I wish we could have this be in its own repo, but also be able to use it from atlas. le sigh

[apparentlymart]

@josephholsten, @phinze suggested to me that "non-esoteric" use-cases belong in Terraform core. I think MySQL passes that test, since it's used by many different organizations. However, I'd be happy to maintain this as an out-of-tree plugin if that is preferred.

(That comment from @phinze also includes a trick for using out-of-tree plugins with Atlas.)

[josephholsten]

I'm sold on non-esoteric. And I must have misread your RDS use case, it makes total sense.

Is this ready for beta testers?

(& I was afraid that was the trick into atlas doing what I want.)

[apparentlymart]

I believe this is ready, but I've not actually put it into practical use yet since I've not reached the point of setting up the shared RDS cluster that this was intended for. So far I've only tested it in a very academic way on a local mysql server, so if others would like to test it in other contexts that would be much appreciated.

[apparentlymart]

Based on the experiences of @chelarua in #3653 I have reworked this so that it is the resource actions themselves that create the database connection, rather than trying to share a connection across the whole Terraform run.

This serves two purposes:

• Allows the MySQL connections to be closed as soon as they are no longer needed, rather than left open until Terraform eventually exits. This is not a huge win for normal Terraform runs but it means that the acceptance tests (which instantiate the provider several times) are much kinder to the MySQL server.
• It works around the chicken-and-egg problem described in Partial/Progressive Configuration Changes #4149, albeit only in the most common case where neither the database server nor the database exist yet and both are being created on the same Terraform run. It can't help with the situation where Terraform needs to recreate the database server for some reason but there is already a mysql_database instance already in the state, but that's just a general flaw in Terraform and not specific to this provider.

[apparentlymart]

My intended future scope for this provider is to get the database set up to the point where an app's own database management infrastructure can take over.

In my experience most applications expect to be provided with the following:

• A MySQL database server. The mysql provider does not try to solve this, since it's expected that the user will use something like aws_db_instance or will create a "normal" server and install MySQL on it using something like Chef.
• An empty database in which they can create tables and other such schema objects. The already-implemented mysql_database resource accomplishes this.
• A user account to log in as. This provider might eventually grow to have a resource like mysql_grant that uses GRANT and REVOKE statements to provision additional users with varying privileges.

I specifically do not intend this provider to ever have things like mysql_table, mysql_view, etc. Schema management for applications is a messy problem that is already well solved elsewhere.

[apparentlymart]

Incidentally, since my earlier comment I've started using this provider in real infrastructure configs, to create databases on RDS instances.

Aside from the aforementioned chicken-and-egg problem when creating the RDS instance and the MySQL database within a single config (which my latest modifications should work around) it has been working just fine. (So far we've been working around it by using -target to apply the aws_rds_instance resource in isolation, before applying the mysql_database resource.)

[phinze]

Typo here, mysql_database I believe.

[apparentlymart]

Haha that's a pretty great typo. I can only imagine what was going on in my brain when I typed that. Thanks, will fix!

[phinze]

Just got a chance to review this. One doc typo but otherwise this is looking great! Merge at will.

[winglian]

@apparentlymart Is it possible to configure multiple mysql providers? for example if we have multiple aws_rds_instances? There doesn't seem to be a way to reference a different provider in a given mysql_database block.

[apparentlymart]

@winglian Terraform core has the idea of a "provider alias" that allows multiple providers of the same type to be instantiated.

For the MySQL provider that would look something like this:

provider "mysql" {
    alias = "foo"
    // ... and then other mysql provider arguments
}
provider "mysql" {
    alias = "bar"
    // ... and then other mysql provider arguments
}

resource "mysql_database" "foo" {
    // Select the appropriate provider using its alias
    provider = "mysql.foo"

    // ... and then other mysql_database arguments
}
resource "mysql_database" "bar" {
    // Select the appropriate provider using its alias
    provider = "mysql.bar"

    // ... and then other mysql_database arguments
}

You can read more about this in the "Multiple Provider Instances" section of the provider configuration docs page.

[aequitas]

+1 on high level objects. Is somebody already working on user/grants resources?

[blalor]

was just wondering about this myself…

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.