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

To install on ubuntu +14.10

$ cd /etc/mysql/conf.d/
$ wget https://gist.githubusercontent.com/Xeoncross/2d0503cee10a6374c627f0faaed9ea3f/raw/755f53a68770a31b4b56c14e11e944e9facb10b5/utf8mb4.cnf

Based on https://gist.github.com/Xeoncross/5d85c3f5547ee04d5bd81113a2c8ec4d

Test with:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

If you have bad clients that set their own encoding then you can force the encoding to remain utf8 with:

[mysqld]
character-set-client-handshake = FALSE

Recommended that you enable innodb_large_prefix since it is deprecated in MySQL 5.7.7 and will be removed. If innodb_large_prefix is enabled (the default in MySQL 5.7.7), the index key prefix limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

In MySQL 5.7, the default innodb_file_format switched to Barracuda/DYNAMIC. Recommend you stick with barracuda (the new default) since it will be the only format in MySQL 8.0. You can check with:

SHOW VARIABLES LIKE "%innodb_file%";

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+

The innodb_default_row_format option, introduced in MySQL 5.7.9, defines the default row format for InnoDB tables (including user-created InnoDB temporary tables). The default setting is DYNAMIC. Other permitted values are COMPACT and REDUNDANT. The COMPRESSED row format, which is not supported for use in the system tablespace, cannot be defined as the default.

All together in a config

[mysqld]
innodb_file_format=barracuda
innodb_file_per_table=1
innodb_large_prefix=1
# Enable for space/memory saving for large tables
#innodb_default_row_format=COMPACT

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)