MariaDB

We use MariaDB instead of MySQL whenever possible. The primary reason is ERPNext supports MariaDB better. The downside of this is:

• Some apps, like FusionAuth, are known to have issues with MariaDB.
• If we need AWS Aurora, it only supports MySQL, not MariaDB. However, Aurora itself is not 100% compatible with MySQL especially newer features.

We use AWS RDS MariaDB instead of DigitalOcean’s managed database, because DigitalOcean requires providing a certificate chain which requires additional configuration and in some cases significant workarounds especially in Node.js based apps.

Parameter groups

Parameter group: lovia-mariadb-10-4

• character_set_server=utf8mb4
• collation_server=utf8mb4_unicode_ci
• max_connections=151
• max_connect_errors=1000 (needed because we’re using Kubernetes Digital so all backend apps are connected from same DigitalOcean IP address)

As MariaDB is used by FusionAuth, WordPress, Camunda, etc. and each can open multiple connections, the MariaDB instance must have plenty max_connections limit.

Converting database and tables to utf8mb4 with collation utf8mb4_unicode_ci

If you already had database and tables in latin1 or utf8 character set, here’s how to change them to utf8mb4 and utf8mb4_unicode_ci combo.

Note: FusionAuth uses utf8mb4 character set but with utf8mb4_bin collation.

Check current character set and collation for a database and specific table:

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "camunda";

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "camunda" AND T.table_name = "ACT_HI_JOB_LOG";

Change character set and collation of database:

ALTER DATABASE `camunda` CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";

Note that you’ll still need to change character set and collation for individual tables. To do this you can generate the needed ALTER TABLE DDL statements:

mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p --database=camunda -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE `" $1 "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1; "}'

You’ll get DDL statements like this that you can then execute:

SET foreign_key_checks = 0; ALTER TABLE `application_daily_active_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1;
SET foreign_key_checks = 0; ALTER TABLE `application_monthly_active_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1;
SET foreign_key_checks = 0; ALTER TABLE `application_registration_counts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1;

Pricing & Reserved Instances

WARNING: Partial/All Upfront payments cannot use AWS Promotional Credits!

Performance Insights & Performance Schema

By using at least t3.medium instance, we can use AWS RDS Performance Insights. As of Oct 2020, the new m6g/r6g instances are not yet supported by Performance Insights.

Amazon Lightsail VPC Peering

In order to have direct/private IP access from Amazon Lightsail to AWS RDS, you’ll need to enable VPC Peering.

To enable VPC peering in Lightsail, you need to have a default Amazon VPC. If you don’t have a default Amazon VPC, you can create yours again (or create one for the first time, if you have an older AWS account). To learn more, see Creating a Default VPC in the Amazon VPC documentation.

1. In the Lightsail console, choose Account on the top navigation menu.
2. Choose Account from the drop-down.
3. Choose the Advanced tab.
4. Choose Enable VPC peering under the AWS Region where you want to enable it.
   If the peering connection fails, try to enable VPC peering again. If it doesn’t work, contact AWS Customer Support.

VPC peering costs $0.01/GB, which is roughly the same as data transfer out costs to the Internet. However, VPC peering is faster (no round-trip to Internet gateway) and more secure.

Configuring MySQL in 1 GB VPS/Lightsail

Note: Tried with 512 MB first but eventually I gave up, due to CPU usage and RAM 🙁

Uninstall snapd: sudo apt purge snapd

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

• bind-address = 0.0.0.0 # required to be access from VPC
• Add:
• max_connections = 51
• innodb_buffer_pool_size = 8M
• innodb_log_buffer_size = 8M

sudo systemctl restart mysql

Reference:

• https://stackoverflow.com/a/40189452/122441
• https://www.mysqlcalculator.com/

Backup Snapshot and Restore using CLI

To create a database snapshot backup, SSH to an instance with security group access to MariaDB/MySQL database, then:

mysqldump -v -h hostname.ap-southeast-1.rds.amazonaws.com -u dbname_production -p --routines dbname_production > dbname_production.sql

Restore:

mysql -h hostname.ap-southeast-1.rds.amazonaws.com -u dbname_production -p < dbname_production.sql