Correctly set MySQL to use utf8 on ubuntu

utf8mb4.cnf

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Increase performance
innodb_file_format=barracuda
innodb_file_format_max=barracuda
innodb_file_per_table=1
innodb_large_prefix=1

# Enable for space/memory saving for large tables
#innodb_default_row_format=COMPRESSED

# New default for tables MySQL 5.7+ / MariaDB 10.2+
#innodb_default_row_format=DYNAMIC

MariaDB ubuntu installation

You can see all all tables that need to be updated to use the new DYNAMIC format with the following query:

USE INFORMATION_SCHEMA; SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE ENGINE='innodb' AND ROW_FORMAT != 'DYNAMIC' AND ROW_FORMAT !='COMPRESSED';

Simple SSH user for read-only access to MySQL

When setting up a MariaDB on Digital Ocean to be accessed from another host.

1. Setup a DO firewall rule to block port 3306 access from any host accept the other droplets you want to connect.
2. Change bind-address to 0.0.0.0 to allow both local and remote access. (The firewall will protect you)
3. Setup a SSH tunnel on your mac/pc so you can login from your local machine to the box.
4. Setup a multi-host user (%).
   4.1. CREATE USER 'myapp'@'%' IDENTIFIED BY 'some_pass';
   4.2. verify: SELECT host FROM mysql.user WHERE User = 'myapp';
   4.3. Make sure to grant permissions as well.

Ubuntu 20.04+ (MariaDB 10.3+) seems to finally default to unicode support:

MariaDB [mysql]> SHOW VARIABLES LIKE 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8    |
+--------------------------+---------+
7 rows in set (0.001 sec)