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

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