MariaDB feature support on Amazon RDS

Topics

• Supported storage engines for MariaDB on Amazon RDS

• Cache warming for MariaDB on Amazon RDS

• MariaDB features not supported by Amazon RDS

• Crypographic library – RDS for MariaDB replaced OpenSSL with Amazon Libcrypto (Amazon-LC), which is FIPS 140-3 certified.

• Simple Password Check plugin – You can use the MariaDB Simple Password Check Plugin to check whether a password contains at least a specific number of characters of a specific type. For more information, see Using the password validation plugins for RDS for MariaDB.

• Cracklib Password Check plugin – You can use the MariaDB Cracklib Password Check Plugin to check the strength of new passwords. For more information, see Using the password validation plugins for RDS for MariaDB.

• InnoDB enhancements – These enhancements include the following items:

  • The change buffer was removed. For more information, see InnoDB Change Buffering.

  • InnoDB Defragmentation was removed. For more information, see InnoDB Defragmentation.

• New privilege – The admin user now also has the SHOW CREATE ROUTINE privilege. This privilege permits the grantee to view the SHOW CREATE definition statement of a routine that's owned by another user. For more information, see Database Privileges.

• Replication improvement – MariaDB version 11.4 DB instances support binlog indexing. You can create a GTID index for each binlog file. These indexes improve the performance of replication by reducing the time it takes to locate a GTID. For more information, see Binlog Indexing.

• Deprecated or removed parameters – The following parameters have been deprecated or removed for MariaDB version 11.4 DB instances:

  • engine_condition_pushdown is removed from optimizer_switch

  • innodb_change_buffer_max_size

  • innodb_defragment

  • TLSv1.0 and TLSv1.1 are removed from tls_version

• New default values for a parameter – The default value of the innodb_undo_tablespaces parameter changed from 0 to 3.

• New valid values for parameters – The following parameters have new valid values for MariaDB version 11.4 DB instances:

  • The valid values for the binlog_row_image parameter now include FULL_NODUP.

  • The valid values for the OLD_MODE parameter now include NO_NULL_COLLATION_IDS.

• New parameters – The following parameters are new for MariaDB version 11.4 DB instances:

  • The transaction_isolation parameter replaces the tx_isolation parameter.

  • The transaction_read_only parameter replaces the tx_read_only parameter.

  • The block_encryption_mode parameter defines the default block encryption mode for the AES_ENCRYPT() and AES_DECRYPT() functions.

  • The character_set_collations defines overrides for character set default collations.

  • The binlog_gtid_index, binlog_gtid_index_page_size, and binlog_gtid_index_span_min define the properties of the binlog GTID index. For more information, see Binlog Indexing.

• Password Reuse Check plugin – You can use the MariaDB Password Reuse Check plugin to prevent users from reusing passwords and to set the retention period of passwords. For more information, see Password Reuse Check Plugin.

• GRANT TO PUBLIC authorization – You can grant privileges to all users who have access to your server. For more information, see GRANT TO PUBLIC.

• Separation of SUPER and READ ONLY ADMIN privileges – You can remove READ ONLY ADMIN privileges from all users, even users that previously had SUPER privileges.

• Security – You can now set option --ssl as the default for your MariaDB client. MariaDB no longer silently disables SSL if the configuration is incorrect.

• SQL commands and functions – You can now use the SHOW ANALYZE FORMAT=JSON command and the functions ROW_NUMBER, SFORMAT, and RANDOM_BYTES. SFORMAT allows string formatting and is enabled by default. You can convert partition to table and table to partition in a single command. There are also several improvements around JSON_*() functions. DES_ENCRYPT and DES_DECRYPT functions were deprecated for version 10.10 and higher. For more information, see SFORMAT.

• InnoDB enhancements – These enhancements include the following items:

  • Performance improvements in the redo log to reduce write amplification and to improve concurrency.

  • The ability for you to change the undo tablespace without reinitializing the data directory. This enhancement reduces control plane overhead. It requires restarting but it doesn't require reinitialization after changing undo tablespace.

  • Support for CHECK TABLE … EXTENDED and for descending indexes internally.

  • Improvements to bulk insert.

