HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How do I set MySQL's "character_set_server" and "collation_server" variables when creating a database?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
character_set_servercollation_servercreatingdatabasemysqlvariableshowandwhenset

Problem

I'm using MySQL 5.5.37. How do I set the default "" and "" variables of my database when initially creating my db? I'm logged in as the root user but my command is not cutting it, as you can see below

mysql> create database if not exists my_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql> use my_db
Database changed
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | utf8              |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | utf8_bin          |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+


Notice that "character_set_server" is set to "latin1" and "collation_server" is set to "latin1_swedish_ci" despite what I specified in my intiail command. How do I change my initial command so that these values are utf8 and utf8_bin respectively?

Solution

Stick these in the [mysqld] section of your MySQL config file:

skip-character-set-client-handshake
collation-server=utf8_unicode_ci
character-set-server=utf8


The documentation for this is here.

Code Snippets

skip-character-set-client-handshake
collation-server=utf8_unicode_ci
character-set-server=utf8

Context

StackExchange Database Administrators Q#166484, answer score: 4

Revisions (0)

No revisions yet.