(upbeat music)
- Hi, I'm Christiaan, a Cloud Support Engineer
here at the AWS office in Cape Town.
Sometimes customers ask me,
"How do I enable functions, procedures and triggers
"for my Amazon RDS MySQL instance?"
I tell them that functions, procedures,
and triggers can be enabled on an RDS MySQL instance
when binary login is enabled,
by setting the log_bin_trust_function_creators parameter
to true in a custom DB parameter group,
associated with your MySQL RDS instance.
This can be done without super privileges,
let's take a look.
In this example, I've already created my RDS MySQL instance,
which is using the default parameter group.
To enable functions, procedures and triggers,
for our MySQL RDS instance, we'll be covering the following.
We'll create a custom DB parameter group
for our MySQL RDS instance.
Then we'll modify the custom DB parameter group
and set the log_bin_trust_function_creators parameter
to True.
Finally, we'll associate the newly created
custom DB parameter group to the MySQL RDS instance.
The first thing we need to do,
is create a custom DB parameter group,
because we can't modify the parameter settings
of a default DB parameter group.
As you know, when creating a DB instance
without specifying your DB parameter group,
the default parameter group is applied.
Let's look at creating a custom DB parameter group.
After signing into the AWS management console,
navigate to the Amazon RDS console.
Click on Instances.
Here I can view my RDS instances.
In the navigation pane, on the left side,
choose Parameter groups.
Here we will find all
our existing custom and default parameter groups
for our various existing database engines.
In the top right corner, choose Create parameter group,
so that we can create a new DB parameter group.
We need to be sure to select
the correct parameter group family
in the parameter group family.
Because the version of my MySQL RDS instance
is MySQL version 5.7, I'll select mysql5.7.
This might differ in your case,
depending on the engine version which you use.
Provide a parameter group name for identification purpose.
I'll call this one enablefunctionsprocedurestriggers.
In the description,
I'll just add Enable functions procedures and triggers.
Now, I'll choose Create, to create a parameter group.
Now that we've created a custom parameter group,
we need to modify the parameter group in question.
Navigate and find the newly created parameter group
or find the parameter group
by using the filter functionality.
When you find the parameter group,
select the parameter group, by choosing the tick box
on the left, next to the name.
Navigate to the top right of the screen,
choose Parameter group actions and then choose Edit,
to edit the parameters.
Now you'll see all the default parameters
and their configuration.
We'll look for the
log_bin_trust_function_creators parameter,
as we'll be changing this parameter.
We can do this again, either by scrolling through
the parameters or by filtering, which would be the easiest.
After you find the parameter in the value column,
choose the dropdown menu and change the configured value
from the default value of zero to one.
Now that we've changed the required parameter,
we can save the change,
by navigating to the top right corner
and choosing Save change.
Note, the changes to the parameter group occur immediately,
regardless of the apply immediate option.
So, we've created a custom DB parameter group,
modified the value of
the log_bin_trust_function_creators parameter
to one and now need to associate the
custom parameter group with our MySQL RDS instance,
so that the parameter will be applied to
the specific instance.
To to this, we'll modify the MySQL RDS instance.
From the Amazon RDS console,
choose Instances on the left navigation pane.
Again, this displays all our running instances.
Locate your MySQL RDS instance that you want to modify
and select the instance, by choosing the tick box
on the left, next to the instance's name.
Now, that we've selected the RDS instance,
we can go ahead and choose Instance actions
and then select the Modify option.
On the modification screen, we can scroll down
to the Database option section
and then choose a dropdown menu choice DB parameter group.
Select the custom DB parameter group, created with
a modified log_bin_trust_function_creators parameter.
This parameter group will be associated
with the RDS instance.
Scroll down to the bottom
and then choose Continue.
On the final screen, in the bottom right,
choose Modify DB Instance.
After modifying the instance,
you'll see the parameter group name
associated with the instance changes immediately,
in the instance details section.
To see this, click on the MySQL RDS instance.
You'll notice a pending reboot message
next to the parameter group, after the modification
is completed on the RDS instance.
The change made won't be applied on the RDS instance,
until we perform a manual reboot,
without failover on the MySQL RDS instance.
To perform the manual reboot of the RDS instance,
so that the changes to the parameter group take effect,
we need to do the following.
Select the RDS instance again, from the Instance list.
Choose Instance actions and then select Reboot.
As mentioned, in order for the change to be applied,
we will need to reboot the instance without failover.
Click on Reboot.
The RDS instance will now reboot.
When the instance becomes available again,
we can see the parameter group now longer displays
the pending reboot status,
instead it displays in sync,
which means that the parameter group change
is applied to the instance.
We're now done with the steps
to enable functions, procedures and triggers
for our MySQL RDS instance.
Thanks for watching and happy cloud computing
from all of us here at AWS.
(upbeat music)