This page describes how to configure database flags for Cloud SQL, and lists the flags that you can set for your instance. You use database flags for many operations, including adjusting MySQL parameters, adjusting options, and configuring and tuning an instance.
In some cases, setting
one flag may require that you set another flag to fully enable the desired
functionality. For example, to enable
slow query logging,
you must set both the slow_query_log
flag to on
and the log_output
flag
to FILE
to make your logs available using the Google Cloud console Logs Explorer.
When you set, remove, or modify a flag for a database instance, the database might be restarted. The flag value is then persisted for the instance until you remove it. If the instance is the source of a replica, and the instance is restarted, the replica is also restarted to align with the current configuration of the instance.
Configure database flags
Set a database flag
Console
- In the Google Cloud console, create a new console project, or open an existing project by selecting the project name.
- Open the instance and click Edit.
- Scroll down to the Flags section.
- To set a flag that has not been set on the instance before, click Add item, choose the flag from the drop-down menu, and set its value.
- Click Save to save your changes.
- Confirm your changes under Flags on the Overview page.
gcloud
Edit the instance:
gcloud sql instances patch [INSTANCE_NAME] --database-flags=[FLAG1=VALUE1,FLAG2=VALUE2]
This command will overwrite all database flags previously set. To keep those and add new ones, include the values for all flags you want set on the instance; any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ("=").
For example, to set the general_log
,
skip_show_database
, and wait_timeout
flags, you
can use the following command:
gcloud sql instances patch [INSTANCE_NAME] \ --database-flags=general_log=on,skip_show_database=on,wait_timeout=200000
Terraform
To add database flags, use a Terraform resource.
Apply the changes
To apply your Terraform configuration in a Google Cloud project, complete the following steps:
- Launch Cloud Shell.
-
Set the Google Cloud project where you want to apply the Terraform configuration:
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
-
Create a directory and open a new file in that directory. The filename must have the
.tf
extension, for examplemain.tf
:mkdir DIRECTORY && cd DIRECTORY && nano main.tf
- Copy the sample into
main.tf
. - Review and modify the sample parameters to apply to your environment.
- Save your changes by pressing
Ctrl-x
and theny
. -
Initialize Terraform:
terraform init
-
Review the configuration and verify that the resources that Terraform is going to create or
update match your expectations:
terraform plan
Make corrections to the configuration as necessary.
-
Apply the Terraform configuration by running the following command and entering
yes
at the prompt:terraform apply
Wait until Terraform displays the "Apply complete!" message.
- Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
Delete the changes
To delete your changes, do the following:
- To disable deletion protection, in your Terraform configuration file set the
deletion_protection
argument tofalse
.deletion_protection = "false"
- Apply the updated Terraform configuration by running the following command and
entering
yes
at the prompt:terraform apply
-
Remove resources previously applied with your Terraform configuration by running the following command and entering
yes
at the prompt:terraform destroy
REST v1
To set a flag for an existing database:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [ { "name": "flag_name", "value": "flag_value" } ] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
For example, to set the general_log
flag for an existing
database use:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [ { "name": "general_log", "value": "on" } ] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
If there are existing flags configured for the database, modify the previous
command to include them. The PATCH
command overwrites the existing
flags with the ones specified in the request.
REST v1beta4
To set a flag for an existing database:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [ { "name": "flag_name", "value": "flag_value" } ] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
For example, to set the general_log
flag for an existing
database use:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [ { "name": "general_log", "value": "on" } ] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
If there are existing flags configured for the database, modify the previous
command to include them. The PATCH
command overwrites the existing
flags with the ones specified in the request.
Clear all flags to their default value
Console
- In the Google Cloud console, create a new console project, or open an existing project by selecting the project name.
- Open the instance and click Edit.
- Open the Database flags section.
- Click the X next to all of the flags shown.
- Click Save to save your changes.
gcloud
Clear all flags to their default values on an instance:
gcloud sql instances patch INSTANCE_NAME \ --clear-database-flags
You are prompted to confirm that the instance will be restarted.
REST v1
To clear all flags for an existing instance:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
To clear all flags for an existing instance:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "databaseFlags": [] } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
View current values of database flags
To view all current values of the MySQL system variables, log into your instance with themysql
client and enter the following statement:
SHOW VARIABLES;
Note that you can change the value only for supported flags (as listed below).
Determine what database flags have been set for an instance
To see what flags have been set for a Cloud SQL instance:
Console
- In the Google Cloud console, create a new console project, or open an existing project by selecting the project name.
- Select the instance to open its Instance Overview page.
The database flags that have been set are listed under the Database flags section.
gcloud
Get the instance state:
gcloud sql instances describe INSTANCE_NAME
In the output, database flags are listed under the settings
as
the collection databaseFlags
. For more information
about the representation of the flags in the output, see
Instances Resource Representation.
REST v1
To list flags configured for an instance:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
GET https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
In the output, look for the databaseFlags
field.
REST v1beta4
To list flags configured for an instance:
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
GET https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
In the output, look for the databaseFlags
field.
Flags managed by Cloud SQL
Cloud SQL adjusts certain system flags depending on the instance machine type. You cannot adjust these values.
- innodb_buffer_pool_instances
-
- 1 for db-f1-micro and db-g1-small.
- 1 if RAM < 7.5 GB.
- 2 if 7.5 GB <= RAM < 13 GB.
- 4 if 13 GB <= RAM < 26 GB.
- 8 if RAM >= 26 GB.
Supported flags
The flags supported in Cloud SQL are the most commonly requested flags for MySQL. Flags not mentioned below are not supported.
For a given flag, Cloud SQL might support a different value or range from the corresponding MySQL parameter or option.
The flags apply to all versions of MySQL supported by Cloud SQL except where noted.
A | B | C | D | E | F | G | I | L | M | N | O | P | Q | R | S | T | U | W
Cloud SQL Flag | Type Acceptable Values and Notes |
Restart Required? |
---|---|---|
activate_all_roles_on_login |
boolean on | off
default: off
|
No |
auto_increment_increment | integer 1 ... 65535 |
No |
auto_increment_offset | integer 1 ... 65535 |
No |
binlog_cache_size | integer 4096 ... 9223372036854775807 |
No |
binlog_expire_log_seconds | integer 0 or 86400 (1 day) ... 4294967295 (max value) Default is 2592000, which equals 30 days. See the Tips section for more information about this flag. |
No |
binlog_group_commit_sync_delay | 0 ... 1000000
Default is 0. |
No |
binlog_group_commit_sync_no_delay_count | 0 ... 1000000
Default is 0. |
No |
binlog_row_image | enumeration full (default), minimal , or noblob |
No |
binlog_stmt_cache_size | 4096 ... 9223372036854775807 |
No |
binlog_transaction_dependency_history_size | integer
For information about how to use this flag and its acceptable values, see Configuring parallel replication. |
No |
binlog_transaction_dependency_tracking | enumeration
For information about how to use this flag and its acceptable values, see Configuring parallel replication. |
No |
character_set_server | string utf8 or utf8mb4 (recommended) |
Yes |
cloudsql_allow_analyze_table | boolean on | off
default: off
|
No |
cloudsql_mysql_audit_data_masking_cmds |
string "" , dql , dml , ddl , dcl , show , call , create_udf , drop_function , create_procedure , create_function , drop_procedure , alter_procedure , alter_function , create_trigger , drop_trigger , create_event , alter_event , drop_event , create_db , drop_db , alter_db , create_user , drop_user , rename_user , alter_user , create_table , create_index , alter_table , drop_table , drop_index , create_view , drop_view , rename_table , update , insert , insert_select , delete , truncate , replace , replace_select , delete_multi , update_multi , load , select , call_procedure , connect ,
disconnect , grant , revoke , revoke_all , show_triggers , show_create_proc , show_create_func , show_procedure_code , show_function_code , show_create_event , show_events , show_create_trigger , show_grants , show_binlog_events , show_relaylog_events
default: create_user , alter_user ,
grant , and update
|
No |
cloudsql_mysql_audit_data_masking_regex |
string max_string_length: 2048
default: Click here. |
No |
cloudsql_mysql_audit_log_write_period |
integer 0...5000 milliseconds
default: 500 milliseconds
|
No |
cloudsql_mysql_audit_max_query_length |
integer -1...1073741824
default: -1
|
No |
default_password_lifetime | integer 0...65535
default: 0
|
No |
default_time_zone | string There are two ways to specify timezones: as timezone offsets and timezone names. For example, +00:00 is the
timezone offset for London (which is in the UTC timezone), and
Europe/London is its timezone name.
You use values to specify timezone offsets, from When using timezone names, automatic adjustment to daylight saving time is supported. When using timezone offsets, it isn't supported. See a list of timezone names that Cloud SQL for MySQL supports. You must update this flag manually, on the primary instance and on all read replicas, to account for it. To set the timezone without causing a restart of the Cloud SQL instance, use the |
Yes |
disconnect_on_expired_password | boolean on | off
default: on
|
Yes |
eq_range_index_dive_limit | integer 0 ... 2147483647 |
No |
event_scheduler | boolean on | off
If you are using the Event Scheduler, configure your instance with an activation policy of ALWAYS to ensure that scheduled events run. |
No |
expire_log_days | integer 0 ... 99 Default is 0, which means no automatic removal. See the Tips section for more information about this flag. |
No |
explicit_defaults_for_timestamp | boolean on | off |
Yes |
ft_max_word_len | integer 10 ... 252 |
Yes |
ft_min_word_len | integer 1 ... 16 |
Yes |
ft_query_expansion_limit | integer 0 ... 1000 |
Yes |
general_log | boolean on | off
See the Tips section for more information about general logs. |
No |
generated_random_password_length | integer 5-255
default: 20
|
No |
group_concat_max_len | integer 4 ... 17179869184 |
No |
init_connect | string |
No |
innodb_adaptive_hash_index | boolean on | off |
No |
innodb_adaptive_hash_index_parts | integer 1 ... 512 |
Yes |
innodb_adaptive_max_sleep_delay | integer 0 ... 1000000 |
No |
innodb_autoinc_lock_mode | integer 0 ... 2 |
Yes |
innodb_buffer_pool_size | integer
Setting this flag for MySQL 5.6 requires a restart. See the Tips section for more information about this flag. | No |
innodb_change_buffer_max_size | integer 0 ... 50 |
No |
innodb_concurrency_tickets | integer 1 ... 4294967295 |
No |
innodb_file_per_table | boolean on | off
See the Tips section for more information about this flag. |
No |
innodb_fill_factor | integer 10 ... 100 |
No |
innodb_flush_log_at_trx_commit | integer 1, 2 Default: 1 .
See the Tips section for more information about this flag. |
No |
innodb_ft_cache_size | integer 1600000 ... 80000000 |
Yes |
innodb_ft_enable_diag_print | boolean on | off |
No |
innodb_ft_enable_stopword | boolean on | off |
No |
innodb_ft_max_token_size | integer 10 ... 252
Supported only in MySQL 5.6 and later versions. |
Yes |
innodb_ft_min_token_size | integer 0 ... 16
Supported only in MySQL 5.6 and later versions. |
Yes |
innodb_ft_num_word_optimize | integer 1000 ... 10000 |
No |
innodb_ft_result_cache_limit | integer 1000000 ... 4294967295 |
No |
innodb_ft_server_stopword_table | string
Supported only in MySQL 5.6 and later versions. |
No |
innodb_ft_sort_pll_degree | integer 1 ... 32 |
Yes |
innodb_ft_total_cache_size | integer 32000000 ... 1600000000 |
Yes |
innodb_ft_user_stopword_table | string |
No |
innodb_large_prefix | boolean on | off
Supported only in MySQL 5.6. |
No |
innodb_lock_wait_timeout | integer 1 ... 1073741824 |
No |
innodb_log_buffer_size | integer 262144 ... 4294967295 |
Yes |
innodb_log_file_size | integer MySQL 5.6: 1048576 ... 274877906944 MySQL 5.7: 4194304 ... 274877906944
|
Yes |
innodb_lru_scan_depth | integer 100 ... 9223372036854775807 |
No |
innodb_old_blocks_pct | integer 5 ... 95 |
No |
innodb_old_blocks_time | integer 0 ... 4294967295 |
No |
innodb_online_alter_log_max_size | integer 65536 ... 9223372036854775807 |
No |
innodb_optimize_fulltext_only | boolean on | off |
No |
innodb_print_all_deadlocks | boolean on | off |
No |
innodb_purge_threads | 1 ... 32
Supported in MySQL 5.6, 5.7, 8.0 Default is 1. |
Yes |
innodb_random_read_ahead | boolean on | off |
No |
innodb_read_ahead_threshold | integer 0 ... 64 |
No |
innodb_read_io_threads | integer 1 ... 64
|
Yes |
innodb_replication_delay | integer 0 ... 4294967295 |
No |
innodb_rollback_on_timeout | boolean on | off |
Yes |
innodb_rollback_segments | integer 1 ... 128 |
No |
innodb_sort_buffer_size | integer 65536 ... 67108864 |
Yes |
innodb_stats_auto_recalc | boolean on | off |
No |
innodb_stats_method | enumeration nulls_equal | nulls_unequal | nulls_ignored |
No |
innodb_stats_on_metadata | boolean on | off |
No |
innodb_stats_persistent | boolean on | off |
No |
innodb_stats_persistent_sample_pages | integer 1 ... 9223372036854775807 |
No |
innodb_stats_sample_pages | integer 1 ... 9223372036854775807 |
No |
innodb_stats_transient_sample_pages | integer 1 ... 9223372036854775807 |
No |
innodb_status_output | boolean on | off |
No |
innodb_status_output_locks | boolean on | off |
No |
innodb_strict_mode | boolean on | off |
No |
innodb_sync_array_size | 1 ... 1024
Supported in MySQL 5.6, 5.7, 8.0 Default is 1. |
Yes |
innodb_thread_concurrency | integer 0 ... 1000 |
No |
innodb_thread_sleep_delay | integer 0 ... 1000000 |
No |
innodb_write_io_threads | integer 1 ... 64 |
Yes |
interactive_timeout | integer 1 ... 31536000 |
No |
internal_tmp_mem_storage_engine | enumeration MEMORY , TempTable This flag is used for MySQL 8.0 only. |
No |
join_buffer_size | integer 128 ... 9223372036854775807 |
No |
local_infile | boolean on | off |
No |
lock_wait_timeout | integer 1 ... 31536000 |
No |
log_bin_trust_function_creators | boolean on | off |
No |
log_output | set FILE | TABLE | NONE |
No |
log_queries_not_using_indexes | boolean on | off |
No |
log_throttle_queries_not_using_indexes | integer 0 ... 9223372036854775807 |
No |
long_query_time | float 0 ... 30000000
Cloud SQL provides the ability to set this flag to less than 1 if needed. If the |
No |
lower_case_table_names | integer 0 or 1
In most cases, use the default value of 0, which makes table and
database names case sensitive. If you need to change the value of this
flag, make sure that you understand how the change affects your
existing tables and databases. |
Yes |
mandatory_roles | string role name
default: empty string
|
No |
max_allowed_packet | integer 16384 ... 1073741824
This value must be a multiple of 1024, if sql_mode=TRADITIONAL or sql_mode=STRICT_ALL_TABLES. |
No |
max_binlog_size | integer 4096 ... 1073741824 |
No |
max_connections | integer 14 ... 262143
|
No |
max_digest_length | integer 0 ... 1048576 |
Yes |
max_execution_time | integer 0 ... 9223372036854775807 |
No |
max_heap_table_size | integer 16384 ... 67108864
See the Tips section for more information about this flag. |
No |
max_join_size | integer 16 ... 9223372036854775807 |
No |
max_length_for_sort_data | integer 4 ... 8388608 |
No |
max_points_in_geometry | integer 3 ... 1048576 |
No |
max_prepared_stmt_count | integer 0 ... 1048576 |
No |
max_seeks_for_key | integer 1 ... 9223372036854775807 |
No |
max_sort_length | integer 4 ... 8388608 |
No |
max_sp_recursion_depth | integer 0 ... 255 |
No |
max_user_connections | integer 0 ... 4294967295 |
No |
max_write_lock_count | integer 1 ... 9223372036854775807 |
No |
net_read_timeout | integer 30 ... 4294967295 |
No |
net_retry_count | integer 10 ... 4294967295 |
No |
net_write_timeout | integer 60 ... 4294967295 |
No |
optimizer_prune_level | integer 0 ... 1 |
No |
optimizer_search_depth | integer 0 ... 62 |
No |
optimizer_trace_max_mem_size | integer 0 ... 9223372036854775807 |
No |
optimizer_trace_offset | integer -9223372036854775808 ... 9223372036854775807 |
No |
parser_max_mem_size | integer 10000000 ... 9223372036854775807 |
No |
password_history | integer 0-4294967295
default: 0
|
No |
password_require_current | boolean on | off
default: off
|
No |
password_reuse_interval | integer 0-4294967295
default: 0
|
No |
performance_schema | boolean on | off
default: default: See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_accounts_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_digests_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_events_stages_history_long_size | integer -1 ... 1048576 |
Yes |
performance_schema_events_stages_history_size | integer -1 ... 1024
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_events_statements_history_long_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_events_statements_history_size | integer -1 ... 1024
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_events_transactions_history_long_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_events_transactions_history_size | integer -1 ... 1024
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_events_waits_history_long_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_events_waits_history_size | integer -1 ... 1024
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_hosts_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_cond_classes | integer 0 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_cond_instances | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_digest_length | integer 0 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_file_classes | integer 0 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_file_handles | integer 0 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_file_instances | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_index_stat | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_memory_classes | integer 0 ... 1024
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_metadata_locks | integer -1 ... 104857600
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_mutex_classes | integer 0 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_mutex_instances | integer -1 ... 104857600
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_prepared_statements_instances | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_program_instances | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_rwlock_classes | integer 0 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_rwlock_instances | integer -1 ... 104857600
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_socket_classes | integer 0 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_socket_instances | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_sql_text_length | integer 0 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_stage_classes | integer 0 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_statement_classes | integer 0 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_statement_stack | integer 1 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_table_handles | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_table_instances | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_table_lock_stat | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_thread_classes | integer 0 ... 256
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_max_thread_instances | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_session_connect_attrs_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_setup_actors_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_setup_objects_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
performance_schema_users_size | integer -1 ... 1048576
See Tips section for more information about performance_schema flags. |
Yes |
query_alloc_block_size | integer 1024 ... 4294967295 |
No |
query_cache_limit | integer 0 ... 223338299392 This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. |
No |
query_cache_min_res_unit | integer 0 ... 9223372036854775807 This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. |
No |
query_cache_size | integer 0 ... 223338299392 This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. |
No |
query_cache_type | enumeration 0 ... 2 This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. |
Yes |
query_cache_wlock_invalidate | boolean on | off This flag is not available for MySQL 8.0 as the query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. |
No |
query_prealloc_size | integer 8192 ... 9223372036854775807 |
No |
range_alloc_block_size | integer 4096 ... 4294967295 |
No |
range_optimizer_max_mem_size | integer 0 ... 9223372036854775807 |
No |
read_buffer_size | integer 8192 ... 2147483647 |
No |
read_only | boolean on | off
Has no effect for replicas. |
No |
read_rnd_buffer_size | integer 1 ... 2147483647 |
No |
show_compatibility_56 | boolean on | off
Supported only in MySQL 5.7. |
No |
skip_show_database | flag on | off |
Yes |
slave_parallel_type | enumeration
For information about how to use this flag and its acceptable values, see Configuring parallel replication. |
No |
slave_parallel_workers | integer
For information about how to use this flag and its acceptable values, see Configuring parallel replication. |
No |
slave_preserve_commit_order | boolean
For information about how to use this flag and its acceptable values, see Configuring parallel replication. |
No |
slave_pending_jobs_size_max | integer
For information about how to use this flag and its acceptable values, see Configuring parallel replication. |
No |
slow_query_log | boolean on | off See the Tips section for more information on slow query logs. |
No |
sort_buffer_size | integer 32768 ... 9223372036854775807 |
No |
sql_mode | string
See the Server SQL Modes
in the MySQL documentation for allowed values, including combined modes,
such as Cloud SQL for MySQL doesn't support empty values for the
|
No |
stored_program_cache | integer 16 ... 524288 |
No |
sync_binlog | integer 0 ... 4294967295
Supported only on read replicas and external server replicas in MySQL 5.7 and 8.0. The default setting of 1 enables the synchronization of the binary log to disk before transactions are committed. This flag is not supported on primary instances or legacy HA failover replicas. If a replica using this flag is promoted, the flag is removed. |
No |
table_definition_cache | integer 400 ... 524288 |
No |
table_open_cache | integer 1 ... 524288 |
No |
table_open_cache_instances | integer 1 ... 64 |
Yes |
thread_cache_size | integer 0 ... 16384 |
No |
thread_stack | integer 131072 ... 9223372036854775807 |
Yes |
tmp_table_size | integer 1024 ... 67108864
See the Tips section for more information about this flag. |
No |
transaction_alloc_block_size | integer 1024 ... 131072 |
No |
transaction_isolation | enumeration READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE |
Yes |
transaction_prealloc_size | integer 1024 ... 131072 |
No |
transaction_write_set_extraction | enumeration
For information about how to use this flag and its acceptable values, see Configuring parallel replication. |
No |
updatable_views_with_limit | integer 0 ... 1 |
No |
wait_timeout | integer 1 ... 31536000 |
No |
Timezone names
Here's the list of timezone names that Cloud SQL for MySQL supports. Timezone names are case insensitive. You can supply the timezone name in any case.
"Africa/Cairo"
"Africa/Casablanca"
"Africa/Harare"
"Africa/Monrovia"
"Africa/Nairobi"
"Africa/Tripoli"
"Africa/Windhoek"
"America/Araguaina"
"America/Asuncion"
"America/Bogota"
"America/Buenos_Aires"
"America/Caracas"
"America/Chihuahua"
"America/Cuiaba"
"America/Denver"
"America/Fortaleza"
"America/Guatemala"
"America/Halifax"
"America/Manaus"
"America/Matamoros"
"America/Monterrey"
"America/Montevideo"
"America/Phoenix"
"America/Santiago"
"America/Tijuana"
"Asia/Amman"
"Asia/Ashgabat"
"Asia/Baghdad"
"Asia/Baku"
"Asia/Bangkok"
"Asia/Beirut"
"Asia/Calcutta"
"Asia/Damascus"
"Asia/Dhaka"
"Asia/Irkutsk"
"Asia/Jerusalem"
"Asia/Kabul"
"Asia/Karachi"
"Asia/Kathmandu"
"Asia/Krasnoyarsk"
"Asia/Magadan"
"Asia/Muscat"
"Asia/Novosibirsk"
"Asia/Riyadh"
"Asia/Seoul"
"Asia/Shanghai"
"Asia/Singapore"
"Asia/Taipei"
"Asia/Tehran"
"Asia/Tokyo"
"Asia/Ulaanbaatar"
"Asia/Vladivostok"
"Asia/Yakutsk"
"Asia/Yerevan"
"Atlantic/Azores"
"Australia/Adelaide"
"Australia/Brisbane"
"Australia/Darwin"
"Australia/Hobart"
"Australia/Perth"
"Australia/Sydney"
"Brazil/East"
"Canada/Newfoundland"
"Canada/Saskatchewan"
"Canada/Yukon"
"Europe/Amsterdam"
"Europe/Athens"
"Europe/Dublin"
"Europe/Helsinki"
"Europe/Istanbul"
"Europe/Kaliningrad"
"Europe/Moscow"
"Europe/Paris"
"Europe/Prague"
"Europe/Sarajevo"
"Pacific/Auckland"
"Pacific/Fiji"
"Pacific/Guam"
"Pacific/Honolulu"
"Pacific/Samoa"
"US/Alaska"
"US/Central"
"US/Eastern"
"US/East-Indiana"
"US/Pacific"
"UTC"
Tips for working with flags
- general_log, slow_query_log
-
To make your
general
orslow query
logs available, enable the corresponding flag and set thelog_output
flag toFILE
. This makes the log output available using the Logs Viewer in the Google Cloud console. Note that Google Cloud's operations suite logging charges apply. To minimize instance storage cost,general
andslow query
logs on the instance disk are rotated when the log file is older than 24 hours (and no changes have been made within that duration) or greater than 100MB in size. Old log files are automatically deleted after the rotation.If the
log_output
is set toNONE
, you can't access the logs. If you setlog_output
toTABLE
, the log output is placed in a table in the mysql system database. It might consume a considerable amount of disk space. If this table becomes large, it can affect instance restart time or cause the instance to lose its SLA coverage. For this reason, theTABLE
option is not recommended. In addition, the log content isn't available in Logs Explorer and it isn't rotated. If needed, you can truncate your log tables by using the API. For more information, see the instances.truncateLog reference page.
- expire_logs_days, binlog_expire_logs_seconds
- If you enable point-in-time recovery, the expiration period of your binary logs is determined by the lesser of your transaction log retention period and the values of these flags. You can use these flags to manage how long binary logs are stored on your replicas. For more information, see the transaction log retention page.
- innodb_buffer_pool_size
The value of the innodb_buffer_pool_size is the size in bytes of the buffer pool. You cannot enable this flag on instances that have fewer than 3,840 MiB of RAM.
This flag is not configurable for shared-core machine types (f1_micro and g1_small). Changing this flag on MySQL 5.6 requires a restart.
In Cloud SQL, the default, minimum allowable, and maximum allowable values of the innodb_buffer_pool_size flag depend on the instance's memory. These values can be roughly calculated as a percentage of the instance's RAM. By default, the value of this flag is typically set close to the maximum allowable value. The maximum allowable allocation percentage increases with instance size. The minimum allowable value is usually about 20% of the instance's RAM.
Approximate values for this flag:
Instance RAM Range Min % Default % Max % 0 - 4.0GB of RAM ~34% 4.0GB - 7.5GB ~20% ~34% ~34% 7.5GB - 12GB ~20% ~52% ~52% 12GB - 24GB ~20% ~67% ~67% 24GB and above ~20% ~72% ~72% Your exact values may vary. To calculate the current value for your instance, you can run the query:
show global variables like 'innodb_buffer_pool_size'
For reference, the minimum allowable, default, and maximum allowable values are provided for the machine types below.
Machine type Instance RAM (GB) Min (GB)
(% of total)Default (GB)
(% of total)Max (GB)
(% of total)db-f1-micro 0.6 - 0.053 - db-g1-small 1.7 - 0.625 - db-custom-1-3840 3.75 0.875
(23%)1.375
(37%)1.375
(37%)db-custom-2-7680 7.5 1.5
(20%)4
(53%)4
(53%)db-custom-4-15360 15 3
(20%)10.5
(70%)10.5
(70%)db-custom-8-30720 30 6
(20%)22
(73%)22
(73%)db-custom-16-61440 60 12
(20%)44
(73%)44
(73%)db-custom-32-122880 120 24
(20%)87
(73%)87
(73%)db-custom-64-245760 240 48
(20%)173
(72%)173
(72%)db-custom-96-368640 360 72
(20%)260
(72%)260
(72%)db-custom-2-13312 13 3
(23%)9
(69%)9
(69%)db-custom-4-26624 26 6
(23%)19
(73%)19
(73%)db-custom-8-53248 52 11
(21%)38
(73%)38
(73%)db-custom-16-106496 104 21
(20%)75
(72%)75
(72%)db-custom-32-212992 208 42
(20%)150
(72%)150
(72%)db-custom-64-425984 416 84
(20%)300
(72%)300
(72%)db-custom-96-638976 624 125
(20%)450
(72%)450
(72%)
For all MySQL versions 5.6 and higher, the default value is ON
.
sync_binlog
to a
value other than 1.
You can only enable this flag for read replicas and external server replicas. For external server replicas, this flag must be set to the default value of 1 for full ACID compliance. You cannot enable this flag on primary instances or failover replicas.
Exhausting the available instance memory can occur when you set
tmp_table_size
and max_heap_table_size
too high for
the number of concurrent queries the instance processes. Exhausting the memory
results in an instance crash and restart.
For more information about working with these flags, see How MySQL Uses Internal Temporary Tables and The MEMORY Storage Engine.
You can't enable this flag on instances that have less than 15,360 MiB of RAM. If you enable this flag, you can't change your machine type to a size that does not support the flag; you must first disable this flag.
System flags changed in Cloud SQL
All other database system flags that are not listed in the supported flags section are called managed flags. For certain managed flags, Cloud SQL sets the flag to a value other than the default setting to ensure Cloud SQL instances run reliably. You can't change the values on these system flags.
Managed flags with a non-default setting are listed below.
Variable Name | Setting in Cloud SQL. | Notes | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
binlog_format | ROW | Differs in MySQL 5.6 only | ||||||||||||||
binlog_error_action | ABORT_SERVER | Differs in MySQL 5.6 only | ||||||||||||||
innodb_doublewrite_pages | 64 | Applies to MySQL 8.0.26 and above | ||||||||||||||
innodb_file_format | Barracuda | Differs in MySQL 5.6 only | ||||||||||||||
innodb_flush_method | O_DIRECT | |||||||||||||||
innodb_flush_neighbors | 0 (in MySQL 5.6) and 2 (in MySQL 5.7 and 8.0) | |||||||||||||||
innodb_io_capacity | 5000 | |||||||||||||||
innodb_buffer_pool_instances |
|
|||||||||||||||
memlock | true | |||||||||||||||
skip_name_resolve | ON | |||||||||||||||
relay_log_info_repository | TABLE | |||||||||||||||
relay_log_recovery | ON | |||||||||||||||
slave_net_timeout | 30 | |||||||||||||||
master_info_repository | TABLE | |||||||||||||||
rpl_semi_sync_master_enabled | 1 | |||||||||||||||
rpl_semi_sync_master_timeout | 3000 | |||||||||||||||
innodb_page_cleaners | 8 | Differs in MySQL 5.7, 8.0 only | ||||||||||||||
admin_address | 127.0.0.1 | Differs in MySQL 8.0 only | ||||||||||||||
create_admin_listener_thread | ON | |||||||||||||||
port-open-timeout | 120 | Differs in MySQL 8.0 only | ||||||||||||||
innodb_io_capacity_max | 10000 |
Troubleshooting
Issue | Troubleshooting |
---|---|
After enabling a flag the instance loops between panicking and crashing. | Contact customer support to
request flag removal followed by a hard drain . This forces the
instance t restart on a different host with a fresh configuration without
the undesired flag or setting.
|
You see the error message Bad syntax for dict arg when
trying to set a flag.
| Complex parameter values, such as comma-separated lists, require special treatment when used with gcloud commands. |
What's next
- Learn more about MySQL system variables.
- Learn more about Operational Guidelines.