To apply the settings of MySQL installed on a DB instance, RDS for MySQL provides the parameter group feature. A parameter group is a set of parameters for which you can set MySQL. When the service is enabled, the default parameter group is provided for each DB engine version. The default parameter group is provided by default.{DB Engine Version Name} and is configured with the recommended default parameter values for each version. Default parameter group can be modified and deleted the same
as other parameter groups.
You can create parameter groups in the parameter console when needed. Parameter groups are created by DB engine version and can be named and there are restrictions as follows.
When you create a parameter group, the parameter is always generated by default. To create based on an existing parameter group, you must create a parameter group using the copy parameter feature.
You can create a new parameter group based on an existing parameter group. The copied new parameter group consists of the parameter values of the original parameter group. There is no association between the original parameter group and the copied parameter group, and changing or deleting the original parameter group does not have any effect on the copied parameter group.
When you group parameters, you change the values of all parameters to the default values for the DB engine version.
When creating or modifying a DB instance, you can select the parameter groups to apply to the DB instance. One parameter group is applied to one DB instance, and one parameter group can be applied to multiple DB instances. If a parameter in a parameter group is changed, the change does not immediately apply to the DB instance. If an associated DB instance exists, the parameter group changes to Need to Apply. On the DB Instances list screen, select the DB instance associated with the parameter
group, and then click Apply Parameter Group Changes to reflect the changes in the parameters to the DB instance. When changes to the parameter group are applied to all associated DB instances, the parameter group changes to Applied.
[Caution] If the parameters that require restart have changed, the DB instance will be restarted during applying changes.
After selecting two different parameter groups in the console, click the Compare button to see what parameters are different. You can compare parameter groups for different DB engine versions as well as the same DB engine version.
You are free to delete parameter groups except those already applied to the DB instances. To delete a parameter group already applied to a DB instance, you must first change the parameter group of all connected DB instances before you delete it.
The parameter contains the following information.
| Item | Description |
|---|---|
| Group | Option group of option file (my.cnf). |
| Name | Option name of option file (my.cnf). When the option name and system variables are different (- Variable: Displayed additionally in the format (- Variable: System Variables). |
| Value | Value to be applied to parameters. |
| Allowed values | Range of values applicable to parameters. |
| Application Type | Either Static or Dynamic.For Static, the DB instance must be restarted to apply the parameter changes.For Dynamic, the parameters are applied immediately without restarting the DB instance. |
| Data Format | Format of the parameter value. |
| Use Formula | Whether the formula is available to use. |
Certain parameters may be better represented by formulas using values associated with DB instances rather than using fixed values. To support this, you can use predefined variables, formulas, and functions for NUMERIC, INTEGER data format.
(), +, -, *, /.INTEGER, decimals are discarded.NUMERIC, it is rounded to the ninth decimal place.max(a, b, ...): Returns the largest of several values.min(a, b, ...): Returns the smallest of several values.sum([a, b, ...]): Returns the sum of multiple values.ramSizeByte: Indicates the byte value of the memory size of the current DB instance type.vCPU: Indicates the number of virtual CPU cores for the current DB instance type.dbPort: Indicates the DB port of the current DB instance.serverId: Indicates the server ID assigned to the current DB instance.readOnly: Indicates 1 or 0 when the current DB instance is read-only.Below example is the default value of innodb_buffer_pool_size parameter, and it indicates setting to 6/10 of the memory size of the DB instance type.
ramSizeByte * 6 / 10
You can change the parameters by selecting a parameter group from the console and pressing the Edit Parameters button. For parameters that cannot be changed, the value appears in plain text, and for parameters that can be changed, the INPUT that can be changed appears. When you press the Preview Changes button on the edit screen, a separate pop-up screen will be displayed to view the changed parameters, press the Reset button to return to the time before the change. All changes made
in edit mode are reflected in the parameter group by pressing the Save Changes button. For information about reflecting DB instances of changed parameter groups, refer to the Apply Parameter Groups.
In GTID mode, the following constraints are applied when doing enforce_gtid_consistency = ON. Note ll: https://dev.mysql.com/doc/refman/8.4/en/replication-gtids-restrictions.html
Queries that fall under the following limitations of replication using GTID will not be allowed (an error will occur):
e.g.
create table tbl_backup as select * from tbl_ori;
should be changed as below:
create table tbl_backup like tbl_ori; insert tbl_backup select * from tbl_ori;| Value | Work from Source | Work from Replica |
|---|---|---|
| OFF | GTID not applied | GTID processing impossible |
| OFF_PERMISSIVE | GTID processing also possible | GTID processing also possible |
| ON_PERMISSIVE | GTID applied | GTID applied |
| ON | GTID only processed | GTID only processed |
To apply GTID smoothly, gtid_mode (gtid application stage) and enforce_gtid_consistency (query application restriction stage) must be applied in the following order through parameter groups: - Note: https://dev.mysql.com/doc/refman/8.4/en/replication-mode-change-online-enable-gtids.html
| Stage | Target | Parameter Setting | Action | Note |
|---|---|---|---|---|
| 1 | Every DB instance | enforce_gtid_consistency = WARN | Check for warnings in problematic SQL when applying GTID. | Change the parameter group and apply. Check for potentially problematic SQL in the warnings and correct it in the APP. Continue until no more warnings are generated. |
| 2 | Every DB instance | enforce_gtid_consistency = ON | Errors occur in problematic SQL. | Change the parameter group and apply. Problematic queries can no longer be executed with GTID. |
| 3 | Every DB instance | gtid_mode = OFF_PERMISSIVE | Prepare Replicas to Handle GTIDs | Change the parameter group and apply. All replicas must first become OFF_PERMISSIVE to avoid issues. |
| 4 | Every DB instance | gtid_mode = ON_PERMISSIVE | Source generates GTID | Change the parameter group and apply. |
| 5 | Every DB instance | - | Check for Remaining ANONYMOUS Transactions | SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';모All servers must return a result of 0 at least once. |
| 6 | Every DB instance | gtid_mode = ON | All transactions use only GTID | Change the parameter group and apply. |
[Warning] * After changing the parameter group at each step, you must always perform [Apply parameter group changes] (parameter-group/#apply). * Changing the gtid_mode and enforce_gtid_consistency parameters may require a DB instance restart. * Disabling GTIDs is done in the reverse order of applying them.