• Binlog changes – These changes include the following items:

  • Logging ALTER in two phases to decrease replication latency. The binlog_alter_two_phase parameter is disabled by default, but can be enabled through parameter groups.

  • Logging explicit_defaults_for_timestamp.

  • No longer logging INCIDENT_EVENT if the transaction can be safely rolled back.

• Replication improvements – MariaDB version 10.11 DB instances use GTID replication by default if the master supports it. Also, Seconds_Behind_Master is more precise.

• Clients – You can use new command-line options for mysqlbinglog and mariadb-dump. You can use mariadb-dump to dump and restore historical data.

• System versioning – You can modify history. MariaDB automatically creates new partitions.

• Atomic DDL – CREATE OR REPLACE is now atomic. Either the statement succeeds or it's completely reversed.

• Redo log write – Redo log writes asynchronously.

• Stored functions – Stored functions now support the same IN, OUT, and INOUT parameters as in stored procedures.

• Deprecated or removed parameters – The following parameters have been deprecated or removed for MariaDB version 10.11 DB instances:

  • innodb_change_buffering

  • innodb_disallow_writes

  • innodb_log_write_ahead_size

  • innodb_prefix_index_cluster_optimization

  • keep_files_on_create

  • old

• Dynamic parameters – The following parameters are now dynamic for MariaDB version 10.11 DB instances:

  • innodb_log_file_size

  • innodb_write_io_threads

  • innodb_read_io_threads

• New default values for parameters – The following parameters have new default values for MariaDB version 10.11 DB instances:

  • The default value of the explicit_defaults_for_timestamp parameter changed from OFF to ON.

  • The default value of the optimizer_prune_level parameter changed from 1 to 2.

• New valid values for parameters – The following parameters have new valid values for MariaDB version 10.11 DB instances:

  • The valid values for the old parameter were merged into those for the old_mode parameter.

  • The valid values for the histogram_type parameter now include JSON_HB.

  • The valid value range for the innodb_log_buffer_size parameter is now 262144 to 4294967295 (256KB to 4096MB).

  • The valid value range for the innodb_log_file_size parameter is now 4194304 to 512GB (4MB to 512GB).

  • The valid values for the optimizer_prune_level parameter now include 2.

• New parameters – The following parameters are new for MariaDB version 10.11 DB instances:

  • The binlog_alter_two_phase parameter can improve replication performance.

  • The log_slow_min_examined_row_limit parameter can improve performance.

  • The log_slow_query parameter and the log_slow_query_file parameter are aliases for slow_query_log and slow_query_log_file, respectively.

  • optimizer_extra_pruning_depth

  • system_versioning_insert_history

• MyRocks storage engine – You can use the MyRocks storage engine with RDS for MariaDB to optimize storage consumption of your write-intensive, high-performance web applications. For more information, see Supported storage engines for MariaDB on Amazon RDS and MyRocks.

• Amazon Identity and Access Management (IAM) DB authentication – You can use IAM DB authentication for better security and central management of connections to your MariaDB DB instances. For more information, see IAM database authentication for MariaDB, MySQL, and PostgreSQL.

• Upgrade options – You can now upgrade to RDS for MariaDB version 10.6 from any prior major release (10.3, 10.4, 10.5). You can also restore a snapshot of an existing MySQL 5.6 or 5.7 DB instance to a MariaDB 10.6 instance. For more information, see Upgrades of the MariaDB DB engine.

• Delayed replication – You can now set a configurable time period for which a read replica lags behind the source database. In a standard MariaDB replication configuration, there is minimal replication delay between the source and the replica. With delayed replication, you can set an intentional delay as a strategy for disaster recovery. For more information, see Configuring delayed replication with MariaDB.

• Oracle PL/SQL compatibility – By using RDS for MariaDB version 10.6, you can more easily migrate your legacy Oracle applications to Amazon RDS. For more information, see SQL_MODE=ORACLE.

