Skip to content

Instantly share code, notes, and snippets.

@xeoncross
Last active March 4, 2021 18:36
Show Gist options
  • Save xeoncross/2d0503cee10a6374c627f0faaed9ea3f to your computer and use it in GitHub Desktop.
Save xeoncross/2d0503cee10a6374c627f0faaed9ea3f to your computer and use it in GitHub Desktop.
Correctly set MySQL to use utf8 on ubuntu
[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
@xeoncross
Copy link
Author

xeoncross commented Dec 21, 2016

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

@xeoncross
Copy link
Author

@xeoncross
Copy link
Author

xeoncross commented Oct 31, 2017

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';

@xeoncross
Copy link
Author

@xeoncross
Copy link
Author

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.

@xeoncross
Copy link
Author

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment