snippetsqlMajor
How to configure global charset on MySQL
Viewed 0 times
globalmysqlhowcharsetconfigure
Problem
I tried to change the charset for our MySQL via my.cnf and it failed. I added the charset settings listed below:
When I had these enabled, MySQL failed to start. How can I make sure everything defaults to utf8 - always?
Update
There are still a few places having issues in being UTF8
Unfortunately I still haven't come up with a soluton... any ideas?
Currently I'm using this configuration successfully:
But PHPMyAdmin is still showing some latin1
```
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
# ------------- MYSQL CHARSETS --------------
character_set_system = utf8
character_set_server = utf8
character_set_results = utf8
character_set_database = utf8
character_set_connection = utf8
character_set_client = utf8
collation_connection = utf8_general_ci
collation_database = utf8_general_ci
collation_server = utf8_general_ciWhen I had these enabled, MySQL failed to start. How can I make sure everything defaults to utf8 - always?
Update
There are still a few places having issues in being UTF8
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ciUnfortunately I still haven't come up with a soluton... any ideas?
Currently I'm using this configuration successfully:
[mysqld]
default-character-set = utf8
init-connect='SET NAMES utf8'
character-set-server = utf8
[mysql]
default-character-set = utf8But PHPMyAdmin is still showing some latin1
```
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_
Solution
You need to incorporate the following into your my.cnf file
You can find more information on the following manual pages
-
http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html
-
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_system
-
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_server
In response to Mark's answer, if you need to convert existing tables/databases to the correct character set you will need a suitable
Alternatively there's a great PHP script here which does it for you.
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-character-set = utf8
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8You can find more information on the following manual pages
-
http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html
-
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_system
-
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_server
In response to Mark's answer, if you need to convert existing tables/databases to the correct character set you will need a suitable
ALTER query. Something similar to the below:ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;Alternatively there's a great PHP script here which does it for you.
Code Snippets
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-character-set = utf8
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;Context
StackExchange Database Administrators Q#8288, answer score: 24
Revisions (0)
No revisions yet.