• Atomic DDL – Your dynamic data language (DDL) statements can be relatively crash-safe with RDS for MariaDB version 10.6. CREATE TABLE, ALTER TABLE, RENAME TABLE, DROP TABLE, DROP DATABASE and related DDL statements are now atomic. Either the statement succeeds, or it's completely reversed. For more information, see Atomic DDL.

• Other enhancements – These enhancements include a JSON_TABLE function for transforming JSON data to relational format within SQL, and faster empty table data load with Innodb. They also include new sys_schema for analysis and troubleshooting, optimizer enhancement for ignoring unused indexes, and performance improvements. For more information, see JSON_TABLE.

• New default values for parameters – The following parameters have new default values for MariaDB version 10.6 DB instances:

  • The default value for the following parameters has changed from utf8 to utf8mb3:

    • character_set_client

    • character_set_connection

    • character_set_results

    • character_set_system

    Although the default values have changed for these parameters, there is no functional change. For more information, see Supported Character Sets and Collations in the MariaDB documentation.

  • The default value of the collation_connection parameter has changed from utf8_general_ci to utf8mb3_general_ci. Although the default value has changed for this parameter, there is no functional change.

  • The default value of the old_mode parameter has changed from unset to UTF8_IS_UTF8MB3. Although the default value has changed for this parameter, there is no functional change.

• InnoDB enhancements – MariaDB version 10.5 includes InnoDB enhancements. For more information, see InnoDB: Performance Improvements etc. in the MariaDB documentation.

• Performance schema updates – MariaDB version 10.5 includes performance schema updates. For more information, see Performance Schema Updates to Match MySQL 5.7 Instrumentation and Tables in the MariaDB documentation.

• One file in the InnoDB redo log – In versions of MariaDB before version 10.5, the value of the innodb_log_files_in_group parameter was set to 2. In MariaDB version 10.5, the value of this parameter is set to 1.

  If you are upgrading from a prior version to MariaDB version 10.5, and you don't modify the parameters, the innodb_log_file_size parameter value is unchanged. However, it applies to one log file instead of two. The result is that your upgraded MariaDB version 10.5 DB instance uses half of the redo log size that it was using before the upgrade. This change can have a noticeable performance impact. To address this issue, you can double the value of the innodb_log_file_size parameter. For information about modifying parameters, see Modifying parameters in a DB parameter group in Amazon RDS.

• SHOW SLAVE STATUS command not supported – In versions of MariaDB before version 10.5, the SHOW SLAVE STATUS command required the REPLICATION SLAVE privilege. In MariaDB version 10.5, the equivalent SHOW REPLICA STATUS command requires the REPLICATION REPLICA ADMIN privilege. This new privilege isn't granted to the RDS master user.

  Instead of using the SHOW REPLICA STATUS command, run the new mysql.rds_replica_status stored procedure to return similar information. For more information, see mysql.rds_replica_status.

• SHOW RELAYLOG EVENTS command not supported – In versions of MariaDB before version 10.5, the SHOW RELAYLOG EVENTS command required the REPLICATION SLAVE privilege. In MariaDB version 10.5, this command requires the REPLICATION REPLICA ADMIN privilege. This new privilege isn't granted to the RDS master user.

• New default values for parameters – The following parameters have new default values for MariaDB version 10.5 DB instances:

  • The default value of the max_connections parameter has changed to LEAST({DBInstanceClassMemory/25165760},12000). For information about the LEAST parameter function, see DB parameter functions.

  • The default value of the innodb_adaptive_hash_index parameter has changed to OFF (0).

  • The default value of the innodb_checksum_algorithm parameter has changed to full_crc32.

  • The default value of the innodb_log_file_size parameter has changed to 2 GB.

• User account security enhancements – Password expiration and account locking improvements

• Optimizer enhancements – Optimizer trace feature

• InnoDB enhancements – Instant DROP COLUMN support and instant VARCHAR extension for ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPACT

• New parameters – Including tcp_nodedelay, tls_version, and gtid_cleanup_batch_size