How to set default character set and collation in MySQL or MariaDB

A consistent default character set plus collation prevents mixed-encoding schemas, mojibake, and surprising sort or comparison results when databases and tables are created without explicit settings.

Default encoding behavior in MySQL and MariaDB is controlled by server variables such as character_set_server and collation_server. New databases inherit these defaults unless a specific CHARACTER SET or COLLATE is supplied during CREATE DATABASE, and new tables inherit from their database unless overridden during CREATE TABLE.

Changing defaults affects new objects, not existing data, and an invalid collation can stop mysqld from starting after a restart. A safe baseline for most mixed environments is utf8mb4 with utf8mb4_unicode_ci, plus explicit conversions only when existing columns must change.

Steps to set the default character set and collation:

Record the current server default character set plus collation.

$ mysql --table -u root -p --execute "SHOW VARIABLES LIKE 'character_set_server'; SHOW VARIABLES LIKE 'collation_server';"
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+

Socket-authenticated installs typically support sudo mysql, and password-based installs typically use mysql --user root --password.

Confirm the intended utf8mb4 collation exists on the server.

$ mysql --table -u root -p --execute "SHOW COLLATION WHERE Charset='utf8mb4' AND Collation='utf8mb4_unicode_ci';"
+--------------------+---------+-----+---------+----------+---------+---------------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_unicode_ci | utf8mb4 | 224 |         | Yes      |       8 | PAD SPACE     |
+--------------------+---------+-----+---------+----------+---------+---------------+

MySQL 8 commonly defaults to utf8mb4_0900_ai_ci, MariaDB commonly provides utf8mb4_unicode_ci plus utf8mb4_general_ci, and a shared collation name avoids cross-version surprises.

Create a MySQL option file snippet that sets server defaults plus client defaults.
```
$ sudo tee /etc/mysql/conf.d/charset.cnf >/dev/null <<'EOF'
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
 
[client]
default-character-set = utf8mb4
 
[mysql]
default-character-set = utf8mb4
EOF
```
On Debian or Ubuntu, includes commonly live under /etc/mysql/conf.d plus /etc/mysql/mysql.conf.d or /etc/mysql/mariadb.conf.d, and RHEL-family installs commonly use /etc/my.cnf.d.

Search the option files for other charset directives that could override the new values.

$ sudo grep --recursive --line-number --ignore-case --extended-regexp 'character-set-server|collation-server' /etc/mysql
/etc/mysql/conf.d/charset.cnf:2:character-set-server = utf8mb4
/etc/mysql/conf.d/charset.cnf:3:collation-server = utf8mb4_unicode_ci

A later-loaded option file can override earlier values, so duplicate directives should be removed or reconciled.

Restart the database service to load the updated option files.
```
$ sudo docker restart sg-mysql
sg-mysql
```
MariaDB commonly uses sudo systemctl restart mariadb when the unit name is mariadb.service.

A failed restart can take the database offline, and an invalid collation name can prevent startup until the option file is fixed.

Verify the service is running after the restart.

$ docker ps --filter name=sg-mysql --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'
NAMES     IMAGE       STATUS
sg-mysql  mysql:8.0   Up 18 seconds

Re-check character_set_server plus collation_server to confirm the new defaults are active.

$ mysql --table -u root -p --execute "SHOW VARIABLES LIKE 'character_set_server'; SHOW VARIABLES LIKE 'collation_server';"
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_unicode_ci |
+------------------+--------------------+

Create a test database that inherits the server defaults.

$ mysql -u root -p --execute "CREATE DATABASE IF NOT EXISTS charset_test;"

Verify the database default character set plus collation via information_schema.

$ mysql --table -u root -p --execute "SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='charset_test';"
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_unicode_ci     |
+----------------------------+------------------------+

Change the default character set plus collation for an existing database when new tables must use the new defaults.
```
$ mysql -u root -p --execute "ALTER DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
```
This updates the database defaults used for new tables, and existing tables plus columns keep their current encodings until explicitly altered.
Convert an existing table to utf8mb4 when existing columns must change.
```
$ mysql -u root -p --execute "ALTER TABLE appdb.users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
```
Table conversion can lock or rebuild tables, can take significant time, and can trigger index-length errors on older schemas because utf8mb4 uses up to 4 bytes per character.

Confirm column-level collations after a conversion using information_schema.

$ mysql --table -u root -p --execute "SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='appdb' AND TABLE_NAME='users' ORDER BY ORDINAL_POSITION;"
+-------------+--------------------+--------------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+-------------+--------------------+--------------------+
| id          | NULL               | NULL               |
| username    | utf8mb4            | utf8mb4_unicode_ci |
| email       | utf8mb4            | utf8mb4_unicode_ci |
| created_at  | NULL               | NULL               |
+-------------+--------------------+--------------------+